Improved User Interface, Usability, and Productivity With OWB 11g Release 2

After logging in, you explore the improved graphical user interface of this release. This tutorial also shows you some of the new features that enhance usability and productivity . These features make large projects more manageable.

Approximately 2 hours

Topics

This tutorial covers the following topics:

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 some of the new or improved features included in Oracle Warehouse Builder 11g Release 2 to enhance the user interface, usability, and productivity. As your developers design more and more complex mappings with huge tables and different types of objects, it becomes important for a tool like OWB to make things more manageable and productive. You will explore the new GUI enhancements and added new functionality that make huge projects easier to manage.

Some of the new features or enhancements discussed in this tutorial are:

Easier navigation and usage of space in the mapping editor with grouping, ungrouping and spotlighting options
Easier copying of attributes, operators and operator groups across mappings
New Automapper that enables quicker mapping of source and target attributes
New subquery filter operator and improved key lookup operator
New configuration templates and improved UI for better use of multi-configuration

Back to Topic List

Before starting this tutorial, you should:

1.

Have completed the tutorial, Setting Up the Oracle Warehouse Builder 11g Release 2 Tutorial Environment

The Setting Up the Oracle Warehouse Builder 11g Release 2 Environment tutorial provides the setup files to configure and setup an environment for this tutorial.

Back to Topic List

Warehouse Builder 11g R2 Architecture and Components

Oracle Warehouse Builder is an information integration tool that leverages the Oracle Database to transform data into high-quality information. The Oracle Database is a central component in the Warehouse Builder architecture because the Database hosts the Warehouse Builder repository and the code generated by Warehouse Builder.

The diagram below illustrates the interaction of the major components of the Warehouse Builder software.

The Design Center is the user interface for designing, managing, scheduling, and deploying ETL processes for moving and transforming data. All metadata associated with the work done in the Design Center is stored in the Oracle Warehouse Builder Repository. The repository is hosted on an Oracle Database and you can use the Repository Browser to report on the metadata in the repository. Also hosted on an Oracle Database is the Target Schema to which Warehouse Builder loads data resulting from the ETL processes that you execute through the Control Center Service.

Design Center

The Design Center provides the graphical interface for defining sources and designing targets and ETL processes.

Control Center Manager

Also in the Design Center client is the Control Center Manager from which you can deploy and execute ETL processes. The Control Center Manager is a comprehensive deployment console that enables you to view and manage all aspects of deployment.

Target Schema

The target schema is the target to which you load your data and the data objects that you designed in the Design Center such as cubes, dimensions, views, and mappings. The target schema contains Warehouse Builder components such as synonyms that enable the ETL mappings to access the audit/service packages in the repository. The repository stores all information pertaining to the target schema such as execution and deployment information.

Notice that the target schema is not a Warehouse Builder software component but rather an existing component of the Oracle Database. As such, you can associate multiple target schemas with a single Warehouse Builder repository. You can have a 1 to 1 relationship or many target schemas to a single repository.

Warehouse Builder Repository

The repository schema stores metadata definitions for all the sources, targets, and ETL processes that constitute your design metadata. In addition to containing design metadata, a repository can also contains the runtime data generated by the Control Center Manager and Control Center Service.

As part of the initial installation of Warehouse Builder, you use the Repository Assistant to define the repository in an Oracle Database.

About Workspaces

In defining the repository, you create one or more workspaces, with each workspace corresponding to a set of users working on related projects. A common practice is to create separate workspaces for development, testing, and production. Using this practice, you can allow users such as your developers access to the development and testing workspaces but restrict them from the production workspace.

Later in the implementation cycle, you also use the Repository Assistant to manage existing workspaces or create new ones.

Repository Browser

The Repository Browser is a web browser interface for reporting on the repository. You can view the metadata, create reports, audit runtime operations and perform lineage and impact analysis. The Repository Browser is organized such that you can browse design-specific and control center-specific information.

Control Center Service

The Control Center Service is the component that enables you to register locations. It also enables deployment and execution of the ETL logic you design in the Design Center such as mappings and process flows.

Reference Material

The following is a list of useful reference materials if you want additional information about the topics in this module:

Oracle Warehouse Builder 11g Release 2: Documentation Library
OWB on Oracle Technology Network (OTN)

Oracle University (5 day Instructor Led course):

Visit the Oracle University website to enroll for this course.

Back to Topic List

Logging In to the Design Center

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

1.

Double-click the OWB Design Center desktop launcher to open the Design Center.

Note: If you did not create the desktop launcher, browse to [ORACLE_HOME]/owb/bin/. Double-click and run owb.sh.

The Design Center Logon window opens. Enter eup_owner as username and password. If you do not see Connection details in the Design Center Logon window, click Show Details.

Check that the host is defined as localhost or <name of your computer>, port as 1521, and service name as <your database service name>. Click OK.

 

2.

The Design Center opens.

The Projects Navigator panel contains two projects: MY_PROJECT, an empty project created when OWB is installed, and BIDEMO_112, a project that you imported in the "Setting Up the Oracle Warehouse Builder 11g Release 2 Environment" tutorial.



Back to Topic List

Examining the Improved Graphical User Interface

To explore the improved graphical user interface, perform the following steps:

1.

The first time you log in to the Design Center, a new Start Page is displayed. This new orientation page is intended to help you find information about the tasks you want to perform.

Examine the Start Page. Click each of the links to examine the resources available. You can return to the previous page by clicking the green left-arrow in the upper left corner of each page.

Note: The Start Page opens automatically only the first time that you log in. To view the Start Page after the first time you log in to OWB, select Start Page from the Help menu.

2.

The Help menu includes several new resources in addition to the traditional help system.

Examine the choices, and click several of them to explore the resources available to you.

3.

The Navigator Panel is now organized into three tabbed panels: Projects Navigator, Locations Navigator, and Globals Navigator.

  • The Projects Navigator has the familiar navigational tree with objects in each project organized by expandable nodes.
  • The Locations Navigator provides convenient access to your location and Control Center definitions.
  • The Globals Navigator organizes objects that can be publicly shared, such as transformations, experts, Knowledge Modules, and data rules.

Click each of the 3 tabbed panels and examine the content. Expand some of the nodes on the navigation trees to see how some of the Warehouse Builder metadata has been reorganized.

4.

In the Projects Navigator, expand BI_DEMO112. Notice the first node under BI_DEMO112 is named Template Mappings. This is where the new Code Template (CT) based mappings are created for connecting to non-Oracle, heterogeneous sources and targets.

Expand Databases -> Oracle -> SALES module. Notice that the first node under SALES is Mappings. These mapping nodes located directly under modules are where the "classic", non-CT, ETL mappings are created.

5.

Expand SALES -> Mappings and single-click TIMES_MAP. From the View menu, select Structure. A Structure Panel opens directly below the Projects Navigator panel, with a context-sensitive entry. In this new Structure Panel, expand TIMES_MAP.

This new Structure Panel allows you to see much of the structure of an object, in this case objects in the TIMES_MAP mapping, without needing to open up the mapping editor for that object.

6.

With the TIMES_MAP mapping still selected in the Projects and Structure panels, open the View menu and select Property Inspector. A new panel, the Property Inspector opens.

Scroll through the Property Inspector and examine the information that it provides for the TIMES_MAP mapping. The new Property Inspector panel provides the convenience of displaying all of an object's properties for inspection in a single easily accessed panel on the OWB desktop.

Back to Topic List

Using the Advanced Search

Let us further examine the Advanced Search capabilities. You can search for objects in the mapping canvas, the available objects tree, or the selected objects tree. Search is performed in the context of the panel which is in focus when the dialog is invoked.

1.

In the Projects Navigator, select the project BI_DEMO112 at the top of the navigator.

 

2.

In the main menu bar, from the Search menu, select Find. In the Find dialog, click Show Advanced to view the advanced search options. The Find dialog box opens as shown in the screenshot below:

 

3.

Suppose you want to search for all the objects with physical name containing %CHAN%. You want to search across the whole project.

Ensure the BI_DEMO112 is in focus before you invoked the Find dialog. In the Find dialog, in the Find field, enter %CHAN%.

You can find objects by their display name, physical name, business name, or the description. In the Find By drop down list, select Physical Name.

Examine the other find options.

 

4.

Click Find. Notice it will highlight the first object with physical name containing the string 'CHAN'.

If this is not the object you were searching for, keep clicking find until you find the desired object or until the search ends.

Suppose you were looking for the CHANNELS_TAB table, click Find until you find the object.

Once you have found the object, you can close the Find dialog by clicking Close. Similarly, you can search for objects in Locations panel, in the Globals panel or within the Mapping Editor panel.

Back to Topic List

Enhancing Usability in the Mapping Editor

In this topic, you use some of the enhancements added in the mapping editor that can help better manage objects and space in the mapping editor.

The grouping, ungrouping, and spotlighting features enable you to manage the real estate on the mapping editor canvas productively. The new Automapper enables you to map at icon level and then use the Automapper UI to map attributes efficiently. The best thing is that the Automapper is fully keyboard-driven. You also get hands-on the easier way of copying attributes and editing the expression inline in an Expression operator.

In this topic you perform the following:

Using the New Automapper
Copying Attributes and Inline Editing in Expression Operator

Back to Topic List

Grouping, Ungrouping, and Spotlighting

1.

Assuming the BIDEMO_112 project is expanded, expand Template Mappings > SALES_MAPS. Right-click LOAD_SALES and select Open.

The mapping, LOAD_SALES, opens in the Mapping Editor. It opens in a small panel that shows little of the mapping. Expand the Mapping Editor panel by double-clicking the panel's blue title bar. (Or, right-click the Mapping Editor panel's title bar and select Maximize.)

If the mapping is not fully visible, click the Auto Layout button followed by the Fit in Window button. The mapping should appear as shown in the screenshot below.

 

2.

During the mapping design process you realize that you are done with the source tables part of the mapping and you need not work on it further, until you finish the remaining part of the mapping. You can group all the source tables.

To group all the five source tables on the left, click on the blank area on the canvas and drag a rectangle over all the five tables. This highlights all the tables with a blue border indicating that they are selected. Observe on the toolbar, the Group Selected Objects icon gets enabled, as highlighted by a red rectangle in the screenshot below.

Click Group Selected Objects.

 

3.

After you click the Group Selected Objects icon, all the five tables collapse into a Folder1 group. Click the Zoom In icon if the folder is not clearly visible.

 

4.

If for some reason you need to work on the source tables in the mapping, you can ungroup the tables by clicking the Ungroup Selected Folders icon.

To ungroup, make sure Folder1 is selected or highlighted. Click UnGroup Selected Folders icon.

Notice that all the five source tables are now visible on the mapping editor canvas.

 

5.

Click Auto Layout and Fit in Window to see the full mapping. Suppose you want to focus on the JOINER and the Expression Operators and hide the remaining operators on the canvas, you can use spotlighting.

Hold the Shift Key and then click both the JOINER and the TO_NUM_EXPR operators to highlight them. Click the Spotlight Selected Objects icon.

All the objects are now hidden and only the two operators you wanted to work on are visible, as shown in the screenshot below.

To revert to the mapping, click the Spotlight Selected Objects icon again. When done, double-click the Mapping Editor panel's tab to get back to the original views. Close the mapping by clicking on the x on the tab.

 

 

Back to Topic

Using the New Automapper

1.

You will now create a new mapping, LOAD_ORDERS. In this mapping you will use the new Automapper. Expand Databases > Oracle > Sales > Mappings. Right-click Mappings and select New Mapping.

In the Create Mapping dialog box, in the Name field enter LOAD_ORDERS. Click OK.

The Mapping Editor opens as a very small panel. To make more room for this editor, minimize the Projects, Structures, and Messages Logs panels.

 

2.

Scroll down the Component Palette, select Table Operator, and drag it to the canvas. In the Add Table Operator dialog box, select Select from existing repository objects and bind. From the SS_XWEEK module, select ORDERS. Click OK.


3.

Similarly, add the ORDER_ITEMS table operator from the SS_XWEEK module into the mapping. From the component palette, drag a JOINER operator on the canvas.

 

4.

From the ORDERS table operator, drag a line from INOUTGRP1 to INGRP1 of the JOINER operator. Similarly, from the ORDER_ITEMS table operator, drag a line from INOUTGRP1 to INGRP2 of the JOINER operator.

If you have experience with OWB, you must have mapped groups of attributes in the manner you just performed. The OUTGRP1 automatically gets populated with all the attributes from both INGRP1 and INGRP2.

Minimize the ORDERS, ORDER_ITEMS and the JOINER operators. You may click Autolayout to arrange the icons on the Mapping Editor canvas.

 

5.

Now, you create a target table, NEW_ORDERS_TGT.

If you minimized the Projects panel in Step 1, restore it now. Then expand Databases > Oracle > Sales. In the SALES module, right-click Tables and select New Table.

In Create Table dialog box, in the Name field enter NEW_ORDERS_TGT. Click OK.

 

6.

A new panel opens for creating the table. Maximize this panel. Notice the multiple tabs at the bottom of the panel. Click Columns tab. Add the following columns with the following data types as shown in the screenshot.

On the toolbar, click the Save All icon. In the Save Confirmation dialog box, click Yes.

 

7.

Switch back to the LOAD_ORDERS panel. In the Projects Navigator panel, drag the NEW_ORDERS_TGT table from the SALES > Tables node to the Mapping Editor canvas.

Minimize the Joiner operator and the NEW_ORDERS_TGT table operators

8.

Drag a mapping line from the Joiner to the NEW_ORDERS_TGT operator. Note this is not mapping between two groups such as INOUTGRP1 or INGRP1. You are mapping at the icon level.

In this case, an Automapper dialog box is displayed.

 

9.

You want to map from the OUTGRP1 of the JOINER to the INOUTGRP1 of the NEW_ORDERS_TGT table. This is already set.

Note Source Connections tab is selected. If you want to map the target attributes to source attributes, you can do so here. Inversely, if you want to map source attributes to the target attributes, you would select Target Connections tab.

 

10.

Click Target Connections tab. You map the source attributes to the target attributes. The Automapper is fully keyboard-driven. You can enter * to get a list of all the target attributes and then select the target attribute that maps to the source attribute.

You can get the list of attributes based on the first letter of the target attribute also.

 

11.

You can press the arrow key to move from one attribute to another and then enter * or the first letter of the attribute to map the attribute.

The Automapper hence enables you to easily find the attributes in the target and map them using the keyboard. Click OK once you are done with the mapping.

Review the mapping.

Back to Topic

Copying Attributes and Inline Editing in Expression Operator

Warehouse Builder supports copy-and-paste reuse of existing transformation logic, defined using operators or operator attributes, in other mappings or pluggable mappings.
You can reuse transformation logic by copying the operator or operator attributes from the source mapping and pasting them into the required mapping. You can also copy and paste operator groups (input, output, and input/output).

In this topic, you create the output attribute of the Expression operator by copy-pasting the attribute from the target.

1.

In the LOAD_ORDERS mapping, from the Components Palette, drag an Expression Operator. Expand the Joiner and the New_ORDERS_TGT table.

Drag a mapping line from DATE_MODIFIED attribute to INGRP1 of the Expression operator. The DATE_MODIFIED attribute gets added.

 

2.

Most the times you want the output attribute of the expression operator to be of the same data type as the target attribute.

For this in previous versions you had to first check the target attributes data type and then create an output attribute in the Expression operator. With this release, you can simply copy paste the target attribute into the Expression OUTGRP1.

In NEW_ORDERS_TGT operator, right-click DATE_MODIFIED and select Copy.

 

3.

Right-click on the OUTGRP1 of the Expression Operator and click Paste.

The DATE_MODIFIED output attribute takes the characteristics of the target attribute. Connect the two attributes by dragging a mapping line.

 

4.

You can also edit the expression for an attribute easily. Double-click DATE_MODIFIED attribute in the OUTGRP1 of Expression operator. The Expression Editor opens.

You can perform inline editing in the field under the Expression header or click on ... (highlighted by a red rectangle in the screenshot below) to start the Expression Builder.

 

5.

In the Expression Builder, you can enter the expression. For example, enter to_date(DATE_MODIFIED). Click OK.

 

6.

Note the expression gets added in the Expression field of the Expression editor. You can also edit the expression inline. Click OK.

Close the LOAD_ORDERS mapping.

Back to Topic

Back to Topic List

Using the New and Improved Operators in the Mapping Editor

In this topic you learn about the new Subquery Filter operator and also get a hands-on the new enhancements of the Lookup operator.

Using the New Subquery Filter Operator
Using the Extended Lookup Operator

Back to Topic List

Using the New Subquery Filter Operator

1.

Expand the Databases > Oracle > SALES node. Right-click Mappings and select New Mapping. In the Create Mapping dialog box, enter FIND_SALES_CUSTOMERS as the name of the mapping.

Click OK. A new mapping canvas opens.

 

2.

In the Projects Navigator panel, expand SALES > Tables node. Drag CUSTOMERS_TAB table to the Mapping Editor canvas.


3.

Similarly drag SALES_TAB table to the Mapping Editor canvas.

 

4.

From the Component Palette, drag the Subquery Filter operator. Arrange the operators on the canvas. Expand the tables to show all of their attributes.

Note: You see two groups in the subquery filter operator, INGRP1 and INOUTGRP1 where the INGRP1 is marked with an (E). Map the subquery table (that is table where you are looking for a match) into INGRP1 and map the data that has the results for further mapping into INOUTGRP1. In this example, you want to know the customers you sold something to, you map the CUSTOMERS_TAB table's attributes into the INOUTGRP1 so that they can be further mapped into a target table.

 

5.

Now, you map the attributes from the source tables into the subquery filter operator. From the CUSTOMERS_TAB table, map the DIMENSION_KEY attribute to the INOUTGRP1 of the subquery filter operator.

Similarly, map CITY_NAME, COUNTRY_NAME, and ISO attributes into INOUTGRP1.

 

6.

From the SALES_TAB table, map CUSTOMERS attribute into INGRP1.

 

7.

Now, you will create the target table. Drag a table operator on the canvas. Select Create unbound operator with no attributes and enter CUSTOMERS_SALES as the operator name.

Click OK.

 

8.

Map the INOUTGRP1 of the subquery filter operator to the INOUTGRP1 of the CUSTOMERS_SALES table operator.

 

9.

Right-click CUSTOMERS_SALES table operator and select Create and Bind. This will create the new table.

In the Create and Bind dialog box, examine the fields and click OK. By default the name is the same as the operator name and the table is created in the SALES module.

 

10.

Examine the property inspector of the Subquery filter operator. Note that the Subquery Filter input role can be set to Exists, In, Not Exists, and Not In.

 

11.

To specify the subquery filter condition, click ... besides the Subquery Filter Condition property in the Property Inspector. In the Expression Builder, expand INGRP1 and the INOUTGRP1. Double-click CUSTOMERS from INGRP1. Click the = button. Then double-click DIMENSION_KEY from INOUTGRP1. You can also type in the following filter condition. Click OK.

INGRP1.CUSTOMERS = INOUTGRP1.DIMENSION_KEY

You can now generate the code for the mapping. On the toolbar, click the Generate icon () and then examine the code.

Back to Topic

Using the Extended Lookup Operator

1.

In the same mapping, suppose you want to perform a look up. From the Components Palette, drag a Lookup operator. In the Add Lookup dialog box, from the SALES module, select CUSTOMERS_TAB table. Click OK.

A lookup wizard launches. Click Next on the Welcome page.

 

2.

In the Name field, enter LKP_CUSTOMERS.


3.

The focus of this tutorial is to show you the new options that have been added. Click Next until you reach Step 6 of 8: Lookup Conditions page, as shown in the screenshot below:

Note: One of the minor enhancements is the option of Freestyle Editing. You can write any arbitrary SQL lookup condition in the Lookup Condition text box.

Click Next.

 

 

4.

Multiple Match Rows wizard page describes the new enhancement of the Lookup operator. Now, the Lookup operator can deal with multiple rows returned in various ways.

Note: You can Select one of the following options:

Error: multiple rows cause mapping to fail: Select this option to indicate that when the lookup operation for the selected output group returns more than one row, the mapping execution fails.
All Rows (number of result rows may differ from the number of input rows):

Select this option to indicate that when the Lookup operator returns multiple rows for the selected output group, all the rows should be returned as the lookup result.

Select single row: Select this option to specify that when the Lookup operator returns multiple rows for the selected output group, only one row from the returned rows must be selected as the lookup result. When you select this option, the fields contained in the section below this option are enabled. Use these fields to specify which row from the lookup result set should be selected as the lookup result. You can set it to pick up the first row, last row, any row, or nth row from the multiple rows returned.

