Difference between 0 and NULL with Data-entry

Giulio Vialetto

Dear all,

since it is available the option "Differancate zero and null" on data entry intro the dataview how this information is managed in the cube? On my knowledge of the platform I knew that 0 could be both 0 or NULL under BOARD point of view.

If the option is enable and data-entry is performed:

1. DE 0 -----> which data is stored in the cube?

2. DE NULL -----> which data is stored in the cube?

Best regards,

------------------------------
Giulio Vialetto
Consultant
KVALUE SRL
Italy
------------------------------

Björn Reuber

Hello Giulio,

Board does not distinguish between 0 (Zero and NULL). In both cases a NULL is saved (so data is deleted from the Cube).

When you enable the option "Differancate zero and Null" Board will save an very small Value (i.e. 0.00000000000012), let's call this a "small zero" into the cell instead of the value to also keep the proportion between each child members of the value you are saving.

When you now use a 0 Board will save a small zero, when you are using Backspace Board will save a NULL (delete the Values from the cube)

Regards

Björn

Fethi ZERARA

@Björn Reuber ,

Thank you for your answer!

Just to undestand well : What's the behavior when we run a Dataflow and we got c= a*b ( a being a monthly binary cube - either 1 or 0 )

the Zeros are stored on the cube or not ?

Thanks for your reply ;)

Björn Reuber

Hello @Fethi ZERARA

in this case we are not storing a Zero, the result will be a NULL (cause in a df board does not distinguish between NULL and Zero)

Regards

Björn

Fethi ZERARA

@Björn Reuber,

Thank you very much for your reply !

Ivano Savogin

Hi @Fethi ZERARA ,
According to previous answer from @Björn Reuber , it should be enough to remind that:

If you don't check “differentiate zero and Null” , Null and 0 have “the same value” NULL and they both are treated as NULL.

If you check the “differentiate zero and Null”, zero is considered as 0+epsilon with epsilon being an “extremely small” number that though has weight and it's taken into account when performing calculations, comparisons, and dataflows.

Once you data entry a 0 on a layout that differentiates Zero and Null, this 0 is entered as it would be 0+epsilon and the dataflow considers this value accordingly (writing 0 instead of leaving blank and considering it as a very small number anyway higher than 0 in the evaluation of the IF clause you indicated).

Fethi ZERARA

@Ivano Savogin

Cristal clear ! Thank you

I was just making sure that running a dataflow that fills a slice of a cube with zeros ‘0’ (as a constant !!) has no impact on the calculation domain and 0 is equivalent to Null in this case.

Björn Reuber

Hello,
please consider also the result, if you use the “epsilon” (as @Ivano Savogin called it) in a Procedure.

If you have 10/0 result is Diff/0, so in fact it is NULL/zero

If you have 10/epsilon result will be a very big number, so you might use 10/round(epsilon,5)

Regards

Björn

Audrey Nobles

How about in a layout, I am using differentiate and null with reverse algorithm and have a need to identify when the user has hit backspace to delete a zero and leave null instead. Looking for how to identify when the entry IS NULL, is it possible in a layout algorithm?

Helmut Heimann

Hi @Audrey Nobles ,

In my view, that is not possible. If you knew the epsilon value, you could subtract the cell value from it and derive from the difference whether the cell value is smaller (i.e. expected to be NULL) or not.
This is an eternal problem with floating point numbers: there is no ‘real’ zero detection, you always work with deviations (such an epsilon value) and therefore the result of such a question is always only a probability that it is 0 or not (NULL).

My conclusion is that once you activated "differentiate NULL and 0" you'll no longer be able to determine whether the cell has a value or not after data entry.

Best,
Helmut