8

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!

Jamiec
  • 133,658
  • 13
  • 134
  • 193

3 Answers3

4

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]
jvilalta
  • 6,679
  • 1
  • 28
  • 36
  • 2
    Thanks for the answer, but im afraid its wrong. This produuces the same error "The Class Hierarchy hierarchy is used more than once in the Crossjoin function" – Jamiec Dec 07 '09 at 16:50
  • Sorry. It works just fine on my cube. Double check that you have the curly braces and parenthesis in the right spots. I'm a bit confused that you talk about a crossjoin function but it's not in your sample MDX. Typically it's denoted by the * between dimensions. – jvilalta Dec 07 '09 at 17:13
  • Are you sure your Cube has the 2 dimensions on the same Hierarchy? I can also ssee no difference between using a * for crossjoins from a comma. I have double checked the order of curlies & parenthesis in my query, same result. I have tried replacing the comma by * with same result. – Jamiec Dec 07 '09 at 17:19
  • I see what you mean. I was using the attribute hierarchies directly instead of hierarchies. That's what the above will work with. I think cross joins across used defined hierarchies aren't supported. – jvilalta Dec 07 '09 at 17:47
  • Thanks for the info and your help with the question. – Jamiec Dec 08 '09 at 08:54
2

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..

user2800124
  • 21
  • 1
0

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.

Arif Imtiaz
  • 1
  • 6