Dimension with variable structure

Hi,


I sometimes have to deal with this kind of problem and to be honest I don't really know what is the best solution.

Say you need to build an entity dimension that starts with the following hierarchy : Head Quarters > Regional Office > City office > District office.

Well fairly easy, you build a flattened hierarchy and starts linking your facts to the entity dimension. The problem arise when there is a structural change in the company organization. And the entity becomes something like : Head Quarter > National office > City Office > District Office > Block office. I just can't figure how to deal with such a scenario.

Can anyone give me some insight on it.


Thanks.

I you don't want to deal with schema and load changes when the hierarchy changes, don't store it as a flattened hierarchy.

Thank you