Planning Analytics with Watson

 View Only
Expand all | Collapse all

Filtering on Attributes in MDX - future-proof syntax

  • 1.  Filtering on Attributes in MDX - future-proof syntax

    Posted Thu September 02, 2021 12:04 PM
    I have a notion in my mind from somewhere that I need to use Properties("<attribute>") syntax in MDX rather than just the Attribute name in square brackets as the square brackets syntax may fall away in future i.e.

    FILTER(TM1SUBSETALL([Company].[Company]) , [Company].[Company].CURRENTMEMBER.PROPERTIES("Size") = "Large")

    Rather than:

    FILTER(TM1SUBSETALL([Company].[Company]) , [Company].[Company].[Size] = "Large")
    Is the square bracket syntax being deprecated at some point? Did I misunderstand something heard or read?

    And no, I cannot remember where I got it from, just made a mental note to start using the Properties syntax)

    If you have an answer either way, please let me know and if the answer relates to using Properties, please share something tangible.

    Thank you.

    George Tonkin

  • 2.  RE: Filtering on Attributes in MDX - future-proof syntax

    Posted Fri September 03, 2021 04:40 AM
    Hi George,

    I'll take responsibility for that;-).

    Allowing an attribute to be used between square brackets is not valid MDX to begin with but something that TM1 has, for reasons I don't understand, allowed in the past. The issue however is that the way it was implemented can actually cause ambiguity, an ambiguity that plays up once you get an element name with the same name then such attribute or a subset name for that matter.

    So TM1 already has a strict mode that enforces that but in the next major version of TM1 using the proper 'PROPERTIES' expression will be the only way to refer to the value of an attribute. Speaking of which, in the next major version of TM1, TM1 12, we are doing the same thing with reference to TM1 subsets and require you to use TM1SubsetToSet function instead of just putting the subset's name between square brackets for the same reasons.


    Hubert Heijkers

  • 3.  RE: Filtering on Attributes in MDX - future-proof syntax

    Posted Fri September 03, 2021 07:54 AM

    Hi Hubert,

    Thank you very much for the reply and the explanation.  Will definitely keep both of those points in mind with any development being done now to avoid tears in future.

    I would like to slip in a follow on question too please:
    If I have defined a numeric attribute and use the below syntax, no results are returned:

    FILTER(TM1SUBSETALL([Company].[Company]) , [Company].[Company].CURRENTMEMBER.PROPERTIES("Flag") = 1)

    So, using the concept that all Attributes are actually stored as strings I tried:

    FILTER(TM1SUBSETALL([Company].[Company]) , [Company].[Company].CURRENTMEMBER.PROPERTIES("Flag") = "1")

    Still no results.  However these two do return results:

    FILTER(TM1SUBSETALL([Company].[Company]) , INSTR(1,[Company].[Company].CURRENTMEMBER.PROPERTIES("Flag"),"1") > 0)
    FILTER(TM1SUBSETALL([Company].[Company]) , LEFT([Company].[Company].CURRENTMEMBER.PROPERTIES("Flag"),1) = "1")

    Is there some trick to working with Numeric attributes when referencing via PROPERTIES("...")?

    Thanks again and kind regards,

    George Tonkin

  • 4.  RE: Filtering on Attributes in MDX - future-proof syntax

    IBM Champion
    Posted Fri September 03, 2021 08:40 AM
    When you use the old architect attributes window you will see that putting a 1 for a numeric attribute is then shown with 6 decimal places.
    So if you do check as a string you can replace "1" with "1.000000" and it should work.

    It might also be an idea to look at using StrToValue to convert the numeric attribute (which as you stated is actually a string) into an actual number. Particularly when you are doing greater than or less than style comparisons.

    Declan Rodger
    Technical Director
    Spitfire Analytics

  • 5.  RE: Filtering on Attributes in MDX - future-proof syntax

    Posted Fri September 03, 2021 09:00 AM
    Hi George,

    Thank you for bringing that to my attention! By default, the value returned by the PROPERTIES function is coerced to be a string. However, at least according to Microsoft's MDX documentation, it accepts a second, optional, parameter for which you can specify TYPED, and if you do it should return the value as strongly typed (read: numeric if it is a numeric attribute). I had a quick peek and it seems that TM1 ignores that second parameter and also verified that that is indeed the only difference between using the `[dim].[hier].[property]` and `[dim].[hier].[member].PROPERTIES("property")` notations. I'll get that fixed!

    To answer your question though, your numeric value is being coerced into a string and I can only imagine that, it being a floating-point number, under the covers, it is coerced to a string as something like "1.0" instead which would explain the issue with the string comparison.


    Hubert Heijkers

  • 6.  RE: Filtering on Attributes in MDX - future-proof syntax

    Posted Fri September 03, 2021 11:56 AM
    Hi Declan/Hubert,

    Thanks for the replies. 

    The filter on "1.000000" definitely worked. Did not get the StrToValue working yet but will tinker.

    Not sure what that Architect thing is :-)
    Too much time spent developing on PAW and had forgotten about the properties window.
    All makes perfect sense now as to why the string comparison did not work as expected.

    All the best.

    George Tonkin

  • 7.  RE: Filtering on Attributes in MDX - future-proof syntax

    Posted Sat September 04, 2021 04:38 AM

    Using TYPED within .properties resulted in an error for me.

    I think Tom gave one workaround, i.e. referencing the element attributes cube instead of the .properties.

    As a further point,

    As far as I can tell STRTOVALUE will work if you wrap it around a cube value, but doesn't work for attributes. It won't work where currentmember is used either as far as I can see (maybe it cant resolve into a single cell?).

    Example of using STRTOVALUE,

    Let us say we have an Admin cube with 2 dimensions, Version and Measure.

    Lets say we have a string measure called Flag and against version Actual we set Flag to a value of 1.

    Now lets apply the MDX to return all company flags equal to this global value, using square bracket notation obviously:

    FILTER(TM1SUBSETALL([Company].[Company]) , [Company].[Company].[Flag] = STRTOVALUE([Admin].([Versions].[Actual], [Measure].[Flag])))

    If the square bracket syntax is disallowed then hopefully the TYPED parameter will solve all issues. Another workaround for this would again be to refer to the element attributes cube.
    Though in the example above it would be easier to simply make the Flag a numeric element rather than string!



    Mark Wragg

  • 8.  RE: Filtering on Attributes in MDX - future-proof syntax

    Posted Mon September 06, 2021 05:09 PM

    Hi Hubert


    Re the requirement to use Properties and TM1SubsetToSet in v12.


    Was there any consultation over this?


    There are a lot of systems out there that have used what was legal TM1 MDX syntax for many years, ie the ability to reference subsets and probably to a lesser extent attributes using the [] notation.


    Manually updating all the existing MDX expressions is just not going to be feasible on a system of any size.


    They don't just appear in Subsets but also in Excel sheets, and potentially now in MDX Views. They will potentially appear in both Public and Private Subsets.


    If IBM want people to upgrade to v12 early then either IBM should provide an update utility to convert existing MDX, or they should provide a TM1S.CFG setting to continue to allow the current syntax. We name subsets in such a way that the likelihood of a clash with an element name is minimal, and therefore if there was a setting to allow the existing syntax to be used that would be our preference. We have several instances where subsets are built by combining existing subsets. This allows for a building block approach that is easier to maintain and provides re-useability. However, all these cases use the [] notation.


    I can remember the extremely painful and lengthy upgrade from TM1 10 to 11, where I had to write numerous VBA routines to update Sheets etc because of differences in syntax that were introduced and where the Action Button upgrade only did half a job. I would hope that IBM are not planning on repeating that experience.




    Paul Simon



  • 9.  RE: Filtering on Attributes in MDX - future-proof syntax