# 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 (in short, it brings data from rows to columns).

It is useful for reducing the number of tables in the schema, which simplifies querying and improves reading performance.

{% embed url="<https://youtu.be/OLe_3zXjREk>" %}

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

![](https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2Fi18kjNeKJ4YbRD9BQZGM%2F0.png?alt=media)

![](https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2F98ko7naJM2moSU7SfvxE%2F1.png?alt=media)

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 can we use the *Denormalize* transformation object to achieve this.

## **Using the Denormalize Transformation**

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

![](https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FhhT8VdnarJtZGSm49bIp%2F2.png?alt=media)

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

![](https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FMj8EZinWTWhyTobqhFw9%2F3.png?alt=media)

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

![](https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FZuTsUwHD4fXVESYV6cHg%2F4.png?alt=media)

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

{% hint style="info" %}
**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.
{% endhint %}

![](https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FBNXJaU5ALtUGC7wVB1h6%2F5.png?alt=media)

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

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

![](https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FvdUj3z2tIOkQzhjK6qdw%2F6.png?alt=media)

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

![](https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FkEEcyl3XvAVeNZfZW3LV%2F7.png?alt=media)

![](https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FeBTGsr7BOC6F1vSkYtBF%2F8.png?alt=media)