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

[thomaszhwang]

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.

[LAndrews]

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".

[thomaszhwang]

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.

[Sponsored content]