SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Design for Alternate Hierarchies on Parent-Child Dimension SSAS 2K5?


Design for Alternate Hierarchies on Parent-Child Dimension SSAS 2K5?

Author
Message
mark.gorczyca
mark.gorczyca
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 86
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
stevefromOZ
stevefromOZ
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: Moderators
Points: 9462 Visits: 3757
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.
mark.gorczyca
mark.gorczyca
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 86
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
ayavuz1
ayavuz1
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 32
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search