Building a code template mapping Part 3 (Oracle Warehouse Builder 11gR2)

Creating an execution unit

Let’s start by creating a new execution unit around the five source table operators. To create an execution unit we have to first select the operators that we want in the execution unit so let’s select the five source table operators by drawing a box around them to select them all at once. We’ll just click on the canvas to the upper left of the operators and hold down the left mouse button while we drag the box down until it encompasses all five operators. We can now select Create Execution Unit from the Execution main menu or we can just click the Create Execution Unit icon that has now become active at the top left of the mapping canvas window as shown next:

A new execution unit will now be created and we can see that it has assigned a default name, EX_UNIT_1 (or some other number depending on how many other execution units were created this session). The first thing we’ll do is rename the execution unit, so we’ll right-click on it and select Open Details from the pop-up menu. We will change the name to sql_server_load and click the OK button.

Let’s now click on it and look at the Code Template tab of the messages window and we can see that no code template has been assigned to it. This execution unit will be for loading data from our SQL Server database and so we want to specify one of the load code templates to use. From our discussion earlier in the topic we said we’d be using the LCT_SQL_TO_ORACLE code template to load from a generic SQL based database into Oracle so let’s click on the dropdown on the Integration/ Load Code Template tab and select the PUBLIC_PROJECT\BUILT_IN_CT\LCT_ SQL_TO_ORACLE entry and our Code Template window should now look like the following when the proper selection is made:

There are also several options we can specify in that window but we’re going to leave them all set to the default as that will suffice for our purpose. A description of what all those options are can be found in the Oracle documentation in the Warehouse Builder Data Modeling, ETL, and Data Quality Guide at the following URL: http:// download.oracle.com/docs/cd/E118 8 2_01/owb.112/e10935/sap_km_mappings. htm#BABJHFHi. This will take you right to the section entitled Setting Options for Code Templates in Code Template Mappings. We have one more step to take and that is to move an operator out of the STAGE_MAP execution unit into the SQL_SERVER_ LOAD unit.

Moving an operator between execution units

The use of code templates gives us some flexibility now in just what tasks we want to accomplish in what database. We’re now able to do more using our source database than we could before with just regular mappings that all executed entirely in the Oracle database. We can add more operators to this new SQL_SERVER_LOAD execution unit other than just the source table operators. It would still work as it is now, but notice that the JOINER is defined over in the Oracle execution unit, which means the actual joining of the source tables would happen in the Oracle database once the tables have been copied over there by the load code template. That means the load code template would have to copy over five tables into the Oracle database and create five temporary work tables over there to hold them and then the Oracle code template would have to do the join. If we were to move that JOINER over into the SQL Server load code template, we could have the SQL Server database do the work of joining the tables and it would then have to copy over just one table to Oracle consisting of the results of applying the join to the five tables. That sounds like a good idea so let’s move the JOINER over to the SQL_SERVER_LOAD execution unit.

To move an operator between units we have to first remove it from the unit it’s in and then add it to the execution unit we want to move it to. So click on the JOINER in the STAGE_MAP execution unit and from the Execution main menu select Remove Operator From Execution Unit. That will cause the JOINER to be relocated outside the box for the STAGE_MAP execution unit. It is now no longer associated with any execution unit.

We now want to make it a part of the SQL_SERVER_LOAD execution unit, so make sure the JOINER is selected by clicking on it and also click on the SQL_SERVER_ LOAD execution unit by using the Ctrl key to select multiple objects, and then from the Execution main menu select Add Operator to Execution Unit. The JOINER will now appear inside the SQL_SERVER_LOAD execution unit box. We can rearrange the display in case objects are not displaying very well by clicking and dragging entire execution units or clicking and dragging individual operators inside execution units, which will resize the execution unit box accordingly. When neatened up, our Execution View should now look similar to the following:

We can’t move any more operators into the SQL_SERVER_LOAD execution unit because the next operator that we could move would be the TRUNC operator, representing the Oracle TRUNC() function on the date; however, SQL Server Transact-SQL (Microsoft’s implementation of SQL) does not have a TRUNC() function. OWB 11gR2 has a heterogeneous transformation set defined in the Globals Navigator under Public Transformations | Heterogeneous | Pre-Defined, which has implementations per platform, which could be used here with no problem but we’re going to stick with the Oracle specific function in this case.

That completes the configuration of our Execution View. Our code template mapping is now ready to deploy and execute. Let’s save our work at this point and then continue to deploy and execute.

Deploying and executing a code template mapping

Now that we’ve completed building our code template mapping we’re ready to deploy and execute it. The first step is to make sure our Control Center Agent process is running as we described earlier in the topic. The process of deploying and executing a code template mapping is identical to the process for deploying and executing a regular mapping; however, the code template mapping will deploy to the Control Center Agent, not the Control Center Service.

To deploy, we’ll right-click on the STAGE_MAP mapping under the MAPPING_ MODULE_1 module in Template Mappings and select Deploy. If this is not the first time we’ve deployed this mapping, we may see a popup like the following:

This will happen if some but not all objects have been deployed successfully previously and the Warehouse Builder will offer to skip them. We’ll choose Use Replace action to force them to be deployed again and click the OK button if that dialog appears.

We’ll then get the Log window popup showing us the progress of our deployment, which as with regular mappings will do a validate and generate first automatically and then deploy it. Our Log window should look like the following after a successful deployment:

If we expand the STAGE_MAP entry we’ll see some validation warnings, which can be safely ignored. If there is a red X appearing and a cca-1103 error ("Failed to check if CODETEMPLATE-xxxxx has been deployed") then the control center agent is most likely not running. Just go back and start it up and deploy again.

Now that we have the mapping deployed, it’s time to execute it. We’ll perform a similar process to deploying by right-clicking on the STAGE_MAP mapping under MAPPING_MODULE_1 in Template Mappings and selecting Start from the popup menu this time.

The Log window will display the job steps as it executes each execution unit of the code template mapping. The steps it displays will depend upon the execution units and assigned code templates that were specified for the mapping. The final results should look similar to the following upon successful execution:

There are other columns of information in that log window to the right that will tell us how many errors and warnings there were in each step along with a start time and the elapsed time for each step.

We can see green check marks beside most of the steps, which is good. That is the signal that the step completed with no errors or warnings. However, there is one step, DROP_WORK_TABLE in the SQL_SERVER_LOAD execution unit, that has a yellow exclamation point appearing, meaning a warning was generated. If we expand that entry we can see there was an error generated under the JDBC step that the table or view does not exist. That is not a problem because this is just a temporary work table that the execution unit creates to perform its tasks, which it’s going to recreate anyway, so it’s not a problem if it doesn’t exist to start with. In fact, we can see a step at the end that it added to drop the work table, which shows a green check mark. It’s possible that the mapping may have had a fatal error during a previous execution that caused it not to drop the work table so this step is there just to make sure it starts with a clean slate.

We saw the record counts in the log window that showed it loaded the data successfully but if we want to see the data ourselves just to verify it we can use the Data Viewer by right-clicking on the POS_TRANS_STAGE table in the Databases | Oracle | ACME_DWH | Tables node and selecting Data from the popup. An error dialog may pop up warning that the connection failed and that the location is invalid and asking whether we want to edit the location details. Simply click Yes and the password can be set for the ACME_DWH_LOCATION location, which is usually the cause of that error. After entering the correct password on the Edit Oracle Database Location popup and clicking OK, we’ll see the data that it has loaded as shown next in the Data Viewer window in the Design Center:

We have now completed implementing our first code template mapping. For connections to other databases, the native JDBC connection is much simpler to set up and can be done completely within OWB so code templates are definitely an excellent addition to the Warehouse Builder. With the default Oracle target code template and the ability to wrap existing OWB regular mappings, the power and utility of the Warehouse Builder has improved tremendously with this new release.

Summary

We have explored the 11gR2 code template technology that has been added to the Warehouse Builder in this topic and have covered much of what we covered in earlier topics for making connections to source databases, defining connections, importing data objects, defining mappings, and deploying and executing them all within the context of the new native JDBC connection capability and the code template feature added to OWB from ODI.

That is it. We have come to the end of our introductory journey through the Oracle Warehouse Builder. Hopefully, you have enjoyed it and will take what you’ve learned and put it to good use in the world of data warehousing with the Oracle Warehouse Builder.