New Approaches to Variable Depth Hierarchies?

View previous topic View next topic Go down

New Approaches to Variable Depth Hierarchies?

Post  robfb on Fri Apr 09, 2010 6:10 pm

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.

robfb

Posts : 11
Join date : 2010-03-18

View user profile

Back to top Go down

Re: New Approaches to Variable Depth Hierarchies?

Post  ngalemmo on Fri Apr 09, 2010 7:08 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: New Approaches to Variable Depth Hierarchies?

Post  robfb on Tue Apr 13, 2010 9:47 am

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...

robfb

Posts : 11
Join date : 2010-03-18

View user profile

Back to top Go down

Re: New Approaches to Variable Depth Hierarchies?

Post  ngalemmo on Tue Apr 13, 2010 11:36 am

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'.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: New Approaches to Variable Depth Hierarchies?

Post  Mederik on Fri Mar 30, 2012 12:49 pm

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

Mederik

Posts : 2
Join date : 2012-03-30
Age : 44
Location : Paris - France

View user profile

Back to top Go down

Re: New Approaches to Variable Depth Hierarchies?

Post  ngalemmo on Fri Mar 30, 2012 1:11 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: New Approaches to Variable Depth Hierarchies?

Post  John Simon on Sat Mar 31, 2012 2:01 am

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.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: New Approaches to Variable Depth Hierarchies?

Post  Mederik on Mon Apr 02, 2012 4:44 am

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

Mederik

Posts : 2
Join date : 2012-03-30
Age : 44
Location : Paris - France

View user profile

Back to top Go down

Re: New Approaches to Variable Depth Hierarchies?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum