Planning Analytics with Watson

Expand all | Collapse all

Write Back/Value Mode and Blank Cells

  • 1.  Write Back/Value Mode and Blank Cells

    Posted 4 days ago
    Edited by Ryan Clapp 4 days ago
    1st big thanks to IBM for recognizing that users want PAfE to behave the same as perspectives! The new modes for custom report modes make the product much easier to use. Now I can be sure that dragging around my DBRW formulae won't break my sheets.

    However, I am seeing an odd behavior when using write back mode and I wanted to confirm the behavior is working as intended.

    • In write back mode when dragging an empty cell(s) on top of an existing DBRW that references a string cell, it deletes the formula instead of writing back a "blank"
    • In write back mode when dragging cell(s) that contain a ' (empty string) on top of an existing DBRW that references a string cell, it commits a "blank" back to TM1

    • In write back mode when dragging an empty cell(s) on top of an existing DBRW that references a numeric cell, it deletes the formula instead of writing back a "blank"
    • In write back mode when dragging cell(s) that contains a 0 on top of an existing DBRW that references a numeric cell, it commits a 0 back to TM1

    I don't particularly like the above behavior as TM1 does not differentiate between Blanks, Empty Strings, and 0s. 


    Enabling UNDEFVALS keeps the above behavior making it even more confusing. 

    • In write back mode with UNDEFVALS enabled when dragging an empty cell(s) on top of an existing DBRW that references a string cell, it deletes the formula instead of writing back a "blank"
    • In write back mode with UNDEFVALS  when dragging cell(s) that contain a ' (empty string) on top of an existing DBRW it commits a "blank" back to TM1

    • In write back mode with UNDEFVALS when dragging an empty cell(s) on top of an existing DBRW that references a numeric cell deletes the formula instead of writing back a "blank"
    • In write back mode with UNDEFVALS when dragging cell(s) that contains a 0 on top of an existing DBRW commits a 0 back to TM1

    How would you write back an UnDefValue via PAfE if a blank cell is not equivalent to one?

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


  • 2.  RE: Write Back/Value Mode and Blank Cells

    Posted 3 days ago
    Ryan,

    Trust you want it to never overwrite that DBRW formula and therefore always just set the appropriate value?

    I.o.w. if the cell is empty (in Excel meaning Numeric but not containing a value - the IsBlank function would return TRUE) you'd expect the 'null' value to be written to the cell referenced by the DBRW making that cell blank (empty/null) in TM1 as well.

    An empty string (') in Excel is not considered to be blank in Excel itself but in Excel you need that to distinguish between a numeric and string cell, whereas in TM1 the type is defined by the element of the last dimension in a cube.
    Writing an empty string to a TM1 cell, provided the cell is a string cell, would have the same effect as writing a 'null' to it and blank (empty/null) that cell in TM1.

    IMHO the UndefVals 'setting/switch' on a TM1 cube shouldn't influence how the PAfE behaves. I.o.w. I think that if you drag a cell that contains a 0 on top of an existing DBRW that PAfE should indeed always send a 0 back to TM1 irrespectively. If your intention was to keep that cell empty then, again IMHO, you shouldn't have put a 0 in it in the first place;-)

    PS This is an important topic as we are removing UndefVals in TM1 12 where, like Excel, making a distinction between zero (0) and blank (null/empty) will be the standard (read: UndefVals is always on if you will).

    Cheers,

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



  • 3.  RE: Write Back/Value Mode and Blank Cells

    Posted 3 days ago
    Right.

    In write back mode I'd expect pafe to always write back. They only way I should be able to delete a formula is with an explicit request to do so. Like a clear cell contents.

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



  • 4.  RE: Write Back/Value Mode and Blank Cells

    Posted 2 days ago
    Hi Hubert 

    What is the reason for effectively making everything an Undefval? The option to use Undefvals has been there since version 6. However, I have never seen anyone use it. There is a reason for that. It tends to complicate models. 

    SQL, for example, has the Null value, however, the Null value isn't always enough. For full semantics, we often want to be able to distinguish whether the special value means either
    a) For this cell combination, the value is Not Applicable, eg the Miles per Gallon for an Electric Car
    b) For this cell combination, the value is Unknown, eg a survey result where no value was selected.

    So the Null value doesn't really do the job, and yet it complicates models, eg we need to write SELECT ISNULL(MyField,0) as MyField, instead of just SELECT MyField, It would seem to me that Undefval is going to do the same to TM1 output, whether it is from rules, or processes, of views, etc.

    One reason that SQL has a Null is because it saves on storage. TM1 with sparse consolidation obviously has the same issue, but until now TM1 has managed that behind the scenes and developers who are sometimes accountants have not had to bother about it. For most financial modelling purposes, there is no need to distinguish between null and 0.

    The typical use case for TM1 is budgeting and forecasting. If someone does not explicitly enter a non-zero value into a cell then the accountant wants the value for that cell to be treated as 0, not undefvals. Similarly the contributor does not want to have to explicitly enter a 0.

    There are numerous models out there that check for 0. It would seem that a lot of these models are going to have to be adapted to check for 0 or Undefvals. If that is the case then this is going to break a lot of existing models.

    Regards

    Paul Simon




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



  • 5.  RE: Write Back/Value Mode and Blank Cells

    Posted yesterday
    Hi Paul,

    First time I came in contact with TM1, must have been late 2002 or 2003 sometime, my first question was "What are all these zero's doing here, I've not put any values in yet!". Looking at the 'issue' a bit more turned out that it was the front-end that put those zero's there whilst TM1 was telling it those cells were undefined all along;-!

    The undefined value has existed since the inception of TM1. It was the value used to indicate that the cell, as the name suggests, was undefined and therefore was to be treated as having no value. Obviously, I was barely a teenager then when the decision was made however to use the value 0.0 (zero) to indicate that the cell did not have value and therefore was undefined. Unlike your personal experience, there are people out there that do want to distinguish between a cell having a value and there not being a value (myself included) which, pretty early on in life TM1 added the UndefVals rule which does nothing else than setting that internal undefined value to a 'not-a-number' number, clearly distinguishable from any other valid number, allowing TM1 to distinguish between an undefined value and a cell containing the value zero and as such allow zeros to be stored.

    Whilst UndefVals properly sets that newly defined undefined value, the rest of the system however had many places where this change had [side] effects as well which weren't implemented properly causing many issues, ambiguities and inconsistencies. The latest changes in that respect were made as recently as in version 11.8.5 related to all kinds of comparison operators were fixed up to deal with undefined cells correctly. And even with those changes in there are still cases where the system, as is, can't give you the guaranteed correct answer, especially when values come from different 'cubes' (note: can be attributes etc as well). In TM1 12 we choose to clean up this mess once and for all, making the system predictable and consistent across the board, for all cases, resulting in the UndefVals function becoming obsolete and having UndefVals effectively by default everywhere always.

    TM1 12 is also the first version of TM1 that is REST API only. The REST API, and it's internal predecessors used by all clients build since 2007, have always returned null for undefined cells irrespective of the fact if UndefVals was on or off. So for clients building on the REST API there is no impact/difference. And format strings already have their rules for how to deal with positive, negative, zero and undefined/null values, that's not changing either so your formatted values don't change either.

    But you are right, there will be cases, in rules and TI, where you'd have to express your intent more correctly perhaps and/or deal with the fact that there not being a number and the number that's there being zero matters for you. Most notably, a bad habit I've seen many times over the years, don't load/put the value 0 if you are not interested in those 0 values or better yet, have the source system filter those values out that you are not interested in loading into TM1 to begin with!

    Hope this helps,

    Cheers,

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



  • 6.  RE: Write Back/Value Mode and Blank Cells

    Posted 22 hours ago
    Hi Hubert

    I think you have a date wrong there. I am pretty sure the Rest API did not exist in 2007, Did you mean 2017?

    It may be that the Rest API returns a null, but most users don't use the Rest API directly.

    I think there is a disconnect here between most users and IBM. Which users have asked for the default to be changed to Undefvals?

    Regards

    Paul Simon

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



  • 7.  RE: Write Back/Value Mode and Blank Cells

    Posted 12 hours ago
    Hi,

    I would be really scared if undefvals was suddenly activated on all our models !
    Hubert, you said "UndefVals is always on if you will".
    Can you confirm this will be something we can disable in the cfg file ?

    Regards,


    ------------------------------
    Catherine BIDOIRE
    ------------------------------



  • 8.  RE: Write Back/Value Mode and Blank Cells

    Posted 10 hours ago
    Hi Paul,

    I think you are missing the bigger point, the majority of people, come TM1 12, will all be using clients/applications/tools that all use, indirectly if you want to call that out explicitly, use the REST API, will not be impacted by this whatsoever. Rules aren't impacted either if zeroes aren't explicitly written into the cube, and even then it depends on the rule if they would or not (and if they do then I'd argue you typically would need UndefVals anyway as there being a zero value apparently matters). The only thing that might need some attention is indeed TI scripts that write data into a cube and that are somewhat oblivious and write the value zero even though they don't intend to write zeros and/or write zero with the intent to clear the cell (read: instead of using a clear). 

    As for the APIs, I said REST API "and its internal predecessors" , a.k.a. the NGTM1API or NGAPI. The first version of the REST API dates back to 2012 (parts of which are still used by PAfE today). The first public release of the REST API was in May 2014 in TM1 10.2.2 (but you knew this already if you pulled the $metadata from the REST API itself;-).

    Cheers,

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



  • 9.  RE: Write Back/Value Mode and Blank Cells

    Posted 10 hours ago
    Hi Catherine,

    TM1 12, the functional database that powers Planning Analytics, is a major new version of TM1 as you know it. Whilst the design and workings of the core engine haven't changed, the larger TM1 'service' is redesigned from the ground up with a "cloud-first", highly-available, containerized environment in mind.

    Details on how future versions of Planning Analytics, powered by TM1 12, are begin delivered/deployed will be forthcoming but I think it is safe to say that you don't have to worry about this suddenly being activated. Also, IBM has already communicated that it will support the current LTSR version of TM1, TM1 11.8, at least until the end of 2024 giving customers ample time to adopt. 

    PS If you are interested in TM1 12 and haven't heard me talk about it yet, I'm doing a presentation at the TM1 Horizon conference next week which will contain a sneak preview of TM1 12.

    Cheers,

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



  • 10.  RE: Write Back/Value Mode and Blank Cells

    Posted 7 hours ago
    Hi Hubert

    What is the situation for those running on the IBM Cloud? Are IBM going to run a v11 and a v12 Cloud, with customers being able to delay moving to v12 until 2024, or are those on the Cloud going to be upgraded to v12 as soon as it is released?

    Regards

    Paul Simon

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