Thursday, April 5, 2012

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 true with SQL Server 2012.


Prequisites

Visual Studio 2010 disc or image
Installed Visual Studio 2010
SQL Server 2012 disc or image
Service Pack 1 for VS2010 due to this

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.

Ending and 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.

No comments:

Post a Comment