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!