Let's dive a little deeper into relationships today. In PowerPivot V1 we could only create one relationship between two tables. Good news coming to V2. We can now create multiple relationships between tables.
Let's have a look at our factInternetSales table. The table has OrderDateKey, DueDateKey and ShipDateKey. All three link to the DimDate table. As you can see in the screenshot below we can create a relationship for each one of them. However, only 1 of them can be the "active relationship". And that is the key-concept to understand in V2.
When you create a pivot table, PowerPivot will use the active relationship (on OrderDateKey). If you want see the Sales per DueDate or ShipDate you must create a DAX calculation and use the new USERELATIONSHIP function. This function will force PowerPivot to use a "non-active" relationships.
CALCULATE(SUM(FactInternetSales[SalesAmount]),USERELATIONSHIP(FactInternetSales[ShipDateKey], DimTime[TimeKey]))
This support for multiple relationships is a very nice addition to PowerPivot. But, personally I think Analysis Services' concept of roll-playing dimensions is more "user-friendly" than PowerPivot's concept of active relationships and USERELATIONSHIP.
Let's have a look at our factInternetSales table. The table has OrderDateKey, DueDateKey and ShipDateKey. All three link to the DimDate table. As you can see in the screenshot below we can create a relationship for each one of them. However, only 1 of them can be the "active relationship". And that is the key-concept to understand in V2.
When you create a pivot table, PowerPivot will use the active relationship (on OrderDateKey). If you want see the Sales per DueDate or ShipDate you must create a DAX calculation and use the new USERELATIONSHIP function. This function will force PowerPivot to use a "non-active" relationships.
CALCULATE(SUM(FactInternetSales[SalesAmount]),USERELATIONSHIP(FactInternetSales[ShipDateKey], DimTime[TimeKey]))
This support for multiple relationships is a very nice addition to PowerPivot. But, personally I think Analysis Services' concept of roll-playing dimensions is more "user-friendly" than PowerPivot's concept of active relationships and USERELATIONSHIP.
1 comment:
thanks for your articles. To my mind we can't compare USERELATIONSHIP and role playing dimensions. RPD are a way to create only one dimension and not duplicate them when you need it (each RPD has an effect on Measures, not only one). In BISM and PowerPivot, you don't have those RPD, you have to duplicate your dimension.
In SSAS UDM model, creating a perfect dimension was a hard job, so it had a sense to give to the BI developper a way to not duplicate them.
Post a Comment