Hitachi Vantara Pentaho Community Wiki
Child pages
  • Row denormaliser
Skip to end of metadata
Go to start of metadata

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

...

  • No labels

2 Comments

  1. 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. 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 ;-)