Hitachi Vantara Pentaho Community Wiki
Access Keys:
Skip to content (Access Key - 0)

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).
Options are provided to convert data types.
Strings are most common as key-value pairs so you must often convert to Integer, Number or Date.
If you get key-value pair collisions (key is not unique for the group specified) specify the aggregation method to use.

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... ...


  1. Jul 21, 2009

    Jakub Holy says:

    See a more detailed description with examples written by Matt in 2006. The poin...

    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 ...)
  2. Oct 27, 2010

    Romain BOYER says:

    Thank you Jakub, your example helped me to find the way for my solution. The si...

    Thank you Jakub, your example helped me to find the way for my solution.

    The simple example available in PDI 3.2 is really great. (/.../pdi/samples/)

    My explanation may help someones:

    • target fieldname is the names of the fields you want to create
    • value fieldname is the name of the field you want to take the information from (values in the columns you create)
    • key value is the value for which this have to match.

    In other words :

    • if [value fieldname] == [key value] 
    • then [target fieldname] for this line = [value fieldname]
    • end

    Think that the key field is the name of the column you want to take to several columns
    You have to define each column you want to create

    Here are all the answers to the questions I had ;-)

This documentation is maintained by the Pentaho community, and members are encouraged to create new pages in the appropriate spaces, or edit existing pages that need to be corrected or updated.

Please do not leave comments on Wiki pages asking for help. They will be deleted. Use the forums instead.

Adaptavist Theme Builder (4.2.0) Powered by Atlassian Confluence 3.3.3, the Enterprise Wiki