Role-playing tables
A role-playing table is a table that can play multiple roles, and this helps to reduce data redundancy. Most often, the Date table is a role-playing table. For example, the FactInternetSales table has three dates to track the processing of an order. There is the Order Date, Ship Date, and Due Date and, without role-playing tables, you would need to have three separate date tables instead of just one. The additional tables take up valuable resources, such as memory, as well as add an extra layer of administrative upkeep.
Each of these dates is very important to different people and different departments within an organization. For example, the finance department may wish to see total sales and profit by the date that a product was purchased, the order date. However, your shipping department may wish to see product quantity based on the ship date. How do you accommodate requests from different departments in a single data model?
One of the things I loved about working with SQL Server Analysis Services Multidimensional was the ease with which it handled role-playing tables; perhaps you also come from a background where you have worked with tools that had built-in support for Role-Playing tables. Unfortunately, Role-Playing tables are not natively supported in Power BI; this is because all filtering in Power BI occurs through the active relationship and you can only have one active relationship between two tables.
There are generally two ways you can handle role-playing tables in Power BI:
- Importing the table multiple times and creating individual active relationships.
- Using DAX and inactive relationships to create calculations that show calculations by different dates.
The first way, and the method we will show here, is importing the table multiple times. Yes, this means that it will take up more resources. The data model will have three date tables, one table to support each date in the FactInternetSales table. Each date table will have a single active relationship to the FactInternetSales table.
Some of the benefits of importing the table multiple times are as follows:
- It is easier to train and acclimate end users with the data model. For example, if you want to see sales and profit by the ship date, then you would simply use the date attributes from the ship date table in your reports.
- Most, if not all, DAX measures will work across all date tables, so no need for creating new measures.
- The analytical value of putting different dates in a matrix. For example, sales ordered and sales shipped by date.
Some of the cons of importing the table multiples times are:
- Resources. Additional memory and space will be used.
- Administrative changes. Any modifications made to one table will need to be repeated for all tables, as these tables are not linked. For example, if you create a hierarchy in one table, then you would need to create a hierarchy in all date tables.
The report in Figure 16 shows total sales and total transactions by year, but which year? Is this the year that a product was purchased or the year a product was shipped? The active relationship is on order date, so the report is displaying the results based on when the product was purchased:
