May 12, 2009 at 12:06 pm
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
May 12, 2009 at 4:30 pm
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.
May 13, 2009 at 9:23 am
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
December 11, 2010 at 12:40 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply