Page 1 of 1
MDX FILTER and non-existent elements
Posted: Tue Jan 28, 2020 9:11 pm
by 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.
Re: MDX FILTER and non-existent elements
Posted: Wed Jan 29, 2020 9:45 am
by 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
Re: MDX FILTER and non-existent elements
Posted: Wed Jan 29, 2020 11:43 am
by Steve Rowe
That's poor, it's a defect surely?
Re: MDX FILTER and non-existent elements
Posted: Wed Jan 29, 2020 3:15 pm
by PavoGa
Mark RMBC wrote: ↑Wed Jan 29, 2020 9:45 am
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.
Re: MDX FILTER and non-existent elements
Posted: Wed Jan 29, 2020 8:32 pm
by 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.
Re: MDX FILTER and non-existent elements
Posted: Thu Jan 30, 2020 1:24 pm
by 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!
Re: MDX FILTER and non-existent elements
Posted: Thu Jan 30, 2020 4:41 pm
by 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)?
Re: MDX FILTER and non-existent elements
Posted: Wed Feb 05, 2020 1:12 am
by PavoGa
Mark RMBC wrote: ↑Thu Jan 30, 2020 1:24 pm
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.