Data Warehousing and Business Intelligence

30 March 2011

Ragged Hierarchy in SSAS

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

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

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.

 

About these ads

4 Comments »

  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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 187 other followers

%d bloggers like this: