TableDenormalization Transform


Denormalization is the reverse of normalization. Row-based data with key-value pairs is transposed into column-based data. This allows an OLAP model with one measure in each row (with the measure name as a key) to be arranged into a relational model in which the measures are held in various columns.

In general, it is necessary to aggregate the measure columns to get the denormalized columns in one row. The aggregation functions are the same as for Table Aggregation transform. For numerical values, it is convenient to use the aggregate function “sum”, and for text values, the aggregate function “max”.

Dynamic Measure Source

The measures can be either defined statically in the measures table or retrieved dynamically from a source that is specified in “dynamic measure source”. If both approaches are mixed, the dynamic measures, that are not defined statically, are appended at the end to the measures list. The dynamic measure source must have exactly one column with an arbitrary name, with each row defining one measure.

Dynamic measures have the following limitations:

  • All dynamic measures have the same aggregation function, defined in “Measure aggregation”.
  • The name of the measures cannot be changed in the transform.
  • The measure type is “numeric”.

Note: In order to use as coordinates all source input columns which are NOT defined as dynamic measures, you have to set the “column include pattern” to “.”

Examples for Denormalization

Example 1:

Settings and Preview in Table Denormalization Transform:

Example 2:

This example you can upload in Integrator from
[Jedox_Install_path]\Jedox Suite\tomcat\webapps\etlserver\data\samples\sampleDenormalzation.xml.

Source (normalized) as Extract Preview:
Please select start line 121 in “Data Preview”, because the lines in target preview are sorted differently!

Output of Table Denormalization Transform (denormalized):