MDX-запрос к сводной таблице на основе условия

Question

Я пытаюсь написать запрос MDX для сводной таблицы.

Подобный запрос в RDBMS выглядит так:

SELECT  stats_Date
        ,ISNULL(SUM(clicks), 0) AS clicks
        ,ISNULL(SUM(CASE WHEN ad_type IN (1,3) THEN clicks END), 0) AS keyword_clicks
        ,ISNULL(SUM(CASE WHEN ad_type IN (2,3) THEN clicks END), 0) AS direct_clicks
FROM    STATS_TABLE (NOLOCK)
WHERE   stats_Date BETWEEN '2015-06-01' AND '2015-06-30'
GROUP BY stats_Date

У меня два измерения [DIM TIME] & [DIM AD TYPE]

Я пробовал ниже запрос MDX для этого:

WITH    
    MEMBER [Measures].[Clicks Keyword] AS     
        IIF
        (
          [DIM AD TYPE].[Ad Type].CurrentMember IS [DIM AD TYPE].[Ad Type].&[1]
         ,[Measures].[clicks]
         ,0
        )
SELECT  {
          [Measures].[Clicks]
         ,[Measures].[Clicks Keyword]
        } ON COLUMNS
        ,{ 
          [DIM TIME].[CalendarHierarchy].[Date]*[DIM AD TYPE].[Ad Type].[Ad Type]
        }  ON ROWS
FROM    [CM_STATS_CUBE]
WHERE   ([DIM TIME].[Month].&[201506]:[DIM TIME].[Month].&[201506]})

Пример вывода этого запроса MDX выглядит следующим образом:

                       Clicks       Clicks Keyword
20150501    Invalid    (null)       0             
20150501    unknown    200          0
20150501    Keyword    500          0
20150501    Ads        300          300
20150502    Invalid    (null)       0
20150502    unknown    400          0
20150502    Keyword    600          0
20150502    Ads        500          500

но я хочу сгруппировать только по stats_date и ожидаемый результат:

            Clicks      Clicks Keyword
20150501    1000        300
20150502    1500        500    

Аналогичный пример для тестирования в базе данных куба [Adventure Works]:

WITH 
  MEMBER [Measures].[Internet Sales Amount US] AS  
    IIF( [Customer].[Customer Geography].CurrentMember IS [Customer].[Customer Geography].[Country].&[United States]
         ,[Measures].[Internet Sales Amount]
         ,NULL
       )
SELECT {
         [Measures].[Internet Sales Amount]
        ,[Measures].[Internet Sales Amount US]
       } ON 0
       ,NON EMPTY{[Date].[Calendar].[Date]} ON 1
FROM [Adventure Works]
WHERE   {[Date].[Date].&[20050701]:[Date].[Date].&[20050702]}

Answer 1

Вам не нужно беспокоиться о перекрестном соединении [DIM TIME].[CalendarHierarchy].[Date]*[DIM AD TYPE].[Ad Type].[Ad Type]

WITH 
  MEMBER [Measures].[Clicks Keyword] AS 
    IIF
    (
      [DIM AD TYPE].[Ad Type].CurrentMember IS [DIM AD TYPE].[Ad Type].&[1]
     ,[Measures].[clicks]
     ,0
    ) 
SELECT 
  {
    [Measures].[Clicks]
   ,[Measures].[Clicks Keyword]
  } ON COLUMNS
 ,{[DIM TIME].[CalendarHierarchy].[Date]} ON ROWS
FROM [CM_STATS_CUBE]
WHERE 
  [DIM TIME].[Month].&[201506] : [DIM TIME].[Month].&[201506];

Также я бы предложил использовать null скорее, чем 0 в вашем IIF функция - это должно привести в порядок результат и ускорить процесс:

WITH 
  MEMBER [Measures].[Clicks Keyword] AS 
    IIF
    (
      [DIM AD TYPE].[Ad Type].CurrentMember IS [DIM AD TYPE].[Ad Type].&[1]
     ,[Measures].[clicks]
     ,null   //<<<<<<<<<<<<<<<<< better to use null rather than 0
    ) 
