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

del.icio.us it!

digg it!

reddit!

technorati!

yahoo!

SQL Server 2008 – AdventureWorksDW BDC Demo

After installing MOSS 2007 on Visual Studio 2008, I wanted to see if I could get the BDC working against the new version of the AdventureWorksDW database for SQL Server 2008. 

I used the AdventureWorks2005.xml application definition file that ships with the MOSS 2007 SDK to create a connection to the SQL Server 2008 AdventureWorksDW database.  BDC’s can be very temperamental at times, so I was expecting some hiccups along the way.  As it turns out it, it was pretty painless and worked without a hitch. 

After I imported the BDC application definition, I setup a little BDC demo to test things out. 

To try this out, you’ll need MOSS 2007 and SQL Server 2008 installed and configured.  MOSS 2007 will run seamlessly on SQL Server 2008 as long as MOSS 2007 SP1 is installed on your WFE servers.

1. Install the AdventureWorks Sample DW BI Database

First, you need to install the AdventureWorks Sample DW BI database.  You can download it from CodePlex. 

I downloaded the full version for 2008 with the msi installer, which is nice because it installs the database and attaches it for you. Running the installation is pretty straight-forward, but if you have any trouble, you can refer to an earlier post on my blog here.

2. Install the Office SharePoint Server 2007 SDK (1.3)

Download and install the Office SharePoint Server 2007 SDK.  I’m working against the 1.3 version of the SDK.  The SDK contains a BDC application definition that were created for the SQL Server 2005 AdventureWorksDW  database.

3. Modify the AdventureWords2005DW.xml file

Before we can import the file, we need to make some minor modifications:

1. Open the AdventureWorks2005DW.xml file using your favorite xml editor.  The file is, located in the C:\Program Files\2007 Office System Developer Resources\Samples\Business Data Catalog\AdventureWorks Samples folder.
2. Modify the RdbConnection Data Source property under the LOBSystemInstance and to reflect your server name
3. Modify the RdbConnection Initial Catalog property to reflect your database name (AdventureWorksDW2008 by default)
4. Do a Save As to rename the file and save your changes

4. Import the BDC Application Definition

We’re now ready to import the application definition file to the MOSS 2007 to create a Business Data Connector (BDC).

1. Open the MOSS 2007 Central Administration web site
2. Click your SSP link under Shared Services Administration to open the SSP Administration site
3. Click the Import application definition link under the Business Data Catalog heading
4. Browse to the modified version of the application definition file (I called mine AdventureWorks2008DW.xml
5. Leave the default settings of Model with Localized Names and Properties checked
6. The installation should import the application file.  When it’s complete, click the OK button to view the Application information for the BDC

 

5. Setup a BDC Demo Page

To test the BDC and setup a nice demo, add 4 web parts onto a page on your site.

1. Add a Business Data List web part wired to Product Categories
2. Add a Business Data Related List web part wired to the Product Subcategory list on the ProductCategoryToProductSubcategory relationship defined in the BDC
3. Add a Business Data Related List web part wired to the Product list, filtered by the ProductSubcategoryToProduct relationship
4. Business Data Detail web part, with a connection to the Product List web part
5. Click the Edit View button on the list web parts and modify the views to hide the ID columns and turn off filtering for the Product Category list
6. Modify the titles on each of the the web parts
7. Try the web parts out and drag them around the page till you get them the way you like them

Now you’ve got a nice BDC demo that looks something like this the screen shot below.  Nice!

del.icio.us it!

digg it!

reddit!

technorati!

yahoo!