MDX - Getting the sum instead of individual values

Question

My data looks like this:

ID                                   |PersonID  |CompanyID  |DateID  |Throughput |AmountType
33F467AC-F35B-4F24-A05B-FC35CF005981 |7         |53         |200802  |3          |0
04EE0FF0-511D-48F5-AA58-7600B3A69695 |18        |4          |201309  |5          |0
AB058AA5-6228-4E7C-9469-55827A5A34C3 |25        |69         |201108  |266        |0

with around a million rows. The columns names *ID refers to other tables, so they can be used as dimensions.

I have an OLAP cube with the column Throughput as Measure and the rest as dimensions.

I have a MDX query that is supposed to calculate Quartiles, other problem with the query is described and solved here: Trying to calculate quartiles in MDX.

The calculation works fine when I filter with one year, but when I filter with two years the result is the sum of both years. I demonstrate with an example. I have simplified the query to only show row counts because it still gives the same problem. The MDX query looks like this with one year:

WITH
SET selection as ({[Dates].[Year].&[2014]})
SET [NonEmptyIds] AS
 NonEmpty(
      [ThroughputID].[ID].[Id].ALLMEMBERS,
  {[Measures].[Throughput]} * [selection]
 )
 SET [ThroughputData] AS 
ORDER
    (    
        [NonEmptyIds],  
        [Measures].[Throughput], 
        BASC
     )
MEMBER [Measures].[RowCount] AS COUNT (ThroughputData)

SELECT
selection ON 0,
{[Measures].[RowCount]}
ON 1
FROM [Throughput]

The result from the above query is:

         |2014
RowCount |116 979

If I change the selection part to filter on 2015:

SET selection as ({[Dates].[Year].&[2015]})

I get this result:

         |2015
RowCount |68 038

Then, If I change the selection part to filter on both 2014 and 2015:

SET selection as ({[Dates].[Year].&[2014],[Dates].[Year].&[2015]})

I get this result:

         |2014    |2015
RowCount |185 017 |185 017

As 116 979 + 68 038 = 185 017, both years shows the sum of the individual years.

Does anyone know what I am doing wrong in the query?

Answer 1

When you have both the years 2014 & 2015 in the set Selection, then

 NonEmpty(
      [ThroughputID].[ID].[Id].ALLMEMBERS,
  {[Measures].[Throughput]} * {[Dates].[Year].&[2014], [Dates].[Year].&[2015]}
 )

what the above means is

Give me the set of Ids which are Non-Empty for the years 2014 as well as 2015

Since the set of Ids corresponding to each year is distinct(I guess), they are getting added up!

Since you have given a sample of code, I have no comments on the usage of sets, but you would have to modify the code quite a bit to get the current count.

WITH
SET selection as 
{
 [Dates].[Year].[Year].&[2015], 
 [Dates].[Year].[Year].&[2014]
}

MEMBER [Measures].[RowCount] AS 
COUNT (
        NonEmpty(
            [ThroughputID].[ID].[Id].ALLMEMBERS,
            [Measures].[Throughput]
                )
      )

SELECT
RowCount ON 0,
selection  ON 1
FROM [Throughput]

Long story short: Since sets are static and current context doesn't change their content, they shouldn't be used inside calculations.

What I did above is instead of referring the set inside my calculation, I refer the current year(in scope) to obtain the Ids and instead of putting this in another named set(which again would have been static), put it in a member's calculation.

Hope this helps.

Answer 2

SEBTHU's answer looks ok to me. I don't see a need to use the currentmember function within your custom measure.

Here is an equivalent script against the AdvWrks cube:

WITH 
  SET [YearSet] AS 
    {
      [Date].[Calendar Year].&[2007]
     ,[Date].[Calendar Year].&[2008]
    } 
  MEMBER [Measures].[RowCount] AS 
    Count
    (
      NonEmpty
      (
        [Customer].[Customer].[Customer]
       ,[Measures].[Internet Sales Amount]
      )
    ) 
SELECT 
  [Measures].[RowCount] ON 0
 ,[YearSet] ON 1
FROM [Adventure Works];

This is what the above returns i.e. not static:

You can also use a combination of the SUM function with IIF to construct a count measure like this - it can be fast in certain contexts:

WITH 
  SET [YearSet] AS 
    {
      [Date].[Calendar Year].&[2007]
     ,[Date].[Calendar Year].&[2008]
    } 
  MEMBER [Measures].[RowCount] AS 
    Count
    (
      NonEmpty
      (
        [Customer].[Customer].[Customer]
       ,[Measures].[Internet Sales Amount]
      )
    ) 
  MEMBER [Measures].[RowCountFAST] AS 
    Sum
    (
      [Customer].[Customer].[Customer]
     ,IIF
      (
         [Measures].[Internet Sales Amount] = 0
        ,null
        ,1
       )
    ) 
SELECT 
  {
    [Measures].[RowCount]
   ,[Measures].[RowCountFAST]
  } ON 0
 ,[YearSet] ON 1
FROM [Adventure Works];

Result of above:

This alternative approach applied to your scenario:

WITH 
  MEMBER [Measures].[CountNonEmptyThings] AS 
    Sum
    (
      [ThroughputID].[ID].[Id]
     ,IIF
      (
        [Measures].[Throughput] = 0
       ,NULL
       ,1
      )
    ) 
SELECT 
  [Measures].[CountNonEmptyThings] ON 0
 ,[Dates].[Years].MEMBERS ON 1
FROM [Throughput];

Answer 3

I don't understand why you're calculating this in a complicated, MDX-based way, with session-defined sets and measures (WITH...). Why not simply create a COUNT-based measure in the cube, sourced on the rows of your fact table, and have the [Dates] dimension slice it?

Then your MDX would simplify down to (for example):

SELECT 
Measures.[YourCountMeasure] ON 0,
[Dates].[Year].Members ON 1
FROM Throughput

The slicing by year would be defined in the cube.

As it is, the problem is here:

SET [NonEmptyIds] AS
 NonEmpty(
      [ThroughputID].[ID].[Id].ALLMEMBERS,
  {[Measures].[Throughput]} * [selection]
 )

This set is defined once. It uses set [selection] (two years). It doesn't get re-evaluated multiple times in your MDX query. The fact that you have separate members of [Dates].[Years] on an axis of your query will not make this set recalculate for that member ("oh look, this set is also defined on the [Dates].[Years] hierarchy - I'd better slice it and recalculate the set based on the current member"). The set is evaluated just once.

A simple COUNT measure like this would probably work:

WITH
MEMBER Measures.CountNonEmptyThings AS
COUNT(NonEmpty(
      [ThroughputID].[ID].[Id].ALLMEMBERS,
  [Measures].[Throughput]))
SELECT
Measures.CountNonEmptyThings ON 0,
[Dates].[Years].Members ON 1  -- or whatever set you like
FROM Throughput

this would get re-calculated for each of the multiple years you put on an axis.