Multi Level Dimension

[Guest_imported]

Hi,

I am looking at design on warehouse for corporate expanses and budgets reports. We need to slice and dice the data based on our corporate organization hierarchy. The problem I have that our hierarchy is a multi depth, so depending on the area the detail would be at different level in the hierarchy tree.

Any suggestion how to model this type of hierarchy as a dimension for start schema?

Thanks
John

 

[yaffle]

I would suggest that you model the hierarchies to correctly reflect the business relationships. What you will have to consider with this sort of 'uneven' data, is how you structure your information.

One option may be to generate 'dummy' key entries to represent aggregations/unavailability of lower level data. E.g. if your organisation hierarchy consists of a head office, some regional offices and some local offices, but you only incur or record certain expenses at regional level not local level, you might have a dimension records something like:[tt]
internal_key head_office region local_office
------------ ----------- -------- ------------
37 National West ALL LOCAL
38 National West REGION ONLY
[/tt]

The internal_key would be used to link in your main fact table with the organisation branch if you are using a star-schema database design; alternatively you may wish to use a multi-dimensional database (MDDB), which could probably handle this for you, and work directly from your aggregate labels.