Grouping by Quarters with Mondrian / MDX: Behavior of Sum and Count vs Avg

2
votes

I'm trying to aggregate (Sum and Average) over groups of quarters, but I'm getting different results depending on which method I use. I'd like to understand why.

The following basic query illustrates my data (Pentaho SteelWheels sample):

SELECT  {Measures.Sales, Measures.Quantity} ON COLUMNS
,       ORDER(
          Time.Quarters.Members,
          Time.CurrentMember.Caption,
          BASC
        ) ON ROWS
FROM    SteelWheelsSales

The result:

Time |  Sales     |Quantity
-----+------------+---------
QTR1 |  445094.69 |  4561
QTR1 |  877418.97 |  8694
QTR1 | 1111260.1  | 10995
QTR2 |  564842.02 |  5695
QTR2 |  847713.97 |  8443
QTR2 |  869565.24 |  8480
QTR3 |  687268.87 |  6629
QTR3 | 1145308.08 | 11311
QTR4 | 1980178.42 | 19554
QTR4 | 2117298.82 | 20969

So the row headers are captions for the quarters and the different occurrences of QTR1, QTR2, QTR3 and QTR4 each belong to a particular year (So the 1st QTR1 is actually [2003].[QTR1], the 2nd is [2004].[QTR1] and the 3rd is [2005].[QTR1], and so on)

What I'd like to have is a result that treats all occurences of [QTR1] as a single group, and where the cell values aggregate the Sales Quantity measure. Let's say I want to see at least the sum (total quantity for all quarters 1, 2, 3 and 4 regardless of years) and the average (average quantity over all quarters 1, 2, 3, and 4 regardless of years)

Now, I saw this question:

How can I merge two members into one in a query?

And while that solution does help it requires that I enumerate the quarters that I need to group together. I would like to do that dynamically.

I came up with this query:

WITH
SET     OrderedQuarters
AS      Order(
          Time.Quarters.Members,
          Time.CurrentMember.Caption,
          BASC
        )
SET     UniqueQuarters
AS      Filter(
          OrderedQuarters,
          OrderedQuarters.Item(
            OrderedQuarters.CurrentOrdinal-1
          ).Caption <> Time.CurrentMember.Caption
        )
MEMBER  Measures.QuantitySum
AS      Sum(
          Filter(
            OrderedQuarters,
            UniqueQuarters.Item(
              UniqueQuarters.CurrentOrdinal
            ).Caption = Time.CurrentMember.Caption
          )
        , Measures.Quantity
        )
MEMBER  Measures.Counter
AS      Count(
          Filter(
            OrderedQuarters,
            UniqueQuarters.Item(
              UniqueQuarters.CurrentOrdinal
            ).Caption = Time.CurrentMember.Caption
          )
        )
MEMBER  Measures.[Sum over Count]
AS      Measures.QuantitySum / Measures.Counter
MEMBER  Measures.AvgQuantity
AS      Avg(
          Filter(
            OrderedQuarters,
            UniqueQuarters.Item(
              UniqueQuarters.CurrentOrdinal
            ).Caption = Time.CurrentMember.Caption
          )
        , Measures.Quantity
        )
SELECT {Measures.QuantitySum
       ,Measures.Counter
       ,Measures.[Sum over Count]
       ,Measures.AvgQuantity} ON COLUMNS
,       UniqueQuarters        ON ROWS
FROM    SteelWheelsSales

Which gives this result:

Time | QuantitySum | Counter |  Sum over Count  | AvgQuantity
-----+-------------+---------+------------------+----------------
QTR1 |       24250 |       3 |  8083 3333333333 | 8083.3333333333
QTR2 |       22618 |       3 |  7539.3333333333 | 8083.3333333333
QTR3 |       17940 |       2 |  8970            | 8083.3333333333
QTR4 |       40523 |       2 | 20261.5          | 8083.3333333333

Now, while the [QuantitySum] and [Sum over Count] measures give me the result I want, I'm not entirely satisfied:

  1. The [QuantitySum] is explicitly calculated with Sum. I would like have it implicitly calculated, since it is already defined in the cube as a sum aggregated measure
  2. I'm explictly calculating the average in the [Sum over Count] calculated measure. I would like to use Avg() and I tried with Measures.AvgQuantity, but that doesn't seem to behave in the way I expect. It looks like it caculates the average for Q1, and then it repeats that value for the other quarters as well. Why? Sum and Count seem to work exactly as intended, why is Avg so different?
