MDX question

Hi guys

I’ve got a question. Very simple scenario: a stand alone dimension exists, called Trip with only 2 columns - trip number and Audited flag (values can be Audited or Not Audited). Total row number around 2500000.

with

member [Measures].x as count([Trip].[Trip].members)

select

[Measures].x on 0

from [$Trip] --2500000

Half rows are Audited, half Not Audited. SP2 pack is installed. I need to find a number of Audited and Not Audited trips. I found that when I use slice – the whole number of rows returned, which is wrong but query is fast (only 1 second)

with

member [Measures].x as count([Trip].[Trip].members)

select

[Measures].x on 0

from [$Trip]

where ([Trip].[Audited Trip Flag].[Not Audited]) –2500000

The following query returns the correct number of rows, but considerably slower comparing with the “slice” version (around 30 sec.)

with

member [Measures].x as Exists([Trip].[Trip].members, [Trip].[Audited Trip Flag].[Not Audited]).Count

select

[Measures].x on 0

from [$Trip] –1250000

Question: why “slice” version does not work and would that be a better query to respond quicker.

Thank you, for any input.

Just found that "set" version is twice as fast - 15 secs

with

set notAudited as ([Trip].[Trip].members, [Trip].[Audited Trip Flag].[Not Audited])

member [Measures].x as notAudited.Count

select

[Measures].x on 0

from [$Trip] --1250000 --15 sec

|||

Hi Konstantin,

have you set right relationship between attributes?

Francesco

|||

Hi Francesco

Yes, the right relationship exists by default - Trip number attribute is a key, Trip Flag is tight to a key through attribute relationship. Any ideas?