Unbalance / ragged hierarchies
-
I am attempting to build a financial application with a GL Account dimension with an unbalanced hierarchy but I cannot get the "missed" or unused hierachy levels to be hidden.
I am building the dimension from an Account dimension table that for each GL Account has the hierarchy defined in columns for GL_Rollup_1 thru GL_Rollup_8. Some accounts have rollups defined thru level 3 or 4; others have rollups defined all the way thru 8.
I have set the HideMemberIf property of each of the hierarchy levels to NoName, but I still have all of the intervening empty levels defined for any accounts that do not go all the way thru level 8.
-
How is your unbalanced hierarchy setup? Is the account number always at the same level or does it appear at the level immediately following it's parent?
Either way, it should display correctly in Analysis Manager. I've run into a few cases where certain front-end tools seem to ignore the hidden member settings. What are you using to display the data?
David
-
As background, this is in SSAS 2005, and I am building and displaying thru BIDS.
As a general schematic of the dimension table:
GL_Account_Key GL_Account_Name GL_Rollup_1 GL_Rollup_2 GL_Rollup_3 GL_Rollup_4 ... GL_Rollup_8
101 1001 Cash Balance Sheet Cash
102 4000 Fee Income Net Income Revenue Operarting Income Fees
So, the account number is always at the same level - from the Account Name. The hierarhcy is builtform the rollups, with a number of the rollups null if it is not needed. In the case of the first account, rollups 3 thru 8 are null. In the hierarchy, I see Balance Sheet followed by Cash; but then I see empty summaries for all of the 3 thru 8 rollups before I can see the detail accounts beneath it.
And, as I mentioned, I am seeing this display when browsing the dimension as well as browsing the cube.
Any suggestions would be appreciated.
Ed
-
Ok... so the problem is that your levels are built as follows:
Rollup1
|-----Rollup2
|-----Rollup3
|-----Rollup4
|-----Rollup5
...
|-----Rollup8
|---- Account Level
I've experienced this same problem before... but in MSAS 2000, the levels are hidden in the dimension browser. However, our front end tool did not hide them.
What I had to do was add the account number to the rollup fields (you still want to leave it in the account key and name fields so you can easily run queries).
So, if you do this, I think your problem will be solved:
GL_Account_Key GL_Account_Name GL_Rollup_1 GL_Rollup_2 GL_Rollup_3 GL_Rollup_4 ... GL_Rollup_8
101 1001 Cash Balance Sheet Cash 1001 Cash
102 4000 Fee Income Net Income Revenue Operarting Income Fees 4000 Fee Income
Then, in the dimension, you will remove the last level for the gl_account_name, leaving only GL_Rollup_1 through 8 (though you may need to add another rollup field)
Let me know if you need clarification. From my experiences this does work, but be aware that I have not done much of this with MSAS 2005 yet. The main advantage is that there are no "in-between" levels that need to be hidden and that is what seems to cause this problem.
David
-
David,
Thanks for the suggestion. I will modify the dimension table and set it up that way. But I understand what you're saying - essentially, because I have the account name as the lowest level in the user hierarchy, it can't suppress the intervening layers.
I'll let you know.
Ed
-
David,
Thanks for the help! It worked. I now have a nice clean view of the hierarchy structrue I need.
Ed
-
Glad I could help!
-
Hi,
I'm also new to SSAS(2005) and I have the same problem with my GL Account Dimension Hierarchy. I have level1, level2.....level6 and the Account code as the lowest level. Level3 to level6 have some null values and using the hidememberif property has not worked. I've tried to make sense of the spolution profered above but can't seem to grasp it entirely. Your assistance is needed please.
-
I've been able to get a solution to the problem. For the rugged hierarchy i just added the mdx compatibility property in the connection string of the datasource in SSAS, then set it to 2.
This way all empty members are hidden in the client application which is proclarity in my case although they still show up in SSAS but this is fine for me. Thanks
-
Glad you found a solution. Sorry if my solution didn't make much sense.
Essentially, when building the dimension, you cannot have empty cells between a level and the account number. But it sounds like your fix works ok, though it may not work with other client tools.
-
I am faced with the same problem. I created a hierarchy where sometimes the value of some levels in null ie:
Cust->AcctLvl1->AcctLvl2->AcctLvl3->AcctNum
Cust->AcctLvl1->AcctLvl2->------>AcctNum
I set HideMemberIf to NoName but no luck. Any solution to this issue?
Thanks
-
Idris Ogueboule (6/22/2009)
I am faced with the same problem. I created a hierarchy where sometimes the value of some levels in null ie:Cust->AcctLvl1->AcctLvl2->AcctLvl3->AcctNum
Cust->AcctLvl1->AcctLvl2->------>AcctNum
I set HideMemberIf to NoName but no luck. Any solution to this issue?
Thanks
The easiest way to accomplish this is to always put the account number in the "AcctLvl" field immediately following the last level and remove the "AcctNum" field as a level in MSAS.
So,
Cust->AcctLvl1->AcctLvl2->AcctLvl3->AcctLvl4 (AcctLvl4 has your Account Number)
Cust->AcctLvl1->AcctLvl2->AcctLvl3 (AcctLvl3 has your account number)
Of course you will still need to have the AcctNum field in your table, butfor the purpose of your dimesion, it is not needed by MSAS.
-
The number of actual levels is unknow when the dimension is processed. we only know that there are 6 levels at the most. What I am trying to do is to skip the levels that are null and go to the lowest one which is acctNum.
The data is in this format:
Cust Lv1 Lv2 Lv3 AcctNum
1 A AB ABC 1000
1 A AC NULL 2000
The idea is to get this hierarchy:
1->
+A->
+AB->
+ABC->
1000
+AC->
2000
so that any level that is null should not be seen or be removed. Please advise!
Thanks
-
Idris Ogueboule (6/22/2009)
The number of actual levels is unknow when the dimension is processed. we only know that there are 6 levels at the most. What I am trying to do is to skip the levels that are null and go to the lowest one which is acctNum.The data is in this format:
Cust Lv1 Lv2 Lv3 AcctNum
1 A AB ABC 1000
1 A AC NULL 2000
The idea is to get this hierarchy:
1->
+A->
+AB->
+ABC->
1000
+AC->
2000
so that any level that is null should not be seen or be removed. Please advise!
Thanks
The only solution is what I advised above... you cannot have Nulls in between the last level and your account number. Since you have a maximum of 6 levels (as possible parents to account numbers) then create 7 level fields in your table. You will have to build your hierarchy table like this according to your above sample:
lvl1______ lvl2_______lvl3_______lvl4_______lvl5_______lvl6_______lvl7_______acctnumber
1________+A_______+AB_______+ABC_____1000_______NULL_______NULL____1000
1________+A_______+AC_______2000______NULL______NULL_______NULL____2000
Note there are no Nulls between the last level and your account number which is stored in the "level" fields. In the Dimension, your levels are defined as lvl1, lvl2, lvl3, lvl4, lvl5, lvl6, lvl7. The "Acctnumber" field will not be a level in the dimension.
Building the dimension in this manner will allow the null values after the account number to be hidden.
I hope that makes more sense.
-
I did something like this recently. What i ended up doing was 'pushing' the leaf node values all the way down. So say it's at level3 currently, I effectively put the same value in L4, L5 and L6 (i knew there was a max of 6). By pushing them down, and then setting the 'show' properties to hide if only child and same as parent, this now displays as per a parent-child hierarchy (ie it appears ragged) yet allows us to support multiple ragged hierarchies whereas a dim will support only 1 p-c hierarchy normally.
HTH,
Steve.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply