MDX FILTER and non-existent elements

[PavoGa]

Take the following MDX statement on a cube value:

Code: Select all

FILTER( TM1SUBSETALL( [Product] ),
     [Sales].([Time].[2020_17], [Sale.m].[Dollars]) > 0)

In this case, assume that [Time].[2020_17] is not a valid element in the Time dimension. This query will execute and may actually return a subset.

What it does is use the currentmember of the Time dimension (one might think of this as the default member of the dimension).

No errors. Tested this several different ways on a cube with four dimensions and for any "bad" element on a dimension, the FILTER used the currentmember of the dimension.

[Mark RMBC]

Hi,

Yes I see this too. I can't imagine a situation where I would allow a non existent element to be included in the MDX but things like this does give cause for caution when using MDX!

The only way I can get the statement to error is if I take off the dimension prefix, so instead of [Time].[2020_17], just [2020_17] or by using strtomember("[Time].[2020_17]").

regards,

Mark

[Steve Rowe]

That's poor, it's a defect surely?

[PavoGa]

Hi,

Yes I see this too. I can't imagine a situation where I would allow a non existent element to be included in the MDX but things like this does give cause for caution when using MDX!

The only way I can get the statement to error is if I take off the dimension prefix, so instead of [Time].[2020_17], just [2020_17] or by using strtomember("[Time].[2020_17]").

regards,

Mark

I agree, but ran into this doing some testing. What happened was I had inadvertently used an element that is going to be added and got a subset returned when the filter obviously included an non-existent element! Anyway, knowing this could cause a headache trying to debug an MDX statement returning a subset that is obviously wrong may help someone debug their MDX.

[lotsaram]

Well there have been defects i the past where MDX with invalid members has caused the server to crash. I kinda prefer that it takes the defaul member instead of crashing, but it would be better again for the query to just fail with an appropriate error message of the (first found) invalid member.

[Mark RMBC]

Oh so this stops server crashes for invalid members, in that case a welcome improvement, given I have fallen foul of this when I first started out on TM1!

[Steve Rowe]

There's plenty of situations where I would rather the server fell over than reported nonsense (especially in dev)....at least I would know I had an issue.

Does this issue occur in every context MDX is used or just in specific objects (TIs, subsets, active forms)?

[PavoGa]

Oh so this stops server crashes for invalid members, in that case a welcome improvement, given I have fallen foul of this when I first started out on TM1!

It is the StrToMember function that crashes the server when presented with an invalid member, at least in our version. Rumors are it has been resolved in later ones.