Dimension hierarchies having One child multiple parent

View previous topic View next topic Go down

Dimension hierarchies having One child multiple parent

Post  arnayj on Thu May 23, 2013 2:14 pm

It getting difficult for me to handle this scenario that one child belongs to different parents as shown in e.g. below.
Any pointers to solve this kind of model so that we are getting correct sum values using hierarchies will be highly appreciated.

RootParent =>
Parent1 =>
Child1
Child2
Child3
Parent2 =>
Child1
Child10
Parent3 =>
Child2
Child20
Child21

arnayj

Posts : 5
Join date : 2011-03-01

View user profile

Back to top Go down

Re: Dimension hierarchies having One child multiple parent

Post  ngalemmo on Thu May 23, 2013 2:49 pm

You need to carry some form of a weighting factor in the hierarchy structure to allocate values of the children to their respective parents.

avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Dimension hierarchies having One child multiple parent

Post  Mike Honey on Thu May 23, 2013 9:10 pm

This challenge is best solved with a cube technology eg SQL Server Analysis Services.

I would build a bridge (view or table) (e.g. "Bridge") which represents all the intermediate relationships between children and parents. I would add that to the SSAS cube design. I would create a dimension (e.g. "Hierarchy") where the key is the concatenation of Child and Parent Keys, which would join directly to the new bridge. You also need a dimension (e.g. "Children") where the key is just the Child Key.

For the dimension usage, "Bridge" is related directly to "Hierarchy" and "Children". Other Facts are related directly to "Children", but related via a Many-to-Many relationship to "Hierarchy".

This solution savoids the issue of double-counting - SSAS correctly aggregates any Other Facts at every level of "Hierarchy".

You can output via Excel Pivot Table, SSRS report (using Aggregate function) or any tool capable of an MDX query.

Good luck!
Mike
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Dimension hierarchies having One child multiple parent

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