Role playing dimension


We have explored Junk dimension and Degenerate dimension in previous posts, now we are going to discuss Role playing dimension in this post. Lets start with the definition followed by an example.

Role playing dimension

A fact table keeps the facts of a business process. It is built of two types of columns: measure columns and dimension key columns. Measure are the quantitative business data about the business process and dimension keys reference to the dimension tables which hold the textual attributes of the business process.

Typically a dimension key column of a fact table refers a dimension table but its also very common where multiple columns of a fact table refer to a single dimension table. When a single dimension table is linked from multiple dimension key columns of a fact table, that dimension table is known as role playing dimension. As multiple columns of a fact table can be associated with a single dimension table, a single database dimension table can play different role as cube dimensions for each association.

Lets have an example of FactInternetSales table from AdventureWorksDW2014 database. Below is a customized look of FactInternetSales table;

Multiple date keys in FactInternetSales table

Multiple date keys in FactInternetSales table

Below is the DimDate dimension table from AdventureWorksDW2014  database;

DimDate

DimDate

We have multiple date columns in the above fact table, which are OrderDateKey, DueDateKey and ShipDateKey, and a single Date dimension table, which is DimDate. We don’t need to duplicate the DimDate dimension table for 3 times when we design the cube for internet sales data, we just need to refer the same DimDate dimension with different name for all three date key columns of the FactInternetSales table.

Role playing dimension

Role playing dimension

 

In above image, we can see that DimDate dimension is playing different role for each of its reference. We have only one DimDate dimension as the database table but there are three date dimensions as cube dimensions (red squared in image). A single date dimension has three different views; Order Date, Due Date and Ship Date as cube dimensions in above example.

There could be “n” number of references to the same dimension table from a fact table, and for each reference, we refer the same database dimension table with different name as cube dimension. Role playing dimension not only reduces the space of the cube by removing the redundant data but it also improves the cube processing time as we need to process only one database dimension.

Next to explore:

  1. Junk dimension
  2. Degenerate dimension

Thank you for reading, if you like this post, do share on your social wall.

Please rate this post and also share your valuable feedback in comment section.

Rate This
[Total: 1    Average: 5/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

Gopal has 8 years of industry experience in Software development. He has a head down experience in Data Science, Database, Data Warehouse, Big Data and cloud technologies and has implemented end to end solutions. He has extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). He also has good experience in windows and web application development using ASP.Net and C#.

Leave a comment

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.