Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only
Back to discussions
Expand all | Collapse all

MDX Property is blank on referenced member's Description alias

  • 1.  MDX Property is blank on referenced member's Description alias

    Posted 4 hours ago

    If you are using REST API calls and/or the TM1Py function tm1.power_bi.get_member_properties you need to read further.

    After a recent upgrade to PA 2.0.9.22 (11.8.3000.9) we noticed that our structures in Power BI were wrong, missing the root members.
    On investigation it turns out that the parent_attribute being used in TM1Py was being returned as blank.
    What we were expecting was the description of the parent.

    The MDX below gives a bit more clarity:

    WITH 
   MEMBER [}ElementAttributes_Customer].[}ElementAttributes_Customer].[ParentDescription] AS 
   [Customer].[Customer].CURRENTMEMBER.PARENT.PROPERTIES("Description")
   MEMBER [}ElementAttributes_Customer].[}ElementAttributes_Customer].[ParentName] AS 
   [Customer].[Customer].CURRENTMEMBER.PARENT.NAME
SELECT 
   {    {  [}ElementAttributes_Customer].[Description]  } +   {
            [}ElementAttributes_Customer].[ParentName], [}ElementAttributes_Customer].[ParentDescription] }   } ON 0, 
   {
      TM1FILTERBYLEVEL(
         [Customer].[Customer].MEMBERS , 0)
   } ON 1 
FROM
   [}ElementAttributes_Customer]

    Where we have a root element like Total Customers we would never specify the alias of Description as Total Customers as this is redundant. TM1 does that inherently, or at least did.

    Running the above code on an older version e.g. PA 2.0.9.20 IF2 we get:


    Running the same code on the same dimension with the same attributes etc. but on PA 2.0.9.22 we get:

    Case is logged with IBM support (and on the TM1Py GitHub #1269) but in the meantime a couple ideas have been thrown around.
    Use CoalesceEmpty() around the properties statement i.e.:

    COALESCEEMPTY( [Customer].[Customer].CURRENTMEMBER.PARENT.PROPERTIES("Description", TYPED),
                 [Customer].[Customer].CURRENTMEMBER.PARENT.NAME)

    No value was returned leading me to suspect that the value is not truly null but rather blank. Confirmed by joining >> and << around the CoalesceEmpty statement as I saw only >><< being returned.

    Using an IIF() statement and testing for description being a blank string, "" using the below syntax substitutes in the name when blank.

    IIF( [Customer].[Customer].CURRENTMEMBER.PARENT.PROPERTIES("Description", TYPED)="",
               [Customer].[Customer].CURRENTMEMBER.PARENT.NAME),
               [Customer].[Customer].CURRENTMEMBER.PARENT.PROPERTIES("Description", TYPED))

    So maybe a workaround for now but the bigger concern is what else may have changed with the recent versions and what may it impact at a later date.

    Please be aware of this and post back if you have noticed similar issues so that I and others can test and log with IBM for resolution.




    ------------------------------
    George Tonkin
    Business Partner
    MCI Consultants
    Johannesburg
    ------------------------------


Related Content