Loading the Target Warehouse
Loading the Target Warehouse
In this lesson, you execute the deployed process flow
to load the target warehouse. You also explore the advanced metadata management
and administration features that Warehouse Builder provides.
Approximately 45 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.
In Oracle Warehouse Builder, you can execute two types
of deployed objects: mappings and process flows. After you deploy process flows
or mappings to your target system, they are available for execution from within
the Control Center Manager. Additionally, process flows can be executed
through Oracle Workflow.
Typically, you want to load a target warehouse with data and
subsequently refresh the data. Either you can execute the Warehouse Builder
mappings individually each time you want to load or reload data or you can execute
a process flow.
The process
flow automates the order and dependency of the mappings execution.
It also enables you to specify what action to perform when
the
mappings
execution ends in error, ends with a warning status, or completes successfully.
In addition, you can even schedule the process flows to execute at regular
time
intervals.
For
example, every Monday the process flow may be scheduled to run to perform data
refresh of the warehouse.
In this lesson, you execute the deployed process flow and
also view the loaded data in the data viewer. This lesson also gives a brief
overview of Oracle Warehouse Builder's Impact and Lineage Analysis capabilities
using the Dependency Manager. There is also a brief introduction to the Repository
Browser that enables you to perform administrative tasks on the target warehouse.
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, 4, and 5
|
Back to Topic List
To load data into the target dimensions and cube,
you need to execute the process flow. The process flow automatically
invokes the mappings to load the dimensions and the cube.
To execute the process flow, perform the following
steps:
| 1. |
From the Tools menu, select Control Center
Manager. In the Control Center Manager console, expand WH_OWF_LOCATION,
expand MY_PF_MODULE, and then expand PK.
Select LOAD_EXPENSE_WH.
To execute a process flow, you can either select Start from
the File menu or click the Start (  )
button on the toolbar.
|
| 2. |
When you click the Start button, the Control
Center Jobs panel switches to the Execution tab. You can
monitor the execution in this panel. The executions are performed asynchronously,
which means that you can close the Control Center and OWB Client while
your Process Flow or Mapping is being executed.
To view the execution details, double-click LOAD_EXPENSE_WH in
the Job column of the Control Center Jobs panel (highlighted in the
screenshot).
In the LOAD_EXPENSE_WH dialog box, click the Execution Results tab
and monitor Row Activity. You can get a count of how many rows were
inserted
in respective dimensions and the cube.
From the File menu of the LOAD_EXPENSE_WH dialog box, select Close.
From the File menu of the Control Center Manager, select Close to
exit.
|
On successful execution of the process flow, all the dimensions
and the cube are now loaded with data.
Back to Topic List
You can view the data loaded in the dimensions and the cube
from within the Oracle Warehouse Builder design client environment by using
the Data Viewer.
| 1. |
To view REL_TIME data, expand Dimensions under
EXPENSE_WH. Right-click REL_TIME and select Data.
Note that
the time data is based on the standard Calendar hierarchy ranging from the
year 2003 for three years, as you had selected during the design of the
REL_TIME dimension. If you expand each year node, you see each year's quarters.
|
| 2. |
Similarly, to view REL_CATEGORY data, right-click
REL_CATEGORY and select Data.
Expand some of the category nodes like Office or Food to see their subcategories.
|
| 3. |
To view REL_EXPENSE cube data, expand Cubes under EXPENSE_WH and right-click REL_EXPENSE. Select Data.
|
Back to Topic List
Oracle Warehouse Builder provides a graphical
interface that displays how changes in your sources impact the design metadata
in your repository. This graphical interface is the Dependency Manager.
The Dependency Manager enables you to determine the impact of the changes
made
to the object definitions or the metadata in the Warehouse Builder repository.
These changes may occur when you reimport source metadata,
when you make changes to the definitions according to end-user or external
requirements, or if your target system is changing. In all such cases where
a change has been made to the metadata in your Warehouse Builder repository,
the Dependency Manager helps you analyze and resolve the impact from the
changes.
For example, in the EXPENSE_WH scenario, all the source data
is coming from the two flat files. If you want to add a column to one of the
flat files, you would want to know what impact it has on the design of
all the warehouse objects.
You perform impact analysis for the export_csv flat file.
| 1. |
Expand the OWB_DEMO project, and
then expand the Files node. Expand SOURCE and
right-click EXPORT_CSV. From the menu,
select Impact.
Note: The interactive impact and lineage analysis is available
in Enterprise ETL option of OWB 10gR2.
The Metadata Dependency Manager opens. The Dependency
Manager displays the initial structural impact graph for the flat file.
It displays all the objects that have a structural dependency on the
export_csv flat file and are, therefore, impacted by the change.
From the first view of the dependency diagram, you understand that any
change in the export_csv file impacts the EXPENSE_DATA external table
directly and also impacts the REL_EXPENSE cube indirectly.
|
| 2. |
The (+) sign to the left of each operator gives the
dependency detail from the lineage perspective. It shows the details
of all the objects
that the object relies on, whereas the + sign on the right
of the operator provides details of the objects that will be impacted
as
a result of change.
For example, if you click the + sign to the left of the REL_EXPENSE
cube operator in the graph, you get an expanded graph.
The graph depicts that the REL_EXPENSE cube is getting inputs from the REL_CATEGORY
and REL_TIME dimensions and also from the REL_EXPENSE table.
The mapping symbol (the 
icon denotes collapsed mapping piece) also depicts that the REL_EXPENSE
cube is associated with the EXPENSE_DATA external table through a mapping.
|
| 3. |
Double-click the icon
(collapsed mapping piece) between the EXPENSE_DATA external table and the REL_EXPENSE
cube to view the mapping.
The collapsed mapping piece expands into REL_EXPENSE_MAP mapping, which shows
the data flow between the EXPENSE_DATA external table and the REL_EXPENSE
cube.
The mapping depicts that the data from the EXPENSE_DATA external
table is
being processed by an expression operator and loaded into the REL_EXPENSE
cube.
|
| 4. |
To find out exactly which attributes are being processed
by the expression operator, you can drill down to attribute level impact
analysis.
For attributes-level analysis, double-click the expression
operator.
Double-click EXPENSE_DATA and REL_EXPENSE inside the mapping box to view
the
input
and the output
attributes.
The screenshot shows a portion of the dependency diagram.
From the Analysis menu, select Close to exit the Dependency
Manager.
|
Therefore, the dependency diagram enabled you to easily trace
and analyze the impact and lineage of a source flat file in the EXPENSE_WH
scenario.
Back to Topic List
You can use Oracle Warehouse Builder Repository
Browser, an HTML-based interface, to view and generate reports on all repository
metadata objects and the relationships between those objects.
You can use
the Repository
Browser
as a stand-alone component. If you want remote access to the repository
metadata objects, you can
use the Portal version—the version of Warehouse Builder Repository
Browser that is integrated with Oracle Application Server.
After a data warehouse is functional, it needs to be administered
and that involves tasks such as monitoring the details of the deployment and execution jobs.
Other tasks include examining the effect of metadata changes through Impact
and Lineage analysis and monitoring the execution schedules of the mappings
for
data refresh
in
the target warehouse.
You now get an overview of various reports that you can view
in the Repository Browser.
To start the stand-alone version of the Repository
Browser, you need to first start the OWB Browser Listener.
| 1. |
To start the OWB Browser Listener, open a command line window by selecting Start
> Run and executing cmd. In the command line window, change directories to {your Oracle - OWB11gClientHome} > owb > bin > win32 .
Execute StartOwbbInst.bat.
The first time you invoke this listener, you are required to select and reconfirm a password for an oc4jadmin account. At these prompts, enter the password as browser. You will need to use your password each time you start up and shut down the OWB Browser Listener.
A message reading "...Oracle Containers
for J2EE 10g (10.1.3.0.0) initialized" appears in the window, as
shown in the screenshot. Now, you are ready to log in to the unified
repository
and view the repository
metadata.
You only need to run one instance of OWB Browser Listener in your enterprise.
Then, anyone with the login credentials and a Web browser can access
OWB Repository Reports, without the need to have OWB Client installed.
Do not close the window; minimize it.
|
| 2. |
To start the Repository Browser, from your desktop, select Start
> Programs > {your Oracle - OWB10gR2clientHome}> Warehouse
Builder > Repository Browser.
A browser window with the following URL appears:
http://127.0.0.1:8999/owbb/RABLogin.uix?mode=design&def_host=&def_port=1521&def_service=&def_net_service=
|
| 3. |
On the login page, enter the following information:
At the bottom of the login page, ensure that the default choice to the right of the Reports prompt is "Design Center", not "Control Center".
Click Login.
The following screen appears:
In the Navigator, you can navigate to your projects in the Unified Repository
and explore the details of the objects inside the project. The MY_WORKSPACE workspace
is shown in the table.
|
| 4. |
Expand MY_WORKSPACE by clicking the plus sign (+) to the left of MY_WORKSPACE, and then expand OWB_DEMO.
You will get a view of all the modules. Expand EXPENSE_WH and
you get a listing of all the objects contained in the module.
You can drill down further by expanding each of the object nodes. Expand
EXPENSE_CATEGORIES. The columns of the external table
are shown.
|
| 5. |
If you want to browse Control Center reports, you do not
need to access the login page again.
To switch over to Control Center Reports, scroll down on the same page
and click Control
Center: Reports in
the Related Links section at the bottom of the page, as shown in
the screenshot.
The following screen appears.
If you want to know the current deployment status of all the objects,
click the Object Summary Report link (highlighted in
the screenshot) in the Deployment section of the Reports page.
|
| 6. |
In the Object Summary Report, you see useful deployment details of each
object. For example, you get to know that REL_EXPENSE cube was deployed
to EXPENSE_WH_LOCATION on 6th September 2007 at 8.06 p.m. and that the object
status is valid.
You can drill down further into deployment details by clicking any of
the links.
Scroll up on the same page and click Execution Summary in
the Available Reports section at the upper-right corner, highlighted
in the screenshot above.
|
| 7. |
The Execution Summary report shows the details of the
execution of the process flow, LOAD_EXPENSE_WH.
The Execution status is marked
as Complete: OK. (The results may vary depending on
your success with the hands-on practice.)
You can get into the details of the execution by
clicking Execution Job Report in the Related Information
column of the table, highlighted in the screenshot.
The
Execution Job Report gives you a full overview of the results of the
execution.
|
| 8. |
In the Execution Job Report, you get the execution
details of each mapping in the process flow.
According to this report, the REL_TIME_MAP mapping is executed
successfully. It gives the details of how many records were selected,
inserted, updated, deleted, or merged.
From the Repository Browser, you can also stop or expedite the execution
of a mapping or a process flow. The command buttons are highlighted in
the screenshot.
You can even start execution of a mapping. For example, to start the
execution of REL_TIME_MAP, click REL_TIME_MAP in the
Activity Details table.
You get the Execution Report for the mapping. To start the execution, click Start in
the Available Reports section.
In the Start Report, you can set the execution parameters and click
Start Execution to start the execution of the mapping.
Note: You may get different results in the reports depending on your
execution history.
|
| 9. |
Spend a few minutes navigating through some other reports.
Then, click Logout at the upper-right corner and close
the browser.
To stop the OWB Browser Listener, navigate to {your Oracle - OWB11gClientHome} > owb > bin > win32 . Execute StopOwbbInst.bat..
|
Back to Topic List
In this lesson, you learned how you can load data into
your target warehouse by executing a process flow. You got a brief exposure
to Oracle Warehouse Builders impact analysis capabilities. Using the Repository
Browser, you examined the various important reports on the repository metadata.
In this lesson, you've learned how to:
 |
Load the Target Warehouse Using Process Flows
|
| |
View the Warehouse Data in the Data Viewer
|
| |
Perform Impact Analysis Using the Dependency Manager
|
|
Administer the Warehouse Using the Repository Browser |
Back to Topic List
Place the cursor over this icon
to hide all screenshots.
|
Bookmark
