MetaShare Setup and Demo

FlexWiki Setup

Download the FlexWikiCore-1.8.0.1739-web-full-Release.zip package from the FlexWiki SourceForge project website. Extract the contents into a folder called FlexWiki under your inetpub\wwwroot folder. After that, you need to convert the FlexWiki folder into a virtual directory. To do this, go into IIS Manager and right click on the FlexWiki folder

Next, convert the directory to a Virtual Directory by clicking the Create button.

After this is done, FlexWiki should be working now. Point your browser at http://localhost/FlexWiki and you should see the FlexWiki home page:

For more help troubleshooting, go to the FlexWiki homepage found at http://www.flexwiki.com.

AdventureWorks Database

The Microsoft sample database, AdventureWorks is required as it is used as the data source for the data warehouse we will be building. These samples can be found here. You will need to download the msi called AdventureWorksDBCI.msi. This is the case insensitive collation version. You can use the case sensitive collation if you like.

Creating the MDWT AdventureWorks Database

The next step is to create the AdventureWorks data warehouse as designed and demonstrated in The Microsoft Data Warehouse Toolkit written by Warren Thornthwaite and Joy Mundy. In the book, Warren and Joy describe making use of this Excel workbook as a template for metadata that should be gathered during the requirements phase of a data warehouse implementation. After the workbook is filled out, a Visual Basic script runs through the workbook creating database scripts to create the tables and columns that are described. This also includes metadata about tables and columns such as a short description of a table or column, the source column of a dimension attribute, and whether a table or column is part of the warehouse ETL audit system.

First, download the “DW Toolkit Excel Data Model 3.01.zip” file from the Tools and Utilities section of the supporting site to the book – www.msftdwtoolkit.com. Unzip the file and open the file ”MDWToolkit Datamodel 3.01 AW version.xls.” After opening, click the button “Generate SQL Script.” This will open a new spreadsheet with a SQL script for creating the database described within the workbook. Don’t forget to turn on macros or the Excel can’t create the scripts. After creating the scripts, paste them into SQL Management Studio, uncomment the CREATE DATABASE statement, and play the script into your database server.

When playing the SQL scripts, I encountered and error from worksheet version 3.01. It says that DimDate doesn’t have an identity column, but the script instructs to SET IDENTITY INSERT ON. This won’t matter in the end.

Downloading the SSIS Project

Next, we need to download the SSIS project that contains the packages. This project can be found at http://www.msftdwtoolkit.com under Tools and Utilities under Chapter Six. Here is the direct link: http://www.msftdwtoolkit.com/ToolsandUtilities/Ch06/MDWT_AdventureWorks_SSIS.zip. If you are interested in running these packages and populating the data warehouse, then there are a few extra steps you must complete. These are not necessary for this demo. Extract these files into C:\MDWT_Projects. This will keep all of the file references intact for the chaining of packages calling sub-packages. After the solution is extracted, run BIDS and open the solution. We will need to run two packages, first the package called “RUN FIRST AND ONLY ONCE.dtsx”. The second packages called “RUN THIS TO LOAD ALL.dtsx” calls all of the sub packages and loads data out of the AdventureWorks database and into the Kimball data warehouse MDWT_AdventureWorks_DW database.

Importing the SSIS Packages

Warren and Joy also wrote SSIS packages to fill the data warehouse they designed and those need to be downloaded and imported to the Integration Services server. First, create a folder in the MSDB SSIS package storage area:

Name the folder MDWT_AdventureWorks:

Now we need to import all of the packages into this new folder. This is done by right clicking on the folder we just created and choosing Import Package. Next you will see a dialog that asks what package you would like to import. In the top drop down box, choose File System. In the Package Path box, browse to your SSIS project and choose the first package you would like to import. After that, click your mouse in the Package Name box and SQL Management Studio will fill in the name for you.

After all of this is filled in, click OK and the package should appear in the MDWT_AdventureWorks folder. Repeat this process until all of the packages are imported. To save time, only the packages that actually load data into the dimensions and fact tables need to be imports. These packages start with the word Dim when they are a dimension load and the word Fact when they are a fact table load.

Generating Collaborative Metadata with MetaShare

Download the latest version of the MetaShare.Generate package from SourceForge. It can be found at http://sourceforge.net/projects/metashare/. After unzipping the package, you should find three files. MetaShare.Core.dll, MetaShare.Generate.exe, and Readme.txt. The Readme lists the command line arguments for MetaShare.Generate but you can also display them by running the exe. To generate the files, here is a sample command line string that will use the localhost as the database and SSIS server, use the default directory for wiki page output and use the folder name for the SSIS package store that we created earlier:

MetaShare.Generate /outputPath:C:\Inetpub\wwwroot\FlexWiki\WikiBases\MyWiki /databaseServerName:localhost databaseName:MDWT_AdventureWorksDW /areaName:MDWT_Adventure_Works_Demo /ssisServerName:localhost ssisPackagePath:/MDWT_AdventureWorks /outputPlatform:FlexWiki

Viewing the Wiki Output

To view the output, go to http://localhost/FlexWiki/ and you should see the FlexWiki home page again. Next, click on the title of the page, “Home Page” and the screen should look like this:

Next, type in MDWT_AdventureWorksDW. This is going to take you to the wiki page that was generated by MetaShare.Generate for the database and its metadata.

Now hit enter. The screen should now look like this:

You can see here that this is a page representing the metadata for this database which includes all of the tables in the database. There are also descriptions of each table. This is from the Description field in the Kimball Method Excel Spreadsheet that was filled out during the requirements phase.

If we click on the DimCustomer table link, you should see this:

Here we are presented with a list of the columns in the Customer dimension with the accompanying descriptions entered into the Kimball Requirements Spreadsheet as well as the data type which was pulled directly out of the SQL Server Catalog.

If we click on the CustomerKey link (this is the key that is used on the fact table) the screen should look like this:

Now we are presented with the full complement of metadata gather in the Kimball Spreadsheet about this dimension attribute.

If you will click on the link labeled “here” under the bulleted item in the Also Found In section, the screen should look like this:

Here you can see the MetaShare went and found that the attribute CustomerKey is found in two places, FactOrders as well as DimCustomer. This gives us the insight that and attribute with this excact same name is being used in more than one place.

If you click “Back” on your browser and then scroll down to the bottom of the page you’ll see the bottom of the CustomerKey wiki page again:

You’ll notice the list that MetaShare has generated of the packages that source the data warehouse attribute that we are currently viewing. MetaShare has found that the package called DimCustomer on the SSIS server loads into this attribute. If you click on this link the screen should look like this:

Here you can see that MetaShare has scanned the DimCustomer package and has listed the steps that it found in the package. Notice that the Description attribute of those SSIS packages is coming through here. Now more than ever, filling in this attribute is important. It can describe in more detail exactly what is going on inside of an SSIS task.

One quick note - if you are using anonymous access to flexwiki, you might have to give the local ASPNET account full access to edit the wiki documents or you won't be able to add user content at the bottom of the page.

MetaShare.Web

I’ll explain MetaShare.Web here and how to update metadata directly in the database.

Roadmap

MetaShare is by no means complete. There are opportunities to extract this same kind of metadata out of an Analysis Serves project as well. There are certainly refinements that can be made on the product to make it easier to use. I’m looking for all of that from anyone who is willing to help.