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?

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 directly use the imported DimDate for all three applications you need to cover the “Order Date”, “Ship Date” and “Due Date”. This means that you cannot slice and dice a single measure with all roles at the same time, which in our example they are “Order Date”, “Ship Date” and “Due Date”. I explain more later in this post.

Solutions:

1.       Importing DimDate into your tabular model several times:

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 several SQL Server views in the  database:

In our example, in “AdventureWorksDW” database, we create three views for each role (Order Date, Ship Date, Due Date). We create those views on top of the existing DimDate with different names resembling the three different roles. Then we import those views into our tabular model and link each of them to the “Internet Sales” table using the appropriate foreign key. As the whole process is the same as what we’ve done previously in the first solution, I’m not going to explain it again. So, at the end of the day, we will have something like this in the database:

You can now import the above views to your tabular instead of importing the whole DimDate table several times. This will reduce the database size and it is a bit easier to understand. However this solution is very similar to the first solution . Basically the architecture is quite the same, but, the way we manage the tables is a bit different.

And the same results in Excel:

3.    Creating several measures: 

The third solution, which is probably the best for the majority of use cases, is completely the opposite of what we have done so far. Well, I can say that the architecture is quite different. In this solution we DO NOT remove the Inactive Relationships and moreover, we DO NOT import several copies of Date dimension.

What we should do in this case is to create new measures for each role which means we will have the following three measures in our example:

1- Total Sales Amount by Order Date

2- Total Sales Amount by Ship Date

3- Total Sales Amount by Due Date

What we are doing in this solution is that we manage to use the relationship which is relevant to the roles. To do that we just need to enforce the data model to activate the relationship we need. We can easily active and inactive relationship in DAX using USERELATIONSHIP function. The USERELATIONSHIP function, disables all active relationships first, then activates a desired relationship. USERELATIONSHIP function can be used as a part of other functions that take filters as arguments. This means that we always use USERELATIONSHIP as a part of a CALCULATE function (or other functions that accept filter arguments). Therefore, the above three measures will look like below:

1- Total Sales Amount by Order Date:= SUM(‘Internet Sales'[Sales Amount])

2- Total Sales Amount by Ship Date :=  CALCULATE(SUM(‘Internet Sales'[Sales Amount]), USERELATIONSHIP(‘Date'[DateKey], ‘Internet Sales'[ShipDateKey])) 

3- Total Sales Amount by Due Date :=  CALCULATE(SUM(‘Internet Sales'[Sales Amount]), USERELATIONSHIP(‘Date'[DateKey], ‘Internet Sales'[DueDateKey]))

As you can see in the first measure we haven’t used USERELATIONSHIP. The reason is that the measure uses the relationship which is active by default in the model, therefore we don’t need to enforce it again. The other two measures on the other hand are enforcing relevant relationships to be used within the measures.

Finally, here is how it looks like when you analyse the model in Excel:

Pros and Cons:

Each of the three solutions discussed above have pros and cons.

Pros of the first two solutions, importing several Date dimensions:

1- If your model is a small model then it would be quicker to develop the model

2- It would be easier for the end user to have different Date options. You will have separate slicers in the visualisation layer for each role.

3- You can have just one measure and slice and dice it by different roles separately

Cons:

1- If your model is not small and you have much more role playing dimensions to manage then you’ll end up importing those dimensions several times which is not efficient

2- Having lots of different role playing dimensions all over the model will be really confusing for the end user and you will really need to spend more time/money to train the end users

3- You consume more storage and memory which is again not that efficient 

Pros of the last solution, creating several measures:

1- You can use all the roles side-by-side as you literally have a separate measure for each role

2– You are not importing several copies of the roles, for instance, you have just one Date dimension that can be used to slice and dice all of your measures across the whole model

3- It is more efficient in terms of storage and memory consumption

4- Your model is much more tidy when you don’t have several roles all over the model

Cons:

1- In large models with lots of different roles, creating lots of different measures to support different roles would be time consuming and also a bit hard to maintain

2- The measure names are getting long

3- Having lots of different measures that look very similar can be a bit confusing for the end user 

All done!