# 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

{% embed url="<https://www.youtube.com/watch?t=17s&v=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://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FOJC9lZROHUgq2qQV3ldQ%2F1.png?alt=media\&token=18f8aaf5-1462-4fd1-91a9-ed9fd9697e89)

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FcEbhp1gpYhxOYi4lmiNW%2F2.png?alt=media\&token=9e22ace8-9c79-4e4a-945c-4a5b2e3eee41)

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.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FIamoAWpoVG3gqH7Ts1fy%2F3.png?alt=media\&token=1e36b948-9b84-4786-8d29-89600f40faa2)

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

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FEb1x3UiCM7IxQsl08SIL%2F4.png?alt=media\&token=c2d495b7-2dbf-4375-a024-8e98c29f3373)

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

<figure><img src="https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FHiii85H34S8DTaTllxn6%2FScreenshot%202024-09-24%20164457.png?alt=media&#x26;token=53f02790-5876-4e34-84e4-a1185c70c0ff" alt=""><figcaption></figcaption></figure>

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

{% 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 %}

<figure><img src="https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2F9wjfnn5RIQKB5h9W33Ll%2FPivot%20Keys.png?alt=media&#x26;token=0180e523-29f7-4fd9-8a7f-422114aebd2e" alt=""><figcaption></figcaption></figure>

*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://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FCKnG30BcJXxZil2CN2kM%2FScreenshot%202024-09-24%20165625.png?alt=media\&token=87d23fda-3c91-4f1d-87f7-5310829fa029)

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

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FWxDkdeqW7HWV67OS118y%2FScreenshot%202024-09-24%20165032.png?alt=media\&token=917cba22-e3f5-4315-8d30-3bc5f4d27066)

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FlmzpNCktxMhzgwRU2o2m%2FScreenshot%202024-09-24%20165307.png?alt=media\&token=bd2e0243-123a-48ab-bbaf-daed0e2c825d)