SSAS Parent Child Dimensions Part 1: The Properties

I’ve saved the best for last, so they say. Well, depending on how you look at it. Over the last week, I’ve blogged about all of the other sections in Analysis Services dimensions except for parent-child, so now it’s time to finish off our series. Parent-child dimensions in Analysis are equipped to handle the ragged and unbalanced hierarchies that the world gives us.You can get decent performance out of them, name your different levels, customize your calculations with unary operators, the works. Before we get into all that stuff, though, let’s look at just a basic parent-child hierarchy and it’s properties:

 

Sidebar: This is the employee dimension in AdventureWorks2012Multidimensional, if you want to follow along.

 

The parent child section of the dimension properties are shorter than some of the others (mainly advanced), and contain the following 5 properties:

image_thumb[3]

MembersWithData: There are 2 options for this property: NonLeafDataVisible and NonLeafDataHidden. NonLeafDataVisible means that if a member is also it’s parent, make it visible. NonLeafDataHidden is if it’s also it’s parent, make it invisible. A quick example shows it better, I think. Below is a snapshot of our employees dimension with the NonLeafDataVisible set. Notice that “Amy E. Alberts” is the manager of this department, but also appears under her employees:

image

 

However, if we set the property to “NonLeafDataHidden”, notice that she now only appears as she should, as the manager of the department:

image

 

MembersWithDataCaption: This setting will only have an affect if the MembersWithData property is set to NonLeafDataVisible. It tells Analysis Services what to name the member of the parent if it appears with its’ children in the hierarchy. It’s a free text field, and you can just type what you need. For example, typing in *(Parent) makes the following appear to our example above of “Amy E. Alberts”:

image

 

NamingTemplate: Now this property is pretty cool. Did you know that you can actually name the different levels of a parent-child hierarchy? Instead of the default “level 1, level 2”, etc., you can create your own naming template for the hierarchy. For example, if you click on the ellipses at the end of the property:

image

The default looks like:

image

However, you can rename each level of the hierarchy to call it what you want. This lets you write MDX against it easier, along with a host of other benefits. Below is the same AdventureWorks employee hierarchy, with new renamed levels:

image

 

RootMemberIf: This property tells AS when to consider a member a parent of it’s hierarchy. The choices are:

ParentIsBlank: Only keys in the dimension that have a parent key of null, zero, or empty string are the top most level in your parent-child hierarchy

ParentIsMissing: Only keys in the dimension where no key can be found are the top most level in your parent-child hierarchy

ParentIsSelf: Only keys in the dimension where the key is equal to itself are the top most level in your parent-child hierarchy

By default it’s set to “ParentIsBlankSelforMissing”,” which means essentially “any of the above”

UnaryOperatorColumn: This property tells AS what column in the dimension is the unary operator of the dimension. For those not familiar with what the unary column is/does, in your dimension you can specify a different mathematical calculation to be performed at each member of the hierarchy. It’s most commonly used in a budget/expense scenario. For example, I can have a column that says “if my attribute is income related, add it, otherwise do something else to it” (based on what you specify)

Advertisements

3 thoughts on “SSAS Parent Child Dimensions Part 1: The Properties”

  1. I am considering about how display my data. Maybe you will know how to do this. 🙂
    I have Parent Child Dimension and sometimes not every child has his own child. Let mi show an example: http://imgur.com/Ct4UaWj
    As you see I have just two values in Level05. And want I want? This: http://imgur.com/6p6nV49
    It’s mean – I want repeat parent as a child until last level column.
    Do you have any idea how to do this?
    Thanks in advance!

    1. Hi Monic,
      You should be able to do this by setting the “MembersWithData” property to “NonLeafDataVisible”. As long as your parent level attributes repeats in the source (reports to itself) it will appear in your reports.

      Chris

      1. Hi Chris!
        Thanks for your reply.
        Your solution is almost good. I mean it works – I see parent’s child as parent, but parent’s grandchild is still Total. And I want to be capable displaying parent as child to last showing Level in Dimension. Is it possible?

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