SCD and Fact update

View previous topic View next topic Go down

SCD and Fact update

Post  kjfischer on Fri Mar 28, 2014 2:53 pm

I need some advice for design on new fact table and type 2 dimension table.

The subject area surrounds employees and their scheduled hours and actual worked hours.

An employee has a home account, but when working may punch in and out of other accounts.

An employee's home account may change over time and it is a requirement to track historically what their home account is with their scheduled and actual work hour facts.

So, I have designed a  user_account_dim as a SCD:
The natural key in the source system is employeeid=33.  The source system assigns a new surrogate key (homeacctid) everyime the employee changes to a new account.
As part of our normal design, a surrogate key is assigned to every new row when it comes into the data warehouse (labor_account_dim_id)

LABOR_ACCOUNT_DIM_IDEMPLOYEEIDHOMEACCTIDEFFECTIVEDTMEXPIRATIONDTM
1333201-JAN-1953 12.00.00 AM18-MAR-2006 12.00.00 AM
2331041918-MAR-2006 12.00.00 AM14-FEB-2011 12.00.00 AM
3332969214-FEB-2011 12.00.00 AM16-SEP-2013 12.00.00 AM
4334423716-SEP-2013 12.00.00 AM11-NOV-2013 12.00.00 AM
5334537211-NOV-2013 12.00.00 AM01-JAN-2015 12.00.00 AM
The source system has an employee's work schedule which includes their schedule from day 1 of employment to months into the future (31-DEC-2015).  It has a row for each day, with a starttime and endtime.

A snippit of fact table would look like this correctly assigning the labor_account_dim_id which was in effect at the time of the schedule.
LABOR_ACCOUNT_DIM_ID
COMBHOMEACCTIDLABOR_ACCOUNT_DESCEMPLOYEEIDSHIFT_START_DATESHIFT_END_DATEEXPECTED_DAILY_HOURS_QTY
210419Strategic Operations3303-DEC-2008 06:00:00 PM04-DEC-2008 06:00:00 AM8
210419Strategic Operations3304-DEC-2008 06:00:00 PM05-DEC-2008 06:00:00 AM8
210419Strategic Operations3305-DEC-2008 06:00:00 PM06-DEC-2008 06:00:00 AM8
329692Sales Operations3314-FEB-2011 06:00:00 PM15-FEB-2011 06:00:00 AM8
329692Sales Operations3315-FEB-2011 06:00:00 PM16-FEB-2011 06:00:00 AM8
444237Human Resources3316-SEP-2013 08:30:00 AM16-SEP-2013 05:00:00 PM8
444237Human Resources3317-SEP-2013 08:30:00 AM17-SEP-2013 05:00:00 PM8
545372Production Business3311-NOV-2013 08:30:00 AM11-NOV-2013 05:00:00 PM8
545372Production Business3311-NOV-2013 08:30:00 AM11-NOV-2013 05:00:00 PM8
545372Production Business3331-MAR-2014 08:30:00 AM31-MAR-2014 05:00:00 PM8
545372Production Business3301-APR-2014 08:30:00 AM01-APR-2014 05:00:00 PM8
Now, the employee get a job and moves into a new account effective 01-APR-2014.  We insert a new row into our dimnesion.

LABOR_ACCOUNT_DIM_IDEMPLOYEEIDHOMEACCTIDEFFECTIVEDTMEXPIRATIONDTM
1333201-JAN-1953 12.00.00 AM18-MAR-2006 12.00.00 AM
2331041918-MAR-2006 12.00.00 AM14-FEB-2011 12.00.00 AM
3332969214-FEB-2011 12.00.00 AM16-SEP-2013 12.00.00 AM
4334423716-SEP-2013 12.00.00 AM11-NOV-2013 12.00.00 AM
5334537211-NOV-2013 12.00.00 AM01-APR-2014 12.00.00 AM
6339999901-APR-2014 12.00.00 AM01-JAN-2015 12.00.00 AM


We want the facts for scheduled work starting from 01-APR-2014 to reflect the new employee home account. (so, the last row in the fact example above).  THere is no need to ever see the original employee home account assigned.  But, we always have to show the home account that is in effect for the schedule date - and we know that the home account can change over time.

Is it ok to go back and update the fact table for those rows affected?  I know that I can do a self join back to the dimension on the employeeid and get the correct dim_id, but I never want the historic one if it no longer is valid.  Any thoughts on the best approach would be appreciated.  Thanks.

kjfischer

Posts : 28
Join date : 2011-05-04

View user profile

Back to top Go down

Re: SCD and Fact update

Post  BoxesAndLines on Fri Mar 28, 2014 5:48 pm

COMBHOMEACCTID shouldn't be in the fact table. It's a dimension attribute. Once you remove it you no longer have issues with updating the fact table. The EMPLOYEEID shouldn't be in your fact table either, but since it is, it offers a solution to your problem. Simply join to the labor dimension using the EMPLOYEEID where current row = 'Y' (add it if you don't have it already) and you get the current HOMEACCTID.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: SCD and Fact update

Post  kjfischer on Sun Mar 30, 2014 3:11 am

BoxesAndLines wrote:COMBHOMEACCTID shouldn't be in the fact table.  It's a dimension attribute.  Once you remove it you no longer have issues with updating the fact table.  The EMPLOYEEID shouldn't be in your fact table either, but since it is, it offers a solution to your problem.  Simply join to the labor dimension using the EMPLOYEEID where current row = 'Y' (add it if you don't have it already) and you get the current HOMEACCTID.

Thanks BoxesAndLines. Agreed that COMHOMEACCTID and LABOR_ACCOUNT_DESC shouldn't be in the fact table - I only put it in the post for illustration. But, in any case, I do not want the CURRENT homeacctid - I ALWAYS want the homeacctid that is in effect for the scheduled fact row. So, for example, for the (yellow highlighted) fact rows in 2006, I want to retrieve the homeacctid/desc of 10419/Strategic Operations and for the first three blue rows, the homeacctid should reflect 45372/Production Business and the last blue line starting Apr-1, should reflect their new homeacctid of 99999/New Home Acct.

kjfischer

Posts : 28
Join date : 2011-05-04

View user profile

Back to top Go down

Re: SCD and Fact update

Post  BoxesAndLines on Sun Mar 30, 2014 7:56 pm

OK, got it. Yes, updating the fact rows is your best option. How feasible it is depends on database volumetrics, tuning, number of updates, etc.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: SCD and Fact update

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum