Data Warehouse (vii): Oracle Warehouse Builder (OWB) Create Data Warehouse

This article outlines the general process of using OWB to create a data warehouse. Oracle's OWB is one of the three most current ETL products. OWB can not only complete data extraction, conversion and loading, but also help users create ROLAP (relational online analysis Process) and MOLAP (multidimensional online) in Oracle database Analysis Process) Data Warehouse objects, data quality management, business intelligence definition, etc.

1 Environment Configuration

It is recommended that you install Oracle 11g directly, such as Oracle 11.2.0.1.0, which contains the OWB and dependent components directly in the installation package. If you are using Oracle 10g (such as Oracle 10.2.0.1), you will need to download the Oracle Database installation package and Oracle databases 10g Companion CD Release 2 (10.2.0.1.0). The latter contains the required Oracle Workflow Server and Oracle HTTP server for OWB.

Installing Oracle Workflow (for Oracle 10g)

Note: do not install by executing the Setup.exe file in the companion directory , but by selecting the universal installer of the Oracle 10g R2 that you have installed on your local computer, in Oracle Universal Installer: Specify the source location interface, click the "Browse" button, locate the Companion\stage\products.xml file, and then click the "Next" button to select "Oracle Database 10g Products 10.2.0.1.0 "To install these components properly. The steps are as follows:

oracle10g installed, under D:\oracle\product\10.2.0.
OWB10G2 installed, under D:\oracle\product\10.2.0\OWB.
Downloaded the Oracle Workflow 2.6.4. When installing, specify the home directory details as follows:
Name: oradb10g_home1
Path: D:\oracle\product\10.2.0\db_1
The process of installing the Oracle Workflow 2.6.4, in the Select Products to install window, selects the second item, Oracle Database 10g product 10.2.0.1.0. The installation of HTTP server is now included.

After the Oracle Workflow 2.6.4 is installed, configure the Workflow configuration Assistant.

Finally, you need to give owf_mgr "EXECUTE any PROCEDURE" permission, connect to SQL Plus with the SYS account, and give Owf_mgr the following permissions.
Grant execute any procedure to owf_mgr;

(*) to install Oracle Workflow separately, select Oracle database10g Companion Product, and select Oracle HTML DB 10.2.0.1.0 If you want to install HTTP server.

Repository Assistant Creating repositories

Before you can use OWB for ETL design and deployment, you need to create repositories and users (including repository owners and database consumers) through the Repository Assistant Wizard.

2 OWB Data Processing flow

(1) Enter "Design Center"

• New control Center
• New Item (Project)
• Define the data source module (modules)
• Define "Target data module"
• Design "Mapping (Mapping)"
• Design process flow to determine the sequencing of the mapping run
• Design "plan (Schedule)"
• Configure (Configure) mappings and workflows, add "schedules" for different mappings and workflows, and create "job" after deployment

(2) Create control center in Design center and configure it as the default control center

(3) Open "Control center Manager"

• Determine the actual physical configuration of the data source and target database, and the registration (register) "Location" lays the groundwork for deployment and execution
• Deploy a project, a location, and all the objects under the module, which do the following: DDL, DML, Control File, workflow code, job generation code, or ABAP, and so on, the generation of code on the target user's database, The deployment of the DDL statement completes the generation of the object on the target user's database, and DML, Control File, Workflow, and job require the next execution to actually complete
• Perform (execute) work to implement periodic ETL

3 Designing ETL in Design Center

Design Center is responsible for designing the work that is automatically stored in the OWB database.

3.1 Entering design Center

Starting with program->oracle-owbhome name->warehouse builder->design Center

3.2 New Item (project)

• Right-click the Project browser blank and select New
• Follow the wizard, enter the project name, such as Test_project, OK

3.3 Creating a new data source module and importing a data source

• Create a data source for an Oracle table, and on the project name-database-oracle that you just created, right-click New

• Follow the wizard to create a data source Oracle module

• Enter the module name and note Select the data source radio button

• Click on the "Edit" button to the right of "location" to enter the connection information editing interface

Data source location name is: hr_location

Username (user name): Database user name of the table connecting to the data source

Password (Password): The password of the database user who connects the tables of the data source

Host: The IP address of the database server that is the table of the data source (note that you want to match the IP in the Listener.ora in the database server .../network/admin directory)

Port: The port of the database server that contains the table that is the data source

Service Name: Services name of the database that contains the table that is the data source

Note: Version Select 10.2, if the user who is logged into design Center does not have permission to access the above user tables, then the authorization statement needs to be executed:

[SQL]View PlainCopy

1. Select ' Grant Select on User2. ' | | t.table_name| | ' to User1; '  From dba_tables t where t.owner=' user2 ';
2. ' Or assign a SELECT permission to all tables
3. 'Grant select any table to user2

• Import Table Structure

One way is after the new module, select the Import after completion check box to automatically go to the Import Wizard, or select the data source module you just created, right-click Import

• Import a table as a data source by following the Import Wizard

3.4 New "Target data module"

• Set up the target data module, "Project name-database-oracle" Right-click on "New"
• Follow the wizard to enter the module name and select the Data Warehouse destination radio button
• Click "Edit" to the right of "location", enter the connection Information edit window, enter the IP, port, username and password of the database server where the target table resides.
• The target table can be set up in the database with DDL scripts, or in the OWB "project name-database-oracle-Target data module name-table" Right-click, "New" is created through a graphical interface, or it can be established directly in the map (this way in this case).

3.5 Design "Mapping"

• "< project name >-database-oracle-< Target data module name >-mapping", right-click "New", enter the map name, "OK" after entering the mapping editor; Select the "Available Objects" tab from the "Browser" navigation form in the upper-left corner to find the table under the data source module. Drag to the right in the Mappings box
• From the all view of the palette (Palette) box, locate table operator (table), and drag to the Map box on the right, and the Add Table operator Operator dialog box pops up to select Create an unbound operator (without attributes) (Create unbound operator with no attributes) ", The implication is that there is no definition of this table in the OWB database (that is, you have not yet created the desired target table in two other ways to create the target table in 3.4), enter the name of the table
• After "OK", enter the mapping design stage, select a property in the data source JQ_CJJL table to drag and drop the target table T_JQ_CJJL "INOUTGRP1" can be, as
  
  
• "Create and bind" T_JQ_CJJL table to "Target data module", this step is to add T_JQ_CJJL to the "table" of the target data module in the OWB repository, so that the table can be deployed later, That is, the table is generated under the target user of the target database. "Right-click" T_JQ_CJJL table, select "Create and Bind"
  
  
• Save, close the map editor
• In Design center, under Project Explorer, locate the < project name >-database-oracle-< Target data module name >-table, and you can see T_JQ_CJJL so that create and bind succeeds.

3.6 Designing "process Flow"

• "< project name >-process Flow-process Flow module" right "new", enter "process Flow module" name such as WF1, click on the "location" to the right of "edit", enter Workflow Administrator's database server IP, port, user name and password, if the database is 10GR2, The workflow version is 2.6.4.
  
  
• "OK" automatically pops up the "New Process Flow Package" window, enter a name
• "OK" automatically pops up the "New Process Flow" window, enter a name. You can also right-click the process flow package to create a new process flow. Go to the Process flow editor
  
• The role of the process flow is to concatenate the mappings, determine the order in which the mappings are executed, and the mappings can be serial or parallel. For simplicity, only one mapping is added. Top left "browser" check "available objects" to locate the mapping mapping_job in the Target data module, and drag it to the "Process flow" box on the right.
• Draw lines between the "START1" icon and the "Mapping_job" icon, drawing lines between Mapping_job and end_success, such as:
  
  

3.7 Design "plan"

• Different "workflow modules" and "Target data modules" need to design different "plans", which are described below for "workflow modules" to establish a "plan"
• "< project name >-plan" right "new", enter "plan module" name like SCH_WF, click "Edit" to the right of "location", enter the IP, port, user name and password of workflow Administrator 's database server
• < project name >-Plan-< Plan module name > Right click New, follow the wizard settings
• Change a setting's schedule, < project name >-Plan-< plan module name >-< plan name > Right-click Open Editor to change previous settings
  
  

3.8 "Schedule" of the Configuration "workflow"

This step associates the workflow created in the previous two steps with the plan, that is, the workflow is executed as scheduled.

• < project name >-Workflow-< Workflow Module name >-< Workflow package name >-< Workflow Name > Right-click Configuration, such as:
  
  
• Click the space to the right of the referred calendar, select the "plan" created in 3.7 from the drop-down box sch_wf_day

At this point, we have finished designing in design center, and we have entered control center to deploy and run the design we just made and complete the ETL process.

4 Create control center in Design center and set the default control Center4.1 to create control center

• In the connection browser of Design center, control center right-click New
• Enter the control center name, edit Control center location, enter the owner of the OWB repository (note that owner owner, not the average user, the general user needs to set the scenario for which the scheme is the owner), the user name, the password, the database server IP Port
• Double-click the control center that you just created, go to the Edit Console window, select the Data Location tab, and from the left "available location" you will need to select the right "selected location" in: Data source module location, Target data module location, process flow module location, planning module location
  
  

4.2 Set the control center created as the default

• "< project name >-configuration-default_configuration" right "open editor", select "Details" tab, drop-down box select the
  
  

5 Deploying and executing ETL5.1 in Control center manager Enter Control center Manager

In Design Center, select menu Tools-Control Center Manager, enter the OWB repository owner password to enter the Control center window

5.2 Register All locations

All locations under the project are listed on the left side of the Control center window.

In the example in this article, because the actual target database is the same as the target database at design time, and we have edited all the locations when designing the ETL process in design center, we do not have to register the location in the control center manager. In practice, however, in general, the target database, data source, process flow, planned database and actual usage are different when the design is developed, so it is necessary to re-register the location when the deployment is on-line. This is a feature that OWB designed once and deployed multiple times.

5.3 Deployment of all objects under a project

• Select "< project name >" and "Object Detail" on the right to list all objects, such as tables, mappings, process flow, work (scheduled process flow or mappings configured)
• Click "Default Actions" and the "Deploy action" column for all objects will have "none" changed to "create", but there are some tables that are data source tables, without deployment, you can click " Creation (create) to None (none). Then click on the fourth small icon "deploy" in the top left corner. (For objects such as tables, external tables, materialized views, queues, and so on, the deployment represents the generation of DDL scripts, and these scripts are executed under the database user at the registered location to create the above objects.) However, these tables are already in the data source and will be an error if deployed again.
  
  
• Note See the third column "status" of control center jobs below, if it is a green checkmark, that the deployment was successful, or you can view the "Deployment Status" column for all objects above to "success", Indicates that the deployment was successful. "Warning" can be ignored first.

Common Errors :

(1) RPE-02072: Oracle Workflow NLS language ZHS is not enabled in the Oracle Workflow repository. Use the Oracle Workflow wfnlena.sql server-side script to enable the language.

[SQL]View PlainCopy

1. C:\oracle\product\10.2.0\db_1\wf\admin\sql>sqlplus owf_mgr/owf_mgr @wfnlena. SQL ZHS Y

Note the path.

(2) Rpe-02260:database user owf_mgr must be a Control Center user. OWB Design Client against the control Center repository to grant the control center User role.

You need to grant Oracle Workflow (OWF) users owf_mgr specific roles that have permission to perform process flow in Control Center.
You do not have to embed the Control Center password in a database link owned by the OWF user. Control Center users have high privileges and their passwords are tightly controlled.
To register OWF users, you can use the Register Warehouse Builder Users Wizard from the Security > Users node in the Global Explorer panel. To access the Security node, be sure to log in to Design Center as the repository owner.
1)
If you are not logged in, sign in to Design Center with your username/password Rep_owner/rep_owner.
Expand the Security node in the Global Explorer panel. Right-click the Users node and select New.
Note: Before you create a new user, you must first save or restore your changes. To save your previous changes, choose Save All from the Design menu, or click on the toolbar.
The Create User dialog box appears.

2)
In the Create User dialog box, you can select from the list of available database users, or you can create a new user who will be automatically registered as a Warehouse Builder user.

In the previous sections of this tutorial, we explained how to install Oracle Workflow Server, how to use the Oracle Workflow Configuration Assistant, and how to designate Owf_mgr as a workflow account. The result is that Owf_mgr is created as a database user. Select Owf_mgr from the Available DB Users list, and click > to move it to the Selected users list. Click OK.

Note that the owf_mgr user has been added to the Security > Users node of the Global Explorer panel.

5.4 Performing "Jobs"

• If "Deploy" succeeds, you can select "Scheduled (scheduled)" At the bottom right, select Wf_job1_job, right-click "Start"
• This "job" will start running at the specified run time of the schedule

5.5 Check that the results are correct

Look at the target table T_JQ_CJJL under target user, whether the data mapped from the Data source table is logged.

Recommended reading:

Highly recommended: http://pan.baidu.com/share/link?shareid=513304&uk=1476405502
Relatively simple: http://download.csdn.net/detail/kingzone_2008/5492245
Official Document: http://pan.baidu.com/share/link?shareid=513296&uk=1476405502

Resources:

Oracle Corporation 2006.Oracle BI Warehouse Builder 10g Release 2 self-paced tutorial

http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/229051_zhs.htm

Http://doc.chinaunix.net/oracle/200802/159388_7.shtml

Data Warehouse (vii): Oracle Warehouse Builder (OWB) Create Data Warehouse