Generate the DDL and TCL for the data warehouse creation

Completed the Postinstallation module

Installed the Oracle9i Warehouse Builder client product. See the Oracle9i Warehouse Builder Installation Guide.

Documentation: Oracle9i Warehouse Builder Installation Guide

OTN: Oracle Warehouse Builder data sheets and white papers

Select Start > Programs > Oracle - OWBclientHome > Oracle 9i Warehouse Builder Client. The Warehouse Builder Logon window opens.

Before logging on with you username and password, you need to establish TNSNames connect information. Click Connection Info.

The Connection Information window opens. Provide the host name, port number, and Oracle SID that were defined during OWB installation.

Click OK. The Warehouse Builder Logon window reopens. Enter the username and password defined for the OWB repository owner during OWB installation and click Logon.

The Welcome to Oracle Warehouse Builder window opens. A project is the highest-level object in OWB. Select a project to work on (initially there will only be My Project; later there will be a list of all the projects that you create.)

Select My Project and click OK.

The Oracle Warehouse Builder window opens. Expand My Project to reveal the MODULES tree entry. For a new project this will be empty.

Right-click MODULES and select Create Module from the drop-down list.

This launches the New Module Wizard, which guides you through the steps required to create a new module. After you review the steps, click Next.

The New Module Wizard: Name window opens. You must name the new module and identify the module type. Because this module represents your source OLTP system, name it Order_Entry_OLTP and select Data Source as the module type. Click Next.

The New Module Wizard: Data Source Information window opens. You use this window to specify the environment from which the source information will come. From the drop-down lists, select Generic Oracle Database Application for the application, Oracle Database 8i/9i for the application version, and Oracle OWB Integrator for Oracle DB and Apps 3.0 for the integrator.

Click Next. The New Module Wizard: Connection Information window opens. You now need to identify the source of the metadata by defining a database link to the source database. This information could come from an Oracle Designer Repository, but for this example you will get the information from the Oracle Data Dictionary. Select Oracle Data Dictionary.

To create the new database link, click New DB Lin.... The New Database Link window opens. Enter Source in the DB Link Name field. Select Host Name, and enter the necessary host name, port number, and Oracle SID to indicate where the Oracle 9i Sample Schema is located. Enter a username and password with read-access to that schema. Click Create and Test.

Click OK. The New Module Wizard: Connection Information window reopens. The metadata you are interested in comes from the Order Entry OLTP system so you need to point to the OE schema. Click Change Sch... to select from a list.

The Schema Choices window opens. Select the OE schema and click OK.

Accept the default values for the rest of the entries and click Next. The New Module Wizard: Finish window opens. You have now defined the source module. Click Finish.

The Oracle Warehouse Builder window reopens. To see your new Order_Entry_OLTP module listed in the project tree, expand MODULES. You can examine the module by right-clicking on the module name and clicking Editor... or Properties....

You now need to create a target module. This process is very similar to the source module creation process. Right-click MODULES and click Create Module.

The New Module Wizard: Welcome window opens. Review the required steps and click Next.

The New Module Wizard: Name window opens. Because this module represents the target data warehouse system, name it Sales_History_DW and select Warehouse Target as the module type.

Click Next. The New Module Wizard: Target window opens. The values default to your local environment.

The metadata for the target warehouse resides in the Sales History (SH) schema. In a normal production environment, you would likely need a new database link to point to a different host system. However, for this demonstration, the SH schema resides in the same database instance as the source Order Entry (OE) schema. Therefore, use the same database link named Source that you created for the first module. Click Change Schema and select SH from the list in the Schema Choices window. Click OK. The New Module Wizard: Connection Information window reopens.

Click Next. The New Module Wizard: Finish window opens. You have now defined the target module. Click Finish.

Expand My Project > MODULES to see the two new modules you created. You can explore and modify the properties if needed.

Right-click the ORDER_ENTRY_OLTP module, and from the drop-down list select Import.

This starts the Import Metadata Wizard. Review the required steps for importing metadata.

Click Next. The Import Metadata Wizard: Filter Information window opens. Now you can filter the objects to the desired type. By default, all types are selected. Deselect the View and Sequence check boxes in the Object Type region because you are only interested in the table information.

Click Next. The Import Metadata Wizard: Object Selection window opens. Expand TABLE in the Available Objects region to see all tables in the OE schema. Click >> to import all the tables into the new ORDER_ENTRY_OLTP source module.

After you have moved all of the tables into the Selected Objects region, click Next. The Import Metadata Wizard: Summary and Import window opens. Examine the list of tables from the OE schema whose metadata definitions will be imported into the ORDER_ENTRY_OLTP source module.

Click Finish. The Importing Progress Dialog window is displayed until the import is complete. The import may run for several minutes.

The Import Results window opens. Examine the results. You can expand the tables to see their columns and constraints.

Click OK to commit and finish the metadata import. After a while, the Oracle Warehouse Builder window reopens. Right-click the ORDER_ENTRY_OLTP module and click Editor... to view the table information that is now included in your source module.

From the Source Module Editor menu, select View and select Schema from the drop-down list. Select all tables and then click OK. Enlarge the Source Module Editor window. The display should look similar to the screenshot below. After examining the schema, close the window.

Next, you repeat the above import process with the SALES_HISTORY_DW module, to import the SH schema’s table metadata.

Click Next. The Import Metadata Wizard: Filter Information window opens. For the target warehouse, accept the default values in the Object Type region and click Next.

The Import Metadata Wizard: Object Selection window opens. Expand TABLE in the Available Objects region to see objects in the SH schema. Click >> to import all the objects into the new SALES_HISTORY_DW warehouse module.

Continue through the Import Metadata Wizard until you finish the process.

Right-click the SALES_HISTORY_DW module and click Editor... to view the table information that is now included in your source module.

The Warehouse Module Editor: SALES_HISTORY_DW window opens. You can now expand the TABLES object and examine the imported objects.

Next, right-click DIMENSIONS and click Create Dimension from the drop-down list.

The New Dimension Wizard: Welcome window opens. Review the required steps for creating a dimension.

Click Next. The New Dimension Wizard: Name window opens. Enter products_dim for the name of the dimension, prod as the prefix to be used in key names, and any description that you want (optional).

Click Next. The New Dimension Wizard: Levels window opens. Use this window to create the three levels that will be used to define this dimension’s hierarchies. Enter Product as the level’s name in the Name field, leave the Prefix and Description fields blank, then click Add. Repeat these steps to add the Subcategory and Category levels.

After adding the three levels, click Next. The New Dimension Wizard: Level Attributes window opens. Here you define the attributes for each level. First, select the Product level from the Level drop-down list. Notice that the system provides a default name of “ID” in the Level Attributes region. You want to rename that attribute as prod_id, so click ID. This causes the Name field to be filled with “ID”. Type over that name value, changing it to prod_ID. For the data type, keep NUMBER from the Data Type drop-down list. For this attribute, which will eventually represent a sequence-generated primary key, leave the Precision and Scale fields as zero, then click Update (not Add).

Enter a second attribute for the Product level, giving it the name prod_name, with a data type of VARCHAR2 and a length of 50. Click Add. Enter a third attribute for the product level, giving it the name prod_desc, with a data type of VARCHAR2 and a length of 50. Click Add.

Repeat the process for the Subcategory level by selecting it from the Level drop-down list. Again, the system provides a default name of “ID” in the Level Attributes region. Click ID and change its name to prod_subcat_desc, with a data type of VARCHAR2 and length of 2000, then click Update (not Add).

Repeat this process for the Category level by selecting it from the Level drop-down list. Click ID and change its name to prod_cat_desc, with a data type of VARCHAR2 and length of 2000, then click Update (not Add).

You have created attributes for each of the three levels you defined for the products_dim dimension. Click Next. The New Dimensions Wizard: Hierarchies window opens. Here, you create a hierarchy of those three levels. Enter the hierarchy name prod_rollup and click Add.

Click Next. The New Dimensions Wizard: Level Relationships window opens. Use this window to select the levels to include in the rollup. To select all three that you created, click >>.

You need to arrange the levels in the hierarchical order in which they will rollup. The order will be Category on top, then Subcategory and Product, respectively, at the lowest level. To promote or demote a level, click the level then click the up or down arrows on the right side of the Selected Levels region. When completed it should look like the screenshot below.

Click Next. The New Dimensions Wizard: Finish window opens. The products_dim dimension is completed; click Finish to create it.

Right-click DIMENSIONS and click Create Dimension from the drop-down list.

The New Dimension Wizard: Welcome window opens. Review the required steps for creating a time dimension.

Click Next. The New Dimension Wizard: Name window opens. Enter times_dim for the name of the dimension, times as the prefix to be used in key names, and any description that you want (optional).

Click Next. The New Dimension Wizard: Levels window opens. Use the table below as you proceed through the wizard to define the dimension.
In the table, the first attribute of each level is italicized. Create the italicized attribute by renaming the level's default 'ID' attribute, changing its data type as indicated, and clicking the Update button (not Add).

When you proceed to the New Dimension Wizard: Finish window, review the objects to be created. Click Finish.

You can now examine the newly created dimensions. To do this, you perform the following steps: In the Warehouse Module Editor, expand DIMENSIONS. Right-click PRODUCTS_DIM and click Editor on the drop-down list.

The Dimension Editor: PRODUCTS_DIM window opens. Examine the Dimension Editor. Here you can see the dimension rollups and the level relationships. Click the pyramid-shaped symbols next to the CATEGORY, SUBCATEGORY, and PRODUCT labels, to expand those levels so that you can see their attributes. When done, close the Dimension Editor.

In the Module Editor, right-click the FACTS object and click Create Fact from the drop-down list.

This starts the New Fact Wizard. Review the required steps for creating a fact table.

Click Next. The New Fact Wizard: Name window opens. Enter sales_fact for the fact table name and any descriptive information you want.

Click Next. The New Fact Wizard: Define Foreign Keys window opens. Create the foreign key relationships from this SALES fact to the two dimensions you created. Select the TIMES_DIM dimension from the Dimension drop-down list. Select the dimension's lowest level, DAY, from the Level drop-down list. From the Unique Key drop-down list select TIMES_DAY_UK and click Add. Overtype the system-generated Foreign Key name with a more meaningful name, SALES_TIMES_FK.

Repeat the above process for PRODUCTS_DIM using the PRODUCT level, and the PROD_PRODUCT_UK unique key. Overtype the system-generated Foreign Key name with a more meaningful name, SALES_PRODUCTS_FK. When completed it should look like the screenshot below. (Remember, a real sales fact table would likely be characterized by additional dimensions, such as Customers, Promotions, and Channels.)

Click Next. The Define Measures window opens. Thus far, you have defined the sales fact table’s foreign keys that reference the five dimensions that characterize the sales fact’s numeric measures. Now, you define those three “measures” that will contain the sales fact’s numeric measurements. Click Add and enter the following information, clicking Add after entering each measure except for the last one:

When complete it should look like the screenshot below.

Click Next. The New Fact Wizard: Finish window opens. The definition for the sales fact table and the relationships to the dimensions are complete.

Click Finish. The Warehouse Module Editor: SALES_HISTORY_DW window reopens. Now you can examine the sales fact table and its relationships. Expand FACTS. Right-click SALES_FACT and select Editor from the drop-down list. The Fact Editor: SALES_FACT window opens. Drag the lower-right corner of the window to enlarge the window so that you can see the fact and its two dimensions. When done, close the Fact Editor window.

The Warehouse Module Editor: SALES_HISTORY_DW window reopens. To ensure that your metadata definitions are valid before Warehouse Builder generates code, you need to validate the dimensions and fact table. To validate all the dimensions at the same time, click DIMENSIONS to highlight it. Click Module from the top menu and select Validate from the drop-down list.

When the validation finishes, you see the validation results for the dimensions. They should have a status of Valid. If there are problems with any of the dimensions, a message will be displayed in the Validation Messages region.

Click Close. Repeat the above process for the fact table. To validate the fact table, click FACTS to highlight it. Then from the top menu click Module. Select Validate from the drop-down list. Again, this should complete with a status of Valid. Click Close.

In the SALES_HISTORY_DW Module Editor window, right-click MAPPINGS and select Create Mapping from the drop-down list.

The New Mapping Wizard: Welcome window opens. Review the required steps for creating a new mapping.

Click Next. The New Mapping Wizard: Name window opens. For the mapping name, enter product_mapping for the name and enter any description you want.

Click Next. The New Mapping Wizard: Finish window opens. Click Finish.

The Mapping Editor: PRODUCT_MAPPING window opens. Maximize this window to fill the screen. Use the Mapping Editor to define your source and target objects to be mapped. In this example, you will map the flow of data from the PRODUCT_INFORMATION table of the ORDER_ENTRY_OLTP source to the PRODUCTS_DIM dimension in the SALES_HISTORY_DW target warehouse. Drag the Mapping Table operator from the top-left corner of the Toolbox palette onto the mapping canvas.

The Add Mapping Table window opens. Accept the default of “Select from existing repository table and bind.” In the scrollable subwindow, click PRODUCT_INFORMATION.

Click OK. The PRODUCT_INFORMATION operator opens on the canvas. Drag a corner of this operator to maximize its height and width, in order to see all of its columns, and move the operator to the left side of the canvas. Drag the Mapping Dimension operator from the Toolbox palette onto the mapping canvas.

The Add Mapping Dimension window opens. Accept the default of “Select from existing repository Dimension and bind.” In the subwindow, click PRODUCTS_DIM and click OK.

The PRODUCTS_DIM operator opens on the canvas. Drag a corner of this operator to maximize its height and width, in order to see all of its columns, and move the operator to the right side of the canvas. To map a source attribute, click the PRODUCT_DESCRIPTION attribute in the PRODUCT_INFORMATION source table operator and drag and drop a mapping line from it onto the PRODUCT_PROD_DESC attribute in the PRODUCTS_DIM target dimension operator.

For this mapping, a sequence is needed to generate the “synthetic key” value for
PRODUCT PROD_ID, which is the unique key in the PRODUCTS_DIM dimension. Drag the Mapping Sequence operator from the Toolbox palette onto the mapping canvas. The Add Mapping Sequence window opens. Select “Create new repository sequence and bind.”

Click OK. The New Sequence Wizard: Welcome window opens. Click Next. The New Sequence Wizard: Name window opens. For the sequence name, enter prod_sequence and click Next. The New Sequence Wizard: Finish window opens. Click Finish. The prod_sequence object opens on the canvas. Click and drag the sequence’s NEXTVAL and drop it on the PRODUCT_PROD_ID attribute of PRODUCTS_DIM. This creates a unique value for the primary index value during the loading process.

This mapping also requires a transformation to combine two source attributes into a single target attribute. You will use the CONCAT transformation. Drag the Mapping Transformation operator from the Toolbox palette onto the mapping canvas. The Add Mapping Transformation window opens. Enter concat in the “Type the prefix or the name of the transformation” field and click Find. The CONCAT transformation becomes highlighted in the scrollable region.

Click OK. The CONCAT object opens on the canvas. Now define the source inputs and the target output. Click and drag PRODUCT_NAME from the PRODUCT_INFORMATION source and drop it onto the char1 input of the CONCAT object. Then click and drag PRODUCT_DESCRIPTION from the same source and drop it onto the char2 input. Finally, click and drag RESULT from the CONCAT object and drop it onto the SUBCATEGORY_PROD_SUBCAT_DESC attribute in the PRODUCTS_DIM target dimension.

Next, drag and drop a mapping line from PRODUCT_NAME in the PRODUCT_INFORMATION source table to PRODUCT_PROD_NAME in the PRODUCTS_DIM target dimension. Your mapping should look like the screenshot below. When done, close the Mapping Editor window.

Open the SALES_HISTORY_DW module. To edit all of the dimensions at the same time, select DIMENSIONS.

Select Module from the menu; then select Generate from the drop-down list.

The generation mode pop-up dialog box opens. You can use this dialog box to generate three types of scripts.

In this example, you want to generate DDL scripts for new dimensions, so select “Generate create scripts” without selecting the “Drop objects first” check box.

Click OK. A Generation Progress pop-up dialog box opens, after which the Generation Results window opens. This window shows a list of your selected dimensions, under which there is a list of the generated scripts. In the list of generated objects, you can select one or more scripts, and either view the code or deploy the script to the target warehouse. You will usually want to view the code before deploying. (The Deploy button – which you should not use in this exercise – allows you to either immediately execute the scripts to create warehouse objects or move data, or to deploy the scripts to the target warehouse file system, for scheduling subsequent execution.)

In this exercise, you will not deploy the scripts, but rather view their code. In the Generated Scripts listing in the bottom half of the window, select the first script in the listing, whose object name is TIMES_DIM, and click View Code.

The TIMES_DIM.ddl window opens. The generated code should look similar to the example below. Review the code and close the window.

Close the Generation Results window.

In the Warehouse Module Editor: SALES_HISTORY_DW window, expand MAPPINGS and select PRODUCT_MAPPING.

Before generating the PL/SQL script, you must first validate your definition. Select Module from the menu; then select Validate from the drop-down list. A Validation Progress pop-up dialog box opens, after which the Validation Results: PRODUCT_MAPPING window opens. The top of the window indicates that the selected object, PRODUCT_MAPPING, has a valid status, and that it could be generated. However, the bottom of the window lists a warning: a possible truncation.

To obtain the details of the warning, highlight the warning listed in the bottom half of the window and click Details. The Validation Details window opens.

The details of the warning indicate possible truncation because source column definitions are longer than their target column definitions. The warning explains that the source column PRODUCT_DESCRIPTION has a data length of 2000, while it maps to target PRODUCT_PROD_DESC with a data length of 50.

Close the Validation Details windows. You can conveniently choose a validation message and jump to a defintion window to edit the object in question. At the bottom of the Validation Results window, select the validation message.

Click Edit. The Mapping Editor: PRODUCT_MAPPING window opens. Maximize the window to see all the objects in this mapping. In the PRODUCT_INFORMATION table on the left, right-click the PRODUCT_DESCRIPTION attribute and click Attribute Properties in the pop-up menu.

The Attribute Properties: PRODUCT_DESCRIPTION dialog box opens. Note the length of 2000 for this VARCHAR2 attribute.

Close the dialog box. Now move across this attribute’s mapping to the PRODUCTS_DIM dimension on the right. Right-click the PRODUCT_PROD_DESC attribute and click Attribute Properties. The Attribute Properties: PRODUCT_PROD_DESC dialog box opens. Note the length of 50 for this VARCHAR2 attribute. You received the warning because the target attribute length of 50 is less than the source length of 2000. Fix this problem clicking on the value 50, and overtyping this field with 2000.

After changing the length from 50 to 2000, close this dialog box.

You can generate intermediate results on portions of your mappings. This allows you to examine the OWB-generated code incrementally. In the Mapping Editor, select INOUTGRP1 in the PRODUCTS_DIM dimension on the right side of the mapping.

With INOUTGRP1</