Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2011-04-22   |   Comments (3)   |   Related Tips: > Analysis Services Dimensions

Problem

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.

Solution

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.


Last Updated: 2011-04-22


next webcast button


next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, April 02, 2013 - 12:19:15 PM - Ralph Back To Top

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 )

END SCOPE

 

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

Thanks for the tip!


Friday, May 06, 2011 - 2:04:58 PM - Naveen Das Back To Top

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).


Learn more about SQL Server tools