Someone asked me how to do a ragged hierarchy in SSAS. I thought it was “natural” in SSAS, i.e. using a “parent column” in the table then make that column a “parent attribute” in a “parent child” dimension. But then somebody else asked me the same question and I realised that the issue was that by default we would see the parent node as a child node and to eliminate that we need to set the MembersWithData property to NonLeafDataHidden. Here’s the complete scenario.
This is what we want to achieve:
This is the source table:
We add that table to the DSV (data source view) and create a self-relationship from ParentKey to CompanyKey:
Then we create a dimension:
Then on the Parent Key attribute we set 2 properties: on the Usage to Parent and the MembersWithData to NonLeafDataHidden:
And the result is as expected:
Very nice – thank you. How did you get the CompanyName to be the MemberName instead of the ParentKey value?
Comment by Paul Goldy — 13 September 2011 @ 10:07 pm |
Hi Paul. By setting the NameColumn property of the Company Key attribute to CompanyName column (the KeyColumn property is CompanyKey column).
Comment by Vincent Rainardi — 13 September 2011 @ 10:32 pm |