Role Playing Dimensions in Power BI

In this post I want to explain how to handle role playing dimensions in Power BI. I wrote an article awhile ago regarding role playing dimensions in SSAS Tabular which is valid for Power BI Desktop.

To recap, in the role playing dimensions in SSAS Tabular article I explained three different solutions:

  1. Importing role playing dimensions several times into the model
  2. Creating database views in the source side (in case your source is a from of RDBMS like SQL Server, Oracle etc…) then import the data into the model
  3. Keep the inactive relationships in the model and create several measures to take care of different roles using USERELATIONSHIP functions in DAX

In this post I show you alternative ways for the first two solutions to handle role playing dimensions without importing data several times into the Power BI model. You also don’t have to create database views on your source database. I show you how to manage this in both DirectQuery and Import modes when connecting Power BI Desktop to a SQL Server database. I explain the third option in another post.

I used AdventureWorksDW2016CTP3, but, you can use any other versions of AdventureWorksDW database or you can mimic the process to your own model.

Note: If you are designing a star schema for your data warehouse you can easily create a Date dimension as explained here.

The idea is to manage role playing dimensions in Power BI Desktop itself in the easiest way possible.

Role Playing Dimensions in Import Mode

  • Open Power BI Desktop
  • Get data
  • Select “SQL Server”
  • Enter the server and database names then click OK

Power BI SQL Server Connection

  • Select DimDate and FactInternetSales from the list then click “Load”
  • “Import” mode is selected by default. Click OK

Power BI Connection Settings

  • Save the model

Power BI Desktop

  • Rename DimDate and FactInternetSales to make them more user friendly

Power BI Desktop Task Pane

  • Create a new calculated table by clicking “New Table” button from “modelling” tab from the ribbon

Power BI Desktop Calculated Table

  • We are creating a copy of “Date” table using DAX expression. To do so just type the following DAX expression:

              Order Date = All(‘Date’)

  • As you might noticed the icon for “Order Date” table (Power BI Desktop Calculated Table Icon) is a bit different than the normal table icon (Power BI Desktop Table Icon) as the new table is a calculated table
  • Create two more calculated tables for “Ship Date” and “Due Date” using the same expression

Power BI Desktop Role Playing Dimension

  • Click “Relationships” to create new relations between the calculated tables we created and “Internet Sales” table

Power BI Desktop Relationships View

We are done now. We have all roles in our model.

  • Put a column chart on the report and put “Fiscal Year” column from “Order Date” to the chart axis then put “Sales Amount” from “Internet Sales” on values. (You can create an explicit measure for “Sales Amount” but for simplicity I keep using the implicit one.)

Power BI Desktop ColumnChart

  • Repeat this for the other two date dimensions

Power BI Desktop ColumnCharts

You can hide the “Date” dimension in data model, or you can unload it in the Power Query layer to reduce the complexity of the model.

Role Playing Dimensions in DirectQuery Mode

Although the DirectQuery is an awesome feature in Power BI it has some few limitations. One of them is that we CANNOT create calculated tables. So we have to go for an alternative solution.

Note: You can learn more about DirectQuery here. But, some information is out-dated as in the new release of Power BI some of the limitations like creating calculated column and calculated measure are resolved. So the limitation on creating a calculated table in DirctQuery model might bet resolved in the next releases of Power BI. (Power BI Desktop current version: Version: 2.33.4337.281 64-bit (March 2016)).

Power BI Desktop Create Calculated Table Disable in DierectQuery

Let’s get the job done in DirectQuery.

The first steps of getting data are the same just you need to select “DirecyQuery” in “Connection Settings” page instead of “Import”.

Power BI Desktop Connection Settings DirectQuery

After loading the model follow the steps below:

  • Click “Edit Queries” from “Home” tab from the ribbon

Power BI Desktop Edit Queries

  • Rename DimDate and FactInternetSales to user friendly names

Power BI Desktop Query Editor

  • Right click on “Date” and select “Reference”

Power BI Desktop Reference Table

  • If you want to see the Power Query “M” scripts behind the seen for a reference table click on “Advanced Editor” from “View” tab

Power BI Desktop Advanced Editor

  • Rename the reference table to “Order Date”
  • Repeat this and create Due Date and Ship Date reference tables
  • Click “Close & Apply”

Power BI Desktop Query Editor 2

Power BI Desktop Query Changes

  • Switch to “Relationships” view

Power BI Desktop Relationships 2

  • Keep just the correct relationships and remove unwanted ones between “Order Date”, “Due Date”, “Ship Date” and “Internet Sales”

Power BI Desktop Relationships 3

Alright, we have all the role playing dimensions in our model. You can hide the “Date” dimension in data model, or you can unload it in the Power Query layer to reduce the complexity of the model.

Pros and Cons:

Pros:

1- If your model is a small model then you can quickly import new role playing dimensions into the model either in Import Mode or DirectQuery Mode, creating relationships and you’re ready to go

2- It may be easier for the end user to have different Date options. You will have separate slicers in the Power BI. However, you’ll need to take care of filter interactivities and cross filtering which means more development time.

3- You will have just one measure to be sliced and diced 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 the dimensions several times which is not efficient

2- Having lots of different role playing dimensions as separate tables 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. It is also quite hard to maintain such a big model with lots of role playing dimensions

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

Bottomline:

At the end of the day it really depends on your customer needs. Read more about the other method to manage Role Playing Dimensions here.

All done!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.