Ragged Hierarchy in SSAS

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 like this:

To eliminate it 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:

create table Company (CompanyKey int, ParentKey int, CompanyName varchar(50))
insert into Company (CompanyKey, ParentKey, CompanyName) values
(1, NULL, ‘Company1’), (2, 1, ‘Company2’), (3, 1, ‘Company3’), (4, 2, ‘Company4’), (5, 4, ‘Company5’),
(6, 1, ‘Company6’), (7, 6, ‘Company7’), (8, 7, ‘Company8’), (9, 7, ‘Company9’)
select * from Company

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:

Let’s create a fact table, then see how it looks in Excel.

create table FactInvoice (FactInvoiceKey int, CompanyKey int, InvoiceAmount money)
insert into FactInvoice values (1,2,430), (2,2,120), (3,6,220), (4,6,250)
select * from FactInvoice

Create a cube:

 

Browse in Excel:

Company 1 is the parent of both Company 2 and 6.