Role-Playing Dimension

---

Role-Playing Dimension is describes a common structured dimension that appears more than one times in the same Fact table. Role-Playing Dimension is used to avoid multiple SQL Query Joins that helps to optimize SQL Query Operation.

An instance, Accumulating fact table is used to records one row for the entire lifetime of transaction with measures of business events that happen over a period of time. Accumulating fact table must be design with combination of Additive facts with multiple date keys that helps to track the lifetime of business event.

Non-additive facts are optional in this type of fact table. The following Accumulating Sales_Historical_Fact table is designed with typical grain and foreign keys that integrates with relevant Shared Dimension and Role-playing Dimension tables.



Instead of six independent Date dimensions, one common Role-Playing Date_Dimension creates that Primary Key appears more than one time with Sales_Historical_Fact table's Order_Date, Ship_Date, Delivery_Date, Payment_Date, Sales_Return_Date and Order_Close_Date Foreign Key to track complete life cycle of Order Transaction.

Directly, more than one times, a Role-Playing Dimension (Parent) table's Primary Key can not integrate with Fact (child) table's Foreign Keys in the Relational Database that occur referential integrity error.

SQL SYNONYM or VIEW concept is helps to create multiple duplicate Physical Copies or Virtual Copies of Role-Playing Dimension table that independent duplicate Physical or Virtual tables can integrates with Accumulating Fact table to avoid referential integrity error.

* * * * *

---