Remove parent as a child from a SQL Server Analysis Services SSAS parent child dimension

By:   |   Comments (3)   |   Related: > Analysis Services Dimensions


In a parent child dimension, when an attribute's usage type is set as Parent and browsed from a client tool, many designers as well as users may get confused based on the displayed results. For example, when you browse a parent attribute in a parent child dimension like Employee and say the parent attribute is Employees, you can browse data members from the parent node until the deepest available level of the child node. By default you will find each parent member having a child member with the same name even though this member might not exist in the data. From a user experience point of view this is not a desirable way of browsing a parent child hierarchy because it may get confusing. In this tip we will look at an example of this issue and how to solve this problem.


The Parent attribute in a parent-child dimension is a special kind of attribute. To visualize the problem, we need to a have a parent child dimension with a parent attribute. For the purpose of this tip, we will use the AdventureWorks sample that ships with SQL Server. Open this solution and deploy it and then browse the Employees attribute of the Employee dimension. Employees is an attribute whose usage type is set to "Parent" in the Employee dimension. When you browse this attribute, you will find the properties as shown in the below screenshot.

sql server parent chlid dimension

As you can see below the parent member is also available as a child member and this has the potential to confuse users.

It would be beneficial to understand the reason for this data representation before we proceed with the solution. Say a manager has two employees and all of them have their own sales targets. As per the hierarchy, the manager should be shown as a parent node and both employees should be displayed as child nodes. From an aggregation point of view, both these employees data can roll up to their manager, but manager is also an employee and can have his own targets. So the final data value associated with manager would be the roll up value of both employees as well as his own individual data value. This is the reason SSAS adds a system generated data member which is the parent member itself in the hierarchy. This has no effect on aggregation, but the intention is to preserve the individuality of the parent node in the display of the hierarchy.

ssas adds a system generated data member

In the below screenshot, you can see the setting for the Employees attribute. The above mentioned issue is a result of a property named "MembersWithData" which has a default value of "NonLeafDataVisible".

Our intention is to hide this system generated data member, so that when the hierarchy is browsed, the parent attribute is not shown as a child member to itself. To change this, modify the value of "MembersWithData" to "NonLeafDataHidden" as shown in the below screenshot.

modify the value of members with data

After changing this value, process the dimension and deploy it. Connect to the cube and browse the parent attribute of the "Employee" dimension. This time when you browse this attribute, you will not find the parent nodes displaying themselves as a child to itself as shown in the below screenshot.

in ssas browse the parent attribute of the employee demension

Next Steps
  • Check out how many attributes can be configured as a parent attribute in a parent child dimension.
  • Use MembersWithDataCaption property to make the system generated data members stand out explicitly to avoid confusion.
  • Read these related SSAS tips.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, April 2, 2013 - 12:19:15 PM - Ralph Back To Top (23117)

I have a situation where the data in the data members is not desirable in the aggregation. I need the parent value to be just the sum of it's children. In the example above this would mean excluding the manager's own sales target from the result. 

I do not want to remove the data from the underlying database nor exclude it in a view or cube partition query etc.

I have been trying to use SCOPE in the MDX script as follows...

SCOPE ( mydimension.members, ....)

    This = IIF (

           NOT ISLEAF (mydimension.myhierarchy.currentmember) ,

           mydimension.myhierarchy.currentmember - mydimension.myhierarchy.currentmember.datamember,

           mydimension.myhierarchy.currentmember )



However, this doesn't seem to work. All the values end up as zero. I have hidden the data members and am using the 2005 product. 

Any thoughts?


Tuesday, March 19, 2013 - 6:11:36 AM - Yvette Back To Top (22863)

Thanks for the tip!

Friday, May 6, 2011 - 2:04:58 PM - Naveen Das Back To Top (13770)

Good post Siddharth...

To be noted is that as you mentioned, the manager's personal sales target will not be shown in this case. The aggregation will work and the sales target at the manager level will include their own personal sales target (though not visible).

get free sql tips
agree to terms