Rico asks how I can implement type 2 changes in a dimension.
Hello Rico,
Type 2 is relatively easy to implement, but it can be a pain for users to understand and use.
Implementing Type 2
Generally I use a set of standard flags to track the changes in the dimension. Each flag is used to determine the state of the record. I am assuming that you are using Surrogate keys in your dimensions and Fact tables.
Date From – The date the record arrived in the dimension
Date To – The date the record is deemed to be changed
Current Flag – The State of the record Y or N
With these flags you can track changes of the dimension at the lowest level such as employee changes.
e.g.
Employee Dimension
You will have to come up with at change capture process. If you have type 1 deployed already it would be the same logic for identifying the changes, with the exception of updating old records and a creating new records in the dimension. Remember you will have to update your surrogate key lookup in you fact table build to load with the current dimension record. I.E. “WHERE Current Flag =’Y’ ”
Implementing Type 2 without Flags
To be honest I have not tried this method but it worth considering if your Business users have problems reporting using the Type 2 Flags. Rather than having one Employee Dimension create a series of mini dimensions against the fact table. This would allow you to track changes against the fact record instead of the Dimension.
E.G.
Employee Dim is broken into three new dimensions (Dim Manager, Dim Employee, and Dim Job Title) as shown in the diagrams below
There is a HUGE draw back to this you end up a large amount of dimensions and a really wide fact table if you have to include a lot of them.
Hope this gets you started.
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
No comments:
Post a Comment