February 23, 2006 at 8:10 am
Problem description follows diagrams.
Accounts (logical view for user)
[Diagram 1]
|
--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]
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
February 23, 2006 at 12:16 pm
I'm not exactly sure I understand what you are trying to do, but I did run into something somewhat similar with hierarchies that vary in depth. While the parent-child was able to help in some cases, I had to either work with snowflaked dimensions through alias tables or create views to achieve what I wanted.
I do know that there are rules about custom roll-ups, specifically when dealing with shared dimensions, that will disrupt the roll-up of other dimensions (sounds like your problem). The only rule I can remember off the top of my head is that custom rollups will default to private dimensions before shared dimensions.
Sorry I can't be more helpful, but I wish you the best of luck!
February 23, 2006 at 3:46 pm
Chris - can you ellaborate on private and shared dimensions?
February 24, 2006 at 10:47 am
You can create customed members for AccountingGroup#1, Account#3 and #4 under the Balance Sheet of the Account dimension. Then provide a formula for each customed member so that it can point to the same account under the Trial Balance. For example, the customed member Account#3 (under Balance Sheet) will have a formula that points to the Account#3 (under Trial Balance).
In the fact table, you should not have any record that associates the Account#3(under Balance Sheet).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply