New Approaches to Variable Depth Hierarchies?

I was wondering what options I might have in dealing with variable depth hierarchies.

For example, I have an organization dimension where a business unit rolls up into a parent, which could then roll-up into one or more additional parents. Also, I have a dimension for financial statement dimension where customer sales & intercompany sales roll into total net sales, which is a part of gross profit, which is a part of operating profit,..., to total Net Income, then I have balance sheet rows for cash>current assets>total assets, etc.

I found Design Tip #17, which states to do an Exploding BOM type approach. Such an approach I'm sure would work, but would also be somewhat unwieldy. I was wondering, since that article was written almost a decade ago, if there are any newer or - dare I say - more elegant approaches. I am working specifically in SQL Server 2005 SSAS.

Nope. There are three ways to go:

1. A flattened hierarchy, which works best with a fixed depth structured hierarchy, but is also useful for ragged hierachies that are not too deep.

2. A recursive parent/child structure which is not easily queried using SQL (although some BI tools have special features to support such tables).

3. An exploded hirearchy, which is the simplest and most flexible way to go from a query perspective for ragged unstructured hierarchies. It covers every possible relationship and allows you to query from any level to any other level in the heirarchy without needing to do recursion.

It hasn't changed because, well, they are just data structures modeling very basic relationships.

I guess this is an example of the old adage, "It is what it is."

Could you elaborate a little on what you mean by a flattened hiearchy? Does that mean you have field names like Child, Parent1, Parent2, Parent3, Parent4, etc.?

For example, if you had this kind of hierarchy:

Corvette > Chevrolet > General Motors > Car Manufacturers
Suburban > General Motors > Car Manufacturers
Pilot > Honda > Car Manufacturers

Would a flattened hierarchy look like this?

Corvette, Chevrolet, General Motors, Car Manufacturers
Suburban, General Motors, Car Manufacturers, Car Manufacturers
Pilot, Honda, Car Manufacturers, Car Manufacturers

It seems that would also be tough to navigate...

Yes, and it can be difficult if it is not a structured hierarchy. It is typically used where there is a known, fixed structure to the levels, such as 'Company', 'Division', 'Department', 'Cost Center'.

Hy, I was just browing in search of any litterature concerning my unbalanced dimension problem i'm trying to solve. I have a client dimension rather deep (up to 12 levels...), quite big (around 90000 different clients) and of course unbalanced. The leafs are not all at the 10th level; some are at the 1st, others at the 6th and so one...Great.

I have to model a relational star schema to be used in Cognos 8.4 and to my knowledge it doesn't like very much recursive (parent-child) hierarchy so I thought to flatten my hierarchy by creating "dummy" members where they are missing in order to transforme my dimension into a balanced hierachy but I don't know why I don't feel like it.

When I read this :

An exploded hirearchy, which is the simplest and most flexible way to go from a query perspective for ragged unstructured hierarchies. It covers every possible relationship and allows you to query from any level to any other level in the heirarchy without needing to do recursion.

...

I don't know what is an exploded hierarchy and how to implement it. Do you mean by exploded hierarchy to create as much parallel hierarchy as there are different levels...?!? Could you explain please ?

Thank you for your help and advices.

Mederik

An exploded hierarchy (aka hierarchy bridge table) is a vertical structure with every possible parent-child relationship derived from a recursive hierarchy structure. Its covered in Toolkit.

Table columns include: parent key, child key, parent level, distance between parent & child, and a leaf flag.

For example, if you had the following recursive parent/child pairs:

A/B
A/C
B/D

It would explode to (columns listed as described above):

A/A/1/0/n
A/B/1/1/n
A/C/1/1/y
A/D/1/2/y
B/B/2/0/n
B/D/2/1/y
C/C/2/0/y
D/D/3/0/y

Joining a fact FK to the child and the parent key to the dimension allows you to roll up measures to any parent in the heirarchy.

The best way to deal with this scenario is to flatten the hierarchy - it will also give the best performance.
For SSAS, if you have multiple parents then you need to essentially create a enumerated path e.g.

"Corvette > Chevrolet > General Motors > Car Manufacturers" would become:

Corvette.Chevrolet.General Motors.Car Manufacturers > Chevrolet.General Motors.Car Manufacturers > General Motors.Car Manufacturers > Car Manufacturers

This ensures each level knows the correct parent value. You can do this within SSAS by selecting a compound key at each level of the hierarchy.

Great !
Thanks for your help ! I'll do this
Mederik