How to track the change of a specific field in a dimension table?

View previous topic View next topic Go down

How to track the change of a specific field in a dimension table?

Post  thomaszhwang on Wed Aug 17, 2011 3:40 pm

I have an Employee dimension table and in this dimension table there is a Salary field. How can I design the table so that we can easily track the change of salary of an employee?

In this dimension table, I have already had an Effective_Start_Date (type: datetime), Effective_End_Date (type: datetime) and Effective_Current (type: bit) to track the changes of those SCD fields. Should I create separate fields (Salary_Effective_Start_Date, Salary_Effective_End_Date and Salary_Effective_Current) to track the salary specifically?

Thanks.

thomaszhwang

Posts : 32
Join date : 2011-08-11

View user profile

Back to top Go down

Re: How to track the change of a specific field in a dimension table?

Post  LAndrews on Wed Aug 17, 2011 4:00 pm


If Salary is a type-2 attribute, then changes will be tracked.

If you need to analyse the changes, then a bit more info may be required

- you may want to have a "change-desc" column (either on the dimension or audit record) that can contain the change drivers. For example, if the type-2 change is driven by changes to the job and salary info, the change desc column could contain "JOB,SALARY"

- if you want to easily determine the amount of change, you may want to add columns such as "Prev Salary" or "Salary Change Amt".


LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: How to track the change of a specific field in a dimension table?

Post  thomaszhwang on Wed Aug 17, 2011 4:02 pm

LAndrews wrote:
If Salary is a type-2 attribute, then changes will be tracked.

If you need to analyse the changes, then a bit more info may be required

- you may want to have a "change-desc" column (either on the dimension or audit record) that can contain the change drivers. For example, if the type-2 change is driven by changes to the job and salary info, the change desc column could contain "JOB,SALARY"

- if you want to easily determine the amount of change, you may want to add columns such as "Prev Salary" or "Salary Change Amt".


This is very helpful. Thank you.

thomaszhwang

Posts : 32
Join date : 2011-08-11

View user profile

Back to top Go down

Re: How to track the change of a specific field in a dimension table?

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