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
------------------------------
Original Message:
Sent: Fri October 15, 2021 06:11 AM
From: Hubert Heijkers
Subject: Write Back/Value Mode and Blank Cells
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
Original Message:
Sent: Thu October 14, 2021 06:27 PM
From: Paul Simon
Subject: Write Back/Value Mode and Blank Cells
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
Original Message:
Sent: Thu October 14, 2021 06:58 AM
From: Hubert Heijkers
Subject: Write Back/Value Mode and Blank Cells
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
Original Message:
Sent: Wed October 13, 2021 03:54 PM
From: Paul Simon
Subject: Write Back/Value Mode and Blank Cells
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
Original Message:
Sent: Tue October 12, 2021 04:06 AM
From: Hubert Heijkers
Subject: Write Back/Value Mode and Blank Cells
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
Original Message:
Sent: Mon October 11, 2021 01:26 PM
From: Ryan Clapp
Subject: Write Back/Value Mode and Blank Cells
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