2
mdxpentahoolapmondrian
Does the Distinct function work in Mondrian? msdn.microsoft.com/en-us/library/ms146033.aspx – whytheq
If you add Measures.Quantity into the set ON ROWS what happens? If it is already defined with an aggregation type of Sum then does it not produce the same result as [QuantitySum] ? – whytheq
In the definition of the measure MEMBER Measures.AvgQuantity what happens if you change Measures.Quantity to Measures.QuantitySum? – whytheq
(hmmm... trying to transfer to an AdvWrks prototype but even your initial basic script acts a lot different in AdvWrks - straight away the result is aggregated by Q1,Q2,Q3,Q4) – whytheq
@whytheq thanks for the replies. Mondrian has DISTINCT, but I don't see how it can help, since the apparently duplicate occurrences of QTR1, QTR2 etc aren't duplicates since each belongs to a different year. I cannot add Measures.Quantity to the ROWS axis since you cannot use the same hierarchy on more than one axis. If I use Measures.QuantitySum in the definition of Measures.AvgQuantity, I get the same result as Measures.QuantitySum - not the average. W/re to AdvWrks: that is really strange IMO. Is Quarter on a level below Year in that Cube? – Roland Bouman

2 Answers

1
votes

This isn't an answer per se, because I haven't tried it out and my MDX knowledge is too old to be at all confident that it will work. However, as mentioned on Twitter, I wonder if the following wouldn't work? Specifically, using Time.Years.Members as your list of members over which to average.

WITH
SET     OrderedQuarters
AS      Order(
          Time.Quarters.Members,
          Time.CurrentMember.Caption,
          BASC
        )
SET     UniqueQuarters
AS      Filter(
          OrderedQuarters,
          OrderedQuarters.Item(
            OrderedQuarters.CurrentOrdinal-1
          ).Caption <> Time.CurrentMember.Caption
        )
MEMBER  Measures.QuantitySum
AS      Sum(
          Filter(
            OrderedQuarters,
            UniqueQuarters.Item(
              UniqueQuarters.CurrentOrdinal
            ).Caption = Time.CurrentMember.Caption
          )
        , Measures.Quantity
        )
MEMBER  Measures.Counter
AS      Count(
          Filter(
            OrderedQuarters,
            UniqueQuarters.Item(
              UniqueQuarters.CurrentOrdinal
            ).Caption = Time.CurrentMember.Caption
          )
        )
MEMBER  Measures.[Sum over Count]
AS      Measures.QuantitySum / Measures.Counter
MEMBER  Measures.AvgQuantity
AS      Avg(
          Time.Years.Members
        , Measures.Quantity
        )
SELECT {Measures.QuantitySum
       ,Measures.Counter
       ,Measures.[Sum over Count]
       ,Measures.AvgQuantity} ON COLUMNS
,       UniqueQuarters        ON ROWS
FROM    SteelWheelsSales
1
votes

In terms of Microsofts AdvWrks cube the Date dimension is structured a little differently so that aggregates across Qrt1/Qtr2.. is easily achieved.

Here is the overall set of included hierarchies:

enter image description here

Here are the two hierarchies connected to quarters:

enter image description here

The level of the Date.Calendar user hierarchy includes the year in the member_caption:

enter image description here

Whereas the attribute hierarchy Date.Calendar Quarter of Year has captions like the ones in your scripts:

enter image description here

WITH 
  SET OrderedQuarters AS 
    Order
    (
      [Date].[Calendar Quarter of Year].[Calendar Quarter of Year].MEMBERS
     ,[Date].[Calendar Quarter of Year].CurrentMember.Member_Caption
     ,BASC
    ) 
  MEMBER Measures.AvgOrderCount AS 
    Avg
    (
      (EXISTING 
        [Date].[Calendar Year].[Calendar Year].MEMBERS)
     ,[Measures].[Order Count]
    ) 
SELECT 
  {
    [Measures].[Order Count]
   ,[Measures].[AvgOrderCount]
  } ON COLUMNS
 ,OrderedQuarters ON ROWS
FROM [Adventure Works];

It returns the following:

enter image description here

An alternative to the EXISTING function would be to cross join with the current member of the quarter hierarchy. This gives the same result:

WITH 
  SET OrderedQuarters AS 
    Order
    (
      [Date].[Calendar Quarter of Year].[Calendar Quarter of Year].MEMBERS
     ,[Date].[Calendar Quarter of Year].CurrentMember.Member_Caption
     ,BASC
    ) 
  MEMBER Measures.AvgOrderCount AS 
    Avg
    (
      ([Date].[Calendar Quarter of Year].CurrentMember*  //EXISTING 
        [Date].[Calendar Year].[Calendar Year].MEMBERS)
     ,[Measures].[Order Count]
    ) 
SELECT 
  {
    [Measures].[Order Count]
   ,[Measures].[AvgOrderCount]
  } ON COLUMNS
 ,OrderedQuarters ON ROWS
FROM [Adventure Works];

So referring to Ethen's answer if existing is not available in Mondrian then maybe this would force context to the calculation:

MEMBER  Measures.AvgQuantity
AS      Avg(
          (Time.Years.Members * Time.CurrentMember)
        , Measures.Quantity
        )

Building on the above I wonder if you cross join the filtered set with years:

MEMBER  Measures.AvgQuantity
AS      Avg(
          Filter(
            OrderedQuarters,
            UniqueQuarters.Item(
              UniqueQuarters.CurrentOrdinal
            ).Caption = Time.CurrentMember.Caption
          )
         * Time.Years.Members
        , Measures.Quantity
        )