How best to handle hierarchy defined in operational system.

View previous topic View next topic Go down

How best to handle hierarchy defined in operational system.

Post  mru22 on Thu Jul 07, 2011 3:52 pm

In one of my sql server databases I have a tables Called CostCenter and Division. A Division can have many Cost Centers.

One of the Columns in the Cost Center table is a HierarchyId Datatype Called FullPath to represent the path since there is one cost center that all other cost centers ultimately report to and a cost center can only have one direct parent.

Since a cost center might be as many as 8 levels deep using the HierarchyId works very well and is fast for data access rather than going the recursive key relationship design.

I added Computed Columns for "FullPathString", and "PathLevel". The PathLevel for example lets me know if a cost center is 4 levels or 5 levels deep.

There are custom Reports that were previously done in Cognos that hardcoded cost centers to display on reports at certain levels. For example one report might look like the following:

Customer Division 150
__Customer Services 100
____Commercial Service 50
Power And Generation 200
____Electrical Power 12
____Generated Power 9
________System Engineering 122
________Engineering Design

As you can see the lines are drawn to represent indenctions so Customer Service for example is level two and System Engineering is level three.

The kicker is that in certain canned reports some cost centers are displayed as individual line items while others rolled up and then another report they might all be rolled up to a the division.

I created a CostCenterReport table in the operational system to manage how cost centers are displayed for each report. I brought that table into the datamart as a "CostCenterReport" dimension which forces me to have snowflake off the Cost Center Dimension.

I was wondering if there would be a better dimension design to handle display preferences for Canned reports. I was reading in the Kimball book how a Many to Many can be created between the dimension and fact to handle hierarchies but didnt think it was very applicable here.

Any thoughts on how to better architect display prefernces for these cost centers based on the report selected ?

Thank you,

mru22

Posts : 34
Join date : 2011-06-14

View user profile

Back to top Go down

Re: How best to handle hierarchy defined in operational system.

Post  hang on Sat Aug 13, 2011 5:59 pm

Dealing with multi level hierarchy can be tricky. The best article on this topic is written by Joy Mundy:
http://www.informationweek.com/news/software/bi/219400252?queryText=%22kimball+university%22

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: How best to handle hierarchy defined in operational system.

Post  mru22 on Mon Aug 15, 2011 5:23 pm

I am trying to implement the Variable Depth Hierarchy as described in the Kimball book. I think that seems to be the best option for my needs so now I just need to figure out how best to implement in SSAS.

Thanks,

mru22

Posts : 34
Join date : 2011-06-14

View user profile

Back to top Go down

Re: How best to handle hierarchy defined in operational system.

Post  John Simon on Mon Aug 15, 2011 7:46 pm

The Kimball bridge table for hierarchies is useful if you are using SQL for your reporting. If you are using SSAS, then you can keep the parent-child hierarchy and use within the SSAS dimension, or flatten the hierarchy out into levels first (see BIDS Helper or my blog for examples).

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: How best to handle hierarchy defined in operational system.

Post  mru22 on Tue Aug 16, 2011 4:58 pm

I dont have a regular Parent/Child hierarchy simply because i have hierarchyId as the column which is a sql server data type.

I created the Bridge table since SSAS doesnt support the HierarchyID. So after creating the bridge table, I was told that a view would be needed between the Parent Dimension and Bridge Dimension

But then I need a reference from the fact table to the Bridge table and wasnt sure how that would work because when I tried processing the cube i received and error that there are duplicates.

Any suggestions on using the Bridge in SSAS without Bids helper for now.

Thanks,

mru22

Posts : 34
Join date : 2011-06-14

View user profile

Back to top Go down

Re: How best to handle hierarchy defined in operational system.

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