How do I model Slowly Changing Hierarchy?

We have an anomaly where over time the hierarchy of a dimension
changes. With that, we are only allowed to have "1" hierarchy. It is
basically an organization hierarchy where people get promoted, demoted
and change ranks over time. The business requirement is to be able to
see that 1 appropriate hierarchy over time and the hierarchy changes.
I know all about slowly changing dimensions and know how to manage
those. But what about hierarchys. We would like to use a Type 2 but
really need some pointers in how to model this.
Thanks,
RicoSlowly Changing Dimensions Type 2
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
EmployeeKey EmployeeID EmployeeName EmployeeJobTitle Manager
DateFrom DateTo CurrentFlag
1001 SD00301 Billy Bob Sales Rep Kate
Hawkins 01/01/2004 12/05/2005 N
1200 SD00301 Billy Bob Sales Rep John
Simon 12/05/2005 12/08/2005 N
1250 SD00301 Billy Bob Sales Manager John
Simon 12/08/2005 12/08/2005 Y
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
Dim Job Title
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/|||Hello Rico,
The table in this post has come out a bit crap. I have reposted it on
Blog.
see:
[url]http://bi-on-sql-server.blogspot.com/2005/07/news-group-post-slowly-changing.html[
/url]
hope this helps
Myles|||Hi Rico,
the particular question is one of the most important yet poorly
understood questions in all of BI. I have written extensively on this
topic on the DWLIST (www.datawarehousing.com).
The 'publicly available answer' is build a type 2 dimension that can be
linked to facts where you need it. This shows you the organisation
hierarchy at the point in time.
However, this is useless when answering the 'BIG QUESTION' from the
CEO/CFO/Director of marketing.
The 'BIG QUESTION' is.....now we have re-organised how are we doing
this year vs last year?
And these guys do not like the answer..."Well, our DW only shows us
the organisation hierarchy at the current point in time so we cannot
really compare this year vs last year."
So, to be able to do this you need to maintain an archive of the
organisation structure so that you can build what are called 'hot
swappable dimensions' to be able to compare last years results against
this years structure and this years results against last years
structure etc.
The 'Sybase wants you to pay' answer is that in the Sybase models
(sorry MSFT folk) there is a technique that allows you to compare the
value of anything across any point in time...so you could compare
transactions against the organisational hierarchy across any point in
time with the exception that people who were not there at that point in
time might show up as 'unknown'. (I implement Sybase IWS for a
living...as far as I am aware MSFT does not have a similar data model
offering today.)
So, the best you can go with from public infomation is type 2 hot
swappable dimensions...you can search Ralph Kimballs web page and tips
for details on hot swappable dimensions and I think they are still in
his data modeling book...
Best Regards
Peter Nolan
www.peternolan.com