SELECT 
  {
    [Measures].[Clicks]
   ,[Measures].[Clicks Keyword]
  } ON COLUMNS
 , NON EMPTY  //<<<<<<<<<<<<<<<<< now if Clicks and Clicks Keyword are both null the respective row will be excluded
  {[DIM TIME].[CalendarHierarchy].[Date]} ON ROWS
FROM [CM_STATS_CUBE]
WHERE 
  [DIM TIME].[Month].&[201506] : [DIM TIME].[Month].&[201506];

---

редактировать

Я бы не стал читать ваш сценарий достаточно подробно - извинения. Вы можете просто объединить набор из двух кортежей:

WITH 
  MEMBER [Measures].[Clicks Keyword] AS 
    Sum
    (
     {
      ([DIM AD TYPE].[Ad Type].&[1],[Measures].[clicks])
     ,([DIM AD TYPE].[Ad Type].&[3],[Measures].[clicks])
     }
    ) 
SELECT 
  {
    [Measures].[Clicks]
   ,[Measures].[Clicks Keyword]
  } ON COLUMNS
 , NON EMPTY  //<<<<<<<<<<<<<<<<< now if Clicks and Clicks Keyword are both null the respective row will be excluded
  {[DIM TIME].[CalendarHierarchy].[Date]} ON ROWS
FROM [CM_STATS_CUBE]
WHERE 
  [DIM TIME].[Month].&[201506] : [DIM TIME].[Month].&[201506];

AdvWrks Пример, который вы разместили, будет просто одним кортежем:

WITH 
  MEMBER [Measures].[Internet Sales Amount US] AS  
      (
        [Customer].[Customer Geography].[Country].&[United States]
       ,[Measures].[Internet Sales Amount]
      )
SELECT {
         [Measures].[Internet Sales Amount]
        ,[Measures].[Internet Sales Amount US]
       } ON 0
       ,NON EMPTY{[Date].[Calendar].[Date]} ON 1
FROM [Adventure Works]
WHERE   {[Date].[Date].&[20050701]:[Date].[Date].&[20050702]}

Если вы хотите добавить в Канаде, то есть три жизнеспособные альтернативы:

1.

WITH 
  MEMBER [Measures].[Internet Sales Amount US & Canada] AS  
      (
        [Customer].[Customer Geography].[Country].&[United States]
       ,[Measures].[Internet Sales Amount]
      )
    +
      (
        [Customer].[Customer Geography].[Country].&[Canada]
       ,[Measures].[Internet Sales Amount]
      )

2.

 WITH 
  MEMBER [Measures].[Internet Sales Amount US & Canada] AS 
    Aggregate
    (
      {
        [Customer].[Customer Geography].[Country].&[United States]
       ,[Customer].[Customer Geography].[Country].&[Canada]
      }
     ,[Measures].[Internet Sales Amount]
    ) 

3. (Переключиться на сумму)

WITH 
   MEMBER [Measures].[Internet Sales Amount US & Canada] AS 
    Sum
    (
      {
        (
          [Customer].[Customer Geography].[Country].&[Canada]
         ,[Measures].[Internet Sales Amount]
        )
       ,(
          [Customer].[Customer Geography].[Country].&[United States]
         ,[Measures].[Internet Sales Amount]
        )
      }
    ) 

Answer 2

Попробуй это:

WITH 
  MEMBER [Measures].[Clicks Keyword] AS 
  AGGREGATE({[DIM AD TYPE].[Ad Type].&[1], [DIM AD TYPE].[Ad Type].&[3]}, [Measures].[Clicks])

SELECT NON EMPTY
  {
    [Measures].[Clicks]
   ,[Measures].[Clicks Keyword]
  } ON COLUMNS
 , NON EMPTY  
  {[DIM TIME].[CalendarHierarchy].[Date]} ON ROWS
FROM [CM_STATS_CUBE]
WHERE 
  ([DIM TIME].[Month].&[201506] : [DIM TIME].[Month].&[201506]);