Dimension Role-Playing in Employee Comp Reporting
The employee hierarchy table is just one physical table, with a date range to keep employee’s employment history. This same employee hierarchy table can be used sometimes to show different business functional teams, for example, LAS VS. Underwriters on the same fact table.
I can simply create two SQL views to handle this, or just use two sub-queries if I don’t want to create too many SQL views.
This is called dimension role-playing. Role-playing in a data warehouse occurs when a single dimension simultaneously appears several times in the same fact table. The underlying dimension exists as a single physical table, but each of the roles should be presented in a separated labeled view.
Another common example in the employee productivity reporting, is the date dimension. We will just need one single date dimension table, but very often, we need to have a second date, such as application date, besides the primary decision date, on the same fact table. Again we only need to create two different SQL views to handle this, with only one underlying physical date dimension table.
Blog Stats
- 777,578 hits
Tag Cloud
Recent Posts
Categories
- Book Reviews (6)
- DAX (2)
- DW & BI Interview Qs (8)
- DW Interview Qs (2)
- SQL Interview Qs (2)
- SSIS Interview Qs (2)
- ETL Best Practices (75)
- I want to learn… (1)
- MDX Challenges (53)
- SQL Challenge (62)
- SSAS (35)
- SSIS Best Practices (120)
- SSRS Expert (78)
- Uncategorized (140)
Links
Archives
- November 2016 (1)
- March 2016 (4)
- February 2016 (1)
- May 2015 (2)
- April 2015 (1)
- January 2015 (3)
- December 2014 (6)
- November 2014 (5)
- June 2014 (1)
- May 2014 (3)
- April 2014 (5)
- February 2014 (2)
- January 2014 (4)
- December 2013 (1)
- November 2013 (2)
- October 2013 (3)
- September 2013 (3)
- August 2013 (1)
- March 2013 (2)
- February 2013 (2)
- January 2013 (2)
- December 2012 (2)
- November 2012 (3)
- October 2012 (3)
- September 2012 (6)
- August 2012 (5)
- July 2012 (3)
- June 2012 (2)
- May 2012 (5)
- April 2012 (3)
- March 2012 (4)
- February 2012 (4)
- January 2012 (4)
- December 2011 (7)
- November 2011 (3)
- September 2011 (1)
- August 2011 (5)
- July 2011 (17)
- June 2011 (14)
- May 2011 (10)
- April 2011 (13)
- March 2011 (26)
- February 2011 (22)
- January 2011 (25)
- December 2010 (7)
- November 2010 (5)
- October 2010 (27)
- September 2010 (25)
- August 2010 (41)
- July 2010 (44)
- June 2010 (27)
- May 2010 (41)
- April 2010 (59)
- March 2010 (37)