Multiple hierarhy in a dimension

View previous topic View next topic Go down

Multiple hierarhy in a dimension

Post  Steven_1999 on Tue Jul 17, 2012 3:04 pm

Hi all,
I have a Gl_balances tables that hold monthly snap shot of GL account. This table is linked to Gl_account table which holds the GL flattened hierarchy by Gl_account_key.


Layout og GL_balances:

Gl_account_key Month_year Amount
9001 06/2012 400,000
9002 06/2012 125,000
9003 06/2012 50,000

Layout of Gl_account:

Gl_account_key account_level1 account_level2 account_level3 account_level4 account_name
9001 P&L Net Income Revenue Tire Sales New Tire Sale
9002 P&L Net Income Expense Administration Rent
9003 P&L Net Income Expense HR Training

and so on ...

Now business wants to create a new hierarchy to move accounts from one level to another, for example move training from HR to Administration. While keep the existing hiwerarchy in place.

Any suggestions on implementing this change.
Thanks
Steven

Steven_1999

Posts : 2
Join date : 2012-07-17

View user profile

Back to top Go down

Re: Multiple hierarhy in a dimension

Post  ngalemmo on Tue Jul 17, 2012 4:16 pm

The easiest thing to do is add a hierarchy ID code on the flat hierarchy table and use it to filter which hierarchy to apply.

Or build a second hierarchy table.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

RE: Multiple hierarhy in a dimension

Post  Steven_1999 on Thu Jul 19, 2012 11:04 am

Not sure how the 2 tables will be linked together since I will have 2 records in the account flat table one with hier_id=1 and one with hier_id=2 for same account account_key 9001 for example which suppose to the primary unique key linked to Gl_balances by account_key.
Thanks

Steven_1999

Posts : 2
Join date : 2012-07-17

View user profile

Back to top Go down

Re: Multiple hierarhy in a dimension

Post  ngalemmo on Thu Jul 19, 2012 12:02 pm

The user would need to select which of the two hierarchies to use. Going with two separate hierarchy tables (with the same PKs) makes it more explicit. Another approach would be to have columns for both hierarchies in the same table and have the user pick the correct ones, although that would probably be the most confusing.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Multiple hierarhy in a dimension

Post  sgudavalli on Fri Jul 20, 2012 6:38 am

as ngalemmo said;
you have pretty much flattened the account dimension and its hierarchy information;

now you may add new columns in the account table to represent the updated hierarchy information. that way you dont need to mess up your balanaces; just update the new hierarchy for each account...

Gl_account_key account_level1 account_level2 account_level3 account_level4 account_name, new_account_level1, new_account_level2, new_account_level3, new_account_level4

9001 P&L Net Income Revenue Tire Sales New Tire Sale Net Income Revenue Tire Sales New Tire
9002 P&L Net Income Expense Administration Rent Net Income Expense Administration
9003 P&L Net Income Expense HR Training Net Income Expense Administration

and if this behavior is expected to continue; better model for type 2 changes...


sgudavalli

Posts : 29
Join date : 2010-06-10
Age : 33
Location : Pune, India

View user profile

Back to top Go down

Re: Multiple hierarhy in a dimension

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