11 Deploying to Target Schemas and Executing ETL Logic

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, an 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.

Creating a Control Center

1. In the Connection Explorer, right-click Control Centers and select New.

   The Create Control Center dialog box is displayed.

2. Complete the dialog box. Click the Help button for additional details.

You can also create a Control Center using the Create Configuration Wizard.

Activating a Control Center

The status bar in the Design Center displays the Active Configuration. A named configuration is associated with only one Control Center. Objects are deployed to the control center associated with the active configuration.

To activate a Control Center:

1. In the Project Explorer, create or edit a configuration so that it uses the Control Center.

   Refer to "Creating New Configurations".

2. Activate that named configuration.

   Refer to "Activating Configurations".

Creating Locations

In addition to the automatically created locations, you can create your own locations that correspond to target schemas that you want to use as sources or targets.

To create a location:

1. In the Connection Explorer, expand the Locations node and then the node that represents the type of location you want to create.

   For example, to create an Oracle database location, expand the Locations node, the Databases node, and then the Oracle node.

2. Right-click the type of location and select New.

   The Create <location_type> Location dialog box is displayed.

3. Complete the dialog box. Click the Help button for additional details.

Using SQL*Net to Create Locations

When you create Oracle locations of type SQL*Net, you must set up a TNS name entry for these locations. The TNS name must be accessible from the Oracle Database home. To do this, run the Net Configuration Assistant from the Oracle Database home.

While setting up a TNS name for use during deployment and execution of maps and process flows, the TNS name must be accessible from the Warehouse Builder home used to run the control center service. To make the TNS name accessible, run the Net Configuration Assistant from the Warehouse Builder home. Next, restart the control center service so that it can pick up the changes.

About Locations, Passwords, and Security

Considering that all Warehouse Builder users can view connection information in a location, note that the passwords are always encrypted. Furthermore, Warehouse Builder administrators can determine whether or not to allow locations to be shared across users and persisted across design sessions. By default, locations are not shared or persisted.

Registering and Unregistering Locations

All modules, including their source and target objects, must have locations associated with them before they can be deployed.

Registering a location establishes a link between the workspace and the locations of source data and deployed objects. You can change the definition of a location before it is registered, but not afterward. After the location is registered, you can only change the password. To further edit a location or one of its connectors, you must first unregister the location. Unregistering deletes the deployment history for the location.

Locations are registered automatically by deployment. Alternatively, you can explicitly register a location in the Control Center.

To register a location:

1. Open the Control Center Manager and select a location from the navigation tree.

2. From the File menu, select Register.

   The Location dialog box is displayed.

3. Check the location details carefully.

   Click Help for additional information.

4. Click OK.

To unregister a location:

1. Open the Control Center Manager and select a location from the navigation tree.

2. From the File menu, select Unregister.

3. Click OK to confirm the action.

Deleting Locations

To delete a location, right-click the location in the Connection Explorer and select Delete. If the delete option is not available here, this indicates that the location has been registered in a control center and is likely being utilized. Verify that the location is not in use, unregister the location in the Control Center Manager, and then you can delete the location from the Connection Explorer.

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" in the Warehouse Builder Online Help.

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. For more information about the core functionality, see "Product Options and Licensing".

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.

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.

Creating New Configurations

To create a new named configuration:

1. In the Project Explorer, select a project and expand the navigation tree.

2. Select Configurations.

3. From the Design menu, select New.

   The Create Configuration wizard opens.

4. On the Name page, provide a name and an optional description.

   Click Set and save as my active configuration for this project to set the new named configuration the active configuration. Any configuration parameters that you set for design objects are saved in this named configuration. Also, any objects that you deploy are deployed to the control center associated with this new named configuration.

5. On the Details page, select the control center that should be associated with this configuration.

   If you have not already created the control center, click New to create one and associate it with the configuration.

6. Click Finish to close the wizard and create the named configuration.

The new named configuration appears in the Configurations folder.

Activating Configurations

There can be only one active configuration at a time. Any objects that you deploy are deployed to the control center associated with the active configuration. To implement your design to a different target system, you must deploy objects to the control center associated with that target system. You can do this by activating the named configuration associated with the control center.

To activate a named configuration:

1. Right-click the named configuration you want to activate and select Open Editor.

2. On the Name tab, select Set and save as my Active Configuration for this project.

To activate a named configuration for the current session only:

1. In the Project Explorer, select a project and expand the navigation tree.

2. Expand the Configurations folder.

3. Select a named configuration.

4. From the Design menu, select Set As Active Configuration.

   The selected named configuration is set as the active configuration for the current session. If you exit Warehouse Builder and log in subsequently, the changes are not saved.

Any changes that you make to the configuration parameters of objects are saved in the active configuration. If you switch to the previous named configuration, then these parameters will have their previous settings. The Control Center associated with this named configuration is now active and stores all new information about validation, generation, and deployment of objects in the project.

Scenario Requiring Multiple Configurations

An enterprise data warehouse typically has multiple environments such as development, testing, and production. The same design objects need to be created in all these environments. But the objects need different physical configuration settings in each environment

Table 11-2 lists the configuration settings for a particular table in the development, testing, and production environments.

You can implement this scenario by creating different configurations for each environment. Specify different values for the configuration properties of the object in each named configuration. By switching among different named configurations, you can change the physical design without making any changes to the logical design. You can easily deploy a single data system to several different host systems or to various environments.

Setting Configuration Properties for a Named Configuration

To set configuration properties for a particular named configuration:

1. Set the named configuration as the active configuration.

   For information about activating a configuration, see "Activating Configurations".

2. In the Project Explorer, right-click the object that you want to configure and select Configure.

3. Specify the configuration properties for the selected object.

4. Repeat steps 2 and 3 for all objects in the project for which you want to set configuration properties.

You can now deploy the design objects. The Control Center that is presented in the Deployment Manager is the Control Center that is associated with the active configuration.

Deploying a Design to Multiple Target Systems

Creating multiple configurations enables you to deploy the same design to multiple target systems. To deploy a design to a particular target system, you activate the named configuration associated with the target system and deploy your design.

To deploy a set of design objects to multiple target systems:

1. If you have not already done so, create a named configuration for each target system to which design objects must be deployed.

   See "Creating New Configurations".

   For each named configuration, ensure that you create a separate control center that points to the target system. Also set the configuration properties for design objects in each named configuration, as described in "Setting Configuration Properties for a Named Configuration".

2. Activate the named configuration associated with the target system to which design objects must be deployed.

   See "Activating Configurations".

3. Resolve errors related to deployment or execution.

   You may encounter some errors even if you validated data objects before deploying them. These errors could be caused by configuration properties you set. Configuration property values represent physical property information that is not semantically checked before deployment. For example, the value you specify for the Tablespace Name property is not checked against the database at validation time. Such errors are encountered during deployment.

4. Deploy the design objects.

   See "Deploying Objects".

5. Repeat steps 2 to 5 for each target system to which design objects must be deployed.

Benefit of Creating Additional Configurations

A named configuration stores the physical details for every object in a project. You can create multiple named configurations for a project, each containing different physical settings for objects. The physical settings for design objects in each named configuration are preserved.

Consider a scenario that requires the same design objects to be implemented in different target systems, that is, different control centers. For each target system, you can create a named configuration and then specify different configuration properties for design objects in that named configuration. To implement your design to a particular environment, you simply activate the named configuration associated with that environment and deploy objects.

Configuration property values belong to the named configuration object and are preserved. You do not have to reset configuration values when you switch between named configurations. The configuration properties that you see in the Design Center are the settings associated with the active configuration. The status bar at the bottom of the Design Center displays the name of the active configuration.

For example, the named configuration associated with the development environment is currently active. So any changes you make to configuration property values are made to the development environment. For the table MY_TABLE, you set the Tablespace configuration parameter to DEV. Next activate the named configuration associated with the production environment. The configuration values displayed will be the same values that you set the last time the production configuration was active. The Tablespace configuration parameter for MY_TABLE is null. You set it to PROD. This change affects only the production configuration. Switching back to the development configuration will show the Tablespace configuration parameter for MY_TABLE remains as DEV. Every object instance in a project has unique configuration values. So, in this example, setting tablespace value for MY_TABLE has no effect on any other table. Each table instance must be individually configured.

Another advantage of multiple configurations is the ease with which it enables you to make changes to your existing environment. For example, you design objects, implement your development environment, deploy objects, and then move to the testing environment. You then need to change some objects in the development environment. To do this, you just activate the named configuration associated with the development environment, make the changes to objects, regenerate scripts, and deploy objects. To return to the testing environment, you activate the testing configuration. There is no need to make changes to the design objects.