How best to handle hierarchy defined in operational system.

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,

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

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,

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

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,