When you are selecting the first or last row or the nth row, you must specify the appropriate ORDER BY clause to sort lookup results because the order in which the lookup results are returned becomes important.

In the screenshot below, the Select single row option with Nth row position is selected. You want the results to be ordered by COUNTRY_NAME in ascending order and then select the second row as the lookup result row.

Click Next until you reach to the end of the wizard and then click Finish. Close the FIND_SALES_CUSTOMERS mapping.

Back to Topic

Back to Topic List

Enhancing the Usability of Multi-Configurations

In this topic, you create a new configuration template and associate it with a new configuration. You learn how configuration templates are a way of setting defaults for a set of configurations. In other words, it’s a way of overriding the seeded defaults for many object types in the repository. The templates help you establish DEFAULT values, which can easily be repeated and easily be slightly modified. The templates allow creating an initial template of default values, and these template can then be reused, and that its default values can be easily overridden.

Create a New Configuration Template
Use the Configuration Template

Back to Topic List

Create a New Configuration Template

1.

Switch to the Globals Navigator tab. Right-click Configuration Templates and select New Configuration Template.

In Create Configuration Template dialog box, enter PRD_CONFIG_TPL as the name of the template. Click OK.

 

2.

The Configuration Template editor l opens. Maximize this panel. To set the default deployment system to Oracle10gr2, in the Configuration Template editor, expand Databases > Oracle Module. Expand the Deployment System TYPE property.

For PL/SQL Generation Mode property, in the PRD_CONFIG_TPL column, from the drop down list select Oracle11gR2.


3.

To enable parallelism at a degree of 8 for all the tables, expand Table node. Expand Parallel node. Set the Parallel Access Mode property to PARALLEL and the Parallel Degree property to 8.

Similarly you can explore the other configuration options and change settings to define defaults. On the toolbar, click Save All () to save the changes. Click Yes in the Save Confirmation dialog box.

 

Back to Topic

Use the Configuration Template

1.

Now, you will associate the configuration template with a configuration. Switch to the Projects navigator panel. (If you cannot find the Projects navigator tab, select Project Navigator from the View menu.)

In the BI_DEMO112 project, right-click Configurations and select New Configuration.

The Create Configuration wizard launches. Click Next on the Welcome page.

 

2.

Enter PRD_CONFIG as the name of the configuration. Click Next.


3.

For now, create a dummy control center, PRD_CC. Click New to create a Control Center. Enter PRD_CC as the name of the Control Center. Leave the other connection details blank and click OK.

Back in the Create Configuration wizard, click Next. Accept the default runtime agent as 'Unspecified'. Click Next.

 

4.

In this step you associate the configuration template with the configuration. From the drop down list, select PRD_CONFIG_TPL. Click Next and then click Finish.

 

5.

Now set the new configuration, PRD_CONFIG, as the active configuration.The Configurations drop down is now on the toolbar, hence easily visible and accessible. Select PRD_CONFIG to set it as the active configuration.

Note: You can also easily create a new configuration by clicking Create New Configuration... in this drop down list. This is an enhancement to make multi-configuration more visible and accessible.

 

6.

Navigate to the Databases > Oracle > SALES node. To view the impact of the configuration template when the new configuration is the active configuration, right-click SALES module and select Configure.

Note, in the Configuration of Sales panel, the PL/SQL generation Mode is set to Oracle11gr2.

Similarly, right-click any table, for example, CHANNELS_TAB, and select Configure. In the Configuration of CHANNELS panel, note that the PARALLEL access is enabled with degree 8.

7.

If you click the Manage Configuration Columns icon() in the Configuration of CHANNELS_TAB panel, you can select multiple configurations to be displayed next to each other so you can easily compare the values and differences.

In the Select Configurations dialog box, check DEFAULT_CONFIGURATION . Click OK.

 

8.

Now, you see that in different configuration templates the table's parallel property settings are different.

This concludes the tutorial. Exit the Design Center. Save you work.

Back to Topic

Back to Topic List

In this lesson, you learned how to:

Back to Topic List

Place the cursor over this icon to hide all screenshots.