TM1 Forum

Posted: **Tue Jan 25, 2022 10:36 am**

Hi

Just noticed strange behaviour in working of mdx query.

We have "Division" dimension and cube with mapping.

Cube name: 08.01.00._Logistics_Map
Mapping-cube dimensions:
Versions
Years
CFO_Logistics
Divions
Meas_Map

MDX:

Code: Select all

{FILTER (
    {TM1FILTERBYLEVEL( 
        {TM1SUBSETALL( [Division] )
        }, 0)
    }, [08.01.00._Logistics_Map].([Versions].[1],[Years].[BLABLABLABLA],[CFO_Logistics].[BLABLABLABLA],[Meas_Map].[2])>0 )
}

So you can write absolutely everything as a member of dimension and this sh*t works!!! How it is possible?? What value it takes when i write "blablabla"? Judging by the result of the query it just takes all the members of [Years] and [CFO_Logistics] in this case...
Have you ever encountered similar behavior?

Posted: **Tue Jan 25, 2022 11:03 am**

Hi,

I feel like I have seen similar behaviour before when looking up cube values, and my usual workaround is to add an AND statement into the mdx to specifically exclude 0 values, so for example, your mdx would become:

Code: Select all

{FILTER (
    {TM1FILTERBYLEVEL( 
        {TM1SUBSETALL( [Division] )
        }, 0)
    }, [08.01.00._Logistics_Map].([Versions].[1],[Years].[BLABLABLABLA],[CFO_Logistics].[BLABLABLABLA],[Meas_Map].[2])>0 and [08.01.00._Logistics_Map].([Versions].[1],[Years].[BLABLABLABLA],[CFO_Logistics].[BLABLABLABLA],[Meas_Map].[2])<>0 )
}

Not sure if this will work for you or even if you have a problem or were just curious. I usually have something like currentmember instead of a specific element, such as blablablabla though!

Unfortunately I have no idea why this behaviour is happening, be interesting if anyone does have any ideas.

regards,

Mark

Posted: **Tue Jan 25, 2022 11:37 am**

Hi Mark

Simple static MDX behaviour looks like this:
{[Year].[2021]} return 2021 element
{[Year].[blablabla]} return empty set

But when we use FILTER function the game changes, expression [Year].[blablabla] in context of "FILTER" function starts working.

The problem is that this behaviour looks confusing and incorrect. You don't know if you made a mistake or not. For example when you use MDX query in TI-process and concatenate variables as a mdx member values.

Posted: **Tue Jan 25, 2022 11:52 am**

Yeah, I understood the problem.

As I said my workaround is to plug in an AND statement to specifically exclude zeroes, but it does make you lose a bit of confidence in the mdx.

Posted: **Tue Jan 25, 2022 2:07 pm**

I did a bit of testing on this as I use MDX a lot and wanted to understand the behaviour, all in PAW 2.0.55 so a little old.

The good news is that if I try and create a set in a TI with the nonsense MDX the TI aborts, so as you would expect some kind of error happens.

Directly in a set I could reproduce the issue, it appears to default to the first member when the member MDX is wrong.

I tested against a period dimension, as that is what I handy.

Code: Select all

{FILTER(TM1FILTERBYLEVEL(TM1SUBSETALL([Period]) , 0) , 
([}ElementAttributes_Period].([}ElementAttributes_Period].[Month Number]) >= 1) 
AND ([}ElementAttributes_Period].([}ElementAttributes_Period].[Month Number]) <= 5))}

This returns a range of periods, I also added another numeric attribute "Test" where the periods were flagged differently, so I could tell the difference.

When I changed the MDX in the set to read the following (which causes a fatal error in a TI)

Code: Select all

{FILTER(TM1FILTERBYLEVEL(TM1SUBSETALL([Period]) , 0) , 
([}ElementAttributes_Period].([}ElementAttributes_Period].[a]) >= 1) 
AND ([}ElementAttributes_Period].([}ElementAttributes_Period].[a]) <= 5))}

Then this returns the Month Number values, which happens to be first in the attributes dimension.

It's not just defaulting back to the last good MDX as if I first query Month Number, then Test and then "a" I get the Month Number results.

HTH, its certainly significantly less scary than it appeared since bad MDX fails on usage in a TI process. Personally all the MDX subsets in a system get created via a TI anyway so that I can reset / recreate and promote them as required. There's now the additional benefit that the MDX gets tested properly too...

Posted: **Tue Jan 25, 2022 2:23 pm**

cheers Steve.

Not sure if this deals with the issue I mentioned, where in order to filter the data as required I have to add in the AND statement but then again my issue isn't the one being dealt with! I think it might be related in some way though.

I also use TI to create subsets, the mdx and other relevant information is held in an mdx cube and the ti picks this up.

regards,

Mark

Posted: **Thu Jan 27, 2022 8:58 am**

Hi Steve!

Thank you for testing on your environment!
The trick is that my ti-process accepts such mdx without errors. MDX expression is defined on the prolog tab. Versions of tm1 on which I tried: 2.0.6 and 2.0.9.9

Posted: **Thu Jan 27, 2022 2:41 pm**

If TI accepts the wrong MDX you probably use SubsetCreateByMDX with an additional argument for the dimension name ?

Posted: **Thu Jan 27, 2022 5:09 pm**

Hi,

It is all a bit odd but surely you would never allow "blablabla", or anything else that didn't exist in the dimension, to be passed into the mdx in the first place?

Seems like you just need a few controls added to the TI to prevent such a thing.

Maren

Posted: **Fri Jan 28, 2022 12:40 am**

This is discussed in this thread.:

Posted: **Mon Jan 31, 2022 11:46 am**

Hi Wim

I use SubsetCreate function and then SubsetMdxSet twice :

Code: Select all

SubsetMdxSet ( Dimension, TempName , MDXExpression ) ;
SubsetMdxSet ( Dimension, TempName , '' ) ;

Just read your articles:
https://wimgielis.com/tm1_subsetcreateb ... set_EN.htm
and https://www.wimgielis.com/tm1_mdxstatem ... m#caveats

I didn't know before that there is an undocumented 3d parameter in the SubsetCreatebyMDX function.

TM1 Forum

Strange mdx filter behaviour

Strange mdx filter behaviour

Re: Strange mdx filter behaviour

Re: Strange mdx filter behaviour

Re: Strange mdx filter behaviour

Re: Strange mdx filter behaviour

Re: Strange mdx filter behaviour

Re: Strange mdx filter behaviour

Re: Strange mdx filter behaviour

Re: Strange mdx filter behaviour

Re: Strange mdx filter behaviour

Re: Strange mdx filter behaviour