posted 10/8/2011 9:56:34 AM by Raunak Jhawar - Views: [1159]
Role Playing Dimension
Yet another very important and rather very interesting feature of any successful implementation of an enterprise DW. As a matter of fact, the decision of using should be ideally be decided when in early stages of data modelling. The benefits are supreme, but again, it like every great feature there are short comings.
In this following article, let us have a walk-through of Role Playing Dimension.
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.
As always, I await your valuable inputs and suggestions for any specific area of DW for which hyou would like me to share my views.
I plan to write next on Factless facts and Bridge facts.
Thank you for your time and patience. I remain.
Sincerely,
Raunak Jhawar
Raunak Jhawar (Member since: 7/12/2011 6:42:24 AM) Specilaist for Intensive Data Computing.
View Raunak Jhawar 's profile
Hi Raunak,
We have created Role Playing Dimension for the Time Table as follows:
Arrival Date
Departure Date
Invoice Date
Evrything worked perfectly with no errors but after including the Role Playing Dimension there was a financial mismatch.
We removed the Role Playing Dimension and created separate time dimension table for each (Invoice, Departure and Arrival) which gave us the correct financials. Is there anything(property/settings) we need to change when we use Role Playing dimension espc. when the dimension is Time?
Thanks in advance!
Regards,
Pallavi
Hello Pallavi,
How are you adding/creating a RP dimension and also, are you defining the dorrect relationship in the dimension usage tab?
Thanks\Raunak
The Role Playing Dimension is an ideal choice for the scenario which you have explained. I have also deployed the Role Playing Time Dimension in many of my live environments which works perfectly fine. The only limitation as Raunak said is that you can not have customised column names and all the column names and hierarchies appear as is based on the main dimension created. The alternative could be create views in the DSV designer using named queries which allows you to have different table names and column names.
I guess the financial mismatch you said should be mainly due to the filtering of the data using the time dimension. Please check the following,
1. The underlying time dinesion table has all the dates covered.
2. Check if the date column in the dimension table and the date columns in the fact table have a time data associated with them as it may cause issues while filtering the data.
Thanks,
Amit K
Hello Amit K,
We have NULL values for some cases in Departure and Arrival Date column. I tried to set NULL value as '01-Jan-1900' (IsNull(DepartureDate , '01-Jan-1900')) but it still give me the incorrect result.
Kindly let us know you have any solution to resolve this issue.
The value '01-Jan-1900' which you have set is in the fact table. To fetch this data using the Date Dimension, for e.g DepartureDate dimension, that particular key '01-Jan-1900' should be available in the dimension table. Ensure that the date dimension tables have this value and the corresponding dimension key should be properly updated in the fact table.
Leave a comment