SSAS MDX for Many to many relationship

Question

Using Adventureworks DW 2008 cube I'm trying to build an MDX query that will return a count of internet orders that did not have a specific reason code for the order.

FactInternetSales has a Many to many with FactInternetSalesReason. Each sale has 1 or more reason codes.

If I try to return orders that didn't use the "On Promotion" reason, I still get the on promotion orders included in the count because they also have other reasons on them.

How do I write the MDX to say that if this order has this reason, don't include it in the count, without regard to any other reasons?

The equivalent SQL against the adventure works sql data warehouse would be:

select count(distinct salesordernumber) from FactInternetSales
where not exists (select salesorderNumber 
                    from FactInternetSalesReason 
                    where SalesReasonKey = 2 
                    and FactInternetSales.SalesOrderNumber = FactInternetSalesReason.SalesOrderNumber
                )

whytheq whytheq · Accepted Answer · 2014-10-29T23:19:43

This is Internet Order Count:

SELECT 
  {
    [Measures].[Internet Order Count]
  } ON 0
FROM [Adventure Works];

This is Internet Order Count with On Promotion excluded:

SELECT 
  {
    [Measures].[Internet Order Count]
  } ON 0
FROM [Adventure Works]
WHERE 
  Except
  (
    [Sales Reason].[Sales Reason].[Sales Reason]
   ,[Sales Reason].[Sales Reason].[On Promotion]
  );

The following turns these exclusions into specific measures:

WITH 
  MEMBER [Measures].[Internet Order Count OnPromotion] AS 
    (
      [Measures].[Internet Order Count]
     ,[Sales Reason].[Sales Reason].[On Promotion]
    ) 
  MEMBER [Measures].[Internet Order Count excl OnPromotion] AS 
      [Measures].[Internet Order Count]
    - 
      [Measures].[Internet Order Count OnPromotion] 
SELECT 
  {[Date].[Calendar].[(All)]} ON 0
 ,{
    [Measures].[Internet Order Count]
   ,[Measures].[Internet Order Count OnPromotion]
   ,[Measures].[Internet Order Count excl OnPromotion]
  } ON 1
FROM [Adventure Works];

SSAS MDX for Many to many relationship

1 Answers