First of all I’d like to explain what a Role-playing dimension actually means. Then I’ll express the way you can implement it in a SSAS tabular model.
When you link a dimension to a fact table several times for logically distinctive roles you’re using a role-playing dimension.
The key points are:
1. You are linking a fact table to a dimension multiple times. The relationships are defined by linking multiple foreign keys in the fact table to a single key in the dimension table.
2. Each linkage represents a single role or concept
The most popular role-playing dimensions are DimDate and DimTime. Do you want to see how to implement Role Playing Dimensions in Power BI, Click here and here.
NOTE: The sample is from Microsoft “AdventureWorksDW” for SQL Server 2012 and might be different from your own data warehouse design.
For instance, in a sales system that you have something like FactInternetSales fact table which has several links, or relationships, to a DimDate or DimAddress for distinct concepts like “Order Date”, “Ship Date” and “Due Date”.
As you see, all of the above columns obviously represent different meanings of date. In the data warehouse design you’ll see something like this:
Although this is absolutely OK in the relational database layer, but, this sort of relationship is NOT permitted in the tabular model, so what should we do?
Continue reading “Role-playing Dimension in SSAS Tabular Models”