SQL Server: Role Playing Dimension

 Role Playing Dimension

Many facts and many dimensions together house any data warehouse. Like ways, there are types of facts and also types of dimension which make actually make the picture complete.

 In this article, we will try to have a brief understanding of Role Playing Dimension

 In plain terms, a role playing dimension would mean a “morphed actor”. The actor remains the same, but is made available to the requirement in different forms or even in multiple forms. This is precisely what can be called as polymorphism that we understand in OOP terminology. Same is the theory applicable to Role Playing Dimension. You will have one “key” dimension and the underlying data model for the warehouse will have many morphed versions.

Technically speaking, you will have one dimension which is expressed differently in a fact table either using views or different role names. You will find generally Time Dimension as an explicit example of role playing dimension, but that is not the only one. Consider the example below:

For example:

Say the DW has defined for a customer technical support company and they have a dimension for Employee and a fact table with associated foreign keys.

We can define a role playing dimension for the Employee table as below:

Service_Request_Created_By_EmployeeID

 Service_Request_Routed_By_EmployeeID

Service_Request_Routed_To_EmployeeID

Service_Request_Resolved_By_EmployeeID

Service_Request_Closed_By_EmployeeID

This is just one example that I can think of at this point of time. The details are reserved with the actual business requirements. The thoughts described here are purely incidental.

Instead of defining six different dimension tables, we can create a multiple views of the original table which would then provide the necessary relationship with the central fact table.

This looks fantastic!! But role playing dimension(s) have a very severe short coming. Suppose the underlying dimension table in the DW has an explicit user hierarchy defined, all the views created will automatically inherit the user hierarchy. Now if the business demands that the stated hierarchy cannot be used to get the desired results, the DW team will have to resort to create a all new dimension table to meet the business needs.