MDX - Top X Sales People by Total Sales for Each Date

Question

I'm trying to do this, but with MDX in my cube:

select
    *
from
(
    select 
        Date, SalesPerson, TotalSales, row_number() over(partition by Date order by TotalSales desc) as Num
    from SalesFact as ms
) as x
where
    Num < 5
order by
    Date, SalesPerson, Num desc

Let's say I have a cube with these dimensions:

Date (Year, Month, Date) - date is always 1st of month SalesPerson

The fact table has three columns - Date, SalesPerson, TotalSales - ie, the amount that person sold in that month.

I want, for each month, to see the top 5 sales people, and each of their TotalSales. The top 5 sales people can be different from one month to the next.

I am able to get the results for one month, using a query that looks like this:

select
      [Measures].[TotalSales] on columns,
      (
            subset
            (
                  order
                  (
                        [SalesPerson].children,
                        [Measures].[TotalSales], 
                        bdesc
                  ),
                  0,
                  5
            )
      ) on rows
from
      Hypercube
where 
      (
            [Date].[Date].&[2009-03-01T00:00:00]
      )

What I'm after is a query that puts Date and SalesPerson on rows, and TotalSales on columns.

I want to see over time each month, and for each month, the top 5 sales people, and how much they sold.

When I try to do it this way, it doesn't seem to filter / group the sales people by each date (get top 5 for each date). The values returned are all over the place and include very low and null values. Notably, the SalesPerson list is the same for each date, even though TotalSales varies a lot.

select
    [Measures].[TotalSales] on columns,
    (
        [Date].[Hierarchy].[Date].members,
        subset
        (
            order
            (
                [SalesPerson].children,
                [Measures].[TotalSales], 
                bdesc
            ),
            0,
            5
        )
    ) on rows
from
    Hypercube

It seems that everything inside "subset" needs to be filtered by the current [Date].[Hierarchy].[Date], but using CurrentMember gives a crossjoin / axis error:

select
    [Measures].[TotalSales] on columns,
    (
        [Date].[Hierarchy].[Date].members,
        subset
        (
            order
            (
                ([SalesPerson].children, [Date].[Hierarchy].CurrentMember),
                [Measures].[TotalSales], 
                bdesc
            ),
            0,
            5
        )
    ) on rows
from
    Hypercube

Error: Executing the query ... Query (3, 2) The Hierarchy hierarchy is used more than once in the Crossjoin function.

Execution complete

I've tried several variations of the last query with no luck.

Hopefully the answers will be helpful to others new to MDX as well.

nklhead nklhead · Accepted Answer · 2012-04-05T18:30:59

I eventually found out how to do what I was looking for. The solution revolved around using the Generate function, and starting with the basic example on MSDN and modifying the dimensions and measure to be the ones in my cube got me going in the right direction.

From http://msdn.microsoft.com/en-us/library/ms145526.aspx

Is there a better way?

Also, be wary of trying to refactor sets into the with block. This seems to change when the set is evaluated / change its scope and will change the results.

with

set
Dates as
{
    [Date].[Hierarchy].[Date].&[2009-02-01T00:00:00],
    [Date].[Hierarchy].[Date].&[2009-03-01T00:00:00],
    [Date].[Hierarchy].[Date].&[2009-04-01T00:00:00]
}

select
    Measures.[TotalSales]
    on columns,
    generate
    (
        Dates,
        topcount
        (
            [Date].Hierarchy.CurrentMember
            *
            [SalesPerson].Children,
            5,
            Measures.[TotalSales]
        )
    )
    on rows
from
    Hypercube

MDX - Top X Sales People by Total Sales for Each Date

1 Answers