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

 MOSS 2007 AdventureWorksDW 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!

AdventureWorksDW BDC Web Part Demo Page

  del.icio.us it!

digg it!

reddit!

technorati!

yahoo!

Advertisements

4 responses to “SQL Server 2008 – AdventureWorksDW BDC Demo

  1. Pingback: Office et SharePoint pour et par les administrateurs : SQL Server 2008 & AdventureWorksDW BDC Demo

  2. Hi,

    nice article. need some support. i am working with 2005. i have added all webpart and associate the other as given but i am not getting result as mentioned in ur layout for example product categories i wired webpart but no oprion box and categories are listed same case with all webpart pls tell me more how do i achieved this demo.
    thx

  3. Thanks for the article, I needed it. There’s one more step, though.
    Create the web part connections:
    Connect Product categories to send it’s information to Product Subcategories. Connect Subcategories to send information to Products. Lastly, connect Products to send info to Product details.

  4. Nice article, I need it. I wonder whether I can create new database instead of AdventureWorksDW?

    1.I created new database in SQL 2005 (not a same server with moss) and table with 5 columns.

    2.I created ADF file imported to moss.
    3.Added Business Data List web part wired to the employee table

    No luck I get error “Unable to connect to database Instance in business data list web part”

    Any suggestion will be helpful.

    Thanks

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