Проблемы с использованием MDX, созданного из сводной таблицы

Question

Недавно я использовал Excel для подключения к службам анализа и создания кода MDX, который использовался для запроса куба. Код показан ниже. Код был также проверен с использованием sql profiler. Однако, когда я захожу в службы анализа, просматриваю куб и помещаю его в управляющую студию, я получаю ошибки. Пожалуйста, дайте мне знать, если код можно исправить. Этот код - именно то, что мне нужно для моих требований, поэтому, если есть какие-либо изменения, пожалуйста, замените его внутри самого куба.

Код

SELECT NON EMPTY Hierarchize(DrilldownMember(CrossJoin({ 
[ColorsDim].[PrimeColor].[All], [ColorsDim].[PrimeColor].[PrimeColor].AllMember}, 
{([ColorsDim].[SecondColor].[All]) }), [ColorsDim].[PrimeColor].[PrimeColor].AllMember, 
[ColorsDim].[SecondColor])) DIMENSION PROPERTIES PARENT_UNIQUE_NAME
            ,HIERARCHY_UNIQUE_NAME ON COLUMNS
            ,NON EMPTY Hierarchize({DrilldownMember({ 
[ColorsDim].[Color_id].[All] },,, INCLUDE_CALC_MEMBERS) }) DIMENSION PROPERTIES 
PARENT_UNIQUE_NAME
                    ,HIERARCHY_UNIQUE_NAME ON ROWS FROM [Model]
                WHERE ([Measures].[Number of records in ColorDim]) CELL PROPERTIES VALUE
                    ,FORMAT_STRING
                    ,BACK_COLOR
                    ,FORE_COLOR
                    ,FONT_FLAGS

ошибка

Query Preparation failed. 

Additional information
The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have mulitple hierarchies, nor should it reference any dimension other than the measures dimension..
Parameter name: mdx (MDXQueryGenerator)

Желаемый вывод

Следующий запрос показывает желаемый результат:

Select non empty
[ColorsDim].[PrimeColor].children *
[ColorsDim].[SecondColor].children
on 0
, non empty
[ColorsDim].[Color_id].children
on 1
from [model]
where [Measures].[Number of records in ColorDim];

Мера в запросе - это количество строк для этого конкретного измерения

Answer 1

Ну, сообщение об ошибке, которое вы предоставили, выглядит для меня как сообщение об ошибке SSRS.

Если вы открываете SSMS с подключением к кубу, а затем открываете запрос MDX, запрос должен выполняться нормально.

Переформатирование запроса, чтобы мы могли более четко видеть, что происходит, дает следующее:

SELECT 
  NON EMPTY 
    Hierarchize(
      DrilldownMember(
        CrossJoin(
          {[ColorsDim].[PrimeColor].[All]
         , [ColorsDim].[PrimeColor].[PrimeColor].AllMember}
         ,{([ColorsDim].[SecondColor].[All]) }
        )
      , [ColorsDim].[PrimeColor].[PrimeColor].AllMember
      , [ColorsDim].[SecondColor]
      )
    ) DIMENSION PROPERTIES PARENT_UNIQUE_NAME
      ,HIERARCHY_UNIQUE_NAME ON COLUMNS
 ,NON EMPTY 
    Hierarchize(
       {
         DrilldownMember(
           {[ColorsDim].[Color_id].[All] }
            ,,, INCLUDE_CALC_MEMBERS
         ) 
        }
     ) 
    DIMENSION PROPERTIES PARENT_UNIQUE_NAME
   ,HIERARCHY_UNIQUE_NAME ON ROWS 
FROM [Model]
WHERE ([Measures].[Number of records in ColorDim]) 
CELL PROPERTIES VALUE
                    ,FORMAT_STRING
                    ,BACK_COLOR
                    ,FORE_COLOR
                    ,FONT_FLAGS

Excel добавляет много стандартного кода, чтобы мы могли избавиться от свойств ячейки и измерения, я подозреваю, что иерархия не нужна, и перекрестное соединение может быть заменено простым * оператор, чтобы дать это:

SELECT 
  NON EMPTY 
      DrilldownMember(
           [ColorsDim].[PrimeColor].[All]
         * [ColorsDim].[PrimeColor].[PrimeColor].AllMember
         ,{([ColorsDim].[SecondColor].[All])}
        )
      , [ColorsDim].[PrimeColor].[PrimeColor].AllMember
      , [ColorsDim].[SecondColor]
      )
     ON 0
 ,NON EMPTY 
     DrilldownMember(
       {[ColorsDim].[Color_id].[All] }
        ,,, INCLUDE_CALC_MEMBERS
     ) 
    ON 1
FROM [Model]
WHERE [Measures].[Number of records in ColorDim]; 

Если вы хотите использовать это в SSRS, вам нужно будет переписать его так, чтобы COLUMNS (или 0) использовал только измерение Measure!

Чтобы сделать так, чтобы ssrs был достаточно счастлив, вы могли бы сделать это:

WITH 
MEMBER [Measures].[Green_YellowGreen] AS
  (
   [ColorsDim].[PrimeColor].[PrimeColor].[Green],
   [ColorsDim].[PrimeColor].[SecondColor].[Yellow-Green],
   [Measures].[Number of records in ColorDim]
  )
MEMBER [Measures].[Green_BlueGreen] AS
  (
   [ColorsDim].[PrimeColor].[PrimeColor].[Green],
   [ColorsDim].[PrimeColor].[SecondColor].[Blue-Green],
   [Measures].[Number of records in ColorDim]
  )
SELECT 
 NON EMPTY
 {[Measures].[Green_YellowGreen], [Measures].[Green_BlueGreen] }
ON 0
, NON EMPTY
  [ColorsDim].[Color_id].children
ON 1
FROM [model];