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

Working in the logical view

For now, however, we’ll continue working in the logical view because we have a few minor modifications to make. Keep in mind while working in the logical view that it is exactly like working in the regular mapping editor. Operators can be moved around and joined together and they can be dragged and dropped from the Component Palette and Projects Navigator just like before.

When using code templates, not all the operators will work with all code templates. We have to be aware of that as we’re building our logical view. We talked earlier in the topic about how some operators are not available for use with Integration Code Templates for example. For a complete list of the operators that cannot be used with integration or load-code templates see the Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide, topic 7 section on Mapping Operators Only Supported in Oracle Target CT Execution Units:

http://download.oracle.com/docs/cd/E118 8 2_01/ owb.112/e10935/sap_km_mappings.htm#WBETL07012

The main modification we’re going to have to make here is to change the source table operators. Recalling back to when we built the STAGE_MAP mapping in topic 6 in the Adding source tables section under Creating a Mapping we dragged and dropped all the source tables from our ACME_POS module under the ODBC node. They are still bound to that module as we can see next when holding the mouse pointer over the title bar of the table operator:

To fix this we can just delete all those existing source table operators and then drag and drop them from the SQL Server JDBC module under the SQL Server node instead of the ODBC node. Another option would be to select each existing table in turn and perform the Synchronize inbound procedure on it to select a different table object but we’re going to see a new feature in a moment, connecting two attribute operator groups together, that we wouldn’t see if we used the option to synchronize. We talked about the synchronize process in topic 9 in the Synchronizing Objects section so, rather than repeat that, we’ll delete the tables and add them again to gain some exposure to additional features of OWB. So let’s do that now.

We’ll just click on each source table and press the Delete key and answer yes to the popup that appears asking for confirmation to make sure deleting them is really what we want to do. We can also right-click on the title bar of the table operator and select Delete from the pop-up menu. To recap, we’re going to delete the following five table operators from our stage_map template mapping logical view:

• ITEMS

• POS_TRANSACTIONS

• REGISTERS

• STORES

• REGIONS

After they are all deleted, we’ll open the SQL_SERVER_JDBC module under the SQL Server node in the Projects Navigator so we can drag the tables back into our mapping from there. We’ll click on the above five tables one at a time under the Tables node of the SQL_SERVER_JDBC module and drag them onto the mapping, placing them where the table operators were that we deleted. We’ll use the above ordering of table names so our connector lines will not cross over when we connect them up to the JOINER operator.

When we have our table operators all placed we can now connect them up to the JOINER. We’ll make use of the feature of the mapping editor that allows us to drag an output group from one operator and drop it on an input group of another to connect all the attributes in that group. The difference now is that there are already attributes defined in the JOINER input groups for each table and so the Warehouse Builder is going to pop up a new screen we haven’t seen yet that asks us how we want to handle the matching.

Let’s do that now with the ITEMS table and drag a connector from the INOUTGRP1 input/output group of the ITEMS table operator and drop it on the ITEMS input group of the JOINER. When we do that a dialog will pop up in which we can specify how to connect up the attributes as shown next:

We can choose from four different options on the left. Each of them will be described next:

• Copy source attributes to target group and map: This option will just copy each source attribute to the target and create a new attribute in the target for it without matching anything. If an attribute with the same name exists in the target it will just rename it when it copies it so it doesn’t conflict with an existing attribute. The Preview button is to have it display for us what it’s going to do so we can verify that’s the behavior we really want. If we choose that option right now and click Preview we’ll see the following in the Messages box, which tells us we really don’t want this option because we want to match the names:

• Match by position of source and target attributes: This option will match up a source attribute to whatever target attribute is in the same position as it regardless of the name. This option looks like it could work for us. Let’s click that option and then click the Preview button. It will fill in source attributes on the Source Connections tab on the right each lined up with the target attribute that it will connect to and we can see that they all line up perfectly. Let’s discuss the other two options first before completing this dialog.

The Source Connections tab is for specifying the source attributes that will be connected to the listed target attributes and the Target Connectors tab is for specifying the target attributes and will be connected to the listed source attributes. They both really provide the same information in the end but just offer a different perspective, either from source to target, or from target to source for specifying the connections. Those tabs are what get updated when we press the Preview button to tell us what it’s going to do.

• Match by name of source and target attributes: This option will do just what it says, match up any matching names between source and target. If there are no matching names, it will create a target attribute to match. Let’s select that option and press the preview button and see what our Source Connections and Target Connections tabs look like now. They look exactly like they did with the previous option that matched by position because, in this case, it just so happens that there are matching source and target names and that the matching names are also in the same position. When this option is selected there are a number of other options that become selectable to dictate some rules it will follow to do the match. We can check and uncheck any of them and click Preview to see the effect. We’ll just leave the first one checked by default to not be case sensitive.

• The final option is Custom: This option will allow us to just type in an attribute name in either the Source or Target Connections tab depending on which one we’d like to specify. If we select that option and press Preview, it will clear out any previously filled in source attributes on the source tab and leave them all blank because we have to explicitly tell it what to match. If we select Custom and then click in the first source field on the Source tab and start typing one of the source attribute names, a pop-up menu of names will appear of all the source attributes as displayed below and we can just select the one we want and it will populate that field. If we type something that doesn’t match the start of any source attributes it will just display (no match found). We can then do the same on each subsequent field until they are all matched. This option would be good if names don’t match and the positions are not the same either.

So, after all that discussion, we can see that either the match by position option or the match by name option will work so we’ll choose one of those and click the OK button and it will draw all the connecting lines for us from ITEMS to the JOINER ITEMS input group. If the OK button is grayed out and not selectable, we need to just click the Preview button first and then we can click OK. That is a real time saver when doing mappings like this and saves us from having to draw individual lines from each attribute to remap them.

Let’s continue now in like manner through the remainder of the table operators dragging each INOUTGRP1 group to the appropriate input group of the JOINER, selecting the match by name option, clicking Preview and then OK. After we have done that. we’ll save our work so far before continuing.

There is one final step we need to take while in the Logical View and that is to set the Loading Type property of the target POS_TRANS_STAGE table. Back in topic 6 when we originally built the mapping, we left it set to the default of insert, which means every running of the mapping will simply insert records into the table and leave any existing records there. We’re now going to change that to truncate/ insert so this code template mapping will first truncate the table (remove all existing records) and then insert the new records.

Let’s click on the POS_TRANS_STAGE table operator in the logical view and in the Property Inspector, scroll down until we see the General Section and under that the Loading Type property. Click the dropdown and change it to truncate/insert. That way, since we’ve already populated that table using the regular mapping we won’t get duplicate records in our staging table after running this mapping. We could also go back and change the original regular mapping to do that if we wanted to be able to run it more than once on the same set of data. The Property Inspector should look like the following after making that setting change:

Our logical view is now complete and back the way it was looking before, so we’re ready to move on to define the Execution view. We don’t need to do anything with any of the other operators since they will work as is, even the target table operator because our target hasn’t changed. We’re still writing to the Oracle database ACME_ DWH target.

Working in the execution view

It is now time to configure our execution view for the mapping. A code template mapping gets broken up into execution units, which group various operators on the mapping for execution. There can be any number of execution units defined for the mapping depending on how complex it is and the processing that is needed. The execution units then get assigned code templates to use to execute them. That is how we will associate code templates with our mapping.

Knowing the different kinds of code templates that are available and the operators we have used in the logical view will help us decide how to break down our mapping into execution units. For instance, we’ve stated earlier in the topic how we’ll make use of a load code template for extracting data out of the source system. We can then look at our logical view of our mapping and decide what operators are involved in the extraction of data from the source system.

Let’s get started with that now. We’ll click the Execution View tab and we’ll be presented with a completely new canvas type interface we’ve not seen yet. It is new in this latest release of the Warehouse Builder just to give us a view of our mapping for defining execution units and assigning code templates. When we first look at the Execution View, it will look like the following:

Well, that doesn’t look very helpful, does it? When we copied the regular mapping into the templates module, it created a default execution unit for us named for the entire mapping and just put everything inside it on top of one another. Fortunately, we have a way to make it look a little better and that is the auto-layout button in the toolbar, which we’ve seen before. It’s circled in the previous image. Just click that and the operators will all expand so we can see better what is going on. We now have something that looks like this:

We can now see that not quite all the operators are inside that default execution unit called STAGE_MAP. The table operators we deleted and recreated are outside the execution unit. If we had looked at this view at the very start before making any edits, we would have seen them inside the execution unit also, but when we deleted them and recreated them, the assignment to an execution unit was deleted too because the table operators we dropped into the mapping to replace the original ones are from the JDBC module, which this default Oracle target mapping is not designed to handle. Remember we said earlier that the default target mapping is really just a wrapper around regular PL/SQL mapping code so any table operators would have to make use of regular modules, like ODBC or a gateway.

Let’s take a look at that execution unit called STAGE_MAP that it created to see if we can use it or if we have to just delete it and create a new one. We’ll click on it either on the top label bar or somewhere inside it but not on any of the operators defined in it. When we do that, we’ll see that one of the tabs in the message window has updated. In fact it’s a new tab that is added to the message window and viewable when working in the Execution View. It is the Code Template tab and currently displays the name of the execution unit we’ve selected as we can see in the following image:

This tab is for assigning the code template that the execution unit will have and we can see that it has automatically assigned the DEFAULT_ORACLE_TARGET_CT code template. That is the one the Warehouse Builder will always automatically assign when a regular mapping is copied since it will allow all the regular operators to be used. In fact, we could run this mapping with every operator inside this one execution unit as it was first copied in before we replaced the source table operators and it would work. It would not have made use of our new JDBC native connection to SQL Server, so for that we have to use a separate code template.

We said earlier that we’d be using a load code template and the default Oracle target code template so we will be able to use this default one that has been assigned for us but we will make some changes to the operators that are in it and will add a new execution unit. We’ll do that now.