Handling of NULL Values in Jedox Integrator
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.
Data Preview
NULL values are represented in a Data Preview for any extract or transform as "«null»". Note that this is only a representation; the value used in subsequent Integrator 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).
Cube Extract
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. |
| excludeEmpty | Empty cube cells are not returned from the extract. |
| 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.
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. |
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.
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 #.
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,...).
OlapData function
For empty cube cells a NULL value is returned by this function.
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.
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.
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.
JSON load
NULL values are written to a JSON file in this way: "Column1":null.
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.
Updated September 27, 2024