Skip Headers
Oracle® Warehouse Builder User's Guide
11g Release 1 (11.1)

B31278-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

25 Deploying to Target Schemas and Executing ETL Logic

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

This chapter describes the implementation environment in Warehouse Builder. It also describes how to create and use schedules to automate the execution of ETL logic.

This chapter contains the following topics:

About Deployment and Execution in 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 from the logical objects in a Warehouse Builder workspace.

The data objects created when you designed the target schema are logical definitions. Warehouse Builder stores the metadata for these data objects in the workspace. To create these objects physically on 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. Use the Design Center or the Control Center Manager to deploy objects.

Note:

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

Deploying a mapping or a 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 runtime.

  • Transfer the PL/SQL, XPDL, SQL*Loader, or ABAP scripts from the Design Center to the Control Center.

After deploying a mapping or a process flow, you must explicitly start the scripts, as described in "Starting ETL Jobs".

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.

You can deploy directly from the Design Center navigation tree or using the Control Center Manager.

Note:

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

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 will have a default deployment action of Create. A table that was previously deployed will have 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 already exists, then an error may result. For example, this may happen if the object has not been previously deployed from Warehouse Builder.

  • Upgrade: Modifies the object without losing data, if possible. You can undo and redo an upgrade. This action is not available for some object types, such as schedules.

  • 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.

Deployment Status

After you deploy an object, 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. Double-click the status to view details about the warning.

  • Failed: Indicates that deployment of the object failed. Double-click the status to view detailed information about why the deployment failed.

About Execution

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

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 execute this mapping, the ETL logic is executed and the data is picked up from the source table, transformed, and loaded into the target table.

The Deployment and Execution Process

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

  1. Select a named configuration with the object settings and the Control Center that you want to use.

  2. Deploy objects to the target location. You can deploy them individually, in stages, or all at once.

    For information about deploying objects, see "Deploying Objects".

  3. Review the results of the deployment. If an object fails to deploy, then fix the problem and try again.

  4. Start the ETL process.

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

  5. Revise the design of target objects to accommodate user requests, changes to the source data, and so forth.

  6. Set the deployment action on the modified objects to Upgrade or Replace.

  7. Repeat these steps.

Note:

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

Deploying Objects

Deployment is the process of creating physical objects in a target location from the metadata using your generated code. As part of the deployment process, 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 Project Explorer or using the Control Center Manager.

Deployment from the Project Explorer 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. The Control Center Manager also validates and generates objects during deployment.

Note:

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

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

To deploy from the Project Explorer:

Select the object and click the Deploy icon on the toolbar. You can also select the object, and then choose Deploy from the Design menu.

Status messages appear at the bottom of the Design Center window. For notification that deployment is complete, select Show Deployment Completion Messages in your preferences before deploying.

To deploy from the Control Center Manager:

  1. Open a project.

  2. 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:

    "Creating Additional Configurations" in the Oracle Warehouse Builder Installation and Administration Guide.
  3. In the Control Center Manager navigation tree, expand the location node containing the object to be deployed. Select the objects to be deployed.

    You can select multiple objects by holding down the Ctrl key while selecting the objects.

  4. Set the deployment action for the selected objects in the Object Details panel.

    • On the Details tab, click Default Actions.

      The default action depends on the type of object and whether the object has been deployed previously. For example, dimensions and schedules do not support the Upgrade action. You can use the Changed Objects filter on the deployment tree to find objects without an action.

    • On the Details tab, click the Deploy Action field, and select an action.

  5. Click the Deploy icon.

Warehouse Builder must be open during generation of the job, but not during deployment. The Control Center Manager can be closed at any stage.

Deploying Business Definitions to Oracle Discoverer

After you create your business definitions, you can deploy them to Oracle Discoverer. The method used to deploy business definitions depends on the version of Oracle Discoverer to which business definitions are deployed and the licensing option you use.

Note:

This feature requires one or more additional options as described in Oracle Database Licensing Information.

Note:

To deploy business definitions to an Oracle Discoverer location, the EUL owner must have the CREATE DATABASE LINK privilege.

Table 25-1 summarizes the combinations possible when you deploy business definitions to Oracle Discoverer using the different licensing options.

Table 25-1 Different Methods of Deploying Business Definitions


Warehouse Builder Core Functionality Warehouse Builder Enterprise ETL Option

Versions Lower than Oracle Discoverer 10g Release 2

Generate scripts for the business definitions, copy these scripts to an .eex file, and import the .eex file into Oracle Discoverer.

See "Deploying Business Definitions Using the Core Functionality".

Use the Control Center to create an .eex file and then import the .eex file into Oracle Discoverer.

See "Deploying Business Definitions to Earlier Versions of Oracle Discoverer".

Oracle Discoverer 10g Release 2

Generate scripts for the business definitions, copy these scripts to an .eex file, and import the .eex file into Oracle Discoverer.

See "Deploying Business Definitions Using the Core Functionality".

Use the Control Center to directly deploy to Oracle Discoverer.

See "Deploying Business Definitions Directly to Oracle Discoverer".


Deploying Business Definitions Directly to Oracle Discoverer

You can directly deploy business definitions to Oracle Discoverer, just like you deploy other data objects, using the Control Center or Project Explorer. The business definitions are deployed to the Discoverer location associated with the Business Definition module that contains these business definitions.

Before you deploy business definitions, ensure that a valid Discoverer location is associated with the Business Definition module. For information about how to associate a Discoverer location with a Business Definition module, see "Setting the Connection Information".

When you deploy business definitions directly to Oracle Discoverer 10g Release 2, the following steps are performed:

  1. Creates an .eex file that contains the definitions of the business definitions.

  2. Connects to the EUL specified in the Discoverer location associated with the Business Definition module containing the business definitions.

    Note:

    If the EUL is in a different database from your object definitions, a connector is created. This connector is deployed when you deploy the business definitions.
  3. Imports the .eex file into Oracle Discoverer.

    During the import, any new business definitions are appended on top of the existing definitions. You must validate the EUL and remove redundant definitions. For example, you deploy an item folder that contains four items. Subsequently, you delete one item from the item folder. When you redeploy the item folder, it still contains four items. This is because only new definitions are being appended, but old definitions are not removed.

Deploying Business Definitions to Earlier Versions of Oracle Discoverer

You cannot directly deploy business definitions to versions of Oracle Discoverer earlier than 10g Release 2. However, you can still transfer your business definitions to Discoverer using the following work around.

When you deploy business definitions to a location that is associated with a version of Discoverer lower than 10g Release 2, the deployment will fail. But an .eex file that contains the business definitions is created. This .eex file is assigned a default name, for example, 2022.eex, and is stored in the OWB_ORACLE_HOME\deployed_scripts directory. You can connect to the EUL using Oracle Discoverer and import this .eex file.

Deploying Business Definitions Using the Core Functionality

When you use the core functionality of Warehouse Builder, you cannot directly deploy business definitions to Oracle Discoverer. You also cannot use the Control Center to create an .eex file as described in "Deploying Business Definitions to Earlier Versions of Oracle Discoverer". However, you can save your business definitions to Discoverer using the steps described in the following section.

Use the following steps to save business definitions to Discoverer:

  1. Associate a valid location with the Business Definition Module that contains the business definitions.

    Although you cannot use this location to deploy business definitions, defining the location ensures that the credentials of the EUL user are included in the generated code. When you define the location, a check is performed to determine if the relational schema that the intelligence objects reference is in the same database as the objects. If they are in different databases:

    • A connector is created to the Discoverer location.

    • The name of the database link used by the connector is included in the generated code.

    The connector is created under the Discoverer location node associated with the Business Definition module. Ensure that you deploy this connector to create a database link.

  2. Right-click the business definition module that contains the business definitions that you want to deploy to Discoverer and select Generate.

    The Generation Results window is displayed.

  3. Navigate to the Scripts tab of the Generation Results window.

    This tab lists all the business definitions with the names of the corresponding files that store the scripts generated for these definitions.

  4. Select all the objects that you want to save to Oracle Discoverer.

    You can select multiple files by pressing down the Ctrl key.

  5. Click the Save As button.

    The Save As dialog box is displayed.

  6. Select the directory in which you want to save the generated scripts. Ensure that you save all the files in a single directory.

    For example, you save all the generated scripts in the directory c:\sales\generated_scripts.

  7. Copy all the generated scripts to a single .eex file.

    Use the operating system commands to concatenate the generated scripts into a single file. For example, in Windows, you open a Command Prompt window and execute the following steps:

    c:> CD c:\sales\generated_scripts
    c:\sales\generated_scripts> COPY *.xml sales_scripts.eex
    

    This copies all the generated .xml files to an .eex file called sales_scripts.eex.

  8. Edit the .eex file created in the previous step using any text editor and perform the following steps:

    1. Add the following lines at the beginning of the file:

      <?xml version="1.0" encoding="UTF-8"?>
      <EndUserLayerExport SourceEULId="20030730144738"
      SourceEULVersion="4.1.9.0.0" MinimumCodeVersion="4.1.0"
      ExportFileVersion="4.1.0">
      
    2. Add the following lines at the end of the file:

      </EndUserLayerExport>
      
  9. Open Oracle Discoverer Administrator and connect to the EUL into which you want to import the business definitions.

  10. From the File menu, select Import.

    The Import Wizard is displayed.

  11. Import the .eex file you created into Discoverer Administrator.

Reviewing the Deployment Results

You can monitor the progress of a job by watching the status messages at the bottom of the window and the Status column of 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 deployment details:

Double-click the job in the Job Details panel.

The Deployment Results window will appear. For a description of this window, select Topic from the Help menu.

To view deployed scripts:

  1. Open the Deployment Results window, as described in the previous steps.

  2. Select the object in the navigation tree.

  3. On the Script tab, select a script and click View Code, or just double-click the script name.

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 ETL, you submit it as a job to the 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 "Process for Defining and Using Schedules".

Like deployment, you can start ETL from the Project Explorer or using the Control Center Manager. You can also start ETL using tools outside of Warehouse Builder that execute SQL scripts.

Starting a mapping or a process flow involves the following steps:

  1. Generating the PL/SQL, SQL*Loader, or ABAP script, as needed.

  2. Deploying the script, as needed.

  3. Executing the script.

To start ETL from the Project Explorer:

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

To start ETL from the Control Center Manager:

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

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

See Also:

Oracle Warehouse Builder API and Scripting Reference for information about using SQL*Plus to start ETL jobs.

Viewing the Data

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

To view the data:

In the Project Explorer, right-click the object and select Data. The Data Viewer will open with the contents of the object.

Scheduling ETL Jobs

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

Deploying to Additional Locations

A workspace may contain numerous target locations. You can deploy an object to only one target location at a time. However, you can deploy different modules (or entire projects) to different locations, or you can deploy the same modules to multiple locations. The procedures vary depending on whether the objects have already been deployed.

To deploy a different module to a different schema on the same system:

  1. Create a target location for the new schema.

  2. Create or edit the module, and associate it with the new location.

  3. Use the same Control Center as the previous deployment.

  4. Deploy as usual.

To deploy a module to a second schema on the same system:

Note: You do not need to create a new configuration to deploy to a second schema, but it will be more convenient if you are changing additional configuration parameters.

  1. Create a location for the schema.

  2. In the Project Explorer, double-click the module.

    The Edit Module dialog box opens.

  3. On the Data Locations tab, move the new location from Available Locations to Selected Locations, then click OK.

  4. In the Project Explorer, select the module and click the Configure icon.

    The Configuration Properties dialog box opens.

  5. Under Identification, select the new location from the list for the Location property, then click OK.

  6. Deploy as usual.

To deploy a module to a second schema on a different system:

  1. Set up the target system:

    1. Install Warehouse Builder on the target schema.

    2. Use the Repository Assistant to create a workspace and a user for the target schema.

  2. In the local Design Center, create a new configuration and a new Control Center for the workspace on the target system. Make this the default configuration.

  3. Define a target location for the remote schema.

  4. Deploy as usual.

Runtime Preferences

You can alter optional runtime platform preferences using the file OWB_ORACLE_HOME/owb/bin/admin/Runtime.properties. This section provides a description of the properties you can alter.

SQL Loader Runtime Preferences

  • property.RuntimePlatform.0.NativeExecution.SQLLoader.oracle_home_10g

    Defines the location of the Oracle 10g home from which SQL*Loader will be executed. This property is selected when the version of target location starts with "10.". If this property is not set, the oracle_home_default property is used.

  • property.RuntimePlatform.0.NativeExecution.SQLLoader.oracle_home_9i

    Defines the location of the Oracle 9i home from which SQL*Loader will be executed. This property is selected when the version of target location starts with "9.". If this property is not set, the oracle_home_default property is used.

  • property.RuntimePlatform.0.NativeExecution.SQLLoader.oracle_home_8i

    Defines the location of the Oracle 8i home from which SQL*Loader will be executed. This property is selected when the version of target location starts with "8.". If this property is not set, the oracle_home_default property is used.

  • property.RuntimePlatform.0.NativeExecution.SQLLoader.oracle_home_default

    Defines the location of the default Oracle home from which SQL*Loader will be executed. This property is selected when no other explicit property is set. If this property is not set, the ORACLE_HOME environment variable of the Control Center is used, otherwise the Control Center's own home is used.

  • property.RuntimePlatform.0.NativeExecution.SQLLoader.sqlldr_exe_10g

    Defines the location or name of the SQL*Loader executable. This property is selected when the version of target location starts with "10.". If this property is not set then the sqlldr_exe_default property is used.The value can specify either the executable terminal name or a full path to the executable. A full path name will be constructed from the specified Oracle home, the bin directory and the terminal name.

  • property.RuntimePlatform.0.NativeExecution.SQLLoader.sqlldr_exe_9i

    Defines the location or name of the SQL*Loader executable. This property is selected when the version of target location starts with "9.". If this property is not set, the sqlldr_exe_default property is used.The value can specify either the executable terminal name or a full path to the executable. A full path name will be constructed from the specified Oracle home, the bin directory and the terminal name.

  • property.RuntimePlatform.0.NativeExecution.SQLLoader.sqlldr_exe_8i

    Defines the location or name of the SQL*Loader executable. This property is selected when the version of target location starts with "8.". If this property is not set, the sqlldr_exe_default property is used.The value can specify either the executable terminal name or a full path to the executable. A full path name will be constructed from the specified Oracle home, the bin directory and the terminal name.

  • property.RuntimePlatform.0.NativeExecution.SQLLoader.sqlldr_exe_default

    Defines the location or name of the SQL*Loader executable. This property is selected when no other explicit property is set. If this property is not set, the standard terminal name for the execution platform is used. For example, sqlldr on UNIX and SQLLDR.EXE on Windows.The value can specify either the executable terminal name or a full path to the executable. A full path name will be constructed from the specified Oracle home, the bin directory and the terminal name.

SQL*Plus Runtime Preferences

  • property.RuntimePlatform.0.NativeExecution.SQLPlus.oracle_home_10g

    Defines the location of the Oracle 10g home from which SQL*Plus will be executed. This property is selected when the version of target location starts with "10.". If this property is not set, the oracle_home_default property is used.

  • property.RuntimePlatform.0.NativeExecution.SQLPlus.oracle_home_8i

    Defines the location of the Oracle 8i home from which SQL*Plus will be executed. This property is selected when the version of target location starts with "8.". If this property is not set, the oracle_home_default property is used.

  • property.RuntimePlatform.0.NativeExecution.SQLPlus.oracle_home_9i

    Defines the location of the Oracle 9i home from which SQL*Plus will be executed. This property is selected when the version of target location starts with "9.". If this property is not set, the oracle_home_default property is used.

  • property.RuntimePlatform.0.NativeExecution.SQLPlus.oracle_home_default

    Defines the location of the default Oracle home from which SQL*Plus will be executed. This property is selected when no other explicit property is set. If this property is not set, the ORACLE_HOME environment variable of the Control Center is used, else the Control Center's own home is used.

  • property.RuntimePlatform.0.NativeExecution.SQLPlus.sqlplus_exe_10g

    Defines the location or name of the SQL*Plus executable. This property is selected when the version of target location starts with "10.". If this property is not set, the sqlldr_exe_default property is used.The value can specify either the executable terminal name or a full path to the executable. A full path name will be constructed from the specified Oracle home, the bin directory and the terminal name.

  • property.RuntimePlatform.0.NativeExecution.SQLPlus.sqlplus_exe_9i

    Defines the location or name of the SQL*Plus executable. This property is selected when the version of target location starts with "9.". If this property is not set, the sqlldr_exe_default property is used.The value can specify either the executable terminal name or a full path to the executable. A full path name will be constructed from the specified Oracle home, the bin directory and the terminal name.

  • property.RuntimePlatform.0.NativeExecution.SQLPlus.sqlplus_exe_8i

    Defines the location or name of the SQL*Plus executable. This property is selected when the version of target location starts with "8.". If this property is not set, the sqlldr_exe_default property is used.The value can specify either the executable terminal name or a full path to the executable. A full path name will be constructed from the specified Oracle home, the bin directory and the terminal name.

  • property.RuntimePlatform.0.NativeExecution.SQLPlus.sqlplus_exe_default

    Defines the location or name of the SQL*Plus executable. This property is selected when no other explicit property is set. If this property is not set, the standard terminal name for the execution platform is used. For example, sqlplus on UNIX and SQLPLUS.EXE on Windows.The value can specify either the executable terminal name or a full path to the executable. A full path name will be constructed from the specified Oracle home, the bin directory and the terminal name.

Other Runtime Preferences

  • property.RuntimePlatform.0.delete_all_job_on_complete

    Represents the action to be taken for audit records of successful completed jobs. Set this property to true to automatically delete audit-execution record and any map-audit records for all jobs that complete successfully. Set this property to false to retain audit records. The default setting is false.

  • property.RuntimePlatform.0.logfile_max_size

    Represents the maximum number of trace messages that will be logged into the Control Center Service log file. If this property is set then a new log file will be created when this value is reached. The value must be at least 1000. The default value is 0 and this means that there is no maximum size and a new log file will only be created when the Control Center Service is started.

  • property.RuntimePlatform.0.max_number_logfiles

    Represents the number of log files that will be automatically retained in the OWB_ORACLE_HOME/log subdirectory. This property must be set to at least 2. the default value is 10. When a new log file is created, the system will ensure that at most this number of log files will be retained in the log subdirectory.

  • property.RuntimePlatform.0.purge_delay_hours

    Represents when the audit details must be purged. Audit details of any deployment or execution jobs with start time older than the specified number of hours may be purged, regardless of job status. The default value is 24 hours. The minimum value you can set is 1 hour.

  • property.RuntimePlatform.0.purge_minimum_minutes

    Audit details of any Deployment or Execution with start time under the specified number of minutes old may not be purged, regardless of job status. The default value is 10 minutes and the minimum value is 1 minute.

  • property.RuntimePlatform.0.recovery

    Controls whether deployments and executions are recovered when the Control Center Service starts up. The default value is true. This means that any deployments or executions that are still busy will be restarted when the service starts up. A value of false means that such deployments or executions are not recovered and are deactivated.

  • property.RuntimePlatform.0.platform_net_host_name

    Defines the host name that should be used when making inward connections to the Control Center. On RAC installations, this would normally identify the entire cluster, not a node within the cluster. This property is used for process flows and schedules where a database link is required to connect back to the Control Center schema.If this property is not set, the host name of the node running the Control Center service is used.

  • property.RuntimePlatform.0.platform_net_service_name

    Defines the TNS name that should be used when making inward database connections to the Control Center. The property is used for process flows and schedules where a database link is required to connect back to the Control Center schema.If this property is not set, the Control Center service connection information is used.

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 originally created using two source schemas, Human Resources (HR) and Order Entry (OE) and was loaded into the Warehouse (WH) target schema. Recently you were made aware of two changes to tables in the HR and OE schemas. The WH schema must be updated to reflect these changes.

Solution

In order to update the WH schema, you must first determine the impact of these changes and then create and execute a plan for updating the target schema. The following steps provide an outline for what you need to 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: Re-Deploy Scripts

Step 7: Test the New ETL Logic

Step 8: Update Your Discoverer EUL

Step 9: Execute 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. In order to do this, you must have a procedure or system in place that can notify you when changes are made to source objects.

In our scenario, you were made aware by the group managing the HR and OE schemas that some objects had been changed. There were two changes, the first was made to the HR schema. The REGION_NAME column was extended from 25 to 100 characters to accommodate longer data. The second change was made to the OE schema. The LOT_SIZE_NUMBER column was added and needs to 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 need to 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 need to 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 need to find a way to integrate the new column into the WH schema. Since 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 need to connect this new column to the SALES cube.

Step 3: Reimport Changed Objects

Since two source objects have changed, you must start by reimporting 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.

Warehouse Builder automatically detects that this is an update and proceeds by only updating changed definitions. The Import Results dialog box that displays at the end of the import process displays 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, 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 need to be reconciled.

In our scenario, we need to 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:

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

    The Metadata Dependency Manager opens and the Impact Analysis diagram reveals that the CUSTOMER dimension in the WH schema is the only object impacted by the REGIONS table.

    This step requires that you have already set up the Repository Browser. For more information on setting this up, see Oracle Warehouse Builder Installation and Administration Guide.

  2. Open the CUSTOMER dimension in the Data Object Editor and update the Region Name level attribute to 100 character length.

  3. 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 they represent in order to generate code based on the updated objects.

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

For Change #2, since it introduced a new column, you do not need to update the data flow the same way you did for Change #1. Make sure 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 shown in Figure 25–3, we can see that this is only the mapping MAP_SALES.

Figure 25-3 Impact Analysis Diagram for ORDER_ITEMS

Description of Figure 25-3 follows
Description of "Figure 25-3 Impact Analysis Diagram for ORDER_ITEMS"

Step 5: Redesign your Target Schema

Since Change #2 introduced the new LOT_SIZE_NUMBER column to the ORDER_ITEMS table, you need to 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:

  1. Add the measure NUMBER_OF_IND_UNITS with the NUMBER data type, precision of 8, and scale of 0 to the SALES cube.

  2. 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.

  3. Open the mapping MAP_SALES and ensure that the table ORDER_ITEMS is synchronized inbound.

  4. Connect the LOT_SIZE_NUMBER column in the ORDER_ITEMS table to the JOIN, and then to the SETOP, and then add it to the AGG operators. Ensure that you are doing a sum operation in the AGG operator.

  5. Finally, connect the LOT_SIZE_NUMBER output attribute of the AGG operator to the NUMBER_OF_IND_UNITS input attribute of the SALES cube.

Step 6: Re-Deploy Scripts

After the mappings have been debugged, use the Design Center to regenerate and re-deploy scripts. Use the Control Center Manager to discover the default deployment action. 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, resolve them and re-deploy the scripts.

Step 8: Update Your Discoverer EUL

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

To update your Discoverer EUL:

  1. Identify the objects that need to be updated in the 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.

  2. In the Project Explorer, 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.

  3. Expand the Item Folders node in the Business Definition Module that contains these changed objects.

  4. Select the objects identified in Step 1, right-click and select Deploy.

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

Step 9: Execute the ETL Logic

After the mappings have been deployed, execute and load data to the target.