Monday, June 3, 2013

PowerPivot CAN do Compound Key Relationships

Recently ran into a situation where PowerPivot was automatically discarded as a tool because it doesn't natively handle compound key relationships.  It can do this easily and will show you how to achieve this. 

If you are not familiar with PowerPivot then Rob Collie's post is an excellent description:

Point No. 6 is the main reason why I've seen folks use it. 

"A Way to handle Massive Volumes of Data in Excel

Normal Excel caps out at 1 Million rows of data per sheet.  PowerPivot goes well beyond that and for most people, you will find it actually HAS no practical limit.
For instance, here’s a workbook I like to demo that has 161 Million rows in a single sheet..."

PowerPivot and SSAS Tabular do not permit compound key relationships natively in the data model. You can achieve this though quite simply and all that is needed is to create a calculated column that concatenates the columns that comprise the compound key. 

Here's how you can do it with illustrations:

Let's look at some sample data from two tables (Sales and Store Info) where I have StoreNo and State as my two fields (compound key) that uniquely identifies a store's sales for the day (label on sample data should be day).

What you can do is create a new column and concatenate the two fields StoreNo and State as below.  Two ways you can concatenate fields in PowerPivot is by using CONCATENATE() or simply using &.

CONCATENATE() limits you to two fields but & can do more.

Simply do the same thing in your other PowerPivot Table as below and I've renamed the column to StoreNoState.

Now you can create a relationship between the tables in the Diagram View as below:

Important to note the way you create your relationships in PowerPivot or SSAS Tabular is important and a simple rule of thumb is to always go from a Measure table (Sales) to a Non Measure table (Store Info) or Fact tables to dimension tables.

That's it!  Now you can move forward as business as usual and can slice Sales by Store Size and State as the two tables are now related:

Hope this helps those out with Compound Key struggles in PowerPivot!  This same process can be done with tables in SSAS Tabular as well.

No comments:

Post a Comment