How to track the change of a specific field in a dimension table?
2 posters
Page 1 of 1
How to track the change of a specific field in a dimension table?
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.
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
Re: How to track the change of a specific field in a dimension table?
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
Re: How to track the change of a specific field in a dimension table?
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
Similar topics
» Using a Dimension Table to Track Changes?
» Create fact table from a dimension (detect change)
» Dimension : add many specific colmuns or add another (one to one link) dimension
» Fact table to track history on 4 dimensions?
» Track and control of dimension and fact tables
» Create fact table from a dimension (detect change)
» Dimension : add many specific colmuns or add another (one to one link) dimension
» Fact table to track history on 4 dimensions?
» Track and control of dimension and fact tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum