Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only
Back to discussions
Expand all | Collapse all

Filtering on Attributes in MDX - future-proof syntax

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

    Posted 09/02/21 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.

    Use:
    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
    ------------------------------

    #PlanningAnalyticswithWatson


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

    Posted 09/03/21 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.

    Cheers,

    ------------------------------
    Hubert Heijkers
    ------------------------------



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

    Posted 09/03/21 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
    ------------------------------

    Original Message


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

    Posted 09/03/21 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
    ------------------------------

    Original Message


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

    Posted 09/03/21 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.

    Cheers!

    ------------------------------
    Hubert Heijkers
    ------------------------------

    Original Message


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

    Posted 09/03/21 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
    ------------------------------

    Original Message


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

    Posted 09/04/21 04:38 AM
    Hi,

    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!

    regards,

    Mark

    ------------------------------
    Mark Wragg
    ------------------------------

    Original Message


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

    Posted 09/06/21 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.

     

    Regards

     

    Paul Simon

     

     




    Original Message


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

    Posted 09/07/21 12:53 PM
    The engineer in me would argue the opposite actually. The next major server version is the perfect time to not provide backwards compatibility or a utility to fix things like this (although I do recognize how painful it could be). If v12 is launched as planned (based on IBM presentations) it will officially drop support for all non-REST based APIs (goodbye NG-API, C API, Java API). This is the best time, if there was one, to remove support for weird non-standard edge cases and force customers to think/test be fore upgrading. Additionally I would love to see a wholesale clean up of the TM1s.cfg file removing old and rarely used options. On a more practical note I don't think customer adoption will be all that fast anyway. v11 support will be long lasting, and I think customers appetite for moving to CP4D is quite low.

    ------------------------------
    Ryan Clapp
    ------------------------------

    Original Message


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

    Posted 09/08/21 06:25 PM
    Hi Ryan

    Firstly I am actually a Chartered Engineer so at least in theory I should know a little bit about engineering. 

    Whenever possible, new software releases should maintain backward compatibility. However, in some cases, in order to improve the underlying architecture of the product, vendors have no alternative but to introduce a breaking change, The proposed change to require subsets in MDX to be referenced using TM1SubsetToSet instead of the [] notation will definitely qualify as a breaking change for most TM1 systems.

    You have suggested that IBM should not provide any option to allow users to continue with the [] notation. Providing an option like this would allow existing users to upgrade to v12, and to modify systems in slower time, eg when not facing pressure to produce year end accounts.

    I don't think either of us can know enough about the changes that IBM are making for v12 to know whether such an option is feasible. You view may be that it is not desirable, but that may not be a view that will be shared by many existing users.

    Where I will take issue is that you appear to have suggested that IBM should also not provide any utility to help existing users convert their existing subsets, MDX views, and spreadsheets. I would certainly hope that IBM will provide this. It should be a relatively simple text processing job. Providing a conversion utility will certainly help to speed up migration to v12 in the on-premises license holders. In the case of cloud based license holders, it may be necessary as they may not have the option to stay on v11 for very long.

    Regards

    Paul Simon



    ------------------------------
    Paul Simon
    ------------------------------

    Original Message


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

    Posted 09/09/21 09:42 AM
    Hi Paul,

    I absolutely agree with you.
    Changing the syntax would mean that all processes, all views, all reports, etc. would have to be looked at and adjusted. A pretty high hurdle for the new version, IBM should definitely readjust there.

    Greetings
    Tarik


    ------------------------------
    Tarik Karaduman
    ------------------------------

    Original Message


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

    Posted 09/08/21 05:52 AM
    @Hubert Heijkers I am all, totally, 100% for using standard MDX notation and no longer supporting the ambiguous TM1 "shorthand" notation of [dimension].[hierarchy].[subset] and [dimension].[hierarchy].[attribute]

    However, as others have already noted there is a significant problem that MDX assumes all member properties are intrinsically string but TM1 supports numeric attributes, which I assume will not be going away. TM1 doesn't support recasting or retyping of member properties. When I enquired about this in the past I was told there were no plans to support this. However, if this isn't supported then [dimension].[hierarchy].[attribute] or a query to the }ElementAttributes cube remains the only way to be able to filter on numeric attributes in MDX.​

    ------------------------------
    Scott Wiltshire
    ------------------------------

    Original Message


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

    Posted 09/12/21 09:33 AM
    @Hubert Heijkers Forewarned is forearmed.

    Are there any other considerations that we should be aware of in terms of potential syntax changes, changes to functions etc. etc?
    Not restricted to MDX, but possibly Rules and TI too.

    If we have an idea of these, we could already start mitigating some of the potential consequences by utilising the correct syntax.

    Please let us know.

    Thank you.​

    ------------------------------
    George Tonkin
    ------------------------------

    Original Message


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

    Posted 09/03/21 08:29 AM
    If I am not mistaken the very idea of numeric member properties is not a standard MDX thing, so I am interested in how this will play out as well (I have a ton of MDX based on numeric attributes not using PROPERTIES and do not relish the tedious refactoring this will entail).

    I can imagine one work around being a cube filter of the appropriate }ElementAttributes_ cube as opposed to trying to use the numeric properties as such.

    ------------------------------
    Tom Cook
    ------------------------------



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

    Posted 04/04/22 10:22 AM
    Hi,

    I have posted this because I want to keep this issue on IBM's radar, as I came across the issue again (will be linking to it for the April AMA).
    I was scratching my head for a bit and then remembered the discussion above! Not sure we ever got a definitive answer.

    I have been working on some MDX to order a Calendar dimension as I require, so the elements are dates, e.g. 01/08/2022, 02/08/2022 etc etc
    Against these elements I have a Date Order numeric attribute called Date Order, so 01/08/2022 is Date Order 1, 02/08/2022 is Date Order 2 etc etc

    I came up with the following MDX to order the n Level Calendar elements by the date order:

    {ORDER(Except (TM1FILTERBYLEVEL({TM1SUBSETALL( [Calendar])}, 0),{Descendants([Calendar].[zSystem Required])} ), [Calendar].[Date Order], ASC)}

    This works absolutely fine.

    However, as per above discussion, stricter MDX rules will be enforced which prevent access to an attribute via the [Dimension].[Attribute] notation
    I am trying to use the 'proper' notation going forward, and tried to rewrite the mdx as follows:

    {ORDER(Except (TM1FILTERBYLEVEL({TM1SUBSETALL( [Calendar])}, 0),{Descendants([Calendar].[zSystem Required])} ), [Calendar].currentmember.properties('Date Order'), ASC)}

    This did not work because it ordered the Date order incorrectly by ordering the elements by date order 1, 10, 100 etc etc, the order then becomes 01/08/2022 (Date Order 1), 10/08/2022 (Date Order 10) etc etc.
    In other words it treats the attribute as a string and no longer as a numeric, as highlighted in the discussion above.

    In order to get around this I referenced the attributes cube directly, so as follows:

    {ORDER(Except (TM1FILTERBYLEVEL({TM1SUBSETALL( [Calendar])}, 0),{Descendants([Calendar].[zSystem Required])} ), [}ElementAttributes_Calendar].([Calendar].currentmember,[}ElementAttributes_Calendar].[Date Order]), ASC)}

    So I suppose the outstanding questions are:

    Will the optional TYPED parameter work with the properties function so that it reads it as a numeric attribute?
    Will there be any kind of utility provided by IBM to ease transition to the next major release?

    regards,

    Mark

    ------------------------------
    Mark Wragg
    ------------------------------



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

    Posted 04/05/22 04:27 AM
    As a workround, you could create a string attribute which would sort properly ie 1 becomes 0001, 10 becomes 0010. This could be done either directly or via a rule against the numeric attribute.

    ------------------------------
    David Usherwood
    ------------------------------

    Original Message


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

    Posted 04/05/22 05:29 AM
    Hi David,

    Yes I agree with you that your workaround would indeed work, but it merely emphasises and illustrates the issue highlighted in this discussion, namely, that attributes are read as string and not numeric!

    The other thing I should mention is that STRTOVALUE would also work, but there are a few caveats, it doesn't like null attributes and if there are, say, null attributes against consolidated level, then you would need to filter based on n level and instead of ASC you would need BASC, so a few caveats to be aware of with that approach. (Thanks to George Tonkin for helping me get to that realisation).

    But I would still like an answer on whether TYPED will work and on whether any utility tools will be available for any upgrades to the next major release. Because if that isn't the case then the work starts now to ensure any existing models can readily transition to the new world.

    regards,

    Mark


    ------------------------------
    Mark Wragg
    ------------------------------



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

    Posted 04/15/22 12:17 PM

    I'm getting the impression people don't trust me when I say "I'll get that fixed!"

    So, just to confirm, today I submitted the PR for the change that'll add support for the, optional, TYPED parameter in the member Properties function which, when specifies, results in it returning the exact same value (read: end up calling the exact same code) then when one used to (hopefully;-) use the bracketed notation to refer to such property.

    @Scott Wiltshire hopefully this takes care of your concern wrt the typing issue as well.

    @Mark Wragg this is your answer to your question if the typed parameter will work with numeric attributes, it does, if the attribute is numeric the numeric value is being passed on to whatever consumes it.

    As for your question wrt a utility that would help ​​to ease the transition, there most definitely is migration utility in the works which takes care of many many more things than MDX related 'topics'. However, thus far, MDX parsing and analysis, presuming these cases can be statically reduces to begin with, has not been part of the scope yet but is on my wish-list as well. Truth be told, it is more a matter of 'technology' then complexity why this currently isn't something we can easily do, but the good news is that we are working to resolve that as well.



    ------------------------------
    Hubert Heijkers
    ------------------------------



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

    Posted 04/19/22 03:45 AM
    Hi Hubert,

    thanks for the reply, much appreciated.

    regards,

    Mark

    ------------------------------
    Mark Wragg
    ------------------------------



Related Content