Warehousing for Analysis and Reporting

Getting started with Oracle Warehouse Builder

Oracle Warehouse Builder offers a set of graphical user interfaces allowing you to implement a data store, either relational or dimensional, integrating and consolidating data from a variety of data sources. During building such a data store, also called a warehouse, you create a set of metadata objects within a workspace hosted on an Oracle database.

Installing Oracle Warehouse Builder:
The following steps assume that you have downloaded Oracle Warehouse Builder 11g Standalone Software and are installing it to be used with an existing installation of an Oracle database.

Launch Oracle Universal Installer, which will check your system and then display the Welcome screen.
On the Welcome screen of the Installer click Next to move on to the Specify Home Details screen.
On the Specify Home Details screen, specify a name for the installation and the full path where you want to install the product. Then, click Next to continue.
On the next screen of the Installer, you can specify the e-mail address that will be used to inform you about security issues. Then, click Next to continue.
After analyzing dependencies, the Installer should display the Summary screen, which might look like the following screenshot:

On the Summary screen, click the Install button to launch the installation process. On the Install screen of the Installer, you will see the process in detail. At the beginning of the installation process, the screen might look like the following screenshot:

If the installation has been completed successfully, the Installer will display the following End of Installation screen:

On the End of Installation screen, click Exit to exit the Oracle Universal Installer.

Creating a Warehouse Builder repository Schema:

The following steps assume you’re creating a Warehouse Builder repository on an Oracle Database 10g. If you’re using an Oracle Database 11g, the first two steps are not required. The fact is, 11g has the OWBSYS schema by default.

Launch SQLPlus that was installed with your Oracle database, connecting to it as sysdba.
From within SQLPlus, run the OWB_ORACLE_HOME/owb/UnifiedRepos/ cat_owb.sql script, using the start SQLPlus command. For example, the command to issue might look like the following:

start c:\oracle\product\11.2.0\dbhome_1\owb\UnifiedRepos\cat_owb.

SQL
As a result, you should see the OWBSYS schema is created. When prompted to enter the tablespace name for the newly created OWBSYS schema, enter USERS. After that, you should see a lot of tables, indexes, synonyms, and roles being created.
As mentioned, on Oracle Database 11g, you don’t need to create the OWBSYS schema as it exists by default. However, you still have to unlock it. You can do this through SQLPlus, connecting as sysdba and then issuing the following commands:
ALTER USER OWBSYS ACCOUNT UNLOCK;
ALTER USER OWBSYS IDENTIFIED BY owbsyspswd;

After the SQL script is completed, make sure to unlock the newly created OWBSYS schema by issuing the following commands:

ALTER USER OWBSYS ACCOUNT UNLOCK;
ALTER USER OWBSYS IDENTIFIED BY owbsyspswd;

Next, you need to run the OWB_ORACLE_HOME/owb/UnifiedRepos/reset_ owbcc_home.sql script to associate the Control Center with the correct OWB home. When prompted to enter OWB Control Center Home, you must enter the full path for the OWB Control Center install. For example, your path might look like the following:

C:/oracle/product/11.2.0/dbhome_1

Creating a Warehouse Builder workspace:
Once you have the OWBSYS schema installed and unlocked, the next step is to launch and complete the Repository Assistant in order to install a Warehouse Builder workspace

On Windows, you can run it by selecting Start | Programs | OWB_ORACLE_HOME | Warehouse Builder | Administration | Repository Assistant. On a UNIX-like system, run the ./reposinst.sh script that can be found in the OWB112/owb/bin/unix As a result, the Welcome screen of the assistant shown next should appear:

On the Welcome screen of the assistant, click Next to continue.
On the Database Information screen, enter the information required to connect to the database. Then, click Next to continue.
On the Choose Operation screen, select Manage Warehouse Builder workspaces. Then, click Next to continue.
On the Choose Workspace Operation screen, select Create a new Warehouse Builder workspace. Then, click Next to continue.
On the New or Existing User screen, select Create a workspace with a new user as workspace owner. Then, click Next to continue.
On the DBA Information screen, enter the username and password of a database DBA. Then, click Next to continue.
On the Enable Optional Features screen, leave the settings at their defaults.

Then, click Next to continue.

On the Select Languages screen, choose a language or languages of interest; for example, English. Then, click Next to continue.
On the Select Tablespaces screen, leave the settings at their defaults, and click Next to continue.
On the OWBSYS Information screen, enter the OWBSYS’s password. Then, click Next to continue.
On the Workspace Owner (New) screen, enter a username and password for the database user that will be the workspace owner, as well as a name for the workspace. For example, you might enter OWBUSER for the database user and myworkspace for the workspace name. Then, click Next to continue.

On the Workspace Users screen, select the usr user and click Next to continue.
On the Summary screen, click Finish to complete the assistant and start the workspace installation.

The installation process can take much time and its progress will be displayed.
After it’s been successfully completed, you should see the following dialog:

Defining source metadata:
The first step in defining source metadata is to create a source module that will group the objects being imported from the underlying database
Creating a source module:
Here, you create an Oracle Database module that will represent an Oracle Database data source holding the data you’re going to utilize. To do this, follow the next steps:

Move to the Projects Navigator and expand the MY_PROJECT\Databases node.
Under the MY_PROJECT\Databases node, right-click the Or item.
In the pop-up menu, select New Oracle Module.
On the Create Module – Welcome screen, just click Next to continue.
On the Name and Description screen, you can specify a name for the module being created or just leave a default name and click Next to continue.
On the Connection Information screen, click the Edit… button to specify the details of the connection.

Once you’re done, click OK to submit the connection information and close the Edit Oracle Database Location dialog, returning to the Connection Information screen.

In the Connection Information screen, click the Next button to proceed to the Summary screen:

In the Summary screen, click Finish to complete the operation. As a result, the ORACLE_1 module should appear under the MY_PROJECT\Databases\Oracle node in the Projects Navigator.
To save the changes you just made, select the File | Save All menu item ofthe Warehouse BUILDER.

Importing database objects:
The following steps walk you through the process of importing:

Right-click the ORACLE_1 module under the MY_PROJECT/Databases/ Oracle node in the Projects Navigator.
In the pop-up menu select Import | Database Objects….As a result the welcome screen of the Import Metadata Wizard should appear.
On the welcome screen, click Next to move on to the Filter Information as shown in the next screenshot:

On the Filter Information screen, check the Table as Object Type and click Next to continue.
On the Object Selection screen, in the Available pane, expand the Tables node. Select the ORDERITEMS table, choose All Levels for importing dependencies, and then move ORDERITEMS to the Selected pane. The ORDERS, PRODUCTS, REGIONS, ï¿½ï¿½ï¿½ï¿½and SALESPERSONS tables will be also imported after you confirm it. As a result, the screen should look like the one shown next:

Click Next to continue.

On the Summary and Import screen, click Finish to start the importing process. Once it’s completed, you should see the Import Results dialog providing you with the information about the actions that have been applied:

In the Import Results, click OK to complete the operation.

To save the results of importing, select the File | Save All menu item of the Warehouse Builder.

Creating a target module:
The following steps walk you through the process of creating a target schema and then a target module upon that schema in Design Center:

In Projects Navigator, move on to the Globals Navigator tab.
On the Globals Navigator tab, expand the Security node.
Under the Security node, right-click the Users node and select New User in the pop-up menu to launch the Create User wizard.
On the Welcome screen of the wizard, click Next to continue.
On the Select DB user to register screen, click the Create DB User… button to open the Create Database User dialog.

In the Create Database User dialog, specify the system user password and then enter the information to create a new database user. For example, you might enter owbtarget as the username and owbtargetpswd as its password. Then, click OK to complete the operation.
In the Selected Users pane on the Select DB user to register screen, you should see the newly created owbtarget user. If so, click Next to continue.
On the Check to create a location screen, make sure that the To Create a location checkbox for the owbtarget user is checked, and then click Next.
On the Summary screen, click Finish to complete the operation. As a result, the owbtarget user should appear under the Security\Users node. The next step is to create a target module upon that user.
create a target module, you can follow the steps in the Creating a source module section. This time, though, you won’t need to define a location in the Edit Oracle Database Location dialog invoked by clicking the Edit… button on the Connection Information screen. Instead, just select the OWBTARGET_LOCATION in the Location select box.

Creating a Time dimension:
The following steps describe how to create a Time dimension:

On the Projects tab of the Projects Navigator, right-click node MY_PROJECT\ Databases\Oracle\ORACLE_2\Dimensions and select New… in the pop-up As a result, the New Gallery dialog shown next should appear:

In the New Gallery dialog, select Time Dimension in the Items pane, and click OK to launch the Create Time Dimension
On the Welcome screen of the wizard, click Next to continue.
On the Name and Description screen, type in TIME_DM in the Name field, and then click Next to continue.
On the Storage select ROLAP and click Next to continue.
On the Data Generation screen, specify the range of data you want to be stored in the time dimension. For example, you might specify 2009 in the Start year scroll box and 3 in the Number of years scroll box, as shown in the following screenshot. Also in this screen, select the Calendar radio button.Then, click Next to continue.

On the Levels screen, select Normal Hierarchy and check each element of the hierarchy, including the following:
Calendar Year
Calendarï Quarter
Calendarï Month
Day

Then click Next to continue.

On the Pre Create settings screen, look through the settings of the dimension being created, and then click Next to start the process of dimension definition.
You should see the progress of this process on the Time Dimension Progress Panel screen. Once it’s successfully completed, click Next to move on to the Summary screen.
On Summary screen, click Finish to create the dimension.
Select the File | Save All menu item of the Warehouse Builder to save the dimension you just created.

Creating a Product dimension:
Another dimension you’ll need to create is a Product dimension that will also be used in the cube. There is no Product wizard in Warehouse Builder. Therefore, you can use the standard Create Dimension wizard, and the following steps describe how:

On the Projects tab of the Projects Navigator, right-click node MY_PROJECT|Databases\Oracle\ORACLE_2\Dimensions and select New Dimension in the pop-up menu. As a result, the Welcome screen of the Create Dimension Wizard should appear.
On the Welcome screen of the wizard, click Next to continue.
On the Name and Description screen type in PRODUCT_DM in the Name field, and then click Next to continue.
On the Storage Type, select ROLAP and click Next to continue.
On the Dimension Attributes screen, you should leave the dimension attributes and their settings at their defaults and click Next to continue.
On the Levels screen, enter the following levels:

* Category
* Item
Then, click Next to continue.

On the Level Attributes screen, make sure that all the level attributes for both the Category and Item levels are checked. Then, click Next to continue.
On the Slowly Changing Dimension screen, select Do not keep history, and then click Next to continue.
On the Pre Create settings screen, look through the settings and click Next to continue.
On the Dimension Creation Progress screen, click Next to move on to the Summary screen.
On the Summary screen, click Finish to complete the create dimension process.
Make sure to select the File | Save All menu item of the Warehouse Builder to save the dimension you just created.

Creating a cube:
To complete this task with Warehouse Builder, you can use the Create Cube wizard.
The following steps will walk you through the process of creating the cube:

On the Projects tab of the Projects Navigator, right-click node MY_PROJECT\Databases\Oracle\ORACLE_2\Cubes and select New Cube in the pop-up menu.
On the Welcome screen of the wizard, click Next to continue.
On the Name and Description screen, enter the cube name in the Name field—SALES. Then, click Next to continue.
On the Storage Type screen, select ROLAP and click Next to continue.
On the Dimensions screen, select all the available dimensions in the Available Dimensions pane and move them to the Selected Dimensions pane. Therefore, you should have the following dimensions:

* PRODUCT_DM
* REGION_DM
* TIME_DM
Then, click Next to continue.

On the Measures screen, enter the following measures:

* QUANTITY with the Data Type as NUMBER
*AMOUNT with the Data Type as NUMBER, with the
Precision as 10, and Scale as 2
As a result, the screen should look like the following:

Then, click Next to continue.

On the Summary screen, look through the settings of the cube being created and click Finish to complete the operation.
Select the File | Save All menu item of the Warehouse Builder to save the cube you just created.

Let’s create table STAGE_TABLE in our target module ORACLE_2. Here are the steps to follow:

On the Projects tab of the Projects Navigator, right-click node MY_PROJECT\Databases\Oracle\ORACLE_2\Tables and select

New Table in the pop-up menu.

In the Create Table dialog, enter STAGE_TABLE in the Name field, and then click OK. As a result, the Data Object Editor for the STAGE_TABLE table should appear.
Click the Column tab of the editor, and enter the following columns for the

* STAGE_TABLE table
*SALES_QUANTITY NUMBER
*SALES_AMOUNT NUMBER(10,2)
*SALES_DATE DATE
*PRODUCTS_NAME VARCHAR2(100)
*PRODUCTS_CATEGORY VARCHAR2(30)
*REGIONS_NAME VARCHAR2(20)
*EMPS_NAME VARCHAR2(40)

Select the File | Save All menu item of the Warehouse Builder to save the table you just created.

Creating a staging mapping
The following steps walk you through the process of creating such a mapping:

On the Projects tab of the Projects Navigator, right-click the Mappings node under the target database module node MY_PROJECT\Databases\Oracle\ ORACLE_2 and select New Mapping in the pop-up menu.
In the Create Mapping dialog, enter SOURCE_STAGE_MAP as the name for the mapping being created, and click OK. As a result, an empty canvas for the new mapping will be opened in the Mapping Editor.
Expand the MY_PROJECT\Databases\Oracle\ORACLE_1\Tables node containing the source tables, and then drag and drop all these tables to the SOURCE_STAGE_MAP‘s mapping canvas in the Mapping Editor. So the Mapping Editor should look like the following screenshot:

The next step is to drag and drop the target to the SOURCE_STAGE_MAP‘s mapping canvas. Hence, expand the MY_PROJECT\Databases\Oracle\ ORACLE_2\Tables node, and drag and drop the STAGE_TABLE table to the mapping canvas, on the right of the source tables.

5.In the Component Palette, find the Joiner operator and then drag and drop it to the mapping canvas between the source tables and the STAGE_TABLE table. Therefore, the mapping canvas now should look like the following screenshot:

As you might notice, the Joiner operator contains only two input group operators by default. In this example, though, you’ll need five of them, as you have five source tables.

On the mapping canvas, right-click the Joiner operator and select Open Details… in the pop-up menu.
In the Joiner Editor dialog, move on to the Groups tab. Here, rename two existing input groups and define three more input groups as follows:
*ORDERITEMS

*ORDERS
*PRODUCT
*REGIONS
*SALESPERSONS
Once you’re done, click OK in the Joiner Editor dialog.

Now you can connect the source tables to the Joiner by dragging and dropping the output operator of the input output group of each source table to the corresponding input operator of the input group in the Joiner.
Repeat step 8 for each source table.

Click the Joiner header on the mapping canvas to select the Joiner. Then, go to the Property Inspector, and click theï¿½ … button on the right of the Join Condition field to open the Expression Builder. The expression provided there should look like the following:
ORDERS.EMPID = SALESPERSONS.EMP_ID AND ORDERS.REGID =
REGIONS.REG_ID AND ORDERITEMS.PRODID =
PRODUCTS.PRODID AND ORDERITEMS.ORDNO = ORDERS.ORDNO

Click OK in the Expression Builder dialog to close it. From the Component Palette, drag the Aggregator component and drop it into the mapping canvas between the JOINER and the STAGE_TABLE icons.
Connect the OUTGRP1 group of the Joiner to the INGRP1 group of the Aggregator. As a result, all the 15 attributes of the Joiner’s OUTGRP1 should appear under the Aggregator’s INGRP1 group.

Select the connection arrow between the ORDDATE attributes, and then delete it by clicking Delete.
From the Component Palette, drag the Transformation operator, dropping it into the mapping canvas between the JOINER and the AGGREGATOR icons. Before you can see it on the canvas, though, the Add Transformation Operator dialog should appear.
In the Add Transformation Operator dialog, select TRUNC under the Date package, and then click OK. As a result, the Transformation operator should appear on the canvas.
Connect the ORDDATE attribute of the Joiner’s OUTGRP1 to the D attribute under the INGRP1 of the TRUNC.
Connect the VALUE attribute under the RETURN group in the TRUNC to the ORDDATE attribute under the Aggregator’s INGRP1.
On the mapping canvas, click the header of the Aggregator to select the component so that its properties become available in the Property Inspector.
In the Property Inspector, click the … button to the right of the Group By Clause field to open the Expression Builder.
In the Expression Builder, build the following expression:

INGRP1.PRODNAME, INGRP1.QUANTITY, INGRP1.UNITPRICE, INGRP1.
CATEGORY, INGRP1.REG_NAME, INGRP1.EMP_NAME, INGRP1.ORDDATE
Then, click OK. As a result, all the previously shown attributes should appear in the OUTGRP1 group of the Aggregator.

Right-click the Aggregator icon in the canvas and then select Open Details… in the pop-up menu.
In the Aggregator Editor dialog, move on to the Output Attributes tab, and change the name and expression for the QUANTITY and UNITPRICE attributes as follows:

QUANTITY_SUM SUM(INGRP1.QUANTITY)
PRICE_SUM SUM(INGRP1.UNITPRICE)

Then, click OK.

The final step is to connect the attributes under the OUTGRP1 group of the Aggregator to the corresponding attributes of the STAGE_TABLE table. Drag a connection arrow between each of the Aggregator’s output attributes and the corresponding STAGE_TABLES‘s attribute.
Select the File | Save All menu item of the Warehouse Builder to save the mapping you just created.

Loading the staging table with data

To load the STAGE_TABLE table with data, you need to execute the SOURCE_STAGE_MAP mapping you just created. This can be done with the following steps:
On the Projects tab of the Projects Navigator, right-click the STAGE_TABLE node under MY_PROJECT\Databases\Oracle\ORACLE_2\Tables and select Deploy… in the pop-up menu. Make sure the deployment completed without errors.
On the Projects tab of the Projects Navigator, right-click the SOURCE_STAGE_MAP node under MY_PROJECT\Databases\Oracle\ORACLE_2\Mappings and select Start in the pop-up menu. As a result, the Confirm Deploy dialog should appear.
In the Confirm Deploy dialog, click OK to confirm the deployment required before executing. The deployment process details will be shown to you in the Job Details window. Once the validation has been performed successfully, you’ll be asked to click the Start button in the Job Details window to continue.

Once the execution process is completed successfully, you can look through the data loaded to the STAGE_TABLE table. For that, right-click the STAGE_TABLE node in the Projects Navigator, and then select Data… in the pop-up menu.

Creating a product mapping:
The following steps will walk you through the process of creating a mapping:

Select the New Mapping in the pop-up menu.
In the Create Mapping dialog, type in PRODUCT_MAP in the Name field, and click OK. As a result, an empty canvas for the new mapping will be opened in the Mapping Editor.
Expand the MY_PROJECT\Databases\Oracle\ORACLE_2\Tables node, and then drag and drop the STAGE_TABLE table to the PRODUCT_MAP’s mapping canvas in the Mapping Editor.
Next, drag and drop the target to the PRODUCT_MAP’s mapping canvas. This time it’s going to be the PRODUCT_DM dimension. Therefore, expand the MY_PROJECT|Databases\Oracle\ORACLE_2\Dimensions node, and drag and drop PRODUCT_DM to the mapping canvas, on the right of the STAGE_TABLE table icon.
Connect the PRODUCTS_NAME attribute of the STAGE_TABLE to the NAME attribute under the ITEM group in the PRODUCT_DM, and then connect the PRODUCTS_NAME attribute to the DESCRIPTION attribute under the ITEM group
Connect the PRODUCTS_CATEGORY attribute of the STAGE_TABLE to the NAME ,attribute under the CATEGORY group in the PRODUCT_DM, and then connect the PRODUCTS_ CATEGORY attribute to the DESCRIPTION attribute under the CATEGORY group.
Select the File | Save All menu item of the Warehouse Builder to save the mapping you just created.

Creating the REGION mapping:
Now create the REGION_MAP mapping, repeating the steps in the preceding section. This time, you will need to connect:

The REGIONS_NAME attribute of the STAGE_TABLE to the NAME attribute under the REGION group in the REGION_DM, and then to the DESCRIPTION attribute under the REGION
The EMPS_NAME attribute of the STAGE_TABLE to the NAME attribute under the EMPLOYEE group in the REGION_DM, and then to the DESCRIPTION attribute under the EMPLOYEE

Creating a cube mapping:

On the Projects tab of the Projects Navigator, right-click the Mappings node under the target database module node MY_PROJECT\Databases\Oracle\ ORACLE_2 and select New Mapping in the pop-up menu.
In the Create Mapping dialog, type in SALES_MAP in the Name field, and click OK. As a result, an empty canvas for the new mapping will be opened in the Mapping Editor.
Find the STAGE_TABLE table under the MY_PROJECT\Databases\Oracle\ ORACLE_2\Tables node, and then drag and drop it to the SALES_MAP’s mapping canvas.
Drag and drop the target to the SALES_MAP’s mapping canvas. This time it’s going to be the SALES cube. Therefore, expand the MY_PROJECT\Databases\ Oracle\ORACLE_2\Cubes node, and drag and drop SALES to the mapping canvas, on the right of the STAGE_TABLE table icon.
Connect attributes in the INOUTGRP1 group of the STAGE_TABLE table to the corresponding attributes of the SALES cube as follows: