Aggregating Member Properties

One of the underutilised features of dimensions in SSAS is the use of member properties. These properties can be used to store information about member attributes in the dimension and add an additional layer of information to dimensional design. The property is stored against the members and exposed only to that member level. For example, a property set a customer attribute would not be visible at the state despite the two members being related. Additionally, the properties are not accessible from other dimensions.

However, it is common to want to see the aggregation of such properties by other dimensions or, the aggregation of the properties with a hierarchy of the attribute dimension. For example consider a situation where an employee dimension may have a property of annual income and we wish to show the total annual income by department.

Since the design of a dimension in the UDM can take such an approach and this choice of design may be appropriate with a SCD2, it seems like a logical query to aggregate a dimension value by reference to some other dimension. There is certainly an argument for storing this information in a measure group, but in this situation, we will examine where the dimension member has the associated property.

Using Adventure Works, we examine how to aggregate the customer property of ‘Total Children’ across another dimension (Sales Area). Our business question may take the form of what are the “Total Children” for each country?


Member Properties

We can retrieve the number of children that a customer has through its properties. Defining the measure MemberChildren will do this;

MEMBER Measures. MemberChildren AS [Customer].[Customer].CURRENTMEMBER.Properties( “Total Children” )


Measures.MemberChildren ON 0,

[Customer].[Customer Geography].[Customer] ON 1

FROM [Adventure Works]


It would be convenient to stop there, expecting that the property could be aggregated by other dimensions. However, the property only applies to customers and is not aggregated in any customer hierarchies or across any other dimensions. This can be demonstrated by showing the members of other levels in the hierarchy a level other than Customer are displayed, there is no property to display. For example, listing the country members (in the customer dimension) will give nulls values for MemberChildren because the property is not associated with country members.


MEMBER Measures.MemberChildren AS [Customer].[Customer].CURRENTMEMBER.Properties( “Total Children” )


{Measures.MemberChildren} ON 0,

[Customer].[Customer Geography].[Country] ON 1

FROM [Adventure Works]

Members In Country

The next question that needs to be addressed is “which customers reside in which area?” The EXISTS function returns the set of members that reside in the context of another, for example, which customers reside in which state. To list the customers the customers of France, we would write the following statement. Note that by specifying the measure group name, we restrict the context to a specific fact (table) relationship.


{[Measures].[Internet Sales Amount]} ON 0,

EXISTS( [Customer].[Customer].[Customer]

        , [Sales Territory].[Sales Territory].[Country].&[France]

        , “Internet Customers”) ON 1

FROM [Adventure Works]

Bringing It Together

The two techniques discussed above can be used to combine member properties with the sets of members specific to some association. Our goal is to define the total children by country, so we need to sum the children for each member that exist in each country (or state etc.).

To do this, we define a measure called MemberChildren which shows a number of each customers children (this is only applicable to the leaf (customer) members). Note that since we need to use this value in a numerical context (we need to sum it), we must explicitly convert the property to a value using the STRTOVALUE function.

Next we define the measure TotalChildren which sums the MemberChildren for all customers that exist within our geographical context. That is, MemberChildren is summed for all customers that exist in the context of another member.

The following query shows how this is done.


MEMBER Measures.MemberChildren AS
STRTOVALUE([Customer].[Customer].CURRENTMEMBER.Properties( “Total Children” ))

MEMBER Measures.TotalChildren AS

SUM(    EXISTS( [Customer].[Customer].[Customer], [Sales Territory].[Sales Territory].CURRENTMEMBER, “Internet Customers”)

        , MemberChildren



{Measures.TotalChildren} ON 0,

[Sales Territory].[Sales Territory].[Country] ON 1

FROM [Adventure Works]


The dimension structure in SSAS is a powerful tool for managing subject area data (dimensions). The information contained in dimensions are often disregarded as the dimension members are only used to slice and dice. However the information can add valuable data to the cube through their properties which can also participate in traditional slicing and dicing.