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;

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

SELECT

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.


WITH

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

SELECT

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

SELECT

{[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.

WITH

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

    )

SELECT

{Measures.TotalChildren} ON 0,

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

FROM [Adventure Works]

Conclusion

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.

 

Advertisements

3 thoughts on “Aggregating Member Properties

  1. well, usually in such cases you can create a new measure group that based on your customer-dimension
    create a new measure there on top of your [Number of Children]-column and use SUM-Aggregation
    link this measuregroup only to your customer dimension

    this solves the problem without using any MDX, works in all conditions (multiselect, subselect) and will also perform better in most cases

    I know that you can solve most of the problems using MDX, but in some cases also a simple design-change solves the problem 🙂

    greets,
    gerhard

    • Hi Gerhard,

      Agree 100%, design the structure with a measure group is a better option and would perform better, but I guess its part of the life cycle for the database.
      Building the dimension with properties is a nice way of accommodating SCD2 and in a lot of cases makes sense to show it as a property.
      Thanks for your reply,

      Paul

  2. As Gerhard said, I would also prefer to build this particular scenario as a separate measure group but nice to know the technique, and I am sure it could be applied elsewhere. Nice post 🙂

    Cheers,
    Jason

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