Home > MDX > Generate, TopCount, and All Others

Generate, TopCount, and All Others

Recently, there was a post on the Analysis Services forum that I made a very sad attempt at answering. The question asked was how to construct an MDX statement that shows the Top 2 children associated with a parent and also show the amount associated with all other children as an aggregated total. After struggling with this problem and posting several real sorry attempts at solving, I think with a whole lot of guidance from Stefano, a decent, easily explainable solution has been derived. I’ll try to explain here and hope others can use the approach to solve similar problems.

This problem is not a new but one that Chris Webb (and likely many others) have written about. His approach was applicable to MSAS but with the ability to evaluate sets using the current context with the EXISTING declarative, the approach given in that post is overly complex now in 2005 and beyond. I apologize in advance to anyone if this solution has already been posted but I searched and couldn’t find any references. Not that I searched all that hard. Plus, I thought it pretty cool so writing about it again is not such a bad thing.

The problem sounds simple enough. First step is to calculate the top products for each category member. Using the Adventure Works as an example, a Generate statement combined with the TopCount function accomplishes what we wish to achieve. So to calculate the Top 2 products associated with each category member, we can create a named set, [Top Items], defined as the following.

WITH SET [Top Items] AS 
        Generate(
                { [Product].[Category].[Category].Members },
                TopCount(
                        EXISTING [Product].[Product].[Product].members,
                        2,
                        [Measures].[Reseller Sales Amount]
                )
        )

There are a couple points to make about the above definition. One, the EXISTING declarative before [Product].[Product].[Product].Members set is critical. If not used, the TopCount set is evaluated only once and those top products are the only ones returned in the final results. That’s not what we want. Second point to make is that the current [Product].[Category] member is NOT included in the resultant set. Stefano’s solution (and mine on the post) added it in the second argument of the Generate function and then used the Extract function to only return the [Product].[Product] hierarchy members. Just not including the category hierarchy in the second argument of the Generate statement accomplishes the same result without the penalty of performing a CrossJoin and then an Extract.

Once a set of the top two products associated with each category member has been constructed, the next step is to determine the aggregate of all other products associated with each category. To achieve this objective, a new calculated member, [Product].[Product].[All].[All Other Products], is defined as the Aggregate of all products less those included in the [Top Items] set. Again, an EXISTING declarative is necessary so that the [Product].[Product].[Product].Members set is evaluated under the context of the current Category member.

MEMBER [Product].[Product].[All].[All Other Products] AS
        Aggregate( { EXISTING { [Product].[Product].[Product].Members } - [Top Items] } )

And lastly, to wrap the whole thing up, a set of [Top Items] and the [All Other Products] member is created and CrossJoin-ed with Categories along the rows. Adding the [All Products] member to the set just to verify that the results are correct verifies the approach.

WITH SET [Top Items] AS 
        Generate(
                { [Product].[Category].[Category].Members },
                TopCount(
                        EXISTING [Product].[Product].[Product].members,
                        2,
                        ( [Measures].[Reseller Sales Amount] )
                )
        )
MEMBER [Product].[Product].[All].[All Other Products] AS
        Aggregate( { EXISTING { [Product].[Product].[Product].Members } - [Top Items] } )
SELECT        { 
                [Measures].[Reseller Sales Amount] 
        } ON COLUMNS,
        {
                { [Product].[Category].[Category].Members }
                * {
                        [Top Items], 
                        [Product].[Product].[All Other Products], 
                        [Product].[Product].[All] 
                } 
        } ON ROWS
FROM        [Adventure Works]

Voila! And very efficiently as well.

Technorati Tags: ,,,

Advertisements
Categories: MDX
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: