10
Managing Loads and Updates

This chapter describes how to load and refresh a data warehouse using scheduling and dependency management tools. After you generate and deploy scripts, register them as jobs with Oracle Enterprise Manager or another scheduling tool. You can use a dependency management tool such as Oracle Workflow to run multiple job processes with dependencies. Schedule these Workflow processes to load or update the warehouse. The Warehouse Builder Workflow Queue Listener monitors the processes and ensures that dependencies are handled in the correct order. After the processes have completed, view the results using the Warehouse Builder Runtime Audit Viewer.

For information about Oracle Workflow, see the Oracle Workflow Guide. For more information about Oracle Enterprise Manager, see the Oracle Enterprise Manager Administrator's Guide.

This chapter includes the following topics:

• Registering Tcl Scripts

• Creating an Oracle Workflow

• Scheduling a Workflow

• Viewing the Results

Registering Tcl Scripts

After the target warehouse module is deployed, the warehouse is created and the Tcl or mapping scripts are stored there. In order to run the Tcl scripts and load the warehouse, first register the scripts with Enterprise Manager or another scheduling tool.

To schedule jobs with Enterprise Manager, the following steps must be completed:

• Create a Windows NT user name.

• Configure a set of Preferred Credentials for Enterprise Manager.

• Start services for Oracle Agents and Enterprise Manager on the machine that hosts the target schema.

For more information, see the Oracle9i Warehouse Builder Installation Guide.

To register deployed scripts with Enterprise Manager:

1. From the Generated Scripts dialog, select the deployed scripts and click Save as File. Use the Control Key to select multiple scripts.

   The scripts must be saved before you register them.

2. Select the Tcl script you want to register with Enterprise Manager and click OEM Register. You can only register Tcl scripts with Enterprise Manager.

   Warehouse Builder connects with Enterprise Manager, registers the load scripts, and displays a confirmation list.

Figure 10-1 OEM Registration Results Dialog

Text description of the illustration wmedploe.gif

After the scripts have been registered with Enterprise Manager, you can either:

• Schedule the jobs from Enterprise Manager.

• Deploy the scripts to Workflow and create multiple job processes with dependencies.

These processes can then also be scheduled with Enterprise Manager. Workflow processes are effective because they contain all the job dependencies and ensure that the warehouse is loaded and refreshed in the correct sequence.

When you load data or update existing data into a data warehouse, you must run the scripts in strict sequence to ensure that all foreign key references are satisfied. The referenced tables must be loaded before the tables making the reference.

For example, dimension tables must be loaded before the related fact table. A materialized view cannot be refreshed until the related fact table and referenced dimensions have been loaded.

Note: If you modify the configuration of a warehouse module or mapping definition after the scripts have been deployed to the Generation Directories or registered with Enterprise Manager, you must: Re-generate the scripts. Re-deploy the scripts to the Generation Directories. Re-register the scripts with Enterprise Manager. You cannot register a job with Enterprise Manager if that job already exists in the Enterprise Manager Job Library. To update an existing job, remove the job from the Job Library first.

Creating an Oracle Workflow

You can create multiple job processes by defining a Workflow process in Oracle Workflow. When you schedule the processes, the Workflow server ensures that the jobs run in the proper sequence. If an exception occurs, the Workflow server terminates the process.

To define a Workflow process, the following steps must be completed:

• Configure, validate, and generate the mappings.

• Deploy the generated mappings to the Generation Target Directories and the PL/SQL packages to the target database.

• Register the generated mappings with Enterprise Manager.

• Define the Workflow Queue Listener directory and host for the warehouse module. These configuration parameters on the warehouse module specify where the Workflow Queue Listener executes.

• Deploy the mappings to the Workflow server using the Workflow Deployment Wizard.

  If you plan to schedule the workflow process with Enterprise Manager, deploy the process to the Enterprise Manager Job Library.

• Define the workflow process using Workflow Builder.

• Schedule the workflow process using Workflow Monitor or Enterprise Manager.

Deploying Scripts to the Workflow Server

After you register the Tcl scripts as jobs with Enterprise Manager, you can deploy the scripts to the Workflow server using the Workflow Deployment Wizard.

To deploy scripts to the Workflow server:

1. From the Tools menu, select Wizards, and then Workflow Deployment Wizard or expand the wizard drawer and click the Workflow Deployment Wizard icon.

   The Workflow Deployment Wizard Welcome page displays.

2. Click Next.

   The Workflow Login page displays.

Figure 10-2 Workflow Deployment Wizard Login Page

Text description of the illustration owf_wiz_.gif

3. Specify the following connection information:
   • User name: User name for the Workflow schema where the mappings are deployed.

   • Password: Password for the Workflow schema where the mappings are deployed.

   • Host name: Computer that the Workflow schema is located on.

   • Port number: Port number that connects to the Workflow schema.

   • SID: SID for the database instance of the Workflow server.

   Warehouse Builder uses this information to establish a session with the Workflow server.

4. Click Next.

   The Maps page displays a list of available mappings to be deployed.

Figure 10-3 Workflow Deployment Wizard Maps Page

Text description of the illustration owf_wiza.gif

5. Select the maps you want to process. Use the arrow buttons to move the maps to Selected maps.

6. Click Next.

   The wizard displays the Functions page, which shows the function names assigned to the mappings.

Figure 10-4 Workflow Deployment Wizard Functions Page

Text description of the illustration owf_wizb.gif

To display the internal function name for a mapping, select the mapping in the navigation tree.

You cannot modify names on this page.

• Click Next.

  The Item Type page displays.

Figure 10-5 Workflow Deployment Wizard Item Type Page

Text description of the illustration owf_wizc.gif

Specify the following:

• An internal name of no more than eight characters.

• A display name of no more than eighty characters. The name can include spaces.

• A description of no more than 240 characters.

  Note: If the item type you are defining already exists with the Workflow server, it is overwritten. This includes any modifications, such as attribute values and process diagram dependencies.

• Click Next.

  The Process page displays.

  Figure 10-6 Workflow Deployment Wizard Process Page

  

  Text description of the illustration owf_wizd.gif

  Specify the following information:

  • An internal name of no more than thirty characters.

  • A display name of no more than eighty characters. This name can include spaces.

  • A description of no more than 240 characters.

  • Click Next.

    The Finish page displays. Verify the contents of this page. Use the Back button to make changes.

  • Check the Deploy the workflow process to OEM? box if you intend to schedule the workflow process using Enterprise Manager.

    The wizard deploys the name of the process to the Enterprise Manager Job Library only if the warehouse module is configured for Enterprise Manager.

  • Click Finish.

    The wizard deploys the mappings as a set of Workflow functions defined with the specified item type to the Workflow server.

  You can now start the Workflow Builder and define a process to load or update the warehouse.

  Defining the Workflow Process

  To define a Workflow process:

  1. Start Workflow Builder.

     The Workflow Builder navigator window displays.

  2. From the Help menu, select About Oracle Workflow Builder to set the client access level to a value less than or equal to twenty and click OK.

  Figure 10-7 About Oracle Workflow Builder Dialog

  

  Text description of the illustration owf_acce.gif

  The Workflow Deployment Wizard assigns each function an access level of twenty during the deployment operation.

  To edit these functions, you must set the access level of the Workflow Builder client to a value less than or equal to twenty.

  • From the File menu, select Open or click the Open icon.

    The Open dialog displays.

  Figure 10-8 Workflow Builder Open Dialog

  

  Text description of the illustration owfopnpn.gif

  4. Select the Database radio button and enter the following connection information for the Workflow server:
     • User name and password for the server

     • SQL*Net connect string

  5. Click OK.

     The Show Item Types window displays a list of item types.

  Figure 10-9 Show Item Types Window

  

  Text description of the illustration owfpanit.gif

  6. Move the item types to the Visible list using the arrow buttons.

  7. Click OK.

     The Workflow Navigator window displays.

  Figure 10-10 Workflow Navigator Window

  

  Text description of the illustration error_ow.gif

  8. Open your load process.

     Workflow Builder displays tree entries for the mappings deployed from Warehouse Builder.

  9. Expand the navigation tree and open the deployed process.

     The process displays in a separate window with the functions overlapped.

  10. Design the Workflow process by dragging the functions into the order you want to run them.

  11. Define the dependencies between the functions.

      Figure 10-11 shows the Workflow process that sequences the functions to load a data warehouse.

  Figure 10-11 Workflow Process Diagram

  

  Text description of the illustration owfgccpr.gif

  After the Workflow server executes this job, the functions are processed so that the execution of a function depends on the successful completion of all its predecessors. The dimension tables are loaded in parallel but the remaining two jobs run sequentially.

  Scheduling a Workflow

  You can schedule a Workflow process using Enterprise Manager if the Workflow process has been deployed to the Enterprise Manager Job Library. Select the Deploy checkbox on the Finish page of the Workflow Wizard to deploy the process.

  To schedule a Workflow process:

  1. Open the Enterprise Manager Job Library, select the Start job of the Workflow process, and click Edit.

     The Edit Job window displays.

  2. Click the Schedule tab.

  Figure 10-12 Schedule Tab

  

  Text description of the illustration edit_sch.gif

  3. Specify when you want this process to run, select the Save to Library radio button, and click OK.

     If you are scheduling the process to begin immediately, select the Submit radio button.

  Viewing the Job

  You can view jobs in the Enterprise Manager Jobs window. You can track the status of a submitted job from the Active tab. After the job has completed, you can check its status in the History tab.

  Figure 10-13 Job History

  

  Text description of the illustration job_hist.gif

  Right-click the job name on the History tab to display a pop-up list that enables you to view more details regarding the job, remove the job from the history log, or create another job like the selected job.

  The Enterprise Manager log contains summary information about execution. For detailed information about the job, use the Warehouse Builder Runtime Audit Viewer. For more information on Enterprise Manager, see the Oracle Enterprise Manager Administrator's Guide.

  Changing Job Parameters

  Parameters are set in the mapping configuration in Warehouse Builder. You can modify parameters in the Tcl script before you submit the job.

  To modify a parameter value for a job:

  1. Open the Enterprise Manager Job Library.

  2. Select the job name and click Edit.

     The Job Editor window appears.

  3. Click the Parameters tab.

  4. Select the Tcl script and modify the parameter in the text box.

  5. Submit the job.

  Figure 10-14 Job Editor

  

  Text description of the illustration param.gif

  Viewing the Results

  The Warehouse Builder Runtime Audit Viewer displays details of a job after it is run. This information can be useful when you are scheduling jobs. The Audit Viewer displays the contents of the Warehouse Builder Runtime Library for a load or refresh job. For example, you can display the number of records read, number of records inserted or updated, and detailed information about individual records when errors occur. This information helps you troubleshoot load errors.

  About the Runtime Audit Viewer

  The Runtime Audit Viewer window has two panes. The left pane contains a navigation tree with objects grouped by object type. The right pane displays information about the node that is currently selected in the navigation tree.

  Figure 10-15 Runtime Audit Viewer Navigator

  

  Text description of the illustration rt_page.gif

  Viewing the Job Audit

  The nodes in the navigation tree below the top Jobs node each represent a job. Jobs are listed in alphabetical order. To set up a mapping job, register it as a Workflow Process. The corresponding node appears in the navigation tree after the first run of the Workflow process.

  Select the Jobs node at the top of the navigation tree to display the list of jobs. This includes DEFAULT_JOB and any other named jobs that have been run.

  Figure 10-16 Job Audit Window

  

  Text description of the illustration jobs.gif

  A mapping can be executed from either Warehouse Builder or Enterprise Manager, or it can be used as a component execution unit within a Workflow process.

  When a mapping is executed from Warehouse Builder, the audit and error information is stored under the category Default Job. When a Workflow process is run, the audit and error information for the mappings is stored under the name of the Workflow process.

  Viewing the Job Instance Audit

  Expand a job node to display the job instance nodes in the navigation tree. Each time a job run starts, a new job instance is added. The text shows the name of the job and the time the run started.

  Figure 10-17 Job Instance Audit Window

  

  Text description of the illustration job_inst.gif

  Selecting a job node in the tree or a job entry in the right hand pane and clicking the View Instances button displays details about job instances. A job instance represents a specific run of the job.

  If the View errors only box is checked, a job instance is displayed only if it has errors.

  Viewing the Task Audit

  A node representing a job instance can be expanded to display Tasks that are part of the run. For example, the execution of a PL/SQL mapping or an SQL*Loader run is represented as a Task. If a Workflow Process consists of several tasks, each running a PL/SQL mapping, a node representing a run of the Workflow Process has a Task node for each of those mappings.

  Figure 10-18 Task Audit Window

  

  Text description of the illustration task.gif

  Table 10-1 lists the information displayed for each Task.

  Table 10-1 Task Information

  Column Name	Description
  Task	Name of the task.
  Date	Date and time the task was started.
  Type	Type of the mapping (for example, PL/SQL).
  Selected	Number of rows selected.
  Inserted	Number of rows inserted into the target tables.
  Updated	Number of rows updated in the target tables.
  Deleted	Number of rows deleted in the target tables.
  Errors	Number of errors detected.
  Status	Status of the task: BEGIN indicates the task has started. COMPLETE indicates the task completed. FAILED indicates the task failed to complete.

  Viewing the Task Details Audit

  You can expand a task node to display a set of Detailed Mappings, which are also called target entries. In many cases there is only one Detailed Mapping for a task, but different scenarios can cause a task to have more than one Detailed Mapping.

  Figure 10-19 Task Details Audit Window

  

  Text description of the illustration det_map_.gif

  Selecting a task node in the tree, or the Targets node below it, or selecting a task entry in the right-hand pane and clicking View Task Details displays information about its Detailed Mappings. A Detailed Mapping entry represents a mapping to a specific target table. A task that affects multiple target tables has a Detailed Mapping entry for each target table. Also, if a PL/SQL mapping is run in set-based fail over mode, and the set-based run detects errors for a specific target table, there are two Detailed Mapping entries for the table. One is for the set-based run and one is for the row-based run.

  If the relevant detailed mapping cannot be started when processing a task, there is no Detailed Mapping entry for it. For example, a set-based run cannot be started with certain Loading Types, such as DELETE. In this case, although a set-based fail over run immediately switches to row-based mode, a pure set-based run causes the mapping to be abandoned. The task itself is not marked as COMPLETE, and it has a reduced or empty list of Detailed Mappings.

  If the View errors only box is checked, only those Detailed Mapping entries with errors are displayed.

  For PL/SQL mappings, the values of the statistics reported depend on the mode in which the mapping is run. For example, in:

  • Set-based mode, if any errors are found, only the first error detected is logged, and the number of errors is set to 1.

  • Row-based modes, multiple errors can be logged.

  • Set-based mode, the value for the number of records selected is always the same as the number of records inserted in the target table. If any errors are detected, the transaction is rolled back, and the number of records selected and the number of records inserted are both zero.

  • Row-based (target) mode, the value for the number of records selected is derived from a cursor that implements all the stages of a mapping. The value corresponds to the number of rows that are applied to the target table.

  • Row-based mode, the cursor used to derive the number of records selected can omit the final stage of the mapping in some circumstances. For example, if the final stage involves a splitter, the number of records selected reflects the records selected as input to the split operation. The number of records can be larger in row-based mode than in set-based mode or row-based (target) mode.

  Using the Warehouse Builder Runtime Audit Viewer

  The Runtime Audit Viewer can only report on audit information that has been stored during the relevant mapping runs. The default Audit Level for a mapping is defined in the mapping configuration parameters. You can change this value when you configure the mappings. This value can be overridden in the runtime Tcl parameters.

  For PL/SQL mappings, the audit levels are:

  • None: No Task, Detailed Mapping, Error, or Error Detail information is available.

  • Statistics: Task and Detailed Mapping information is available, but Error and Error Details are not.

  • Error Details: All the information relevant to the Audit Viewer is available. This is the default value.

  • Complete: This level stores additional information for all rows mapped. This extra information is not used by the Audit Viewer.

  To use the Runtime Audit Viewer:

  1. Select Warehouse Builder Runtime Audit Viewer from the Start menu.

     The Runtime Audit Logon dialog displays.

  Figure 10-20 Runtime Audit Logon Dialog

  

  Text description of the illustration rt_login.gif

  2. Specify the runtime connection information and click OK.

     The Runtime Audit Viewer displays. The left pane contains a navigation tree. The right pane contains a list of details for the currently selected node.

  Figure 10-21 Runtime Audit Viewer

  

  Text description of the illustration rt_first.gif

  3. Select objects in the left pane to display detailed information in the right pane.

     When the Audit Viewer first opens, all of the objects are rolled up under the Jobs node. As you expand the nodes, you see the following layers of objects:

     • Jobs: Lists the jobs that have been run.

     • Job Instances: Lists the runtime results of a particular job.

     • Tasks: Lists the results of the tasks within the jobs that have run.

     • Detailed Mappings: Lists the results of mappings that have been run.

  Viewing Specific Results

  You can specify the results you can view by defining date range, or by performing a search. By default, the most recent job instance is displayed in the tree.

  To select a date range:

  1. From the View menu, select Date Range.

     The Date Range dialog displays.

  Figure 10-22 Date Range Dialog

  

  Text description of the illustration rt_dtrng.gif

  2. Choose a range option:
     • Recent: The most recent instance of each job is displayed. This is the default value.

     • All: All instances of each job are displayed.

     • Range: Instances run within a specific date range are displayed.

  3. Click OK.

  To search for objects by name:

  1. From the Edit menu, select Find.

     The Find Tree Objects dialog displays.

  Figure 10-23 Find Tree Objects Dialog

  

  Text description of the illustration rt_find.gif

  2. Choose from the options listed in Table 10-2.

     Table 10-2 Find Tree Options

     Option	Description
     Name	Type the name of the object you are searching for. Names of targets are enclosed in double quotes. You can use the asterisk (*) as a wildcard. For example: D* matches all names beginning with D. D*2 matches all names beginning with D and ending with 2.
     Object Type	Search for either one or all object types.
     Search In	Specify the scope of the search: Current Folder: searches the currently selected object folder in the navigation tree. All Folders: searches the whole navigation tree.
     Match Case	Determine whether to match the case used in the Name field.

  3. Click Find.

     A list of objects matching the search criteria displays. You can click one of these entries and then click Select in Tree to select the relevant object in the navigation tree.

  Figure 10-24 Find Tree Objects Dialog with Matching Items

  

  Text description of the illustration find_rav.gif

  Refreshing the Audit Viewer

  You can refresh the data displayed in the Audit Viewer by selecting Refresh from the View menu. This is useful if you are checking for errors or troubleshooting a job.

  Purging Runtime Entries

  You can purge jobs from the runtime tables using the Runtime Audit Viewer. To purge runtime entries, select Purge from the Job menu or the toolbar. The Audit Viewer displays the Purge Jobs dialog. You can purge jobs according to Job Date or Job Name.

  Figure 10-25 Purge Jobs Dialog

  

  Text description of the illustration purge.gif