MDX to Filter on Numeric Attribute

Post Reply
User avatar
gtonkin
MVP
Posts: 530
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

MDX to Filter on Numeric Attribute

Post by gtonkin » Tue Dec 05, 2017 6:00 pm

I am trying to find a better way to filter on a Numeric Attribute (contains 0/1) where the name of the Attribute also happens to be the name of one of the Members.
Using the standard syntax to filter on an attribute returns the context error as the MDX is ambiguous.
Using the following code does not work as the value being evaluated seems to be something other than 1:

Code: Select all

{FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [<dim>] )}, 0)},
[<dim>].CurrentMember.Properties("<attr>")="1")}
Note: Converting the result using StrToValue yields an error confirming that both numeric and string attributes are treated as strings in MDX

Getting a bit more creative, the following does seem to work:

Code: Select all

{FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [<dim>] )}, 0)},
INSTR(1,[<dim>].CurrentMember.Properties("<attr>"),"<value>",0)>0
)}
So,
-Any better ideas to get the MDX more simple for future reviewers/maintainers?
-Anyone know what is returned by [<dim>].CurrentMember.Properties("<attr>") other than a plain old 1?
-Any way to TRIM the result - tried trim, rtrim, ltrim?
-Is there a way to fully qualify the attribute within the MDX, similar to how you would need to address ambiguous elements in Rules?

And lastly, yes, I could have changed the name of the attribute as an easy way out but where's the challenge then?

tm123
Posts: 59
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: MDX to Filter on Numeric Attribute

Post by tm123 » Tue Dec 05, 2017 7:50 pm

You can use the }ElementATtributes_dimName cube in your filter. Can you check if MDX below will give you want you want?

{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [<dim>] )}, 0)}, [}ElementAttributes_<dim>] .([}ElementAttributes_<dim>].[<NumericAttribName>])=1)}

User avatar
gtonkin
MVP
Posts: 530
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: MDX to Filter on Numeric Attribute

Post by gtonkin » Wed Dec 06, 2017 6:15 am

Hi TM123, it does indeed give me the results-too long looking at a problem from one angle. Thanks for the assistance!
FYI - my initial MDX seems to work fine on PAX 2.0.1 - something must have been fixed.

Still curious though about the answers to some of the other questions posed in my initial post.

lotsaram
MVP
Posts: 3008
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

Re: MDX to Filter on Numeric Attribute

Post by lotsaram » Wed Dec 06, 2017 9:40 am

Hey George this looks a lot like a PMR which I had open. 89862,112,848 "Inconsistent behavior in TM1 MDX evaluation"
PMR wrote:There are 3 ways which TM1 allows attributes to be queried via MDX which deliver inconsistent results. TM1 allows both string and numeric attributes

1/ TM1 "shorthand" MDX notation e.g.
{ FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Date] )}, 0)}, [Date].[Serial TM1] > 20000 )}

The issue with TM1 shorthand notation (which is what is traditionally recorded in the architect/perspectives subset editor) is that it is ambiguous whether a member, a subset or an attribute is being referenced which can lead to inconsistency in the case of name conflicts.

2/ TM1 "industry standard MDX" using member properties. e.g.
{ FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Date] )}, 0)} , [Date].CurrentMember.Properties("Serial TM1") > 20000 )}

This syntax is generally preferred as it conforms to Microsoft MDX norms and is non-ambiguous. However in TM1 this notation forces all member properties to type STRING so the above member set query would fail and return an empty set.

3/ "background attribute cube query" e.g.
{ FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Date] )}, 0)}, [}ElementAttributes_Date].( [}ElementAttributes_Date].[Serial TM1] ) > 20000 )}

This member set query will work for a numeric attribute.

All 3 of the above queries work and return consistent results for String type attributes but the standard member properties type notation does not work for numeric attributes and the type is coerced to string. Behavior should be consistent for all 3 expression types.

Currently developers are forced to code a workaround by using the 3rd expression type for numeric attributes.
Support did open a collab with development but they came back with "this is expected behavior." Essentially from the point of view of MDX all member properties are expected as strings and you can't retype them. So the only work around is to query the attribute cube.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
gtonkin
MVP
Posts: 530
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: MDX to Filter on Numeric Attribute

Post by gtonkin » Wed Dec 06, 2017 9:56 am

Thanks for the response and info Lotsa - will be using option 3 for the foreseeable future as many clients are still on 10.2.2 FP4.

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

Re: MDX to Filter on Numeric Attribute

Post by Steve Rowe » Wed Dec 06, 2017 10:24 am

Sort of off topic but...
Isn't this a symptom of the way numeric attribute are implemented in the first place? Numeric attributes aren't really numeric cells in the same way that data is and IMO are just a "mask" on a string attribute. Personally I rarely use them since they behave like strings and I can't write rules versus them.

If I want a numeric property of an element I'll build a cube for this purpose, though lotsarams use case of time is a good example where I wouldn't....

User avatar
gtonkin
MVP
Posts: 530
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: MDX to Filter on Numeric Attribute

Post by gtonkin » Wed Dec 06, 2017 10:31 am

Steve Rowe wrote:
Wed Dec 06, 2017 10:24 am
...Personally I rarely use them since they behave like strings and I can't write rules versus them...
Thanks Steve, agree on the strings and the usage of another cube as you can secure better as well as use picklists to populate the text "attribute".

Post Reply