TableNormalization Transform

image_pdfimage_print

Normalization transposes column based data into row based data with key-value pairs. This allows the arrangement of a relational model, with measures in various columns, into an OLAP model, with one measure in each row with the measure name as a key. The column names from the data source are the measure coordinates in the resulting table. A column with the measure values from the source is created with name indicated in “Field name value” (default: #value).

As of Jedox Version 7.1, normalization can be done by any dimension in Upload Wizard (before it can only be done by the dimension Measure).

The aggregation functions are the same as for TableAggregation transform. Note that for high data volume, the performance of TableNormalization is better if no aggregation is done (aggregation=”none”).

 

“Ignore empty values” option

If the data source of a TableNormalization transform contains a huge amount of 0 values in the key figure columns, this may lead to a very high number of rows. These rows can be filtered during the normalization process with the option “Ignore empty values”. If set, this will increase the performance and, in the case of additional aggregation, also reduce the memory used.

Advanced settings:

  • Ignore empty values: If the data source of a TableNormalization transform contains a huge amount of 0 values in the key figure columns, this may lead to a very high number of rows. These rows can be filtered during the normalization process with the option “Ignore empty values”. If set, this will increase the performance and, in the case of additional aggregation, also reduce the memory used,
  • Column include pattern: Regular expression for column names to be included as output columns of the transform
  • Column exclude pattern: Regular expression for column names to be excluded as output columns of the transform
  • Measure include pattern: Regular expression for measure names to be included as output columns of the transform
  • Measure exclude pattern: Regular expression for measure names to be excluded as output columns of the transform
  • Keep source order: If set the order of output rows respects the order in the source with first appearance of a particular key. If not set the order is arbitrary but the performance might be better.
image_pdfimage_print
Was this post helpful?
NoYes (-8 rating, 12 votes)
Loading...