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;
Below is the DimDate dimension table from AdventureWorksDW2014 database;
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.
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:
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.
