Row denormaliser
PLEASE NOTE: This documentation applies to an earlier version. For the most recent documentation, visit the Pentaho Enterprise Edition documentation site.
Description
The De-normalizer step allows you de-normalize data by looking up key-value pairs. It also allows you to immediately convert data types.
Options
Option | Description |
|---|---|
Step name | Name of the step; this name has to be unique in a single transformation. |
Key field | The field that defined the key of the output row. |
Group fields | Specify the fields that make up the grouping here. |
Target fields | Select the fields to de-normalize by specifying the String value for the key field (see above). |
Metadata Injection Support
You can use the Metadata Injection supported fields with ETL Metadata Injection step to pass metadata to your transformation at runtime. The following Value fields of the Row Denormaliser step support metadata injection:
Target Filename, Value Fieldname, Key Value, Type, Format, Length, Precision, Currency, Decimal, Group, and Aggregation
Example
CustomerId | Key | Value |
|---|---|---|
101 | COUSINS_FIRST_NAME | Mary |
101 | COUSINS_SECOND_NAME | J. |
101 | COUSINS_LAST_NAME | Blige |
101 | COUSINS_BIRTH_DATE | 1969/02/14 |
101 | COUSINS_INCOME | 1723.86 |
101 | ...45 more keys... | ... |
See a more detailed description with examples written by Matt in 2006.
The point is that in the source database we have a table that stores a variable set of custom attributes for an entity like this:
CustomerId (PK)
CustomAttributeName
CustomAttributeValue
Customer1
preferredMeal
pancakes
Customer1
birthday
1989-11-17
Customer2
anotherAttribute
12345
Here, clearly, we can add any number of custom attributes to our customers using key (CustomAttributeName) - value (CustomAttributeValue) pairs. However in the data warehouse we would like to turn those into columns of their own:
CustomerId
preferredMeal
birthday
anotherAttribute
Customer1
pancakes
1989-11-17
N/A
Customer2
N/A
N/A
12345
The Row denormalizer stpe can does exactly this. It's attributes would be:
The key field: CustomAttributeName
The fields that make up the grouping: CustomerId
Target fieldname: e.g. preferredMeal, birthday, anotherAttribute ...
Value fieldname: CustomAttributeValue
Key value: values of the column 'CustomAttributeName' in the source table (preferredMeal, birthday, anotherAttribute ...)