Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

PowerPivot Schema Flexibility

Posted by thomasivarssonmalmo on January 15, 2011

In SSAS cubes we mostly use the starschema model with a central fact table and dimension tables as navigation paths down to the measures. That model will work in many scenarios and return valuable insights into business processes. In this model we do not connect fact tables to other fact tables directly but the relation appears with related dimensions.

With the arrival of PowerPivot much talk have been focused around DAX, the expression language, or formula language, for PowerPivot. I believe that this is only part of the story.

PowerPivot have a more flexible model where you can do this but also relate fact tables with different granularities directly to each other. This will give you added flexibility in a Pivot Table.

This is a recreation of a report scenario that I have tried on real production data but I will use one of the other Adventure Works demo databases that is more transaction oriented. I have created a product table(product attributes) and a date table(single dates, Year and YearMonth(200801) attributes. Fact tables are both SalesOrderHeader and SalesOrderDetail.

Result different granularity

You will recognize the dimension tables as the slicers but I have used some TSQL code to build this in this more detailed version of the Adventure Works processes. The two fact tables are the OrderHeader table and the Order detail table. Both tables include the OrderNumber column that I have dragged to the values area and changed the formula from SUM to COUNT.

The count function

The relations tab in PowerPivot is where you create the more flexible model.

Relations between the fact tables

In this model Product is related to the SalesOrderDetail(ProductId) column and  Date to the SalesOrderHeader table(OrderDate). SalesOrderDetail is related the SalesOrderHeader table with the SalesOrderId column.

Finally, this is the behaviour in the Pivot Table with this setup.

Click on Categories

Product categories and year level of the order date column are used as slicers above. If you click on a product category member in the slicer only the count measure of the SalesOrderDetail table will change(CountSalesOrderDetailsRecords).

Click On Year

If you click on single years both the CountSalesOrderHeader measure and the CountSalesOrderDetailsRecords will change, since the top fact table filters the lower fact table.

Edit: I would like to add that PowerPivot models are optimized as star schemas without a relation between the fact tables.  My approach above will face performance challanges with too much data.


4 Responses to “PowerPivot Schema Flexibility”

  1. Thanks Thomas for a good post. Dont you have an RSS Feed??

  2. […] We are no longer constrained to dimensions and facts. As Thomas Ivarsson showed us in his PowerPivot Schema Flexibility post, we can avoid certain data manipulations when building a model in PowerPivot. The same holds […]

  3. wordpress uses

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: