Hierarchy as Type 2 Change - Use Surrogate or Natural Key?

I realise hierachies have been covered a number of times on here but i've not been able to find an answer to my specific question, but apologies if it has and i've missed it.

I just wondered what other peoples experiences are with creating and managing Hierachies.

As i see it you can store the hierarchy relationship using the dimension surrogate keys however this can cause an explosion in terms of data if someone at the top of the tree regularly has a lot of type 2 changes as this will obvioulsy create a new record with a new SK then obvioulsy those below them will need new records as their ReportsTo value will have changed and so on and so forth.

so i wonder if people model the hierachy using natural keys instead so that a new record is created only when the fundamental person has changed rather than a specific detail about that person.

I suppose it comes down to what the business needs to know (and we'll need to ask them) ie whether they need to know that Employee1 reported to Manager1 at Location1 even though Manager1 has now moved to Location2. Or whether the knowledge that Employee1 reports to Manager1 is sufficient.

If people use the natural key, then in their cube (SSAS in my case) i presume you would use a view for the employee dimension which would retrieve the SurrogateKey of the Current record for their managers NaturalKey?

So if their managers has type 2 changes then this change in SK will be handled in the cube rather than additional data in the DW being created.

Does that sound like a reasonable wasy of implementing it?

If people use Natural keys then would they store these in a seperate bridging table or within the employee dimension itself?

Thanks as always for any advice

Add new rows when there are changes in columns that you want to track. If you don't need to retain history, then define the column as Type 1. I tend to add new rows when there are new codes but when the description of the code changes, I will update the descriptions.

Always use surrogate keys.

There is a technique for maintaining a type 1 key in a type 2 dimension that has been discussed at length in the past. You could use that in the hierarchy bridge.

One thing I've wondered about (although never had the chance to do) with regards to organisational structure hierarchies in particular, is whether it is better to depersonalise it. If the roles in the organisation don't change as often as the people in the roles do, then model the hierarchy based on the roles and use a bridge table to the employee dimension to record who was in each role at any particular time.

This should reduce the number of cascading Type 2 changes, although it could increase the complexity of code in the reporting/analysis layer.

I've had success with a hybrid approach.

As ngallemo suggests, I use my surrogate keys to propulate my hierarchy table.

Assuming your bridge has parent & child, for each I also include the natural key (say employee ID) and the current value of the label desired for the hierarchy node (e.g. Employee Name).

If other attributes are required, then the dimension can be snowflaked off of the bridge .... use the surrogate key if you want historical attribute values, use the natural key if you want current attribute values (assuming your dimension has a current record flag).

Dave Jermy wrote:One thing I've wondered about (although never had the chance to do) with regards to organisational structure hierarchies in particular, is whether it is better to depersonalise it. If the roles in the organisation don't change as often as the people in the roles do, then model the hierarchy based on the roles and use a bridge table to the employee dimension to record who was in each role at any particular time.

This should reduce the number of cascading Type 2 changes, although it could increase the complexity of code in the reporting/analysis layer.

Many ERP systems set things up that way. Personnel hierarchies are often based on position rather than the person in the position.