Data Warehousing and Data Science

30 March 2011

Ragged Hierarchy in SSAS

Filed under: Analysis Services — Vincent Rainardi @ 6:08 pm

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:

Parent nodes are displayed as child nodes

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:

Cube Design


Browse in Excel:

Browse in Excel - Field ListBrowse in Excel - Columns and RowsBrowse In Excel

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



  1. 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 | Reply

    • 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 | Reply

  2. Have you tried to create a report from this?
    I think the downside is that you can’t. Pls try.

    Comment by Ken — 2 November 2013 @ 7:39 pm | Reply

    • Thank you for your comment Ken. What kind of report are you trying to create?

      Comment by Vincent Rainardi — 3 November 2013 @ 9:03 am | Reply

  3. This was great and helped show us how to do a ragged hierarchy nicely. But when showing factual data which in our case can be tied at any level of the hierarchy, it works better to set MembersWithData to NonLeafDataVisible. SSAS will then only show the “parent” node under itself if it has data.

    Comment by Eric Knapp — 5 September 2014 @ 2:21 pm | Reply

  4. How would you handle multiple properties associated with the parent-child? AKA showing a CompanyName, CompanyDescription, and CompanyType instead of just CompanyName?

    Comment by Byron R — 2 February 2018 @ 4:48 pm | Reply

    • Same as above Byron, i.e. in the Company table add CompanyDescription and CompanyType columns, add those 2 columns to the DimRaggedHierarchy in the DSV, and add them to the Dim Ragged Hierarchy dimension as 2 new attributes. In Excel pivot table you can then put those 2 attributes into the Row Labels.

      Comment by Vincent Rainardi — 2 February 2018 @ 6:51 pm | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at

%d bloggers like this: