Oracle Data Integrator 12c introduces several new tools aimed at increasing developer productivity, improving data loading performance, and enabling closer integration with Oracle Fusion Middleware.
Probably the most exciting of these new tools, particularly for users of Oracle Database’s Oracle Warehouse Builder feature who are looking to upgrade to Oracle Data Integrator, is the new “flow-based” mapping editor. While the more traditional set-based mappings are still supported, the flow-based user interface enables you to create rich multistep mappings that move and transform data by using a palette of mapping operators.
Creating a Flow-Based Mapping
To quick start you can download the ODI 12c Getting Started appliance from the OTN and follow the instructions.Then Locate the Pop.TRG_PRODUCT mapping, and double-click it to open it within the mapping editor. Ensure that the Logical tab is selected at the bottom of the mapping editor screen, as shown
In this example, data from the SRC_PRODUCTS table is joined with data from the TRG_PROD_FAMILY table, and then it is passed through a Distinct operator before being loaded into the TRG_PRODUCT target table.
Creating a Deployment Specification
Oracle Data Integrator separates mappings into logical and physical definitions. To configure the failed mapping for incremental loading, I would typically change the IKM to one that uses an incremental load strategy—IKM Oracle Merge, for example. If I wanted to preserve the original Control Append version of the mapping in earlier versions of Oracle Data Integrator, I needed to create an additional copy of the mapping that used the incremental load knowledge module. And if I wanted to continue using both mappings, I then needed to keep the logical definition of each mapping in sync over time.
Oracle Data Integrator 12c, however, introduces a new enhancement called deployment specifications that enables you to create additional physical definitions for a single mapping. we’ll now use this enhancement to create an additional incremental load version of the mapping that we can use to update the target table after the initial load.
Before you create the new deployment specification, you will need to change the target Integration Type setting in the logical mapping definition to enable incremental loading as well as Control Append loading. To do so, switch back to the Logical tab in the mapping editor, navigate to the Integration Type setting in the TRG_PRODUCT – Properties panel, and change the selected value from Control Append to None; this enables the full range of integration types to be selected for that target table in the physical mapping definition.
Now navigate to the bottom of the mapping editor and click the Create new deployment specification button next to the existing Pop.TRG_PRODUCT_DS tab.Now switch to the Physical tab for the mapping, and click the TRG_PRODUCTdata store icon in the ODI_DEMO_TRG_UNIT execution unit to select it. Navigate to the TRG_PRODUCT – Properties panel on this screen, and expand the Integration Knowledge Module properties area. Note how theIntegration Knowledge Module value has automatically changed to IKM Oracle Merge.GLOBAL. Change it back to IKM SQL Control Append, so that the functionality of this original deployment specification stays as it was before.
By default, the new deployment specification is given the name Deploy1. To change it, navigate to the Deploy1 – Propertiespanel on the right-hand side of the screen and change the Name property to
You’ve created an additional deployment specification, and now you can configure it to use an incremental load IKM. To do so, click the TRG_PRODUCT data store icon in the ODI_DEMO_TRG_UNIT execution unit and then, when its Properties panel is displayed, check that the Integration Knowledge Module value is set to the default IKM Oracle Merge.GLOBAL, as shown
Now your mapping has two physical definitions associated with it: one for the initial load and one for subsequent incremental (merge) loads. To test your new deployment specification, save your updated mapping definition and click Run. When the Run dialog box appears, select your new deployment specification from the Deployment menu to use it for the mapping execution.
You could also use this approach to create deployment specifications that leverage Oracle GoldenGate for changed-data capture or use other loading, integration, or check knowledge modules for a logical source-to-target data mapping.