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:

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:

 

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

 

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”:

 

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:

The default looks like:

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:

 

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)