SSAS - pivot query result

Question

I have to create a list of Dimensions and attributes used by each cube (5 cubes in total). Some are used by multiple cubes.

With following script:

SELECT [CUBE_NAME] AS [CUBE],
 [DIMENSION_UNIQUE_NAME] AS [DIMENSION],
 LEVEL_CAPTION AS [ATTRIBUTE]
FROM $system.MDSchema_levels
WHERE level_origin=2
AND LEVEL_NAME <> '(All)'

I have been able to get a list of what I need like this:

 CUBE | Dimension | Attribute
 A    | Person    | Name
 A    | Person    | First Name
 A    | Location  | City
 B    | Person    | Name
 B    | Person    | First Name
 C    | Product   | Productname

To get a clearer view, I was searching for a way to turn it like this:

 Dimension | Attribute    | Cube A | Cube B | Cube C 
 Person    | Name         | X      | X      | 
 Person    | First Name   | X      | X      | 
 Location  | City         | X      |        | 
 Product   | Productname  |        |        | X

(The X indicates which attributes excists in which cube) I know it is possible to pivot this in SQL, but I have no clue how to do it in MDX. Any suggestions/ideas?

Answer 1

You seems to want conditional aggregation:

SELECT Dimension, Attribute, 
       MAX(CASE WHEN CUBE = 'A' THEN 'X' END) AS CubeA,
       MAX(CASE WHEN CUBE = 'B' THEN 'X' END) AS CubeB,
       MAX(CASE WHEN CUBE = 'C' THEN 'X' END) AS CubeC
FROM $system.MDSchema_levels
WHERE level_origin=2 AND LEVEL_NAME <> '(All)'
GROUP BY Dimension, Attribute;