MDX – Counting Consecutive Days

A recent blog post (here) questioned a method for returning the most consecutive days that a customer has brought product for. For example, given a particular customer what’s the largest continuous date span of their purchases? More realistically, a business question may be ‘what are the customers who purchase for more than 5 days in a row’?

In addition to the answer provided in the post, this blog provides a little more support to the solution. To make the data more accessible, the question is based on [Adventure Works] and considers months instead of days (ie, for a customer, what’s the most number of months they have continuously purchased for?). We are using the [Date].[Calendar] hierarchy and the [Internet Sales Amount] measure.

Approach

This approach looks at the problem in two ways. Firstly, we define a measure that can be applied to a specific period that counts the number of periods that have had (continuous) sales. Secondly, we apply this to all periods to determine the largest number of consecutive periods.

Monthly Continuous Periods

If we consider a single customer and their monthly sales (as below), the continuous sales for any month end on the first subsequent month of null sales. For example; the [July 2007] continuous periods end in [August 2007] and [October 2007] end in [December 2007]. The end of the continuous period is defined as the first null in the set which starts in the next month and on the last month.

The number of continuous periods for a month is also the number of members in the set which range from the first period until (but not including) the first (null) period.

select [Measures].[Internet Sales Amount] on 0 ,[Date].[Calendar].[Month] on 1 from    [Adventure Works] where (    [Customer].[Customer].&[12301] )

The end of the continuous period (or the first null member) is satisfied by the expression;

filter( {[Date].[Calendar].currentmember.nextmember : null }
,[Measures].[Internet Sales Amount] = 0
).item(0)
// note that we are only interested in the first null item (and use the .item(0) function to retireive this

The query above can be extended to show the ending month (name) of the continuous period;

with member [Measures].[end of range member] as filter( {[Date].[Calendar].currentmember.nextmember : null } ,[Measures].[Internet Sales Amount] = 0 ).item(0) .member_name select { [Measures].[Internet Sales Amount] ,[Measures].[end of range member] } on 0 ,[Date].[Calendar].[Month] on 1 from    [Adventure Works] where (     [Customer].[Customer].&[12301] )

Additionally, since a continuous period cannot exist when the current month has no sales, months without sales can be excluded from the calculation (they will be null).

with member [Measures].[end of range member] as iif ( [Measures].[Internet Sales Amount] = 0        , null        , filter( {[Date].[Calendar].currentmember.nextmember : null }                    ,[Measures].[Internet Sales Amount] = 0).item(0)                    .member_name )

Given the end period (note that we know the start since it’s a relative period), we can specify the continuous range between the start and the end. This set includes an additional member (the last one is not really needed because its value is null), and so it can be excluded from the set. Although it can be excluded using the .prevmeber function, it can be left in the set and removed from the count. The set is simply defined within the iif statement;

with member [Measures].[end of range member] as iif ( [Measures].[Internet Sales Amount] = 0 , null ,         filter( {[Date].[Calendar].currentmember.nextmember : null } ,[Measures].[Internet Sales Amount] = 0).item(0)  .member_name ) // inclusive member [Measures].[set (inclusive)] as iif ( [Measures].[Internet Sales Amount] = 0         , null         , settostr(                   [Date].[Calendar].currentmember : filter( {[Date].[Calendar].currentmember.nextmember : null } ,[Measures].[Internet Sales Amount] = 0).item(0) )                   ) // count (just reduce 1) member [Measures].[set count] as iif ( [Measures].[Internet Sales Amount] = 0       , null  ,   {  [Date].[Calendar].currentmember :             filter( {[Date].[Calendar].currentmember.nextmember : null }     ,[Measures].[Internet Sales Amount] = 0).item(0) }.count-1       ) select { [Measures].[Internet Sales Amount] ,[Measures].[end of range member] , [Measures].[set (inclusive)] , [Measures].[set count] }  on 0, [Date].[Calendar].[Month] on 1 from    [Adventure Works] where ( [Customer].[Customer].&[12301]   )

The Largest Number of Consecutive Periods

Having defined the number of consecutive periods for relative periods, we can define the maximum value for all periods using the max function. Simply specify range set (first argument of max) as the period members. Our query becomes;

with member [Measures].[Range Count] as iif ( [Measures].[Internet Sales Amount] = 0         , null , {[Date].[Calendar].currentmember : filter( {[Date].[Calendar].currentmember.nextmember : null } ,[Measures].[Internet Sales Amount] = 0).item(0) }.count-1     ) member [Measures].[Max Periods] as max([Date].[Calendar].[Month], [Measures].[Range Count]) select [Measures].[Max Periods] on 0 from    [Adventure Works] where (    [Customer].[Customer].&[12301]   )

Other Considerations

The query searches for continuous periods until the last date period (regardless of year). Often, with this type of situation, comparatives are required on an annual basis and so there would be a need to alter the ‘end’ date to be the last data for the current year.

There are also some performance improvements that can be applied to the query; However, I think that this demonstrates the method rather simply and performance addressed as needed.

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.