Setting Up the Environment for Migration

Purpose

In this tutorial, you set up the project environment for migrating a Oracle Warehouse Builder 10gR1project to Oracle Warehouse Builder 10g Release 2 (OWB 10gR2). This tutorial also discusses some of the new architectural and configuration concepts introduced in Oracle Warehouse Builder 10gR2.

Time to Complete

Approximately 40 minutes

Topics

This tutorial covers the following topics:

	Overview
	Scenario
	Prerequisites
	Setting Up the OWB 10gR1 Project
	Creating the Unified Repository Using the Repository Assistant
	Examining the New Design Center
	Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so 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

This tutorial covers how to set up the environment for migrating an existing system that was built using Oracle Warehouse Builder 10gR1 to the current version, Oracle Warehouse Builder 10gR2.

What is migration?

In today's dynamic information architecture, when every organization’s demands and requirements for more knowledge is growing, it is important to upgrade the tools that help you create that knowledge, with new capabilities and enhancements. Migrating to the current version consists of upgrading your Oracle Database environment, upgrading your Warehouse Builder software, migrating your design metadata, and migrating your runtime system.

The objective of this migration process is not to scrap off your old system and re-build a new system using the latest OWB release. Rather, the objective is to smoothly move your existing system from OWB 10gR1 to OWB 10gR2 in order to give you the benefits of new capabilities as well as save on your investments and your time.

Why migrate to OWB 10gR2?

You may not be sure whether it is really worthwhile to disturb your existing steady system and migrate to a new infrastructure or you may have already decided to migrate to the all new and exciting world of the latest release of Warehouse Builder.

Oracle Warehouse Builder 10gR2 is a major release packed with a set of rich features and capabilities that enables you to deliver quality information at the right time and helps you process this information to derive knowledge. Some of the new features are:

	Simplified architecture and the new look and feel: The set up and installation procedures have been simplified. There is a single unified repository to manage both runtime and design metadata. Even your metadata, whether runtime or design related can now be viewed from a single unified browser. New look and feel includes a modern Java UI and productivity enhancements such as zoom, bird's eye view, auto layout, and dockable panels.
	Data profiling: Warehouse Builder now offers a complete data profiling and correction solution. With data profiling, you can discover and measure defects in your data prior to and during the process of creating your data warehouse or BI application. You can then automatically derive business rules and mappings to clean data, derive quality indices such as Six Sigma, and use auditors to continuously monitor data quality.
	Relational and dimensional data object designer: Warehouse Builder introduces a new Data Object Editor to create, edit, and configure relational and dimensional data objects. This allows the schema designer to use a single interface regardless of whether the design asks for specialized dimensional objects or the latest partitioning features on tables.
	Complete OLAP 10g integration: With the 10gR2 release, Warehouse Builder extends the support for Oracle OLAP into modeling and direct maintenance using the new OLAP features such as compressed cubes and partitioning. Warehouse Builder now allows you to directly load any source data into the OLAP option, without requiring a temporary staging area for relational data.
	Transportable modules: This feature enables you to extract large amounts of data from remote Oracle databases while enjoying the highest performance possible. OWB’s Transportable Module feature uses Transportable Tablespace in 9i and 10g or Datapump in 10g only.
	Pluggable mappings: The pluggable mapping is a reusable grouping of operators that has the advantages of a single operator. You can publish pluggable mappings to a library so that other designers can use them, or keep them private.
	Business Intelligence Objects Derivation: Warehouse Builder enables you to derive and define Business Intelligence (BI) objects that integrate seamlessly with Oracle's Business Intelligence tools such as Discoverer and Business Intelligence (BI) Beans.
	Experts: Experts are solutions that enable advanced users to design solutions that simplify routine or complex tasks that end users perform in Warehouse Builder. You can design these solutions in Expert Editor, which resembles the Process Flow Editor and shares many of the same commands and navigational tools.
	User-defined objects and icons: Warehouse Builder now offers complete support for user-defined types including objects, varrays, and nested tables. This enables you to use more elaborate data storage and transaction formats such as those used to support real-time data warehousing.

With numerous new functionalities and improvements focusing on enabling quality information to derive high-end knowledge at the right time, you must upgrade to OWB 10gR2.

Back to Topic List

Scenario

In a real migration scenario, before you begin migrating OWB stuff, you may choose to be with the current compatible version of the database or choose to upgrade to a higher compatible version of the database. You can migrate your Oracle environment to a different instance of Oracle Database, or you can continue on the same instance.

If you intend to upgrade to Oracle Database 10g while maintaining the same database instance, refer to the Oracle Database 10g Upgrade Guide for instructions.

If you intend to create a new database instance, you must take the additional steps of either migrating a complete database or selectively migrating portions of a database. To migrate a complete database, refer to the Oracle Database 10g Upgrade Guide for instructions. It is recommended that you avoid the option of selectively migrating portions of a database because this will involve complex upgrading steps such as precreating tablespaces to exactly match existing tablespaces and also creating matching schema names, and so on.

It is best either to use the same instance (provided the database version is compatible with OWB 10gR2) or to migrate the complete database to a new instance of Oracle Database.

This tutorial covers a scenario in which you will migrate an existing project named OWB_DEMO built using OWB 10gR1(10.1.0.4), to OWB 10gR2. To keep it simple, the migration will be in the same instance of Oracle Database 10gR2. The existing project will consist of four dimensions: channel, geography, product, and time, and a sales cube. Each dimension will derive its data from flat files through external tables.

The migration process will involve moving the design metadata as well as the runtime metadata from existing repositories into a single unified repository supported by OWB 10gR2.

Before you can begin migrating, you need to set up your existing OWB 10gR1 system and also set up the new Warehouse Builder 10gR2 infrastructure. This tutorial will focus on the steps to prepare for the migration process.

Back to Topic List

Prerequisites

Before starting this tutorial, you should:

Back to Topic List

Setting Up the OWB 10gR1 Project

In this topic, you set up the OWB 10gR1 project that you will migrate to OWB 10gR2. To set up the project, you need to perform the following two steps:

	Import the OWB_DEMO10104.mdl
	Deploy the objects

Back to Topic List

To import OWB_DEMO10104.mdl, perform the following steps:

1.	Select Start > Programs > {your Oracle - OWB10gR1clientHome} > Warehouse Builder > OWB Client. In the Warehouse Builder Logon window, enter design_client/design_client as the username/password. Click Connection Info to specify the connection details. In the Connect Information dialog box, specify the following details: Host Name: localhost or <machine name> Port Number: 1521 Oracle Service Name: orcl10g or <database service name you are using> Click OK. Click Logon. The design client displays.
2.	In the Project menu, select Metadata Import > File. The Metadata Import Utility window displays.
3.	In the Metadata Import Utility window, click Browse to select the mdl file. Select the OWB_DEMO10104.mdl file from c:\owb_demo_setup folder or the path where you downloaded and extracted the owbdemo_files.zip file. Click Import. In the Metadata Import Results window, click View Log File to examine the import results. In the Log File window, ensure there are no objects reported as replaced, skipped, or deleted.   Click OK. Click Close.
4.	Expand OWB_DEMO > Databases > Oracle > TGT. Expand Mappings. Expand Dimensions. Expand Cubes. It should show objects as shown in the screenshot.
5.	Expand External Tables. Expand Locations > LOC_TGT. Expand Files > SRC_FILES and also expand Locations under the Files node, as shown in the screenshot. Collapse the nodes if you want.
6.	Right-click SRC_FILES and select Properties from the menu. In the Module Properties window, select Connection tab. Verify that the directory is set to c:\owb_demo_setup\sourcefiles. If you extracted the files in a different path, edit the Directory field to specify the path to the source files. Note: If you have extracted the files in a different path and you click Browse to select a new location, Warehouse Builder issues a warning as shown in the following screenshot. Click OK and the Flat File Directory Chooser window displays. Here you can select the location of the source files. You may also get an invalid directory error if c:\owb_demo_setup\sourcefiles is not existing or valid, as shown in the screenshot. Click OK and edit the Directory field to specify the path to the source files. In the Module Properties window, click OK.
7.	Expand Locations (if not already expanded) under the Oracle node. Right-click LOC_TGT and select Properties from the menu. Click the Details tab in the Location Properties window. Verify that the Database version is set to the version that you are using. This mdl was tested with Oracle 10gR2 (10.2), and, therefore, it will show 10.2. Click OK.
8.	Expand Runtime Repository Connections. Right-click OWB_RT and select Properties. In the Runtime Repository Connection Properties window, click Details tab and verify the connection details. Note: If you created the runtime repository owner and the runtime access user with the same name as specified in the Prerequisites section and if you are using orcl10g as the service name, then you will not need to modify any of the details. If there is a change in any of the parameter names, modify the details as required. Click OK. From the Project menu, select Commit. In the Commit Confirmation window, click Yes to save the changes. Now, you will deploy all the imported objects.

Back to Topic

To deploy the objects, perform the following steps:

1.	From the Project menu, select Deployment Manager. In the Select Runtime Repository Connection window, select OWB_RT and click OK. In the Connection information window, enter rta_101 as the runtime access username password. Click OK. The OWB Deployment Manager displays.
2.	The deployment tree is initially collapsed into a list of Locations and Collections that can be expanded to display their contents. On selecting LOC_TGT, in the Details tab, all the objects are shown as Not Deployed.
3.	Before you begin deployment, you need to register all the locations. To register the File location, LOC_SRC_FILES, right-click LOC_SRC_FILES and select Register. In the Location Registration dialog box, enter the following details: User Name/Password: Enter the username and password you use to connect to the operating system. Hostname: localhost or machine name where your flat files are saved Root Path: c:\owb_demo_setup\sourcefiles or specify the path where the source text files are saved Click OK.
4.	Similarly, right-click LOC_TGT and select Register. In the Location Registration dialog box, enter the following details: Schema Name/Password: owbtgt/owbtgt or enter the username/password of the target schema where all the objects will be deployed Service Name : orcl10g or service name of the database instance you are using Host Name: localhost or machine name Click OK.
5.	Similarly, register the Workflow location, PROC_LOC. Right-click PROC_LOC and select Register. In the Location Registration dialog box, enter the following details: Schema Name/Password: owf_mgr/owf_mgr Service Name : orcl10g or service name of the database instance you are using Host Name: localhost or machine name Click OK.
6.	Before you select all the objects for deployment, you must deploy the connector that connects the file location and the target location. Expand LOC_TGT > Connectors and select LOC_TGT_LOC_SRC_FILES. In the Details tab, click Default Action. The Deploy Action changes to Create. Click the Generate/Deploy icon (highlighted by a red rectangle in the screenshot) to start deploying. Note: You may need to Commit your changes before deployment can proceed. In the Pre Deployment Generation Results window, if validation and generation is successful, click Deploy. In the Deployment Results window, click OK. On successful deployment of the connector, move on with the deployment of other objects.
6.	Expand LOC_TGT > TGT and select TGT. In the Details tab, click Default Action to set the deploy action for all the objects. The Deploy Action changes to Create. Click the Generate/Deploy icon, as highlighted in the screenshot. Note: You may need to Commit your changes before deployment can proceed.
7.	You get the pre-deployment generation results, as shown in the screenshot. Click Deploy to deploy the objects in the target schema. Note: You may ignore the warnings, if you get any.
8.	The Deployment Results are successful. Click OK. The following screenshot shows the status of all the objects deployed. Note: All the dimensions and the cube are now existing in the target schema, but they are not populated with data. To populate the dimensions and the cube with transformed data from the source flat files, you need to either individually execute the mappings or execute a process flow that sequentially executes all the mappings to load the data. In the next step, you will deploy the process flow, LOAD_WH.
9.	Expand PROC_LOC > PROC_MOD > PFP and select LOAD_WH. In the Details tab, click Default Action. The Deploy Action changes to Create. Click the Generate/Deploy icon to start deploying. If validation and generation is successful, click Deploy in the Pre Deployment Generation Results window. The deployment is successful as shown in the screenshot. Click OK.
10.	You will execute the process flow to upload data into the dimensions and cube. To execute the process flow, select LOAD_WH and click the Execute icon, as highlighted in the screenshot. On completion, the Execution Results for LOAD_WH window displays. Examine the Row Activity section in the window. You will know how many records were inserted into each dimensions and the cube. For example, there were 25504 number of rows inserted in the SALES cube. Note: In a normal migration scenario, the user will have already executed the process flows and so prior to migration, data will already exist in the target dimensions or cubes. After migration, you will expect that you need not re-execute the mappings or a process flow to reload the same data. In the migration process that you will perform in forthcoming lessons, both design and runtime metadata will be migrated and there will not be any need to re-deploy the mappings or process flows. Because you have uploaded the data prior to migration by executing the process flow, you will not even need to re-execute the process flow to upload the same data after migration. This proves that the migration process does deal with user's data but only the metadata. From the File Menu, select Exit to exit the Deployment Manager. You may exit the design client for now. From Project menu, select Exit to exit the design client. In Exit Confirmation dialog box, click Yes to commit the changes.

Back to Topic

Back to Topic List

Creating the Unified Repository Using the Repository Assistant

Using the Repository Assistant, you can define an Oracle database as a Warehouse Builder repository. In the OWB 10gR2 release, the architecture is much simplified with a feature of having a single, unified repository for both design and runtime metadata. In OWB 10gR1, you had to compulsorily deal with two separate schemas for design and runtime metadata and multiple users such as runtime repository owner, runtime access users, design client users, and target users.

In OWB 10gR2, you can have a single, unified repository. The Repository Assistant guides you through defining a unified repository with one repository owner and a separate repository user enabled as a deployment target. In other words, the repository user can access both the Design Center, the user interface for designing ETL processes, and the Control Center, the user interface for deploying and auditing.

If you still prefer split repositories for design and runtime metadata, you can use the Advanced setup option of the Repository Assistant.

Using the Advanced setup option in the Repository Assistant, you can also define multiple users who can access the repository or also implement a remote target schema.

In this topic, you create a unified repository using the Repository Assistant.

Back to Topic List

Back to Topic List

Examining the New Design Center

The Design Center provides the graphical interface for defining sources and designing targets and ETL processes. Also in the Design Center is the Control Center Manager (in 10gR1, Deployment Manager) from which you can deploy and execute ETL processes.

You will now examine the new look and feel of the Warehouse Builder Design Center. First, you need to log in to the Design Center.

To start the OWB Design Center, perform the following steps:

Back to Topic List

1.	Select Start > Programs > [Oracle - OWBclientHome] > Warehouse Builder > Design Center. The Design Center Logon window displays. Enter rep_owner as username and password. If not showing the connection details, click Show Details. Select the Connection details option, if not selected already, and specify the connection details. Enter host as localhost or <name of your computer>, port as 1521, and service name as orcl10g or <your database service name>. Click OK.
2.	The Design Center displays. Note: The Design Center is the main client application of Warehouse Builder. It provides easy-to-use graphical interfaces that enables you to design, deploy, create, and monitor business intelligence systems. The Design Center is divided into three new panels: Project Explorer, Connection Explorer, and Global Explorer. Project Explorer: Warehouse Builder enables you to organize objects into projects within a navigation tree known as the Project Explorer. You may be familiar with this organization, except there are new objects added and few objects have moved to a different panel or down the navigation tree. The Project Explorer on the left contains one project: MY_PROJECT, an empty project created when OWB is installed. Connection Explorer: The Connection Explorer allows you to manage locations, connections, and control centers from one central location. You may be familiar with locations and connectors. The control centers are an equivalent of runtime repository connections you created in OWB 10gR1. Global Explorer: The Global Explorer provides an additional layer of management to users with administrative privileges. From this panel, a repository owner can manage public transformations, public Experts, public user-defined modules, and configure security policies for different database users. A repository user does not by default have access to the Security node in this panel and only a repository owner has the privileges to manage users and roles.
3.	Click (+) to expand MY_PROJECT. Click (+) to expand Database. The following screenshot compares OWB 10gR1 navigation tree with OWB 10gR2, highlighting (using red rectangles) the new nodes in the new Design center. Note: The screenshot is only for your preview. If you want to compare the OWB 10gR1 navigation tree with OWB 10gR2 yourself, select Start > Programs > {your Oracle - OWB10gR1clientHome} > Warehouse Builder > OWB Client. In the Warehouse Builder Logon window, enter design_client/design_client as the username/password. Now you have both OWB 10gR1 and OWB 10gR2 client application open for comparison.
4.	The most important change for you to learn about, before you start with the migration process, is Configurations and Control centers. Expand Configurations in Project Explorer panel as shown in the screenshot. Each project comes with a default configuration (DEFAULT_CONFIGURATION) that is assigned a default control center (DEFAULT_CONTROL_CENTER). The DEFAULT_CONTROL_CENTER is associated with the unified repository you create, for example, rep_owner is the repository you created in the previous topic. You may create a new control center pointing to a different repository and assign it as an active control center for DEFAULT_CONFIGURATION or a new configuration. You can define multiple named configurations for the same set of objects and use this if you are deploying the same system into multiple environments such as test and production. If you define multiple configurations, you assign one as the project’s active configuration, which is shown in the status bar. Each configuration has an associated control center taking care of deploying to the correct environment.
5.	In the Connection Explorer panel, you can manage all types of locations, connectors, and control centers. You can create a new control center pointing to a different repository and containing references to a collection of relevant locations and connectors. The DEFAULT_CONTROL_CENTER accesses the unified repository by default. Note: You will use the Control Center to assist you in the deployment and execution process. The Control Center provides a complete deployment platform as well as intelligent components that allow you to manage the deployment and future upgrades and execute mappings and process flows immediately. The Control Center also enables you to view data about previously deployed objects to make decisions about upgrading the system.
6.	If you are a highly privileged user with administrative rights such as the repository owner, you will have access to the Global Explorer panel. Through Global Explorer panel, you can manage users, set security policies for the users and also manage roles, as shown in the screenshot. After having got the initial preview to the new Warehouse Builder design environment, you are ready to get your old warehouse project moved to the new enhanced OWB 10gR2 environment. From the Design menu, select Exit to close the Design Center.

Back to Topic List

Summary

In this tutorial, you learned how to:

	Set up the OWB 10gR1 project
	Create a Unified Repository using the Repository Assistant
	Log in to the new Design Center

Back to Topic List

Place the cursor over this icon to hide all screenshots.