12 Deploying to Target Schemas and Executing ETL Logic

Oracle Warehouse Builder provides functionality that supports a single logical model and multiple physical models. It enables you to design your data warehouse and implement this design on multiple target systems. In addition, Oracle Warehouse Builder supports multiple physically different implementations of the same object definitions.

This chapter describes the implementation environment in Oracle Warehouse Builder. It also describes how to deploy objects and run ETL logic.

This chapter contains the following topics:

"Overview of Deployment and Execution in Oracle Warehouse Builder"
"Steps in the Deployment and Execution Process"
"Deploying Objects"
"Starting ETL Jobs"
"Starting ETL Jobs in SQL*Plus"
"Managing Jobs Using SQL Scripts"
"Example: Updating a Target Schema"

Overview of Deployment and Execution in Oracle Warehouse Builder

After you design your data warehouse, you must implement this design in the target schema by deploying and executing design objects. The Control Center Manager offers a comprehensive deployment console that enables you to view and manage all aspects of deployment and execution. It provides access to the information stored in the active Control Center.

About Deployment

Deployment is the process of creating physical objects in a target location according to the logical objects defined in Oracle Warehouse Builder workspace. The data objects created when you designed the target schema and defined ETL objects are logical definitions. Oracle Warehouse Builder stores the metadata for these data objects in the workspace. To create these objects physically in the target schema, you must deploy these objects. For example, when you create a table using the Design Center, the metadata for this table is stored in the workspace. To physically create this table in the target schema, you must deploy this table to the target schema.

As part of the deployment process, Oracle Warehouse Builder validates and generates the scripts for the object, transfers the scripts to the Control Center, and then invokes the scripts against the deployment action associated with the object. You can deploy an object from the Projects Navigator or using the Control Center Manager.

Note:

Whenever you deploy an object, Oracle Warehouse Builder automatically saves all changes to all design objects to the workspace. You can display a warning message by selecting Prompt for commit on the Preferences dialog box.

You can deploy only those objects for which you have the COMPILE privilege. By default, you have this privilege on all objects in the workspace. However, the workspace owner may have instituted a different security policy.

Note:

Always maintain objects using Oracle Warehouse Builder. Do not modify the deployed, physical objects manually in SQL. Otherwise, the logical objects and the physical objects are not synchronized, which may cause unpredictable results.

About Deployment Actions

As soon as you define a new object in the Design Center, the object is listed in the Control Center Manager under its deployment location. Each object has a default deployment action, which you can display. The default deployment action for an object is based on a comparison of its current design status to its current deployment status. For example, a table that has not been previously deployed, has a default deployment action of Create. A table that was previously deployed, has a default action of Upgrade. You can override the default by choosing a different deployment action in the Control Center Manager.

The default is set by the previous action and varies depending on the type of object.

These are the deployment actions:

Create: Creates the object in the target location. If an object with that name exists, then an error may result. For example, this may happen if the object has not been previously deployed from Oracle Warehouse Builder.
Upgrade: Modifies the object without losing data, if possible. You cannot undo or redo an upgrade action. This action is not available for some object types, such as schedules.

Note:
When you use the Control Center to upgrade a table that contains a ROW MOVEMENT clause in its DDL script, the upgrade fails.
To solve this problem, before you deploy the table using an Upgrade action, set the Row Movement configuration parameter of the table to NULL and then deploy the table.
Drop: Deletes the object from the target location.
Replace: Deletes and re-creates the object. This action is quicker than Upgrade, but it deletes all data.

About Deployment Status

After you deploy an object, Oracle Warehouse Builder assigns a deployment status to it. The status represents the result of the deployment. You can view the deployment status in the Control Center Manager.

The deployment status can be one of the following:

Not Deployed: Indicates that the object has not yet been deployed to the target schema.
Success: Indicates that the object has been successfully deployed to the target schema.
Warning: Indicates that some warnings were generated during the deployment of the object.
Failed: Indicates that deployment of the object failed.

About Deploying Dimensional Objects

To deploy MOLAP dimensional objects, ensure that the version of the location and the PL/SQL Generation Mode configuration parameter of the Oracle module are set. The location version must be at least 11gR1, and the configuration parameter PL/SQL Generation Mode must be either default, 11g Release 1 or higher to generate 11g AWs that support cube organized MVs.

About Deploying Mappings and Process Flows

ETL objects include mappings and process flows. Deploying a mapping or process flow includes these steps:

Generate the PL/SQL, SQL*Loader, or ABAP script, if necessary.
Register the required locations and deploy any required connectors. This ensures that the details of the physical locations and their connectors are available at run time.
Transfer the PL/SQL, XPDL, SQL*Loader, or ABAP scripts from the Design Center to the Control Center.

To successfully deploy Oracle Warehouse Builder process flows to Oracle Workflow, ensure access to the correct version of Oracle Workflow as described in the Oracle Warehouse Builder Installation and Administration Guide.

About Deploying Code Template (CT) Mappings and Web Services

Before you deploy CT mappings or Web services, you must start the Control Center Agent as described in "Starting the Control Center Agent (CCA)".

Deploying Code Template mappings or Web services includes the following steps:

Generate the .ear files.
Transfer the .ear files to the OC4J server associated with the Control Center Agent.

About Deploying Schedules

Depending on the ETL objects that are associated with the schedule, you can deploy schedules to Oracle Database or Concurrent Manager.

See Also:

"Overview of Deploying Schedules" for more information about deploying schedules.

For remote Oracle Workflow locations and remote Oracle Warehouse Builder 10g locations to which schedules are deployed, ensure that the target location has the CREATE SYNONYM system privilege. If the evaluation location is specified or the deployment location references a different database instance from the Control Center schema, then the deployment location must have the CREATE DATABASE LINK system privilege.

About Execution

For objects that contain ETL logic (such as mappings, process flows, transformations, Code Template mappings, and Web services) there is an additional step of execution. Execution is the process of executing the ETL logic defined in the deployed objects.

Typically, objects are deployed and are run multiple times. When there are changes in the object definition, you must redeploy the objects. For example, you deploy a mapping after it is defined. The mapping can be run or scheduled to be run at specific intervals (daily or weekly). If the mapping definition changes, then you must redeploy the mapping.

For example, you define a mapping that sources data from a table, performs transformations on the source data, and loads it into the target table. When you deploy this mapping, the PL/SQL code generated for this mapping is stored in the target schema. When you run this mapping, the ETL logic is run and the data is picked up from the source table, transformed, and loaded into the target table.

Another example is of defining a Web service that checks if the data in a table conforms with the data rules defined for the table. When you deploy a Web service, the .ear file generated for the Web service is transferred to the Control Center Agent. When you run the Web service, the ETL logic defined in the Web service is run and a check is performed to verify that the data in the table does not violate any data rules defined on the table.

About Configurations

Oracle Warehouse Builder separates the logical design of the objects from the physical details of the deployment. It creates this separation by storing the physical details in configuration parameters. An object called a named configuration stores all of the configuration settings. Use named configurations to implement different physical parameters for the same design on different systems (for example, development, production, testing). It enables you to easily move Oracle Warehouse Builder applications from the development to the test environment and then into production. For example, on the development system, you can specify the parallel settings as NOPARALLEL. On the production system, you can specify the parallel setting as PARALLEL with a degree of 16.

You can create a different named configuration for each deployment location, with different settings for the object parameters in each one. Each named configuration is associated with only one control center.

See Also:

Oracle Warehouse Builder Installation and Administration Guide for more information about creating multiple configurations.

About Viewing and Setting Configuration Properties for Different Configurations

When you create multiple configurations in a project, you can set different configuration parameters for an object in each configuration. At any time, only one of this configurations is activated, and is called the active configuration. For example, you have three configurations, PROD_CONFIG, DEV_CONFIG, and QA_CONFIG. For a table, SALES_TAB, you can set different configuration parameters in each of the three configurations.

See Also:

Oracle Warehouse Builder Installation and Administration Guide for more information about multiple configurations.

When you right-click and object and select Configure, the configuration parameters for the object in the active configuration, are displayed in a new Configure tab. If you change the active configuration by selecting a different configuration using the Configuration list on the toolbar, then the configuration parameters for the newly-activated configuration are listed to the right of the existing configuration parameters.

You can display the configuration parameters for other configurations defined in your project by clicking Manage Configuration Columns on the toolbar displayed at the top of the Configure tab. The Select Configurations dialog box is displayed. This dialog box lists all the configurations defined in the project. Select the configurations for which you want to display configuration parameters in the Configure tab and click OK. The parameters for the selected configurations are displayed, adjacent to the current configuration parameters.

You can compare the configuration property settings for different configurations by clicking Highlight Differences on the toolbar displayed at the top of the Configure tab. Oracle Warehouse Builder highlights the configuration parameters that have different settings in the various selected configurations. It enables you to compare the configuration settings an object in each configuration.

To delete the configuration parameters for a particular configuration from the Configure tab, click Manage Configuration Columns. In the Select Configurations dialog box that is displayed, deselect the configuration whose settings you want to remove from the Configure tab, and click OK.

The set of configuration parameters for the active configuration are always displayed in the Configure tab and you cannot delete this set.

Steps in the Deployment and Execution Process

During the lifecycle of a data system, you typically take these steps in the deployment process to create your system and the execution process to move data into your system:

Select a named configuration, from the list of configurations on the toolbar, with the object settings and the Control Center to use.
Deploy data objects and ETL objects to the target location. You can deploy them individually, in stages, or all at once.

For information about deploying objects, see "Deploying Objects".
Review the results of the deployment. If an object fails to deploy, then fix the problem and try again.

For more information about deployment results, see "Reviewing Deployment Results".
For executable objects such as mappings or process flows, you can either start the ETL process immediately or schedule its execution for a later date.

For information about starting the ETL process, see "Starting ETL Jobs".

For information about scheduling ETL objects, see "Scheduling ETL Jobs".
Revise the design of target objects to accommodate user requests, changes to the source data, and so forth.
Set the deployment action on the modified objects to Upgrade or Replace.

For more information about deployment actions, see "About Deployment Actions".
Repeat steps 1 to 4.

Note:

Oracle Warehouse Builder automatically saves all changes to the workspace before deployment.

Deploying Objects

You can deploy objects using the Projects Navigator or Control Center Manager. Deployment from the Projects Navigator is restricted to the default action, which may be set to Create, Replace, Drop, or Update. The default action is determined by changes to the object design since it was last deployed. To override the default action, use the Control Center Manager, which provides full control over the deployment process.

After deploying an ETL object, you must explicitly start the scripts that perform the ETL operations defined in the ETL object, as described in "Starting ETL Jobs".

Deploying Objects Using the Control Center Manager

When you use the Control Center Manager to deploy objects, Oracle Warehouse Builder automatically deploys all dependent objects of the objects being deployed.

Note:

Numerous settings on the Preferences dialog box control the behavior of Control Center Manager. Additional settings control the actual deployment process.

From the Tools menu, click Preferences. The settings are listed under Control Center Monitor and Deployment. Click Help for descriptions of the settings.

Steps to Deploy Objects Using the Control Center Manager

In the Projects Navigator, open the project containing the object that is to be deployed.
Select Control Center Manager from the Tools menu.

The Control Center Manager that provides access to the control center for the active configuration of the project is displayed. If this menu choice is not available, then check that the appropriate named configuration and Control Center are active.

See Also:
Oracle Warehouse Builder Installation and Administration Guide for information about configurations.
If you are deploying relational or ROLAP dimensional objects, ensure that the implementation details of these objects are specified. You can do this by performing binding.

For more information, see "Relational Implementation of Dimensional Objects" and "ROLAP Implementation of Dimensional Objects".
(Optional) If you are deploying Code Template mappings or Web services, start the Control Center Agent as described in "Starting the Control Center Agent (CCA)".
In the Control Center Manager navigation tree, expand the location node containing the object to be deployed.
Select the objects to be deployed.

Select multiple objects by holding down the Ctrl key while selecting the objects.

You can deploy all the objects contained under a particular node by selecting the node. For example, to deploy all tables in a particular module, select the Tables node under that module. To deploy all objects in a module, select the module.
In the Details tab of the Object Details panel, set the deployment action for the selected objects.

To deploy the selected objects using the default deployment action, click Default Actions on the Object Details tab.

See Also:
"About Deployment Actions" for more information about deployment actions.
Deploy the selected objects.

To deploy objects to the Control Center Manager, click Deploy on the toolbar.

To deploy objects to a local file, from the File menu, select Deploy and then To File. Choose the directory in which you want to save the file and provide a name for the file.

Note:
Deploying to a local file option creates a file in the specified directory. The file permissions on this file are set to the Oracle owner. You must change the permissions on the file to read it.

Deploying Objects Using the Projects Navigator

When you deploy objects using the Projects Navigator, the deployment action used is the default action set by Oracle Warehouse Builder. For more information about the default deployment actions, see "About Deployment Actions".

Before you deploy an object, ensure that the object is generated successfully. Generation creates the code required to create the object in the target schema.

Steps to Deploy Objects Using the Projects Navigator

In the Projects Navigator, expand the project and then module that contain the object you want to deploy.
Ensure that you successfully deploy all dependent objects of the object being deployed.

For example, before deploying a relational dimension, ensure that all the tables that store the implementation details of the dimension and the sequence used to generate the surrogate identifier are deployed. While deploying a process flow, ensure that all mappings or transformations used in the process flow are successfully deployed.
If you are deploying relational or ROLAP dimensional objects, ensure that the implementation details of these objects are specified. You can do this by performing binding.

For more information, see "Relational Implementation of Dimensional Objects" and "ROLAP Implementation of Dimensional Objects".
If you are deploying Code Template mappings or Web services, start the Control Center Agent as described in "Starting the Control Center Agent (CCA)".
Select the objects to be deployed and click Deploy on the toolbar.

or

Select the objects to be deployed and then choose Deploy from the menu.

To select multiple objects, hold down the Ctrl key while selecting objects.

After the deployment is complete, a new tab is opened in the Log window to display the details of each deployment.

Deploying Target Systems to a Remote System

You must perform the following additional steps to deploy design objects to a target schema that is different from the one in which you define the design objects.

Install Oracle Warehouse Builder on the target system.
Use the Repository Assistant to create a workspace and a repository user on the target system.
In the Globals Navigator, create a new configuration and new Control Center that uses the configuration. The Control Center should correspond to the workspace you created on the target system.
Set the newly created configuration as the default configuration.
Create a location corresponding to the remote target schema.
Deploy the design and ETL objects.

Reviewing Deployment Results

After you deploy objects, you can review deployment results and check the deployment status. If the objects were not deployed successfully, then you can view the error messages generated for the deployment.

Viewing Deployment Results for Objects Deployed Using the Control Center Manager

When you use the Control Center Manager to deploy objects, you can monitor the progress of a job using the Status column in the Control Center Jobs panel. When the job is complete, the new deployment status of the object appears in the Details tab. You can review the results and view the scripts.

To view deployed scripts:

In the Control Center Jobs panel of the Control Center Manager, double-click the job related to the deployment for which you want to view deployed scripts.

The Jobs Details window is displayed for the selected job. This window displays details of any errors that occurred during the deployment.
In the Job Details window, select the object in the navigation tree.
On the Script tab, select a script and click View Code, or just double-click the script name.

Viewing Deployment Results for Objects Deployed Using the Projects Navigator

When you use the Projects Navigator to deploy objects, a new tab is displayed in the Log window for each deployment. This tab contains a node tree with the object name and deployment status. Expand the node to view the following nodes:

Validation: Expand this node to display the validation messages for the object.
Scripts: Expand this node to view the scripts that are generated for the object.
Deployment: Expand this node to view the deployment status. Details of errors that occurred during deployment are listed here. While deploying PL/SQL mappings, information about whether the error occurred in the package body or the package specification is also included.

To view deployed scripts:

In the Log window, select the tab related to the deployment for which you want to view deployed scripts.
Expand the node displaying the object name, and then the Scripts node.

The scripts used for the deployment are listed under the Scripts node.
Double-click the script to view.

A new tab is opened in the Document window containing the generated code. This tab contains the Source, Spec, and Body subtabs. The Spec subtab contains the package specification, and the Body subtab contains the package body.

If the object deployment fails, you can use the Spec and Body subtabs to view the code in which the error occurred.

Starting ETL Jobs

ETL is the process of extracting data from its source location, transforming it as defined in a mapping, and loading it into target objects. When you start execution of a mapping, process flow, or data auditor, you submit it as a job to Oracle Warehouse Builder job queue. The job can start immediately or at a scheduled time, if you create and use schedules. For more information about schedules, see "Defining Schedules".

Like deployment, you can run a mapping, process flow, or data auditor from the Projects Navigator or the Control Center Manager. You can also start these jobs by using tools outside of Oracle Warehouse Builder that runs SQL scripts.

Starting a mapping, process flow, or data auditor involves the following steps:

Deploying the objects, as needed.

For more information about deploying objects, see "Deploying Objects".
Executing the object by using the Projects Navigator or Control Center Manager as described in the following sections.

To start ETL from the Projects Navigator:

Select the mapping or process flow, then select Start from the File menu.

For every ETL object that you run (start), a new tab containing an execution log is displayed in the Log window. The tab title is the object name followed by the job ID. Use this tab to monitor the status of the execution and to view execution results.

When you run objects that use the Control Center Agent, such as Code Template mappings or Web services, the job log files are located in the OWB_HOME/owb/jrt/log/jrt/jobjob_id/log.xml directory path. In this directory path, job_id is the ID of the job that is run. Use this log file to troubleshoot any errors that may occur during the deployment and execution of Code Template mappings or Web services.

To start ETL from the Control Center Manager:

Select the mapping or process flow, then click Start on the toolbar.

Alternatively, you can select the mapping or process flow, then select Start from the File menu.

For information about executing Web services, see "Executing Web Services".

See Also:

"Starting ETL Jobs in SQL*Plus" for information about using SQL*Plus to start ETL jobs.

Viewing Execution Results for ETL Jobs

After executing ETL objects, Oracle Warehouse Builder displays the execution results in a tab in the Log window. A separate tab is used to display the execution results of each ETL job.

The execution results provides detailed information about the ETL job. The information is displayed using the following columns in the execution results tab:

Rows Selected: Represents the number of rows selected from the source objects during the ETL job.
Rows Inserted: Represents the number of rows inserted into the target objects during the ETL job.
Rows Updated: Represents the number of rows in the target objects that were updated as part of the ETL job.
Rows Deleted: Represents the number of rows deleted from the target objects during the ETL job.
Errors: Represents the number of errors encountered during the ETL job execution.
Warnings: Represents the number of warnings encountered during the ETL job execution.
Start time: Represents the time at which the ETL job was started.
Elapsed time: Represents the time taken to complete the ETL job.

Execution Results Tab Icons

Icons at the top of the execution results tab enable you to select the information that should be displayed on the tab. Table 12-1 displays the icons and their uses.

Table 12-1 Execution Results Tab Icons

Icon	Name	Description
	Show Warnings	Lists any warnings that occur during the ETL job execution in the Execution Results tab. This is a toggle switch.
	Show Errors	Lists any errors that occur during the ETL job execution in the Execution Results tab. This is a toggle switch.
	Show Parameters	Lists the values of parameters that are used during the ETL job execution. The parameters are listed under the Parameters node.
	Stop Job	Stops the execution of the ETL job currently being run. This icon is disabled when the execution is complete or has not started.
	Go To Source	Displays the editor for the ETL object that is currently being run.
	Show Details	Displays the Execution Details dialog box.

Return Status of ETL Jobs

Every ETL job that is completed should have one of the following values as its return status.

SUCCESS - Mapping completes successfully with no errors.
WARNING - Mapping completes with errors but does not exceed the "Maximum Number of Errors" parameter.
ERROR - Mapping does not complete, or mapping has more errors than the "Maximum Number of Errors" parameter.

Viewing the Data

After ETL is completed, you can easily check any data object in Oracle Warehouse Builder to verify that the results are as you expected.

To view the data stored in a data object:

In the Projects Navigator, right-click the object and select Data. The Data Viewer opens with the contents of the object.

Scheduling ETL Jobs

You can use any of the following methods to schedule ETL:

Use the scheduler.

See "Defining Schedules".
Use the Oracle Database Scheduler with the PL/SQL package DBMS_SCHEDULER PL/SQL.

See Oracle Database PL/SQL Packages and Types Reference for details about the DBMS_SCHEDULER package
Use a third-party scheduling tool.

