How to work with Role Playing Dimensions

If you have attended any of our Power BI training in Australia, you will be aware of our lesson plan. We provide instructed led trainings with a step by step guiding manual. However, we do also focus on the common problems that can occur while implementing the BI solution at your workplace.

In this blog post, a step-by-step guide on dealing with role playing dimensions is presented but let’s summarize the concepts covered so far:

1. Importing Data

Importing data refers to procuring the data tables into Power BI desktop or service. This step involves creating a connection with the database to access the data.

Here is a guide on how to Import Data.

2. Data Preparation

Data Preparation involves ensuring that the data is capable of delivering value. In this phase, the raw data is manipulated to extract useful information from it.

For a step-by-step guide on Data Preparation. 

3. Creating Date Table

The date Table is important for temporal analysis. The date table is created using the CALENDER() function or the CALENDERAUTO() function. Both of these functions have different input parameters and produce different results.

For a step-by-step guide on Creating Date Table 

4. Setting Model Design

The model design consists of creating relationship between the data tables procured for the task at hand. The tables are linked on the basis of primary and foreign keys. If these keys are absent then the table cannot be linked to another table.

For a step by step guide on Setting Model Design.

5. Creating Surrogate Key

Surrogate Key is the unique identifier in any table. It is important to create a relationship between the two tables. This key acts as a primary key in one table and a foreign key in the other.

For a step-by-step guide on Creating Surrogate Key 

6. Merging Queries

Merging queries involves combining two data tables based on a common column. This column should have similar values in both tables.

For a step-by-step guide on Merging Queries 

7. Creating Calculated Columns

Calculated Columns are created to provide you with insights related to your data set and make data visualization easy and meaningful.

For a step-by-step guide on Creating Calculated Columns.

8. Flattening the Product Table

Flattening of the table refers to linking the tables attached to the ends of the schema. In data terminology it means changing the snow flake to star schema because star schema is the most referred data model by industry experts. Modeling. Flattening of Product table is important due to two reasons. Firstly, it helps to organizes the data model and secondly, hierarchy can be created in the same table of the data model.

For a step-by-step guide on Flattening the Product Table. 

9. Creating Product Hierarchy

Power BI allows you to create Product Hierarchy for columns present in the same table.

For a step by step guide on Creating Product Hierarchy

10. Active vs. Inactive Relationship

The Sales table contains two date fields, Order Date and Ship Date. We have already configured the Order Date with the Date Table.

For a step-by-step guide on this relationship. 

11. Working with Role-Playing Dimensions

Role Playing Dimensions in a data model are dimensions that are used multiple times and for different purposes. As an example, the Date in our data model is a Role Playing dimension. It is used to signify the Order Date as well as the Ship Date. However, only one active relationship can exist between two tables, and designing measures to use inactive relationships is not preferred.

To work with Role Playing Dimension:

1. Click on the Modeling Tab in the Ribbon.

2. Click on New Table.

3. Type the following DAX expression in the Formula Bar.