Denormalize Transformation

You can create many-to-one mappings with the help of a Denormalize transformation object in Astera. Denormalizing, also known as pivoting, allows you to combine a number of records into a single record (simply stating, it brings data from rows to columns). It is useful for reducing the number of tables in the schema, which simplifies querying and possibly improves reading performance.

Video

Sample Use Case

The TaxInfo source data contains information about TaxType (City Tax, County Tax, State Tax, and Federal Tax), Tax Amount, and SSN (Social Security Number) of taxpayers.

We want to reduce the number of rows and create separate fields for City tax, County tax, State tax, and Federal tax.

Let’s see how we can use the Denormalize transformation object to achieve this.

How to Use Denormalize Transformation

1. First, we will use the Sort object to sort our source data based on the key field, SSN in our case.

1. Drag-and-drop the Denormalize transformation object from the Transformations section in the Toolbox.

1. Right-click on the Denormalize transformation object and select Properties from the context menu.

Following are the properties available for the Denormalize transformation object:

Layout Builder Window:

The Layout Builder window is used to add and/or remove fields, as well as to select their data type. The fields added in the Layout Builder will show in the Output node inside the object, as well as in all Input nodes corresponding to the number of mapping groups created (see below), with the exception of the key field(s).

Denormalize (Many-to-One) Transformation Properties Window:

• Select Keys: Using the Select Keys dropdown, select the field or fields that uniquely identify the record. These keys will be used to match records between the normalized source and the denormalized destination.

• Sort Input: Check this option only if values in the matching field (or fields) are not already sorted.

• Driver Field Value: Enter the pivot values for your Denormalize transformation object. Using the example below, the pivot values would be City, State, Federal, and County.

Note: Entering Driver Key Values is required prior to mapping the Denormalize object. For each entry in the Driver Field Value grid, a new input mapping group is created in the object box.

General Options Window: This window shares options common to most objects in the dataflow.

• Clear Incoming Record Messages: When this option is checked, any messages coming in from objects preceding the current object will be cleared. This is useful when you need to capture record messages in the log generated by the current object and filter out any record messages generated earlier in the dataflow.

• Do Not Process Records with Errors: When this option is checked, records with errors will not be output by the object. When this option is off, records with errors will be output by the object, and a record message will be attached to the record. This record message can then be fed into downstream objects in the dataflow, for example a destination file that will capture record messages, or a log that will capture messages and collect statistics as well.

• The Comments input allows you to enter comments associated with this object.

After you have configured the properties, click OK.

1. An Input mapping node will be created for each value previously specified in the Driver Field Value grid.

1. Map the fields and preview the output to view the denormalized data.