Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Design for Alternate Hierarchies on Parent-Child Dimension SSAS 2K5? Expand / Collapse
Author
Message
Posted Tuesday, May 12, 2009 12:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 13, 2013 9:14 AM
Points: 2, Visits: 81
Hello and thank you in advance for any guidance!

Currently, I'm working on a budgeting cube design and have been faced with a new design requirement. I have an "Account" dimension (parent-child) with a default hierarchy. Each record in the dim_Account table has an "dim_account_key" (PK), and a "parent_key". Each account's parent in the default hierarchy is determined by it's parent_key value.

My task now is to allow for "alternate hierarchies" so that the account dimension can be browsed from different user perspectives (i.e. Finance may want to browse data based on the default hierarchy, while another dept may want it's own rollup of accounts).

Does anyone know the best practices for accomplishing this as far as dim table design and/or use of MSAS? Currently, I'm considering adding additional parent_key (parent_key_2, parent_key_3, ... parent_key_n) columns to the dim_account table, and then using the additional parent_key column to create new parent-child hierarchies.

This is totally new to me, and I'm wondering if there is a better approach that this?

Thanks!
Mark G
Post #715349
Posted Tuesday, May 12, 2009 4:30 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Yesterday @ 9:32 AM
Points: 1,835, Visits: 3,540
Hey Mark,

I've been working on an extremely similar problem. The issue with simply adding additional Parent_Keys is that each dimension in SSAS will allow only a single P-C hierarchy to be defined. So, you can go and create these, but you'd have to create a new, fully independent 'Account' dimension (yes, the full dimension) to house each of the additional PC hierarchies.

An alternate approach is to flatten the PC hierarchies out, effectively taking a self-referencing row-based approach and making it in to a column based approach. This then makes your attributes the same as any other user hierarchy. There are still some gotchas in this approach (for e.g. where the hierarchy is ragged, I am pushing the lowest child node value all the way through to the leaf level), but in general, will allow you to have multiple hierarchies in the dimension.



Steve.
Post #715532
Posted Wednesday, May 13, 2009 9:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 13, 2013 9:14 AM
Points: 2, Visits: 81
Hi Steve,

Thanks for the advice. I had a feeling as soon as I posted the message that AS might not allow more than one PC hierarchy per dimension, should've done some research on that first ... but your response was pretty helpful. I'm going to explore that approach, only concern being making a determination of how many alternate hierarchies I'll make available through addt'l columns in my dim table.

Thanks again for the response!
Mark G
Post #716117
Posted Saturday, December 11, 2010 12:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, May 4, 2013 2:28 PM
Points: 18, Visits: 31
Hi Mark,
You might want to use Bids Helper to flatten your parent child dimension. Then you can add more columns or tables to use alternate hierarchies. Bids Helper does that for you.

The trick is to set AS dimension hierarchy level property (Hide Member) = if child equals parent.

Post #1033366
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse