Note: In this article, "0" and “zero” are used interchangeably. Both denote an explicitly stored value, in contrast to “empty” or “null”, which denote an empty, not-existing value.
As of Jedox 7.0, the Jedox OLAP Server can optionally store zero (0) values in cubes. This storage can be enabled for individual cubes; by default, it is not enabled.
Prior to Jedox 7.0, the Jedox OLAP Server did not differentiate whether a numeric base cell in a cube was completely empty, or held the value 0. If a user entered 0 as value on a base cell, that cell value was deleted from the cube storage in memory. The same happened for a set of base cells if the user splashed a 0 as value on their parent cell.
However, in some usage scenarios, a zero value in a cell has a different informational value than if the cell is completely empty. For these scenarios, the new option to store zero values has been introduced.
With regard to used memory of a cube, a stored 0 is a value just like any other stored numeric value. It doesn’t consume more or less memory than any other cell.
Enabling storage of zero values
The option to store zero values is enabled for each cube on an individual basis. The setting is made in the “Advanced” section of Cube Properties in the Modeler:
After enabling the option, the existing empty cells in the cube will not be automatically converted to 0 values. They will remain empty until new 0 values are entered.
Note: when the option is disabled on a cube, all existing base cell 0 values are deleted. This change cannot be undone!
The Number of filled cells value in the Information section of a cube includes any stored 0 values.
Behavior when viewing/reading cube values
If the “Store Zero Values” option is not enabled for a cube, the output of cell values in PALO.DATA/C/V formulas is comparable with Jedox 6.0. A "0" will be visible as a value for all empty cells. However, if the option “Store Zero Values” is enabled, the output of these formulas changes:
- If the cell is empty, the PALO.DATA() formula will display an empty string in the cell. The actual cell value is an empty string.
- If the cell value is a real stored 0, the formula will show a 0 as a value. If one of the base cells underneath an aggregated element has the value 0, and all other base cells are empty, the consolidated value will also be 0, and is shown as 0.
- Certain formula calculations on such cells will return different results, depending on whether the value is empty or 0. A SUM() formula result would be identical. However, an AVERAGE() formula would return a different result: it would not consider an empty value for its result, but would consider a 0 value.
- In Jedox Web, if the data range for a line chart includes an empty value, the value is not plotted at all in the line chart. By contrast, a 0 value would be plotted at data point 0 on the data axis of the chart.This distinction is also true for rule-calculated values (see Output of values in rules).
A view in Jedox Excel Add-In on a cube where the “Store Zero Values” option is enabled.
The value for “Feb” and “2019” is a stored 0; the aggregated value for “Qtr. 1” is also 0.
When exporting a cube, the Ignore empty cells option is relevant for the storage of zero values. If this option is not set, the export returns all cells for the defined slice. If the Ignore empty cells option is set, the export will not include empty (or “null”) cells. However, it will include cells with the value 0.
Writeback behavior
If the option to store zero values is not enabled for a cube, the writeback behavior is backwards compatible with previous versions of Jedox; in other words, when writing zeros, cube results will be like in earlier Jedox versions. If you write 0 in a cell, the cell is deleted. The same happens if you splash zeros, or if zero values are included in an ETL load.
In the Excel Add-in and Jedox Web (in the Reports component) you can also delete a cell by using the Delete key on the cell (in Excel: Ctrl + Delete). This behavior also stays as before.
If the option to store zero values is enabled, the writeback behavior changes. If you enter a 0 in a base cell, this 0 is now stored as a value in the OLAP cube. In aggregated cells, if you splash 0 (with #0), the base cells will still be deleted, which prevents other users (who may not be aware that the option is used) from accidentally creating a large number of stored 0 values. To splash zeros on an aggregated cell, the new input commands #00 and !00 should be used.
The Delete key (CTRL + Delete in Excel) deletes cell values (on base or aggregated level). For copy operations, the 0 values in the source are copied to the target area.
Rule calculation
Rules can use empty or 0 values in several ways. For example, a rule could include an IF() function that checks whether a value is 0: IF(... == 0,...,...). Also, a rule could return 0 as a value.
Rule calculation is also impacted by the OLAP server's ability to differentiate 0 values and empty values. The Jedox rule calculation engine uses a consistent approach, regardless of whether a cube has been enabled to store zero values or not. However, there is some impact in regard to backwards compatibility for database models, and specifically for rules created with Jedox 6.0 or earlier versions.
Generally, as of Jedox Version 7.0, the OLAP server uses a new constant value in rule definition: null. This constant denotes a non-existing value. In terms of operations (see below), it is regarded as identical to the values 0 or ''''; but in output of rules, it is different.
The Marker Index treats stored 0 values like any other numeric value; base cells holding 0 value are indexed by a Rule Marker.
Value comparison in rules
When doing value comparison in rules using the == operator, zeros, nulls, and empty strings are regarded as the same value. So the following expressions are all true: 0 == null, 0 == "", and null == "".
To see whether a value returned from a PALO.DATA function (also its shorthand syntax, [‘...’]) is empty, a new rule function has been introduced: ISNULL(). This function returns the value true if the cell value is empty and the value false if the value is 0.
This approach ensures that comparisons in rules are backwards compatible. As of Jedox Version 7.0, a comparison [‘...’] == 0 will return "true" as well, regardless of whether the cell value is empty or 0 (the latter only being possible if the cube has the Store Zero Values option enabled).
Output of values in rules
The output of a rule is different in cubes that have the option Store Zero Values enabled, depending on whether the rule returns 0 or null.
Consider the following simple rule: [‘2019’] = 0. Spreadsheet cells for which this rule is calculated display the value “0” (same as in previous versions), regardless of whether the Store Zero Values option is enabled or not. By contrast, the rule [‘2019’] = null would show up as a dash in a spreadsheet cell if Store Zero Values is enabled. If the option is disabled, the cell would also show “0” as value.
Important: when exporting a cube, values from rules that return 0 are not filtered out by the Ignore empty cells option, regardless of whether the Store Zero Values option is set or not. Values from rules that are null (empty) are filtered out. To also filter out 0 values from rules, cube extracts in Jedox Integrator introduce a new filter option, “excludeEmptyAndZero”. Details can be found in the article Handling of NULL Values in Jedox Integrator.
Updated September 27, 2022