MDX to count common members - EXISTS alternative

Question

Let me describe the issue giving example from [Adventure Works] cube.

Following MDX returns count of 17473

SELECT NON EMPTY { [Measures].[Internet Order Count] } ON COLUMNS,

[Internet Sales Order Details].[Sales Order Number] on ROWS

FROM [Adventure Works])

WHERE ( [Sales Reason].[Sales Reason].&[1] -- price

and following returns count of 3515

SELECT NON EMPTY { [Measures].[Internet Order Count] } ON COLUMNS ,

[Internet Sales Order Details].[Sales Order Number] on ROWS

FROM [Adventure Works]

WHERE ( [Sales Reason].[Sales Reason].&[2]) -- on promotion

I would like to count [Sales Order Number] which are common in [Sales Reason].&1 and [Sales Reason].&[2]

SQL equivalent would be:

select count(distinct f.SalesOrderNumber)
from FactInternetSales f
join FactInternetSalesReason fs 
on f.SalesOrderNumber = fs.SalesOrderNumber and f.SalesOrderLineNumber = fs.SalesOrderLineNumber
where fs.SalesReasonKey = 1 and fs.SalesOrderNumber in
      (select SalesOrderNumber from FactInternetSalesReason fs1 where fs1.SalesReasonKey = 2)

-- sales reason 1 = 17473
-- sales reason 2 = 3515
-- common 1689

I got common count using following mdx:

WITH MEMBER [Measures].[common] AS count

   (  exists ( exists ([Internet Sales Order Details].[Sales Order Number].[Sales Order Number].Members,
         [Sales Reason].[Sales Reason].&[1],"Internet Orders" 
         ),
         [Sales Reason].[Sales Reason].&[2],"Internet Orders" 
        )
    )

SELECT NON EMPTY [Measures].[common] ON COLUMNS

FROM [Adventure Works] 

-- 1689

But use of EXISTS is rather slow for my requirement. Please suggest an alternative.

Also please see related thread here

Thank you

whytheq whytheq · Accepted Answer · 2015-01-10T14:28:21

Please try adding both reasons to the WHERE clause as a single set:

SELECT 
  NON EMPTY 
    {[Measures].[Internet Order Count]} ON COLUMNS
 ,[Internet Sales Order Details].[Sales Order Number] ON ROWS
FROM [Adventure Works]
WHERE 
  {
    [Sales Reason].[Sales Reason].&[2]
   ,[Sales Reason].[Sales Reason].&[1]
  };

Here is an alternative that runs faster, only looks at the common orders, and does not use the EXISTS function:

WITH 
  SET [AllOrders] AS 
    [Internet Sales Order Details].[Sales Order Number].[Sales Order Number].MEMBERS 
  SET [OrdersIntersection] AS 
    Intersect
    (
      NonEmpty
      (
        [AllOrders]
       ,{
          (
            [Sales Reason].[Sales Reason].&[1]
           ,[Measures].[Internet Order Count]
          )
        }
      )
     ,NonEmpty
      (
        [AllOrders]
       ,{
          (
            [Sales Reason].[Sales Reason].&[2]
           ,[Measures].[Internet Order Count]
          )
        }
      )
    ) 
  MEMBER [Measures].[commonCount] AS 
    [OrdersIntersection].Count 
SELECT 
  //NON EMPTY //<<not needed
    [Measures].[commonCount] ON COLUMNS
FROM [Adventure Works];

MDX to count common members - EXISTS alternative

1 Answers