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.
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?
Let’s have a look at the tabular model in SQL Server Data Tools (SSDT) and see how it looks when we import the model straight from SQL Server data source.
· Open SSDT and create a new analysis services tabular project (I assumed you know how to create a new project in SSDT)
· Click on “Import From Data Source”, then select “Microsoft SQL Server” then click “Next”
· Enter the server name and select “AdventureWorksDW” from the database list then click “Next”
· Entre impersonation information and click “Next”
· Click “Next”
· Here you can select all tables and views you need to import into your tabular model. In our sample we just need “FactInternetSales” and “DimDate” tables. So tick the “FactInternetSales” and “DimDate” tables and then click “Finish”.
· Close the “Table Import Wizard”
· Switch to “Diagram View”. As you can see there is just one Active relationship between DimDate and FactInternetSales tables and both other relationships are Inactive that means you cannot use the imported DimDate for all three applications you need to cover the “Order Date”, “Ship Date” and “Due Date”.
1. Importing DimDate into your tabular model several times to fulfill your needs. In our sample we need to import it three times to cover “Order Date”, Ship Date” and “Due Date”.
a. Delete the inactive relationships
b. Double click on the DimDate table name to rename it to a user friendly name. Name it “Order Date”.
c. To make our sample more untestable I created a new hierarchy named “Order Date Details” which includes “CalendarYear”, “EnglishMonthName” and “FullDateAlternateKey”. A also renamed the columns to make the more user friendly to “Year”, “Month” and “Full Date”. In addition, I set all other columns in the DimDate table to “Hide from Client Tools”. I also renamed the “FactInternetSales” table to “Internet Sales”.
d. We have successfully setup the “Order Date” date and now we need to import the DimDate table again to support the “Ship Date”. To do so, from the “Model” menu select “Existing Connections…”
e. Click “Open”
f. Click “Next”
g. Select DimDate from the list again and click “Finish”. This process will import the DimDate table to the model again. We will then set it up to cover “Ship Date”. To do so, link “ShipDateKey” from “Internet Sales” table to “DateKey” from “DimDate” table.
h. Now repeat the above sections from b to g but, name the “DimDate” table “Ship Date”. Repeat the above sections again to add “Due Date” to the model.
i. We are done and we can simply slice and dice based on all of the above dates.
j. As the tabular model does not detect the measures automatically we need to define at least a measure to be able to test the solution. To do so switch to “Grid View” and select the “Internet Sales” then define a measure for “Total Sales Amount”. To do so just click on measures section under the “SalesAmount” column then click the Sigma () button from the toolbar. Then rename the created measure to “Total Sales Amount”.
k. Now we can test the solution by selecting “Analyze in Excel” from “Model” menu
l. Tick “Total Sales Amount” and “Due Date Details” hierarchy. You can drilldown to month and day levels.
m. You can do the same for each of the other dates or you can make a combination of dates if you need such a report.
2. Creating three different SQL Server views in the “AdventureWorksDW” database for each date application, import them into your tabular model and link them to the “Internet Sales” table using the appropriate foreign key. As the whole process is the same as what we’ve done in the above I’m not going to explain it again. So, at the end of the day, we will have something like this in the database:
As you see I just selected the columns that I need to make it as simple as possible. Then you can import the above views to your tabular instead of importing the whole DimDate table several times. This will reduce the databased size and it is a bit easier to understand. So if you are working on a medium size projects and above you’d be better to create individual database views and import them into your tabular model.
And the same results in Excel: