Use Case: Dynamic Mapping for Metadata Changes in Relational Sources

Source Tables

Target Table

Dynamic Mapping

• •Read transformations that can read from dynamic sources
• •Dynamic ports in the downstream transformations that can pass new and changed columns
• •Write transformation that can write to dynamic targets
• •Run-time links that can connect ports to the Write transformation at run time

1. 1. Fetches the structure of the data objects and metadata changes in the source files.
2. 2. Passes the new and changed columns to each transformation through dynamic ports.
3. 3. Connects the new and changed ports to the Write transformation.
4. 4. Writes the transformed data to the target.

Read_CUSTOMER

Read transformation that represents the relational source CUSTOMER. The relational table contains a separate row for each customer.

Read_ORDERS

Read transformation that represents the relational source ORDERS. The relational table that contains a separate row for each customer order.

Join_CustomerOrders

Joiner transformation that joins the CUSTOMER and ORDERS sources.

Agg_CustomerOrders

Aggregator transformation that aggregates the total customer orders.

Write_CUSTOMERSUMMARY

Write transformation that represents the relational target CUSTOMERSUMMARY. The relational table contains a column for the mapping to write the aggregated value for total orders grouped by customer.

Step 1. Configure the Read Transformations

1. Add two Read transformations that represent the CUSTOMER and ORDERS relational data objects.
2. Configure the Read_CUSTOMER transformation to fetch column and metadata changes directly from the sources at run time.
1. a. Select the Read_CUSTOMER transformation.
2. b. In the Properties view, click the Data Object tab.
3. c. Select At run time, get data object columns from the data source.
The following image shows the Data Object tab settings of the Read_CUSTOMER transformation:

3. Configure the Read_ORDERS transformation to fetch column and metadata changes directly from the sources at run time.
1. a. Select the Read_ORDERS transformation.
2. b. In the Properties view, click the Data Object tab.
3. c. Select At run time, get data object columns from the data source.

Step 2. Configure the Joiner Transformation

1. Add a Joiner transformation Join_CustomerOrders to the mapping.
2. Create dynamic ports in the Joiner transformation:
1. a. From the Read_Customer transformation, drag the All Ports group to the Master group in the Joiner transformation.
The Developer tool creates a dynamic port From_Read_CUSTOMER in the Master group and the Output group.
2. b. From the Read_Orders transformation, drag the All Ports group to the Detail group in the Joiner transformation.
The Developer tool creates a dynamic port From_Read_ORDERS in the Detail group and the Output group.
The dynamic ports include all ports from the corresponding Read transformations as generated ports.
The following image shows the All Ports groups from the Read transformations linked to the two dynamic ports in the Joiner transformation:

3. In the Properties view, click the Join tab.
4. Click the New button, and define the join condition as C_CUSTKEY = O_CUSTKEY_DTL.
The following image shows the Join tab with the join condition defined:

Step 3. Configure the Aggregator Transformation

1. Add an Aggregator transformation Agg_CustomerOrders to the mapping.
2. Create dynamic ports in the Aggregator transformation:
1. a. From the Output group in the Joiner transformation, drag the From_Read_CUSTOMER dynamic port to the Aggregator transformation.
A dynamic port From_Read_CUSTOMER appears in the Aggregator transformation.
2. b. From the From_Read_ORDERS dynamic port of the Output group in the Joiner transformation, drag the O_TOTALPRICE_DTL generated port to the Aggregator transformation.
The following image shows the ports from the Joiner transformation linked to the Aggregator transformation:

3. In the Properties view, click the Ports tab.
4. Click the New button to create a port to aggregate prices of the orders.

The Developer tool creates a new port called Field.
5. Select the new port, and change the column values as follows:
• - Name: O_TOTALAMOUNT
• - Type: decimal
• - Precision: 10
• - Scale: 2
• - Input: Clear the selection to make this port an output-only port.
The following image shows the ports in the Aggregator transformation:

6. In the Expression column for the O_TOTALAMOUNT port, click the Open button.

The Aggregator Expression window appears.
7. Replace the existing expression in the editor with the following expression: SUM(O_TOTALPRICE_DTL)

8. Click Validate to validate the expression.
9. Click OK.
10. Click OK to exit the Aggregator Expression editor.
11. In the Properties view, click the Group By tab.
12. Specify the group by port to aggregate the total price by market segment as follows:
1. a. Make sure that Value from the Specify by list is selected.
2. b. Click Choose.
The Ports dialog box appears.
3. c. Select the checkbox next to C_CUSTKEY and click OK.
The following image shows the selected group by port:

Step 4. Configure the Write Transformation

1. Add the CUSTOMERSUMMARY relational data object as the Write transformation.
The Write transformation appears in the editor as Write_CUSTOMERSUMMARY.
2. Verify that the Write transformation is configured to automatically re-import metadata changes.
1. a. In the Properties view, click the General tab.
2. b. Make sure that Synchronize input ports is chosen.
3. Configure the Write transformation to get columns directly from the target table at run time.
1. a. In the Properties view, click the Data Object tab.
2. b. Select At run time, get data object columns from the data source.

Step 5. Create and Configure a Run-time Link

1. Press Ctrl and drag the All Ports group from the Aggregator transformation to the All Ports group of the Write transformation.
The Run-time Linking dialog box appears.
2. Verify that Link Policy in the Link Properties area is selected to automatically link ports by name at run time.

3. Click OK.
The Developer tool creates a run-time link between the Aggregator transformation and the Write transformation.

Step 6. Validate and Run the Mapping

1. In the mapping editor, click Edit > Validate.
2. When the mapping is valid, click File > Save to save the mapping.
3. Click Run > Mapping.
The Run Mapping window displays the progress of the mapping run. The mapping runs and writes the output to the target file.
4. In the Object Explorer view, locate the CUSTOMERSUMMARY data object in your project and double click the data object.
The data object opens in the editor.
5. Click Window > Show View > Data Viewer.
The Data Viewer view appears.
6. In the Data Viewer view, click Run.
The Data Viewer view runs and displays the data.
In this example, the C_TOTALAMOUNT column displays the aggregated total price of customer orders.

Step 7. Run the Mapping after Changes to the Source Schema

1. In the mapping editor, view the changes to the mapping.
The Read and Write transformations automatically reflect the new columns. The dynamic ports in the Joiner and Aggregator transformations automatically have the new columns C_COMMENT and O_COMMENT from the respective Read transformations.

2. To validate the changed mapping, click Edit > Validate.
3. When the mapping is valid, click File > Save to save the mapping.
4. Click Run > Mapping.
The Run Mapping window displays the progress of the mapping run. The mapping runs and writes the output to the target file.
5. In the Object Explorer view, locate the CUSTOMERSUMMARY data object in your project and double click the data object.
The data object opens in the editor.
6. Click Window > Show View > Data Viewer.
The Data Viewer view appears.
7. In the Data Viewer view, click Run.
The Data Viewer view runs and displays the data.
8. Verify that the mapping shows the expected results after the source schema has changed.
The C_TOTALAMOUNT column displays the aggregated total price of customer orders.