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.
It seems that the problem has taken care of itself. In each of the dimensions I had an attribute with the same name as the dimension. By renaming and in some instances deleting the atributes with the same name as the dimension it now appears that the hierarchy works properly in the SSAS browser and returns the results I want.|||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.
|||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.
|||MOLAP is used as the storage mechanism. I have not optimized the aggregates and get reasonable performance on a quad processor with 8gb. The cube is 6.16gb. I have never tried other forms of storage as MOLAP is efficient. The db is 120gb. With aggregation, the cube size will increase.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.
|||
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.
