This article explains the handling of NULL values in different parts and components of Jedox Integrator. NULL values generally represent the absence of an object or a value. It is especially important to clarify how they are handled differently than an empty string ("") or the numerical value 0.
As there have been changes on this topic with Jedox 7.0, the differences are pointed out in brackets [ ].
Data Preview
NULL values are represented in a Data Preview (CLI option -d) for any extract or transform as "«null»". [Before Jedox 7.0: NULL values are represented as empty string].
Note that this is only a representation; the value used in subsequent ETL components is the NULL value.
Extracts
The following extract types can result in rows containing NULL values: Relational, Relational Table, Constant Table, Excel, XML, JSON, and File (for missing columns at the end of a row). To convert a NULL (or space value) directly in the extract, a default value can be defined for each column in the field structure (Alias Map).
Extract Cube
The behavior depends on the "Empty cells" option that is selected:
| includeEmpty | The extract returns a NULL in the value column for empty cube cells. |
| includeEmptyDefaultMapping | The extract returns "0" (for numerical cells) and "" (for string cells) for empty cube cells. This corresponds to behavior in Jedox 6.0 when the "ignore empty"=false option is selected. |
| excludeEmpty | Empty cube cells are not returned from the extract. This corresponds to behavior in 6.0 when the option "ignore empty"=true is selected. |
| excludeEmptyAndZero | If the option "store zero values" is set for the cube, all "0" (for numerical cells) and "" (for string cells) values are not returned. Otherwise the option is identical to "excludeEmpty". |
For the dimension columns, NULL values are not possible as a result. [Before Jedox 7.0, NULL values could not occur. For empty cells the cell value was "0" or "".]
Tree-based extracts
In all tree-based extracts, NULL values (and also empty values) cannot occur as the name of the element.
Transforms
TableView
Filtering of NULL values in a TableView transform for the different operators:
| equal, inRange, inAlphaRange | The condition is never fulfilled for a NULL value. |
| like | NULL values are represented as empty strings, the condition is fulfilled (e.g. for this Regular Expression: ^$). |
| isEmpty | The condition is true for NULL values and for (possibly trimmed) empty strings. |
| isNull | The condition is true for NULL values but not for (possibly trimmed) empty strings. [new in 7.0] |
TableJoin
A join condition on two columns. For a join condition between a NULL value in one column and an empty value in the other column, the behavior depends on the setting "Persisted Join":
| set | NULL and empty values are distinguished; i.e., the condition is not fulfilled (as in a SQL JOIN). |
| not set | NULL and empty values are NOT distinguished; i.e., the condition is fulfilled (as in Lookup function). |
TableAggregation, TableNormalization, TableDenormalization
NULL and empty values in target/key columns are distinguished; i.e., corresponding values in the measures are aggregated separately.
RScript
A NULL value in an input column is transferred as NA value to the R Script engine. Note that this is not the same as a NULL value in R. [Before Jedox 7.0, NULL values were transferred erroneously as empty strings to the R Script engine.]
Tree-based transforms
In all tree-based transforms, NULL values (and also empty values) are not allowed as the name of the element.
Fieldtransform functions
In Fieldtransform functions, NULL values are generally handled as an empty string. For example, the UpperCase function returns "" for an input column value of NULL. Exceptions are the functions listed explicitly below. For Fieldtransform columns without a function, the NULL value remains.
Map function
A static mapping can be defined for NULL values as a map condition or map result by using the keyword #null. For empty values, use the keyword #. [Before Jedox 7.0, only the keyword #space was available to cover empty and NULL values.]
Examples:
| From | To |
| #null | A null value |
| 0 | #null |
| #space | #null |
Groovy/JavaScript function
If the input of a script-based Groovy or Java function is a NULL value, then it is also transferred as a NULL to the Script engine; i.e., it has to be handled as NULL inside of the script. This is valid for all input column types (object, string, double,...).
See the article Groovy Basics for more information.
OlapData function
For empty cube cells a NULL value is returned by this function [Before Jedox 7.0, NULL values could not occur. For empty cells the cell value was "0" or "".]
Loads
Cube load
NULL values in columns corresponding to dimensions are not allowed. The behavior concerning NULL values in the value column are outlined below:
- For numerical base cells in CREATE, UPDATE, or ADD modes, the NULL values can be ignored and the rows are omitted.
- For all other cases, already-existing values in the OLAP cube will be removed.
For cubes that do not support storing 0 values, all 0 or empty values are handled as NULL values. [Before Jedox 7.0, the same is valid.]
Note: If rows with 0 or empty values are loaded in a Cube Load with persisted drillthrough and no cube cell is created in the cube, those rows won't be returned in a Drillthrough request on this cube.
Dimension load
For element names, NULL values (and also empty values) are not allowed. [The behavior concerning NULL values as attribute values has not changed in Jedox 7.0: as in previous Jedox versions, NULL values are mapped to default values (numerical cells use zero and string cells use empty string).]
In ADD mode, the zero/empty string values can be ignored and the rows are omitted. In CREATE, INSERT, and UPDATE modes, already-existing attribute values are removed. Note that attribute cubes can never store 0 values.
File load
NULL values are written by default as empty strings to the CSV files. With the "defaultValue" option, you can define any other representation of the NULL value, e.g. «null» as used in Data Preview. [This option is new in Jedox 7.0.]
JSON load
NULL values are written to a JSON file in this way: "Column1":null [Before Jedox 7.0, NULL values were written erroneously as empty string "Column1":""]
XML load
The following behavior can be configured with the skipNull option:
- set: for null values, no XML nodes or attributes are created.
- not set: for null values, empty XML nodes or attributes are created.
[Before Jedox 7.0, behavior was not set.]
Updated September 27, 2022