While we typically focus on vertical business industry BI solutions here, let's talk today about product information and some examples of how to build and maintain an effective product hierarchy in your Microsoft BI solution. As I wanted to keep this blog posting title short, I should note that I am not going to go into the details of a full PIM system solution. Instead, I am going to speak of modeling product data in your BI hierarchies for reporting purposes.

Let's start with one of the best sources for best practices in data warehouses and ETL for SQL Server: Adventure Works. The datawarehouse in the Adventure Works sample database has dimensions for products, product subcategory and product subcategory (pictured below). I also highly recommend downloading and looking through Project Real at http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx. While Adventure Works (AW) has good product information in it, Project Real has good real-world customer data.

 

When you use such a star schema architecture with SSAS 2005, it is as simple as using the cube wizard to derive the proper dimensions and facts with natural hierarchies. As a matter of fact, the wizard will decipher and compile these for you, allowing the developer to override the default settings if needed.

In the case of Adventure Works, what you end up with is a hierarchy of:
PRODUCT
  \/
PRODUCT CATEGORY
  \/
PRODUCT SUBCATEGORY

Now you can use this for drill down reports, scorecards and other purposes, including a PIM solution where you can build a central location of your company's product information for tracking, cataloging and monitoring.

Another tool that you can use to build your product catalog is PerformancePoint Server. The above example is developed using Business Intelligence Development Studio in SQL Server. This is an example of a hierarchy is PPS using the member set capability in the Business Modeler: