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, and 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” (default: #value). Normalization can be done by any dimension in the Upload Wizard.
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”).
Settings
| Ignore empty values (checkbox) |
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 |
| Measure aggregation |
See Aggregation Functions |
| 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. |
Note: when the input is coming dynamically from the Source or Transform, but the "Field name" is left empty, the name of the source column/ function is used for the Field name. When the target is manually defined as constant, then the name "constant" is chosen instead of the value from the "Input" field. Adding more than one constant column results in error, because there would be two columns with the name "constant."
Updated September 27, 2022