Normalizer Mapping Example

A retail organization receives sales totals for the stores in the organization. The organization receives a row of data that contains store information and four sales amounts. Each sales amount represents the total sales for one quarter of the year.

The following example shows how to define a Normalizer transformation to return the sales data to a Store target and a Sales target. The Store target receives one row for each store. The Sales target receives four rows from each store. Each row contains one quarter of sales data.

A Sequence Generator transformation generates a unique ID for each store. The Normalizer transformation returns the StoreID with each output row.

Create a mapping with a Read transformation, a Sequence Generator transformation, the Normalizer transformation, and two Write transformations.

Normalizer Example Mapping

Create a mapping that contains the Normalizer transformation to normalize multiple-occurring quarterly sales data from a flat file source.

The Normalizer transformation generates a separate output row for each quarter of sales and writes the normalized sales amounts to a the Sales target. The Normalizer transformation writes the Store information to a Store target.

The following figure shows the Normalizer transformation mapping:

The mapping contains the following objects:

Normalizer Example Definition

The source is a flat file that contains store information and quarterly sales data. Define the structure of the source data in the Normalizer view.

The STORE flat file contains the following source data:

StoreID	Store_Name	Store_City	District	Manager	Quarter1	Quarter2	Quarter3	Quarter4
1	BigStore	New York	East	Robert	100	300	500	700
2	SmallStore	Phoenix	West	Radhika	250	450	650	850

Add the flat file to a mapping as a Read transformation, and then create an empty Normalizer transformation. Drag the ports from the Read_STORE data object to the Normalizer transformation to create the Normalizer definition.

The Normalizer view contains one instance of the Store_Name, Store_City, District, and Manager fields. The Normalizer view contains four instances of a field called QUARTER. Merge the QUARTER fields to create one SalesByQuarter field that occurs four times.

The following figure shows the Normalizer definition with merged Quarter fields:

Normalizer Example Input and Output Groups

After you modify the input hierarchy, the Normalizer transformation has one input group and one default output group. You need to reorganize the output ports into two groups. You need one group that contains the Store information and one group that contains the Sales information.

The input group contains a port for each field in the source. The output group contains ports for the store fields and port for the multiple-occurring SalesByQuarter field. The output group also contains a generated column ID, GCID_SalesByQuarter, that corresponds to the multiple-occurring SalesByQuarter field.

To return the quarterly sales to a different target, create a new group in the Overview view. In the Output1 group, add the following fields:

Update the default output group. Remove the following fields:

The following image shows the input group and output groups in the Overview view:

The StoreID is the generated key that links the Store information with the Sales information. Verify that both output groups return the StoreID.

Normalizer Example Mapping Output

Add the Write transformation to the mapping and connect the Normalizer transformation output ports to the data objects.

When you run the mapping, the Normalizer transformation writes the following rows to the Store target:

StoreID	Store_Name	Store_City	District	Manager
1	BigStore	New York	East	Robert
2	SmallStore	Phoenix	West	Radhika

The Normalizer transformation writes the following rows to the Sales target:

StoreID	SalesByQuarter	GCID_SalesByQuarter
1	100	1
1	300	2
1	500	3
1	700	4
2	250	1
2	450	2
2	650	3
2	850	4