Multiple hierarhy in a dimension

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

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.

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

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.

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