SQL Server 2008 – How To Build and Deploy AdventureWorks OLAP Cubes

I’m trying to setup a simple OLAP demo, using MOSS 2007 Excel Services to display pivot tables and other spreadsheet data, connecting to SQL Server 2008 Analysis Services OLAP cubes.  In this case, I want to get it working with the AdventureWorksDW sample database.  I’m documenting the steps here to help others, and so that I can do it again without the pain!

To complete these steps, you will need SQL Server 2008 installed with the database engine, Analysis Services, FILESTREAM and Full Text Search services enabled.  You’ll also need the Visual Studio Business Intelligence project templates installed to build and deploy your cube using SQL Server Business Intelligence Development Studio (BIDS).

1. Install the AdventureWorksDW Sample Database

First, you’ll need to install the AdventureWorkdsDW sample database from CodePlex.  To install it, you’ll need FILESTREAM and Full Text Search enabled.  For more detailed instructions, see my blog post, SQL Server 2008 – Installing the AdventureWorks Sample Databases.

2. Install and Deploy the AdventureWorks Cubes

After you’ve installed the AdventureWorks DW database, you need to setup a SQL Server Business Intelligence Developer Studio (BIDS) project to create and deploy a cube.  You can find the sample BIDS project located at C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008 Analysis Services Project.  There are 2 projects there, depending on your version of SQL Server you’re using.

1. Open the sample Adventure Works.sln BI solution using Visual Studio
2. Change the data connection options for the source database
   1. Expand the solution folders in the Solution Explorer and double-click the Adventure Works.ds data source to open the properties.  This is a data connection to the AdventureWorksDW database, which will serve as the source database for the cubes we’re creating
   2. Edit the connection string and set the connection properties.  I just changed localhost to server name of my database server, using the default Windows Authentication mode.
   3. Click the Test Connection button to test your connection
   4. Exit the configuration dialog to save your changes
3. Change the data connection options for the Analysis Services database
   1. Select Project > Properties from the Visual Studio menu to open the project options dialog
   2. Select the Deployment options
   3. Change the server to the database server / instance here Analysis Services is installed.  This is where your cube will be created.
   4. By default, the database name is Adventure Works DW 2008.  This is the Analysis Services database that will be created to host the cubes.
   5. Click OK to exit the properties dialog and save your changes
4. Build the project
   1. Select Build > Build Solution from the Visual Studio menu to build the solution and check for errors.
   2. Select Build > Deploy Solution from the Visual Studio menu to deploy the project.  This will create the cubes and other Bi database objects in the database. 

3. Browse the AdventureWorks Cubes

After your cube has been deployed, you can browse your cube data in the Visual Studio BIDS interface, or in SQL Server Management Studio:

Browse cube data in Visual Studio

1. Select the AdventureWorks cube in the cubes folder of your BIDS project
2. Right-click the cube and select Browse from the context menu
3. Drag/drop measure and dimension data to play with your cube data

Browse cube data in SQL Server Management Studio

1. Open SQL Server Management Studio (Start > Programs > MIcrosoft SQL Server 2008 > SQL Server Management Studio)
2. Connect to the Analysis Services database
   1. Select File > Connect Object Explorer from the SQL Server Management Studio menu
   2. Select Analysis Services in the Server Type dropdown
   3. Enter the server name and authentication options and click Connect
   4. Expand the Analysis Services server in the object explorer and browse to the Adventure Works DW 2008 > Cubes
   5. Select the AdventureWorks cube, right-click, and select Browse from the context menu
   6. Drag/drop measure and dimension data to play with your cube data, as show below