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: