Search code examples
pivot-tablemdxssas-tabularssms-2014excel-pivot

Issues using mdx generated from PivotTable

I recently used excel to connect to analysis services and generate a mdx code that was used to query the cube. The code is shown below. The code was also verified using sql profiler. However when I go to analysis services and browse the cube and put in the cube in management studio, I get errors. Please let me know if the code can be fixed. This code is exactly what i need for my requirements so if there are any modifications please replace it within the cube itself.

Code

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

Error

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)

Desired Output enter image description here

The following query shows the desired output:

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];

The measure in the query is a count of rows for that particular dimension

Solution

  • Well the error message you've provided looks to me like an SSRS error message.

    If you open SSMS with a connection to the cube and then open an mdx query the query should run ok.

    Reformatting the query so we can see more clearly what is happening gives this:

    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 adds a lot of boilerplate code so we can get rid of the cell and dimension properties, I suspect hierarchize is not needed, and the crossjoin can be replaced with the simple * operator, to give this:

    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];

    If you want to use this in SSRS then you will need to re-write it so that COLUMNS (or 0) only uses the dimension Measures!

    To make it so that ssrs is happy enough you could do this:

    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];