I've created an "Account" dimension using parent child relationships and added a unary operator to the dimension e.g. "+" and "-" to aggregate things like gross margin = sales - cost of sales.
The dimension hierarchy shows correct operators and levels (it is a ragged dimension) in the dimension browser, however when I go to the cube browser and place the hirearchy in the rows some of the members which have children cannot be "expanded" to see the children while others can. Some members when expanded show children which do not belong to them (i.e. different than what is shown in the dimension browser) and some of the values in the grid return #VALUE.
Also as diferent members are expanded it seems to effect the value of the fact data in the grid for other members i.e. expanding members lower in the dimension list will suddenly populate values in the grid which were #VALUE for members that were higher up in the dimesion list.
The odd thing is that while I can't tell for sure (since I can't see down to the leaf level on many of the members) the aggregates do seem to be applying the unary operator correctly.
Incorrect parent child aggregate values in SSAS browser
Jim Homminga
jcmikkelsen
Your account dimension needs to be set-up correctly. It can have a ragged hierarchy. Ensure that the sort order is correct. eg.
Key ParentKey Account Descr
1000 Null Gross Sales
2000 Null Advertising
2100 2000 TV Advertising
2200 2100 Magazine Adv.
etc.
When u drag the account hierarchy on Mgt Studio, you should see the top levels, 1000 and 2000. Then u can drill down into children of 2000.
Just to let u know there is a bug in backing up a cube db with one of the db files > 4gb. I have to partition the cube and try again. I have logged it on the forum here.
M3talh
Just to let you know that I am using the Account Dimension with success with 115m source records. I am using the unary operator + and ~ and custom members in certain cases.
Attribute names and dimension names caught me out earlier on as well.
Let me know if you need any info.
Sven De Bont
Again, thanks a million for the info. You were right, I was able to drag the account hierarchy on to Mgt Studio and drill down. I was also able to do the MDX query and return the fact data for the non leaf level (parent) members of the dimension.
I really appreciate your input.
Mark Costello
Thank you very much for your comment. I'm really glad to see that it is working with such a large data set.
May I ask how deep do the levels go in your account dimension and what storage mechanisim do you use ROLAP, HOLAP, MOLAP
I am finding that I when I do an MDX query in SQL Management Studio that I'm only able to return leaf level values not aggregates. I'm using the default (I believe MOLAP) storage mechanisim. Is this the way it is supposed to work I've worked with other OLAP tools for almost 20 years where Excel is used as the front end and have never had to worry about differenciating between leaf or non leaf data nor worry about preaggregating data in the cube. I'm a bit new to the MSAS world though so forgive me if I'm asking novice questions.
Thank you again for your insight.