MDX FILTER and non-existent elements

Post Reply
User avatar
PavoGa
Community Contributor
Posts: 412
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

MDX FILTER and non-existent elements

Post by PavoGa » Tue Jan 28, 2020 9:11 pm

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.
Ty
Cleveland, TN

Mark RMBC
Regular Participant
Posts: 215
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: MDX FILTER and non-existent elements

Post by Mark RMBC » 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

User avatar
Steve Rowe
Site Admin
Posts: 1989
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: MDX FILTER and non-existent elements

Post by Steve Rowe » Wed Jan 29, 2020 11:43 am

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

User avatar
PavoGa
Community Contributor
Posts: 412
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: MDX FILTER and non-existent elements

Post by PavoGa » Wed Jan 29, 2020 3:15 pm

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.
Ty
Cleveland, TN

lotsaram
MVP
Posts: 3351
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: MDX FILTER and non-existent elements

Post by lotsaram » Wed Jan 29, 2020 8:32 pm

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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Mark RMBC
Regular Participant
Posts: 215
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: MDX FILTER and non-existent elements

Post by Mark RMBC » 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!

User avatar
Steve Rowe
Site Admin
Posts: 1989
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: MDX FILTER and non-existent elements

Post by Steve Rowe » Thu Jan 30, 2020 4:41 pm

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)?

User avatar
PavoGa
Community Contributor
Posts: 412
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: MDX FILTER and non-existent elements

Post by PavoGa » Wed Feb 05, 2020 1:12 am

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.
Ty
Cleveland, TN

Post Reply