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

Browsing OLAP Cube Using Visual Studio

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

Browsing OLAP Cube Using SSMS


  del.icio.us it! digg it! reddit! technorati! yahoo!

About these ads

5 responses to “SQL Server 2008 – How To Build and Deploy AdventureWorks OLAP Cubes

  1. I ended up with the following error messages after struggling with the server issue. Turns out the program mis loaded originally and a reinstall fixed the server. Do you have any suggestions for this problem though?:-
    ______________________________
    Error 1 OLE DB error: OLE DB or ODBC error: Invalid object name ‘FactResellerSales’.; 42S02. 0 0
    Error 2 Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘Fact Sales Summary’, Name of ‘Sales Channel’ was being processed. 0 0
    Error 3 Errors in the OLAP storage engine: An error occurred while the ‘Sales Channel’ attribute of the ‘Sales Channel’ dimension from the ‘Adventure Works DW Standard Edition’ database was being processed. 0 0
    _______________________________

    thanks

  2. nice, got mine working in order in no time

  3. Nice work,
    got this up and running without problems.
    Thanks,

  4. Great help – thanks!

  5. Thanks, Stuart! Well written! Only thing that tripped me up was permissions on the deployment and connection testing. I normally don’t have sysadmin role on our SQL servers. Just happened to have an account on the SQL server I’m playing in that does have sysadm role and ended up using it for the deployment of the cubes. What would be the minimal server role required to deploy these?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s