Deploying Objects in the Target Warehouse
Deploying Objects in the Target Warehouse
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
Note: This tutorial and its setup scripts support OWB 11g Release 1, only. Earlier versions of this Oracle By Example tutorial are available for OWB 10g Releases 1 and 2.
This tutorial covers the following topics:
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.
Back to Topic List
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 |
Back to Topic List
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 from None 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 
button on the toolbar.
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.
Because this is your first deployment to this target location, you need to register the location.
In the Edit Oracle Database Location window, enter expense_wh as
the password, localhost as the host, 1521 as the port, and orcl as the service name (or if you have different values for your setup, use enter them accordingly). 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"  to
"run"  to "completed successfully"
 . Note that
Deploy Status has changed to Success for both external tables.
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).
The Job Details: OWB_DEMO(number) dialog box appears (ignore the appended number in parentheses in the title of the dialog box). In the navigation tree in the upper left corner of the dialog box, select External Tables. In the upper right corner of the dialog box, click the Script tab. 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 Job Details: OWB_DEMO dialog box, select Close from the File menu
to close the dialog box. Do not exit the Control Center Manager.
|
You have now successfully deployed the two external tables
to the EXPENSE_WH schema.
Back to Topic List
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 Control Center Manager, 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 button
on the toolbar.
In the Control Center Jobs panel, monitor the deployment progress. The
deployment is successful.
|
| 4. |
Before you deploy the cube,
you need to deploy the related dimensions.
Before you deploy the dimensions and the cube, ensure that their deployment
option is set to Deploy to Catalog Only. To set the deployment
options, switch from the Control Center Manager to the Design Center. 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.
Change the Deployment Options property to Deploy to Catalog Only for these objects:
- REL_EXPENSE cube
- REL_CATEGORY dimension
- REL_TIME dimension
Note: Oracle Warehouse Builder provides four deployment
options for dimensional objects:
|
Deploy All: For a relational or ROLAP implementation,
the dimensional object is deployed to the database and a CWM definition
to the OLAP catalog. For a MOLAP implementation, the dimensional
object is deployed to the analytic workspace.
|
|
Deploy Data Objects Only: This is
the default deployment option. This option deploys the dimensional
object only to the database. You can select this option only for
dimensional objects that use a relational implementation. This option
is equivalent to the code generation of OWB 10gR1.
If you deploy the objects using this option,
you will not be able to view the loaded data through the Data Viewer
because the Data Viewer uses OLAP catalog metadata to show data.
|
|
Deploy to Catalog Only: This option
deploys the CWM definition to the OLAP catalog only. Use this option
if you want applications such as BI Beans or Discoverer for OLAP
to access the dimensional object data after you deploy data only.
Using this option, you can view the loaded
data in the dimensions and the cube in the Data Viewer.
|
|
Deploy
Aggregation: This deploys the aggregations defined on the cube
measures. This option is available only for cubes. |
From the Design Center, return to the Control Center Manager. In EXPENSE_WH, expand and select Dimensions. Observe that
Design Status for the two dimensions 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
button.
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 the REL_EXPENSE cube 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
button.
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.
|
Back to Topic List
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 button.
Deployment of these mappings takes a few seconds longer than deployment of the previous objects.
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.
Note: The deployment might show a successful status, but with warnings. Warnings about the source column length being longer than the target column length can be ignored in the case of our example.
To view the generated code for the mapping,
double-click the OWB_DEMO job at the top of the list in the Control Center Jobs
panel.
|
| 3. |
In the Job Details: OWB_DEMO dialog box, click Mappings in the navigation tree in the upper left panel. 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. Keep Control Center Manager open.
|
Back to Topic List
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. |
In Control Center Manager, 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: Here you deploy the process flow package,
and not the process flow within it. In the next lesson, you execute the process flow.
From the File menu, select Deploy > To Control Center or
click the Deploy
button.
Because this is your first deployment to this WH_OWF_LOCATION location, you need to register the location. In the Edit Oracle Database Location window, enter owf_mg as the password, localhost as the host, 1521 as the port, and orcl as the service name (or if you have different values for your setup, use enter them accordingly). Click Test Connection to test the connection to the target schema.
If connection is successful, click OK.
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. |
Back to Topic List
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.
Back to Topic List
|
Bookmark
