This has been causing me a lot of trouble. Hopefully someone can help me out... A problem description follows the diagrams.
[Diagram 1 - what users should see as navigation]
Accounts
|
--Trial Balance
|
--Account#1
--Account#2
--AccountGrouping#1
|
--Account#3
--Account#4
--AcountGrouping#2
|
--Account#5
--Account#6
--Account#7
--Account#8
|
--BalanceSheet
|
--Account#9
--Account#10
--AccountGrouping#3
|
--Account#11
--Account#12
--AccountGrouping#1
|
--Account#3
--Account#4
[Diagram 2 - table layout]
PKID AccountID Description ParentAccountID
1 1 Accounts
2 2 Trial Balance 1
3 3 Account#1 2
4 4 Account#2 2
5 5 AccountingGroup#1 2
6 6 AccountingGroup#2 2
7 7 Account#3 5
8 8 Account#4 5
9 9 Account#5 6
10 10 Account#6 6
11 11 Account#7 2
12 12 Account#8 2
13 13 BalanceSheet 1
14 14 Account#9 13
15 15 Account#10 13
16 16 AccountGrouping#3 13
17 17 Account#11 16
18 18 Account#12 16
19 5 AccountGrouping#1 16
Please note from diagram 1 that AccountingGroup#1, and subsequently Account#3 and #4 are the SAME accounts that have a place in two different rollup hierarchies.
Please note from diagram 2 that this is an "unbalanced (variable depth) hierarchy" with an FK(ParentAccountID) to PK(AccountID) relationship that defines the rollups. Also, please note that "AccountingGroup#1" (AccountID=5) has a different PKID for table integrity, but the AccountID is used to place the account group in multiple rollups. Subsequently, it was my expectations that the accounts defined beneath the group should come through with it.
Now for the problem - when I build the cube, the parent-child hierarchy dimension will place AccountGrouping#1 (and its accounts) under trial balance, but NOT under Balance sheet. I guess this is the case because it was defined under Trial Balance first.
Anyway, I'm hoping there is someone else out there that has "shared" attributes within a hierarchy and can tell me how to get this to work - since my idea obviously does not work.
THanks in advance
Ryan
Same Member - multiple rollups (unbalanced hierarchy)
Stimo
Try to incorporate the dimension grouping table's key into a view against the fact table. You will could use a custom rollup formula if you want to show an 'all' member, because there will be duplicates.
Something like
select dim_grouping_table_key, ... from fact_table
inner join dim_table on fact_table_key = dim_table_key
inner join dim_grouping_table on dimension_grouping_table_key = dim_table_key
Then you would replace your fact table with this view, and in the granularity of the cube you would link on the grouping table's key.
Not sure if this will work in your scenario, but it did resolve our issue of having the same category in multiple locations.