Benny Austin

PowerPivot: DAX Expression for Type 2 SCD Lookup

I’ve been playing with PowerPivot lately and must admit that PowerPivot has immense potential towards self-service BI. The greatest strength of PowerPivot is the ability to create relationship with different sources of data and piggyback on this relationship for various kinds of analysis. PowerPivot relationship requires one-to-many relationship between the parent and child tables. But what happens if the relationship between parent and child tables is many-to-many instead? For e.g. you might want to establish a relationship between a fact table that has a Business Key to the dimension table and that dimension table maintains history. In this post I explain how to establish a relationship between two tables that do not exhibit one-to-many relationship on their Business Key using a technique that is similar to an ETL Type 2 SCD lookup using Data Analysis Expressions (DAX). This being my first post on PowerPivot, I’m expecting some rough edges. Well, I ‘m having a crack at it anyway. 

For this example, my fact table is [SalesOrderDetail] from AdventureWorks. I would like to establish a relationship between [SalesOrderDetail] and [DimProduct] from the AdventureWorksDW. [DimProduct] table is a type 2 SCD dimension that maintains historical changes to Products. This means that for each Product in [SalesOrderDetail] there could be many date dependant Product records in [DimProduct]. The end game is to associate the fact record with the surrogate key of the [DimProduct] that is relevant to the Product applicable at the [Order Date] 

1. The first step is to get the Business Key of the Product. In this example the Business Key is not readily available in the [SalesOrderDetail] table. So it is derived by defining a relationship between [SalesOrderDetail] and [Product] table in AdventureWorks as shown below (Screen Capture 1). Take note that this relationship between the AdventureWorks tables is one–to-many. 

Screen Capture 1 - One-to-Many Relationship

Screen Capture 1 - One-to-Many Relationship

2. With the relationship defined, the Product Business Key can be simply derived using the DAX Expression – RELATED(Product[ProductNumber]) (Screen Capture 2) 

Screen Capture 2 - Product Business Key Using RELATED()

Screen Capture 2 - Product Business Key Using RELATED()

3. The Product Business Key gives an entry-point to query the [DimProduct] table from AdventureWorks DW. Using the DAX expression given below, every fact record is now linked to its Product Surrogate Key using Product BusinessKey and Order Date thereby paving way to define a relationship between the [SalesOrderDetail] from AdventureWorks and [DimProduct] from the AdventureWorksDW 

=MinX(Filter(DimProduct, DimProduct[ProductAlternateKey]=[ProductBusinessKey] && ([OrderDate] >= DimProduct[StartDate] && [OrderDate] <= DimProduct[EndDate])),DimProduct[ProductKey])
Screen Capture 3 - Type 2 SCD lookup Using DAX  Expression

Screen Capture 3 - Type 2 SCD lookup Using DAX Expression

The Filter() function in the above DAX expression, pretty much does a Type 2 SCD lookup on the [DimProduct] table using the Product Business Key and the Order Dates and returns a single row of [DimProduct] as a table. The MinX() function works on the table expression returned by the Filter() function to return the Product Surrogate Key. 

4. The Product Surrogate Key now has a many-to-one relationship with the [DimProduct] table in AdventureWorksDW and the relationship can now be defined without any ambiguity (Screen Capture 4). 

Screen Capture 4 -  Relationship

Screen Capture 4 - Relationship

As you can see DAX can be effectively used to facilitate creating of relationship between data sources even though they do not readily exhibit one-to-many relationship between parent and child tables. 

Benny Austin

About these ads

2 comments on “PowerPivot: DAX Expression for Type 2 SCD Lookup

  1. prav
    August 12, 2010

    The analysis of parent and child many to many relationship is useful while pulling the data from different tables with different level relationship to maintain the granularity level of data.
    This post is really great for data analysis.

    thanks

  2. Pingback: Kasper de Jonge PowerPivot Blog » Slowly changing dimensions and PowerPivotPowerPivot for Excel 2010: Give Your Data Meaning

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

Information

This entry was posted on August 8, 2010 by in Power BI and tagged , , , , , , .

Copyright

© Benny Austin.
Follow

Get every new post delivered to your Inbox.

Join 190 other followers

%d bloggers like this: