[OWB] OWB Executing

Executing (Oracle Warehouse Builder 11gR2)

Now we have our staging table deployed to the target database, the pos_trans_ stage table, and have successfully deployed the stage_map mapping to load that table from our source database. This means we now have enough of our target database deployed to be able to execute the stage_map mapping to load the staging table. Let’s do that now so that we will have progressed through the entire process once. Loading the staging table is the first step we have to take to load our database before we can proceed to load the actual target dimensions and cube. After we execute this mapping, we can go back and deploy the remaining objects, and execute them to load the dimensions and cube.

The process of executing a mapping can be performed from the Design Center as well as from the Control Center Manager. The process of executing is very similar to deploying. Results are displayed in the Control Center Jobs window, which is the same as that of the deployment results, but on a different tab, that is the Execution tab. We also get results displayed in the Log window in the Design Center.

To execute a mapping we might think to look for a menu entry that says Execute, but we will not find it. We need to select the menu entry that says Start to start the code running. This menu entry is available from the pop-up menu by right-clicking on an item in the tree view, and from the File menu when an item is selected in the tree view. These menu entries are available in both the Design Center and the Control Center Manager. We can also click the Start icon in the toolbar, the one on the right in the above image in the Control Center. If executing from the Design Center, the start icon will appear as a large green arrow.

Let’s just go ahead and do that now. We’ll find the stage_map entry under Mappings, which is in the acme_dwh_location in the tree view. Right-click on it and select Start.

When executing code, it’s always a good idea to make sure the most recent version of the code has been deployed successfully. Before selecting Start, it is good to just glance at the Object Details window in the Control Center Manager for the object, which appears when the object is right-clicked, and make sure that the deployment status shows Success and the design status shows Unchanged. If we had a problem with the deployment and the status is other than Success, we will have issues running it. If the design status shows Changed, we don’t have the most recent version of the object deployed. We can then fix any issues first, re-deploy, and then execute it.

Having determined that we have successfully deployed the most recent version, we continue and select Start. So the Control Center Manager begins executing the mapping code. As it executes, the first thing we’ll notice is that the Control Center Jobs window will update to display the Execution tab with our newly submitted job as the first entry and the Design Center Log window has updated to add another tab to display the results from this execution. The tab name will be the mapping name followed by the job id in parentheses like it did for all the deployments. Upon completion, if we have checked the preference option to display deployment completion status (which applies to execution status also), we’ll get the results popup window.

The important thing to notice about this dialog box is the success or failure message. The counts (at least for the processed count) are not accurate. This is a minor bug, as it did indeed process this mapping. This is verifiable by double-clicking on the status for our job in the Control Center Jobs window to display the details about this execution. When we do that, we get the following dialog box in which we can clearly see the mapping executed and can see the counts of rows that were processed:

The contents of this dialog are also shown in the tab in the Design Center Log window. It displays more or less for the inserted count depending on how much sample data we actually have in the source database. As of this execution, there were 10026 records in the source pos_transactions table but we performed an aggregation on that data to sum it by day which resulted in 156 records actually being loaded.

The parameters entry in the above screenshot is for displaying the mapping input parameters if there are any defined in the mapping. We talked about mapping input parameters in topic 5 when discussing the various operators available to us when creating a mapping. They are for passing values into a mapping.

The input parameters are included with configuration options for running the mapping that involve the operating mode among others. We discussed the operating mode previously when talking about generating code and viewing the code for the various operating modes. We discussed accessing those parameters by selecting Configure… from the pop-up menu by right-clicking on a mapping in either the Design Center or the Control Center Manager and saw an example of the screen with the parameters.

Instead of having to take that extra step to set the runtime parameters, there is an option that can be checked off to automatically display a dialog of those parameters, including mapping input parameters, when a mapping is started. That would allow us to set those parameters each time a mapping is run. To set that option access the Preferences menu entry from the Tools main menu in the Design Center. In the resulting dialog, expand the OWB entry on the left and click on the Deployment entry. There will be an option with a check box labeled Prompt for Execution Parameters on the right that can be checked to automatically display the parameters dialog when a mapping is run.

The runtime parameters are set along with the operating mode. We have covered the default operating mode previously, but the others are all more advanced than we’ll have time or the need to cover here. There are good explanations of all the runtime parameters in the online help accessible by pressing the Help button. Select the Configuring Mappings Reference link and then the Runtime Parameters link from the resulting help dialog box to access detailed explanations of all the runtime parameters. For our purpose, the defaults will all be fine.