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

Topics

This tutorial covers the following topics:

Loading the Target Warehouse Using Process Flows

Viewing Data in the Data Viewer
Managing Metadata Using the Dependency Manager
Administering the Warehouse Using the Repository Browser

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.

Overview

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

Loading the Target Warehouse Using Process Flows

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.

Place the cursor over this icon to see the image

 

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

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

Back to Topic List

Viewing Data in the Data Viewer

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

 

2. Similarly, to view REL_CATEGORY data, right-click REL_CATEGORY and select Data.

Place the cursor over this icon to see the image

 

3.

To view REL_EXPENSE cube data, expand Cubes under EXPENSE_WH and right-click REL_EXPENSE. Select Data.

Place the cursor over this icon to see the image

Place the cursor over this icon to see the image

 

Back to Topic List

Managing Metadata Using the Dependency Manager

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

 

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.

Place the cursor over this icon to see the image

 

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.

Place the cursor over this icon to see the image

 

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.

Place the cursor over this icon to see the image

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

Administering the Warehouse Using the Repository Browser

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, from your Desktop, select Start > Programs > {your Oracle - OWB10gR2clientHome} > Warehouse Builder > Administration > Start OWB Browser Listener. A command window appears.

A message reading "...Oracle Application Server Containers for J2EE 10g (9.0.4.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.

Place the cursor over this icon to see the image

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=

Place the cursor over this icon to see the image

 

3.

On the login page, enter the following information:

Username: rep_owner

Password: rep_owner

Host address: localhost

Host Port Number: 1521 (already set)
Host Service Name: orcl or <your specified db name>

Place the cursor over this icon to see the image

Click Login.

The following screen appears.

Place the cursor over this icon to see the image

The Control Center reports are categorized into deployment, execution, and management reports. Before you examine the control center reports, you will examine the design repository. In Related Links, click Design Repository: Navigator.The following screen appears:

Place the cursor over this icon to see the image

In the Navigator, you can navigate to your projects in the Unified Repository and explore the details of the objects inside the project. The rep_owner repository is shown in the table (highlighted in the screenshot by a red rectangle).

 

4.

Expand rep_owner, 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.

Place the cursor over this icon to see the image

You can drill down further by expanding each of the object nodes. Expand EXPENSE_CATEGORIES. The columns of the external table are shown.

Place the cursor over this icon to see the image

 

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.

Place the cursor over this icon to see the image

The following screen appears.

Place the cursor over this icon to see the image

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 31st December 2005 at 6.28 p.m. and that the object status is valid.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

You get the Execution Report for the mapping. To start the execution, click Start in the Available Reports section.

Place the cursor over this icon to see the image

In the Start Report, you can set the execution parameters and click Start Execution to start the execution of the mapping.

Place the cursor over this icon to see the image

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, select Start > Programs > {your Oracle - OWBclientHome} > Warehouse Builder > Administration > Stop OWB Browser Listener.

Back to Topic List

Summary

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.