In the earlier lessons, you have designed and configured the logical definitions of your target system. You can now deploy and create the physical instance of your target.
In this lesson, you deploy all the relational and dimensional objects that you previously created, and also deploy the ETL mappings that you designed.
Approximately 30 minutes
This tutorial covers the following topics:
|
Overview | |
|
Prerequisites | |
|
Deploying the External Table | |
|
||
|
Deploying the Mappings | |
|
Deploying the Process Flow | |
|
Summary | |
Place the cursor over this icon to load and view all
the screenshots for this tutorial. (Caution: Because this action loads all screenshots
simultaneously, response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.
Deployment is the process of creating your target system from the logical design or model. The process includes generating scripts, such as DDL that creates data objects (such as tables, views, and dimensions). The process also includes generating SQL, PL/SQL, and SQL*Loader scripts that load data into data objects.
There is an important distinction to note about deployment:
| |
When you deploy relational database or dimensional objects, such as sequences, tables, dimensions or cube, the DDL scripts are generated and executed simultaneously to physically create the objects. |
| |
When you deploy ETL mappings, the deploy procedure only generates a PL/SQL script. You need to explicitly execute the scripts to perform ETL. |
To deploy objects, Oracle Warehouse Builder provides two options: you can either use the Control Center Manager or you can deploy objects directly from the Project Explorer in the Design Center.
The Control Center Manager offers a comprehensive deployment console that enables you to view and manage all aspects of deployment, including configuration and validation. In the Control Center Manager, you can view the deployment history of an object. The deployment history is also used to automatically determine the default deployment action. These options are not available if you deploy objects from the Project Explorer tree in the Design Center.
In this lesson, you use the Control Center Manager to deploy your objects. You can deploy all the objects of your EXPENSE_WH module at once, but in this lesson the objects are deployed in steps.
Before starting this tutorial, you should have:
| 1. | Downloaded the owbdemo_files.zip file and extracted the files to the directory on your computer's hard drive |
| 2. | Completed lessons 1, 2, 3, and 4 |
To deploy the two external tables, perform the following steps:
| 1. | To deploy, you must launch the Control Center Manager. From the Tools menu, select Control Center Manager.
The Control Center Manager is launched. The Control Center Manager console appears as shown in the screenshot.
|
| 2. |
Observe that in the Control Center Manager console, you can access the design objects based on their locations, because the navigation tree is organized on locations. Note: When you create locations during the design process, you create logical definitions that are limited to merely the name, type, and version of the location. When you register locations, you specify the actual physical connection information that will be used during deployment to connect to the various data sources and targets. Locations enable you to deploy the same logical design into multiple physical implementations with no change to the design. For example, you can create and register another location that points to a QA instance of your target system. Connectors define a path to transfer data from one location to another location. Expand EXPENSE_WH_LOCATION, and then expand EXPENSE_WH. On the right, in the Object Details panel, examine the design status, deploy status, and other details of all the objects that you designed in previous lessons.
|
| 3. | In the navigation tree on the left, expand and select External Tables. In the Object Details panel on the right, check whether the Design Status is set to New for both external tables, EXPENSE_CATEGORIES and EXPENSE_DATA. Click Default Actions (highlighted in the screenshot) and observe that Deploy Action is changed to Create. Note: The Default Actions button automatically updates the action for the objects you select. It intelligently determines the most appropriate action based on the metadata details in the repository. For example, for objects whose design status is set to New, their action is automatically set to Create.
|
| 4. | To deploy the selected external tables,
from the File menu, select Deploy > To Control Center
or click the Deploy
You can monitor the progress of deployment in the Control Center Jobs panel on the lower right of the console, as shown in the screenshot.
This is your first deployment and you need to register the target location. In the Edit Oracle Database Location window, enter expense_wh as the password. Click Test Connection to test the connection to the target schema.
If connection is successful, click OK. |
| 5. | Again, note that in the Control Center Jobs panel, the deployment
status changes from "generate" You can view the generated code for the successfully deployed external tables. Double-click OWB_DEMO in the Job column in the Control Center Jobs panel (highlighted in the screenshot).
In the OWB_DEMO dialog box, click the Script tab (highlighted in the screenshot). Note that for each external table, Warehouse Builder generates DDL scripts. To view the generated code for the Expense_Categories external table, select Expense_Categories. The View code button is enabled. Click View Code.
Scroll in the Expense_Categories.ddl window to view the generated code.
From the Code menu, select Close to close the code window. In the OWB_DEMO dialog box, select Close from the File menu to close the dialog box. |
You have now successfully deployed the two external tables to the EXPENSE_WH schema.
In the following steps, you deploy the REL_EXPENSE cube with the two dimensions REL_CATEGORY and REL_TIME.
| 1. |
Before you deploy the dimensions and the cube, you need to deploy the relational tables and sequences that these dimensions and cube are based on. In EXPENSE_WH, expand Tables. Expand Sequences. To deploy all the tables and sequences, select Tables, press and hold [Ctrl], and select Sequences.
Observe the Design Status of the three tables and two sequences.
|
||||||||||||
| 2. |
Click Default Actions to
set Deploy Action to Create. From the File menu, select Deploy
> To Control Center or click the Deploy
In the Control Center Jobs panel, monitor the deployment progress. The deployment is successful.
|
||||||||||||
| 4. |
You can now deploy the cube. Before the cube is deployed, you should have deployed the related dimensions. Before you deploy the dimensions and the cube, ensure that the deployment option is set to Deploy to Catalog Only. To set the deployment options, right-click the appropriate dimension or cube in the design center and select Configure from the menu. In the Configuration Properties dialog box, set the deployment options property to Deploy to Catalog Only. The screenshot shows Configuration Properties dialog box for REL_EXPENSE cube.
Note: Oracle Warehouse Builder provides four deployment options for dimensional objects:
In EXPENSE_WH, expand and select Dimensions. Observe that Design Status for all the objects is set to New. Click Default Actions to change Deploy Action to Create. To deploy, select Deploy > To Control Center
from the File menu, or click the Deploy
Note that in the Control Center Jobs panel, the deployment status changes from "generate" to "run" to "completed successfully."
|
||||||||||||
| 5. |
In EXPENSE_WH, expand and select Cubes. Note that Design Status for all the objects is set to New. Click Default Actions to change Deploy Action to Create.
To deploy, select Deploy > To Control Center from the File menu,
or click the Deploy Again, note that in the Control Center Jobs panel, the deployment status changes from "generate" to "run" to "completed successfully."
The REL_EXPENSE cube and the REL_TIME and REL_CATEGORY dimensions are successfully deployed.
|
||||||||||||
You have deployed the external tables, the dimensions, and the cube, you now deploy the ETL mappings. When you deploy a mapping, Warehouse Builder generates appropriate scripts. For mappings, you need to follow an extra step of executing these scripts to actually perform ETL from the source to the targets.
You will execute the mappings in the next lesson. The steps to deploy the mappings are similar to those that you performed earlier.
| 1. |
In EXPENSE_WH_LOCATION, select Mappings. In the Object Details panel, three mappings are selected with Design Status set to New.
|
| 2. | Click Default Actions to change Deploy Action from
None to Create. From the File menu, select Deploy > To Control
Center or click the Deploy
In the Control Center Jobs panel, the deployment status changes from "generate" to "run" to "completed successfully." The mappings are deployed successfully and are ready for execution. To view the generated code for the mapping, double-click the OWB_DEMO job in the Control Center Jobs panel (highlighted in the screenshot).
|
| 3. | In the OWB_DEMO dialog box, click the Script tab. Note that Warehouse Builder generates a .pls (PL/SQL) code for each of the deployed mappings. To view the generated code for REL_CATEGORY_MAP, select REL_CATEGORY_MAP and click View Code.
Scroll down in the code window to examine the generated code.
From Code menu, select Close. Then in the Job Details window, from File menu, click Close to exit. |
In the previous lesson, you designed a process flow that automates the execution of the mappings for loading the target dimensions and the cube.
You need to deploy the process flow, before it can be executed to trigger the execution of the deployed mappings. To deploy the process flow, perform the following steps:
| 1. |
Expand WH_OWF_LOCATION > MY_PF_MODULE > PK. Select PK. In the Object Details panel, the process flow package PK is selected for deployment. Design Status is set to New. Click Default Actions to set Deploy Action to Create. Note: You deploy the process flow package, and not the process flow. However, you execute the process flow. From the File menu, select Deploy > To Control Center or
click the Deploy
The process flow package, PK, is deployed successfully.
Note: While deploying the process flow package if you get the error, "RPE-02072: Oracle Workflow NLS language ...has not been enabled in the Oracle Workflow repository. Please enable using the Oracle Workflow wfnlena.sql server-side script.", you need to run two scripts in order to enable and load the NLS language, required by the Workflow Repository. Perform the following: Log on to the owf_mgr schema in SQL*Plus. (Ensure that the account is unlocked.) At SQL prompt, enter the following command: @<ORACLE_HOME>\wf\admin\sql\wfnlena.sql Substitute <ORACLE_HOME> with your Oracle home path. This script requires two inputs, enter GB and Y respectively. GB is the code for english language. For other languages, you need to know the appropriate language code. Now, run wfnladd.sql at the SQL prompt, as shown below: @<ORACLE_HOME>\wf\admin\sql\wfnladd.sql
|
| 2. |
To view the generated code for the process flow, double-click the OWB_DEMO job in the Control Center Jobs panel. In the PK dialog box, click the Script tab (highlighted in the screenshot). Note that Warehouse Builder generates the .xml script for the process flow. To view the generated code, select PK. The View code button is enabled. Click View Code.
Scroll down in the code window to examine the generated code.
Exit the code window. From the File menu, select Close to close the PK dialog box. In the Control Center Manager, from File menu, select Close to exit. |
In this lesson, you learned to physically implement the logical designs of dimensions and the cube. You also deployed the mappings to generate the scripts that could be executed to extract, transform, and load data from source to the new targets.
In this lesson, you've learned how to:
| |
Deploy the External Tables |
| |
Deploy the Cube and the Referenced Dimensions |
| |
Deploy the Mappings |
|
Deploy the Process Flow |
Note: You were instructed to deploy all the objects in four separate steps. However, Warehouse Builder enables you to more easily deploy all the objects with a single click of the Deploy button. You were instructed to follow a step-by-step approach for the purpose of better understanding.
In the next lesson, you will run the process flow to load the target dimensions and cube with transformed data.
Place the cursor over this icon to hide all screenshots.
button on the toolbar. 
to
"run" 
to "completed successfully"
. Note that
Deploy Status has changed to Success for both external tables.