MDX query containing 2 dimensions from the same Hierarchy

7
votes

The relevant structures from my cube are that I have a Hierarchy with "Class" and "SubClass". I also have a Measure called "Value" which is what im trying to obtain.

A simple query may look like:

SELECT
 NON EMPTY ([Measures].[Value]) ON COLUMNS,
 NON EMPTY ([Some Dimension].[Class Hierarchy].[Class]) ON ROWS
FROM [MyCube]

And I can obviously read the SubClass using the HIerarchy which is returned to Adomd.

My issue is twofold, firstly how would I "flatten" this hierarchy so as to receive both Class and SubClass as discrete members in the CellSet? This does not work:

SELECT
 NON EMPTY ([Measures].[Value]) ON COLUMNS,
 NON EMPTY (
   [Some Dimension].[Class Hierarchy].[Class], 
   [Some Dimension].[Class Hierarchy].[Sub Class]
) ON ROWS
FROM [MyCube]

The Class Hierarchy hierarchy is used more than once in the Crossjoin function

Second issue, what I actuually need to do is filter the above on particular classes, again this wont work for the same reason as above.

SELECT
 NON EMPTY ([Measures].[Value]) ON COLUMNS,
 NON EMPTY (
   {[Some Dimension].[Class Hierarchy].[Class].&[ClassA],[Some Dimension].[Class Hierarchy].[Class].&[ClassB]}, 
   [Some Dimension].[Class Hierarchy].[Sub Class]
) ON ROWS
FROM [MyCube]

Any help much appreciated. MDX is driving me nuts!

3
mdxadomd.net

3 Answers

4
votes

You are missing the MEMBERS property on your dimension.

For your first example try this:

SELECT 
    NON EMPTY ([Measures].[Value]) ON COLUMNS,
    NON EMPTY {(
            [Some Dimension].[Class Hierarchy].[Class].MEMBERS,
            [Some Dimension].[Class Hierarchy].[Sub Class].MEMBERS)} ON ROWS
FROM [MyCube]

For your second example try this:

SELECT 
    NON EMPTY ([Measures].[Value]) ON COLUMNS, 
    NON EMPTY {(
            [Some Dimension].[Class Hierarchy].[Class].&[ClassA],
            [Some Dimension].[Class Hierarchy].[Class].&[ClassB],
            [Some Dimension].[Class Hierarchy].[Sub Class].MEMBERS)} ON ROWS
FROM [MyCube]
2
votes

Use Subqueries in your WHERE Clause. MDX will always restrict the use of one dimension on one Axis only.

Sub Query is a way to get around that. I recently learnt this trick after MDX drove me nuts as well..

0
votes

Hi this query worked for me.

 SELECT NON EMPTY { [Measures].[App Count] }
 ON COLUMNS, 
 NON EMPTY 
 {(
 EXISTING 
 (
 [MART TIME DIM].[Date].[Date] .MEMBERS) * 
 [New Ren DIM].[New Ren CODE].[New Ren CODE].ALLMEMBERS
 )}
 ON ROWS FROM [SubmissionCube]  
 where
({
[MART BROKER DIM].[BROKER ID].&[10812]},{[MART TIME DIM].[Year].&[2015],
{[MARTTIME DIM].[Year].&[2016]}
})

Please be carefull with the '}' in the where clause as the query has.