TM1 Forum

I've been testing the UNDEFVALS functionality lately (using TM1 9.5.1) and by now I can't imagine that this functionality is of use to anyone.
Main reason for this is that it seems to be impossible to return a populated cell (containing either a non-zero or zero value) to an unpopulated state (containing the 'UNDEF' value) - apart from deleting/recreating the cube or restoring a backup (you can force cells with a rule, but than the cell is not writable anymore and the cell is still visible in a zeroed out view).
I wanted to investigate the effect of UNDEFVALS on data spreading - f.i. whether the Repeat Leaves function applied to all populated cells would also write to cells containing a zero. But unfortunately the Repeat Leaves on populated cells will skip both empty cells and cells populated with zeroes.

Am I missing something here? Is anyone using this functionality and is satisfied with how it works?


Michel

Undefvals is clearly an attempt to introduce the 'null' concept in RDBMS's.

If you were building TM1 from scratch now clearly you would make the empty cells contain a null value and not zero so you could store a zero value legitimately. Unfortunately the implementation is half hearted and like you I tried to play with this to see if it was a useful technique and I could not get it to work.

I think I tried (IIRC) [area] = undefvals ; and I think you get a compile error.

I think there was never the critical mass of user pressure to get this to work although I have found problems where you add values together to make 0 and the result disappears from the input of a TI. In retrospect it is logical that it would do this but when you add A with 100 values to B with 100 values and only get 99 values in your TI it is very surprising. So you hit the occasional problem and you find some other way to solve this.

If you have a small test cube with UNDEFVALS and you export it having unchecked the 'Skip Zero/Blank Values' box, you will get a file full of the number 4.94066e-324.

If you enter this number into a cell then TM1 will blank it when you recalculate and remove it the next time you save.

Duncan P wrote:If you have a small test cube with UNDEFVALS and you export it having unchecked the 'Skip Zero/Blank Values' box, you will get a file full of the number 4.94066e-324.

If you enter this number into a cell then TM1 will blank it when you recalculate and remove it the next time you save.

Interestingly, a year and a bit later, a Technote was released on that very issue. It can be found here.