Setup SQL Server 2012 and Adventure Works cube with Visual Studio 2010

-

This tutorial will show you how to deploy a cube onto SQL Server 2012 using VS2010 and might help you avoid some of the problems I've encountered. It is complementary with Multidimensional Modeling (Adventure Works Tutorial) and uses its Adventure Works db and cube project.

While this states that it is not possible to deploy a cube using Visual Studio it is no longer the case with SQL Server 2012.

Prequisites

Installing SQL Server 2012 or adding required features

Note: Running VS2010 during the installation might cause errors so close it.

Run SQL Server Setup by selecting New SQL Server stand-alone installation or add features to an existing installation from Installation tab of the SQL Server Installation Center. Move through the process up to Installation Type. If you already have an installed SQL Server you would like to use, pick Add features options and select your server instance, like below:

Pick the following features:

Notably SQL Server Data Tools contain Buisness Intelligence Developement Studio (BIDS) for Visual Studio 2010 that will allow you to deploy a cube.

Some of the features might be redundant (I'm particularly uncertain about the Integration Services) but are definitely sufficient for deploying a cube and running some MDX queries.

Complete the installation process. You will be probably required to insert or mount the VS2010 disc or image.

Service Pack problems

If you get the following message installing the SP1 from the given URL will fix the issue (Service Pack 1 for VS2010 due to this)

Rule "Prior Visual Studio 2010 instances requiring update." failed. This computer has an installation of Visual Studio 2010 that requires a Service Pack 1 update that is needed for a successful installation of SQL Server based on your feature selection. To continue, install the required Visual Studio 2010 Service Pack 1 from SQL Server media or from http://go.microsoft.com/fwlink/?LinkID=220649.

Adding the AdventureWorks database

Download the AdventureWorksDW2012 Data File from Adventure Works for SQL Server 2012 project. While you're at it and if you haven't already, download Analysis Services Tutorial SQL Server 2012 we will be using for the cube deployment.

Connect to your DB server (File -> Connect Object Explorer..., Server type: Database Engine) and load the .mdf file using SQL Server Management Studio and Attach... option:

You might need to remove the log file that causes errors.

Deploying AdventureWorks Cube

Using VS2010 run your the cube solution. I'll be using the one from Analysis Services Tutorial SQL Server 2012 Lesson 2 Complete.

Go View Designer:

In the General tab Edit the Connection Sting. Select your server and the AdventureWorksDW database and Test the connection. Make a note of your server. Will be using that later.

In  the Impersonation Data tab, check if your settings are correct. Incorrect settings will usually cause this error:

Error 1 The datasource , 'AdventureWorksDW', contains an ImpersonationMode that that is not supported for processing operations.  0 0 
Error 2 Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Adventure Works DW', Name of 'AdventureWorksDW'.  0 0 

Go to properties of Analysis Services Tutorial project. In Deployment tab input the server name from View Desinger -> General.

Deploy the project using Run with Deployment configuration.The deployment should be successful.

Connecting to Cube and running MDX queries

Go to SQL Server Management Studio. Connect to your Analysis server (File -> Connect Object Explorer..., Server type: Analysis Services)

Open the query editor for your cube using New Query:

You might want to Browse your cube (Cubes->[Analysis Services Tutorial]->Browse).

If there are no metadata for your cube shut down VS2010 and retry. If it doesn't help then there might have been a problem with your cube deployment. Also check if the selected cube is the right one (just above metadata.

You also could use File -> New -> Analysis Services MDX Query.

This might solve your problems if you don't see the cube.

References

Stuart's Tech Punch post has helped with writing this tutorial. It walks you through setting up AdventureWorks in SQL Server 2008.

Please let me know if this post has helped you or if you find anything to improve.

Comments

Post a Comment

Popular posts from this blog

Installing FANN on Java project and NativeLibrary.loadLibrary problems with 32bit/64bit

-
If you try to install FANN  and get it working with a Java project you might run into a few problems. The whole process is explained on the fannj (the Java binding) project's wiki but not in great detail. Installing Fannj Get fannj Store it somewhere and add the jar to your project. Get FANN Extract it somewhere. Set up the DLL by either: copying the fannfloat.dll to System32, or adding the following line before you try to use FANN:  System.setProperty("jna.library.path", [fannfloat.dll dir path]); Solutions to common issues No Java Native "Exception in thread "main" java.lang.NoClassDefFoundError: com/sun/jna/Platform" Solution : Download the JNA and add the jar to your project. Can' find the FANN library Exception in thread "main" java.lang.UnsatisfiedLinkError: Unable to load library 'fannfloat': The specified module could not be found. Solution : Add debugging code (or better yet: use a debugger) after y...
Read more

MongoDB and distinct values in arrays

-
This post is a mini-tutorial on how to deal with array fields in MongoDB, specifically arrays of items that may or may not be duplicated. Let's create a collection of unicorns, smilarly to  The Little MongoDB Book examples: Find should now return something like this, the _ids will vary: Adding a value into an array We've found out that our unicorn Scott also loves playing games, so lets push a new value into our document's loves array: Using push , we could insert into our the same value multiple times. Pushing a value if its unique We have found out that all our unicorns love movies and want to update the whole collection. Unfortunately Scott already has got movies in his loves field. As shown here , we can use addToSet , which won't add a duplicated value to an array: The third parameter of update is upsert (false by default). The fourth is multi which makes the query update every document that matches the criteria (false by default meaning th...
Read more