Starting ETL Jobs in SQL*Plus

In addition to executing objects using the Control Center Manager, you can use SQL*Plus. Use a script provided with Oracle Warehouse Builder named sqlplus_exec_template. Alternatively, you can use sqlplus_exec_background_template to run a job in the background.

Take these steps to run the SQLPLUS_EXEC_TEMPLATE script in SQL*Plus:

From the Tools menu of the Design Center, select SQL*Plus.

The SQL*Plus panel is displayed.
Connect as an Oracle Warehouse Builder user, not as a repository owner.
Start the script, using syntax such as the following:
```
@%OWB_HOME%\owb\rtp\sql\sqlplus_exec_template MY_RUNTIME MY_WAREHOUSE PLSQL
MY_MAPPING "," ","
```
See Also:
"The SQLPLUS_EXEC_TEMPLATE SQL Script" for a complete description of the syntax.

Managing Jobs Using SQL Scripts

Numerous SQL scripts are installed with Oracle Warehouse Builder so that you can manage deployment jobs, execution jobs, and the Control Center using SQL scripts. The scripts are located in OWB_HOME/owb/rtp/sql directory. Comments in these scripts explain how to use them.

See Also:

Oracle Warehouse Builder Installation and Administration Guide for information about using these scripts.

Example: Updating a Target Schema

Scenario

You are in charge of managing a data warehouse that has been in production for a few months. The data warehouse was created by using two source schemas, Human Resources (HR) and Order Entry (OE) and was loaded into the Warehouse (WH) target schema. Recently you learned of two changes to tables in the HR and OE schemas. The WH schema must be updated to reflect these changes.

Change #1: The first change was made to the HR schema. The length of the REGION_NAME column in the REGIONS table was changed from 25 to 100 characters.

Figure 12-1 displays a representation of the changes to the REGIONS table.

Figure 12-1 Changed REGIONS Table

Description of "Figure 12-1 Changed REGIONS Table"
Change #2: The second change was made to the OE schema. A row called LOT_SIZE_NUMBER was added to the ORDER_ITEMS table with a data type of NUMBER, precision of 8, and scale of 0.

Figure 12-2 displays a representation of the changed ORDER_ITEMS table.

Figure 12-2 Changed ORDER_ITEMS Table

Description of "Figure 12-2 Changed ORDER_ITEMS Table"

Solution

To update the WH schema, you must first determine the impact of these changes, and then create and run a plan for updating the target schema. The following steps provide an outline for what you must do:

"Step 1: Identify Changed Source Objects"

"Step 2: Determine the Impact of the Changes"

"Step 3: Reimport Changed Objects"

"Step 4: Update Objects in the Data Flow"

"Step 5: Redesign your Target Schema"

"Step 6: Redeploy Scripts"

"Step 7: Test the New ETL Logic"

"Step 8: Update Your Discoverer EUL"

"Step 9: Run the ETL Logic"

Case Study

Step 1: Identify Changed Source Objects

The first step in rolling out changes to your data warehouse is to identify the changes in source objects. You must have a procedure or system in place that can notify you when changes are made to source objects.

In this scenario, the group managing the HR and OE schemas informed you that some objects had been changed. The first change was made to the HR schema. The REGION_NAME column was extended from 25 to 100 characters to accommodate longer names. The second change was made to the OE schema. The LOT_SIZE_NUMBER column was added and must be integrated into the WH schema.

Step 2: Determine the Impact of the Changes

After you have identified the changes, you must determine their impact on your target schema.

For Change #1, made to the HR schema, you must update any dependent objects. This entails reimporting the REGIONS table and then updating any objects that use the REGION_NAME column. To identify dependent objects, you can use the Impact Analysis diagram. You also must update any mappings that use this table.

For Change #2, made to the OE schema, in addition to reimporting the table and updating mappings, you must find a way to integrate the new column into the WH schema. Because the column was added to keep track of the number of parts or items in one unit of sales, add a measure called NUMBER_OF_IND_UNITS to the SALES cube in the WH schema and have this measure for each order. Then you must connect this new column to the SALES cube.

Step 3: Reimport Changed Objects

Because two source objects have changed, you must reimport their metadata definitions into your workspace. Select both the REGIONS table in the HR schema and the ORDER_ITEMS table in the OE schema from the navigation tree and use the Metadata Import Wizard to reimport their definitions.

