Planning Analytics

 View Only
Expand all | Collapse all

Write Back/Value Mode and Blank Cells

  • 1.  Write Back/Value Mode and Blank Cells

    Posted Mon October 11, 2021 01:26 PM
    Edited by System Fri January 20, 2023 04:41 PM
    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
    ------------------------------
    #PlanningAnalyticswithWatson


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

    Posted Tue October 12, 2021 04:06 AM
    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 Tue October 12, 2021 05:23 AM
    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 Wed October 13, 2021 03:55 PM
    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 Thu October 14, 2021 06:59 AM
    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 Thu October 14, 2021 06:27 PM
    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 Fri October 15, 2021 04:08 AM
    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 Fri October 15, 2021 06:38 AM
    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
    ------------------------------



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

    Posted Fri October 15, 2021 09:18 AM
    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
    ------------------------------



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

    Posted Sun October 17, 2021 07:49 PM
    Hi Paul,
    My experience with On Cloud is that your TM1 server only gets upgraded when the client requests it via a support ticket.

    Regards,

    ------------------------------
    Craig Sawers
    ------------------------------



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

    Posted Fri October 15, 2021 06:12 AM
    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
    ------------------------------



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

    Posted Sun October 17, 2021 05:38 PM
    Hi Hubert

    I am aware that in future people will be using PAX and PAW and that everything will use the REST API.

    I don't know if things are going to work differently in v12, but I did some experiments with our current version 2.0.9.8, and found the following...

    I built a small two dimensional cube with Total as a Consol of A & B and a measures dimension with Units, Price, and Sales, and put a rule
    ['Sales'] =  N: ['Units'] * [.Price'] ;

    The rules file had SKIPCHECK and UNDEFVALS at the top.

    I ran some tests using the Rest API and looked at the output. 

    The intersection of Total and Sales returned a null until I added a feeder from Units. After that it returned a 0 regardless of whether eg the cell for B and Units was a null.

    I entered a 0 and I found that this was being stored in the cube. The issue is therefore not confined to TI but also occurs with the PAX Cube Viewer.

    I tried an Exploration View in PAX and found that behaved differently as it displayed a 0 even though I had cleared the value using Spread Clear and the underlying REST API was returning a value of 0 with a formatted value of ''.

    I tried entering a 0 into a cell and found that the REST API showed that this was stored as 0 rather than null whether it was entered in the PAX Cube Viewer or a PAX Exploration View.

    Does this mean that a 0 is being stored in the cube and that it is taking up the same amount of space, as eg 500, whereas previously it would not have done so?

    Does this also mean that a calculated result like Sales is also taking up space, if there is 0 in Units and therefore the calculated result is 0? The Rest API shows that a 0 is being returned, but I cannot tell whether that is TM1 returning a 0 or whether a cached result is being stored for Sales rather than just the feeder marker as would be the case without UNDEFVALS.

    The documentation on UNDEFVALS says that when UNDEFVAL (null) is used in any calculation or comparison then it will be treated as a 0, rather than given an UNDEFVAL result. Therefore a 0 would be expected. My question is about the impact on storage of putting a 0 into a cell instead of clearing it to a null.

    There are various systems that I have seen where users are likely to be entering 0s. For example, in a manpower planning system they may enter a 0 into future months to indicate that an employee has left. Given that a user can enter a 0 and this is stored as a 0, there is no easy way to prevent this, as we cannot put programming around it as we can with a TI process.

    In TI processes 0s can still arise. For example, if the data source row has a mix of zero and non-zero data with a series of CellIncremetnNs to put this in the cube, then it would seem that we will need to put IF( vVal <> 0 ) ; around the CellIncrementN's to prevent them storing zeroes?

    Another issue arises from the use of CellIncrementN, which is common. For example, we load data from partners and this is mapped from their local Chart of Accounts to the central Chart of Accounts. Several local rows may be mapped to the same central account, We therefore typically clear data with ViewZeroOut and load using CellIncrementN. It is possible that individual figures will be non-zero, but the net result will be a zero. I cannot see an easy way to avoid that during processing. There is no TI function to clear data in a cell. The only thing that occurs would be to read in the data back from the destination cube and construct views and use ViewZeroOut to clear any data where a 0 had been stored. There would be the problem of how to only read in genuine 0s rather than nulls. 

    I think that there are potentially more problems with this change than IBM might be expecting.

    May be what is needed is some sort of transparent voting system for changes like this. If the majority of customers by license count want this change then the rest of us wlll need to live with it, but it might be worth checking that the majority do want this. 

    I am concerned that it may be a little like the change in TM1 Web where someone decided that the Row references should change from being 1 based to 0 based, which seems like a change to help the developers rather than something the typical accountant user would want. This certainly caused us a lot of problems.

    Regards

    Paul

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



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

    Posted Sun October 17, 2021 06:57 PM
    I think this is a very important topic and should continue to be discussed.

    But, does anyone else have the same issue with PAFE that I called out?

    Sent from my mobile phone





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

    Posted Mon October 18, 2021 04:54 AM
    Hi Ryan

    Apologies for somewhat high-jacking your thread. Unfortunately we only have PAFE 67 and the WriteBack Mode was only introduced in 68.

    Regards

    Paul

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



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

    Posted Mon October 18, 2021 04:28 AM
    Hi 

    I did some further experiments. It is fairly common to use a rule like
    ['Price'] = C: 0 ; 
    as it does not make sense to consolidate prices (yes I know that you could calc an average, but I am interested in the difference between 0 and null)
    With this rule in place the Rest API is showing a Value of 0 and Formatted Value of '0.00'
    Again does that mean that a 0 is being stored as a cached result with UNDEFVALS in place?

    I then changed the rule to 
    ['Price'] = C: UNDEF ; 
    Now the Rest API shows a Value of null and a Formatted Value of '#N/A'
    Is it going to be necessary to change all similar rules to set the value to UNDEF instead of 0?

    In this case there appears to be another difference between an Exploration View and the PAX Cube Viewer

    With the Exploration View showing a blank, while the Cube Viewer shows the #N/A formatted value returned by the Rest API​. Personally I would prefer to see the blank.

    Regards

    Paul

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



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

    Posted Mon October 18, 2021 04:51 AM
    Hi Paul,

    First up, rule derived values are never stored IN the cube. Depending on how much time it took to calculate them they would end up in the calculation cache or not (in this case it would not).

    Your intent here is clear, you like that cell to be blank (as opposed to say that the price is zero/products are free), so setting to UNDEF is the correct way of expressing that.

    Cheers,

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



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

    Posted Mon October 18, 2021 02:11 PM

    Hi Hubert

     

    I have been working with TM1 for over 20 years. I am aware that calculated cells are not stored in the cube. However, the amount of memory used by TM1 does grow as cached results are stored. If TM1 is going to start storing zeroes in the cache because of the change to Undefvals, then this would seem to be an issue. On base level cells, zeroes are going to be stored in the cube. Again, this would seem to be an issue.

     

    I think you may have misunderstood, what I was saying. If we must have UNDEFVALS then I would prefer to see a blank as per the Exploration view, as opposed to the #N/A shown in the PAX cube viewer, but overall I would prefer to leave things as they are ie to have the option whether we want to use UNDEFVALS or not, and my option would be to not have them, and it would therefore show a 0.

     

    Regards

     

    Paul

     

     

     

     






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

    Posted Mon October 18, 2021 02:25 PM
    Hi Paul,

    Again, rule derived results are, if stored, stored in the calculation cache. This is completely unrelated to what we've been discussing and the UndefVals rule (other than perhaps influencing what the value is that would be stored if it got stored). I.o.w. it does not impact the amount of memory used for calculation cache. 

    As for differences between clients/front-ends, that's at their discretion. The REST API returns a NULL which apparently the two clients you reference seem to be interpreting differently. As I've tried to explain the REST API tries to correctly express that those cells are blank, not zero, even without the UNDEFVALS rule, and it would be up to clients to choose how they represent that blank and/or if they use the formatted value returned by the server which, if using the default format, still returns a 0 indeed.

    Cheers,

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



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

    Posted Mon October 18, 2021 04:41 AM
    Hi Paul,

    Looks like you've uncovered some other cases where today, if one uses UndefVals, you'd be getting unexpected (read: incorrect IMHO) results. One more example of an issue/inconsistency that we are trying to fix. 

    I think the problem is actually with the old behaviour where everybody is talking about zero cells all the while that they were really blank in Excel speak (or undefined/null). And it's implementation left no room for dealing correctly with the distinction between real zeros and blank cells. like Excel does, which is what UndefVals introduced 25+ years go while also changing the comparators logic. Most scripting/programming/query languages agree with that logic but it is incompatible with how it used to work without UndefVals, causing all kinds of havoc internally, most notably because UndefVals is per cube and not database/model wide.

    Let me go back and look at the details once again. Got a couple of scenarios in my head but it could be as simple as fixing all the comparators consistently while still having an option to never ever store a zero value, who knows.

    Cheers,

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



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

    Posted Mon October 18, 2021 02:41 PM

    Hi Hubert

     

    For the most part, I don't believe that most people do want to distinguish between a zero and a null. In any case, as I mentioned before if someone really wanted the full semantics, we really need two values, Not Applicable, and Not Known. However, both SQL and TM1 only offer the ambiguous null. The same is also true of many programming languages like JavaScript. In all cases the use of a null value is more about saving space, than about the meaning of the data.

     

    As an example, our core business collects Actuals across 6 segments. However, our partner organisations can only supply values for 3 segments. Our budgeting and forecasting is also only across 3 segments. When loading data into those 3 minor segments (dimensions) we have to load to an element. We could have loaded to a default code, but that would not make it clear that the split was not available for those dimensions. We therefore instead created a zz_Not Applicable element in the minor dimensions and we load into that. This is then semantically clear, but there is no need for UNDEFVALS. The case is different but it is addressing the same issue.

     

    I would agree that because UNDEFVALS is cube based that using a system where one cube has UNDEFVALS in its rules, while another cube does not, is going to cause unpredictable results, especially if one cube references the other in a rule. However, that is one of the reasons that I have never come across anyone actually using UNDEFVALS.

     

    I would therefore tend to agree that if UNDEFVALS is going to be used, then it should be a setting that is either on or off for the whole system, rather than individual cubes with developers having to create a rule file for every cube with UNDEFVALS at the top, which is easily going to be forgotten, particularly for cubes that don't otherwise need rules.

     

    However, I would still prefer the option to have UNDEFVALS on or off.

     

    If UNDEFVALS is turned off, then as there is some degree of programming between any input and a value getting in to a cube, then it should be possible for that programming to change a 0 into a null, or in reality clear out what is currently stored and store nothing, which as I understand it is what currently happens without the UNDEFVALS statement. Only at query time is that 'nothing' shown as a 0.

     

    If a customer decides to turn UNDEFVALS on then the assumption would be that someone storing a 0 actually wants a 0 to be stored in the cube, and will take steps to deal with any increase in size that results from that.

     

    Regards

     

    Paul