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 by Oracle Data Integrator, the flow-based user interface enables you to create rich multistep mappings that move and transform data by using a palette of mapping operators.
Oracle Technology Network has also made the Oracle Data Integrator 12c Getting Started appliance—an Oracle VM VirtualBox—available; you can download it to your development environment and use it to try out the new tools in Oracle Data Integrator 12c without having to separately install and configure all the components. (Note that this appliance is meant for testing and training purposes only and is not supported for use in production environments.)Creating a Flow-Based Mapping
In this example, I explore one of the new flow-based mappings within the sample Oracle Data Integrator 12c project in the Oracle Data Integrator 12c Getting Started Oracle VM VirtualBox appliance. I demonstrate how mappings include source and target data store objects and make use of mapping operators to filter, aggregate, sort, and otherwise transform data. I also take a look at the new deployment specification capability within Oracle Data Integrator 12c that enables you to create more than one physical specification for a mapping so you can create a single mapping definition that handles initial as well as incremental loading scenarios.
If you have not done so already, download the Oracle Data Integrator 12c Getting Started appliance from the Oracle Technology Network website and follow the instructions to extract the Oracle VM VirtualBox appliance files and log in to the appliance. (This demonstration appliance includes a partially created Oracle Data Integrator 12c project within the work repository.) Once you’ve installed and logged in to the appliance, double-click the Oracle Data Integrator 12c Studio icon on the desktop and, when prompted, connect to the Oracle Data Integrator work repository.
To get started with flow-based mappings in the Oracle Data Integrator 12c Getting Started appliance, do the following:
Navigate to the Oracle Data Integrator Designer Navigator tab on the left-hand side of Oracle Data Integrator’s Studio user interface (Oracle Data Integrator Studio), and then expand the Demo project underneath it. Expand the Sales Administration folder within this project and then the Mappings entry to display the list of mappings within the project.
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 Figure 1.
If you’ve used Oracle Warehouse Builder in the past, the flow-based mapping style in the mapping editor should be very familiar to you. You now create Oracle Data Integrator 12c mappings by dragging and dropping data store items from the Models panel on the left-hand side of the screen onto the mapping canvas. Data flows are drawn between the various data stores, and operator components transform the data.
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.
Let’s try to execute the mapping. Click Run (the green triangle) in the Oracle Data Integrator Studio toolbar, leave the default Run option values, and click OK. When the Information dialog box displays the “Session Started” message, click OK to dismiss it.
To see the status of the mapping execution, select Window -> ODI Operator Navigator (you may need to close the Thumbnail panel at the top left of the screen to see the Operator Navigator properly after it opens). Expand the Agent entry on the Session List panel, and then expand the entries under your agent execution, so that you can see the 90 – Load TRG_PRODUCT – IKM Oracle Insert entry. See how the execution of this step has failed? Double-click the step itself, and read the error message; it is displayed on the right-hand side of the screen. You will see that it failed because of a unique constraint violation on the target table. But why did that happen?
To investigate, click back to the Pop.TRG_PRODUCT tab on the right-hand side of the screen in Oracle Data Integrator Studio to open the mapping editor again. Ensure that the Logical tab is still selected, and then click the TRG_PRODUCT data store in the mapping to select it. Navigate to the TRG_PRODUCT – Properties panel at the bottom right of the screen, and expand the Target properties section, as shown in Figure 2.
The integration type for this mapping is Control Append, which means that Oracle Data Integrator will always insert all the rows from the source transformations into the target table, even if they’ve already been loaded. This is why the target table’s unique key constraint was violated and the mapping execution failed. This type of integration approach is appropriate when you’re first loading a target table, but after the initial load, you’ll typically want to incrementally update the table with new data.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 integration knowledge module 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. I’ll now use this enhancement to create an additional incremental load version of the mapping that I can use to update the target table after the initial load.
To get started with deployment specifications in the Oracle Data Integrator 12c Getting Started appliance, do the following:
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 switch to the Physical tab for the mapping, and click the TRG_PRODUCT data 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 the Integration 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.
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.
By default, the new deployment specification is given the name Deploy1. To change it, navigate to the Deploy1 – Properties panel 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 integration knowledge module. 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 in Figure 3.
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.
Check the Oracle Data Integrator Operator Navigator panel; you should see this mapping execution complete successfully, because it will not have attempted to write existing values back into the target table. 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.Mapped and Specified
The new flow-based mapping editor in Oracle Data Integrator 12c enables you to create multistep source-to-target mappings that can have multiple physical specifications associated with them for different types of load scenarios. Although you can still create traditional set-based mappings in the new release, most developers will want to adopt this new way of mapping data in Oracle Data Integrator 12c, due to its ease of use, increased functionality, and incorporation of the best aspects of Oracle Warehouse Builder.
Photography by Igor Ovsyannykov, Unsplash