Oracle Warehouse Builder automatically detects that this is an update and proceeds by only updating changed definitions. The Import Results dialog box that appears after the import process shows the details of the synchronization. Click OK to continue the import and commit your changes to the workspace. If you do not want to continue with the import, then click Undo.

Step 4: Update Objects in the Data Flow

If the change in the source object altered only existing objects and attributes, such as Change #1 in the HR schema, use Impact Analysis diagrams to identify objects that must be reconciled.

In this scenario, you must reconcile the column length in all objects that depend on the REGIONS table to ensure that the data continues to load properly.

To update objects in the data flow:

Select the REGIONS table in the HR schema from the navigation tree. Select View and then click Impact.

A new tab is displayed in the Document Editor containing the Impact Analysis diagram. This reveals that the CUSTOMER dimension in the WH schema is the only object affected by the REGIONS table.

This step requires that you have set up the Repository Browser. For more information about setting this up, see Oracle Warehouse Builder Installation and Administration Guide.
Open the CUSTOMER dimension in the Dimension Editor and update the Region Name level attribute to the 100-character length.
Open the MAP_CUSTOMER mapping that connects the source to the target. For both the REGIONS Table operator and the CUSTOMER Dimension operator, perform an inbound synchronization from data object to mapping operator.

The mapping operators must be synchronized with the mapping objects that they represent to generate code based on the updated objects.

You have now completed updating the metadata associated with Change #1.

Because Change #2 introduced a new column, you need not update the data flow as you did for Change #1. Ensure that you perform an inbound synchronization on all the mappings that use an ORDER_ITEMS Table operator. From the Impact Analysis diagram for the ORDER_ITEMS table, you can see that only the mapping MAP_SALES is affected.

Step 5: Redesign your Target Schema

Because Change #2 introduced the new LOT_SIZE_NUMBER column to the ORDER_ITEMS table, you must redesign your WH target schema to incorporate this new data into your cube. You can do this by adding a new measure called NUMBER_OF_IND_UNITS to your SALES cube.

To redesign the target schema:

Add the measure NUMBER_OF_IND_UNITS with the NUMBER data type, precision of 8, and scale of 0 to the SALES cube.
View the lineage diagram for the SALES cube to determine which mappings contain the SALES cube. Perform an inbound synchronization on all SALES cube mapping operators.
Open the mapping MAP_SALES and ensure that the table ORDER_ITEMS is synchronized inbound.
Connect the LOT_SIZE_NUMBER column in the ORDER_ITEMS table to the Joiner, and then to the Set Operation, and then add it to the Aggregator operator. Ensure that you are doing a Sum operation in the Aggregator operator.
Finally, connect the LOT_SIZE_NUMBER output attribute of the Aggregator operator to the NUMBER_OF_IND_UNITS input attribute of the SALES cube.

Step 6: Redeploy Scripts

After the mappings have been debugged, use the Design Center to regenerate and redeploy scripts. Use the Control Center Manager to discover the default deployment action. Oracle Warehouse Builder detects the type of deployment to run.

Step 7: Test the New ETL Logic

After you have reconciled all objects and ensured that the WH target schema has been updated to reflect all changes, test the ETL logic that is be generated from the mappings. Use the Mapping Debugger to complete this task. If you find any errors, then resolve them and redeploy the scripts.

Step 8: Update Your Discoverer EUL

If you are using Oracle Discoverer as your reporting tool, proceed by updating your EUL.

To update your Oracle Discoverer EUL:

Identify the objects that must be updated in the End User Layer (EUL) because of changes made to their structure or data. In this case, the changed objects are the REGIONS and SALES_ITEMS tables and the SALES cube.
In the Projects Navigator, select all the objects identified in Step 1, right-click and select Derive.

The Perform Derivation Wizard displays and updates these object definitions in the Business Definition Module that contains these objects.
Expand the Item Folders node in the Business Definition Module that contains these changed objects.
Select the objects identified in Step 1, right-click and select Deploy.

The changes to the objects are updated in the Oracle Discover EUL.

Step 9: Run the ETL Logic

After the mappings have been deployed, run and load data into the target.