Modeling the Target Schema

In this tutorial, you create dimensions, configure metadata for a schema module, and create a Type 2 slowly changing dimension in Oracle Warehouse Builder 10g Release 2.

Note: The screenshots in this tutorial may appear slightly different from yours depending on the order in which you perform the other tutorials in this OBE Series. You can ignore the irrelevant items you see on your screens.

Approximately 45 minutes

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

Oracle Warehouse Builder has many features and functionality to facilitate better schema design, mainly focused on increased design capabilities for indexes, partitions and allowing for detailed configuration of physical storage and sizing properties on objects. The latter, configuration properties, is also close related to the named configurations giving you complete control over physical implementations per environment without changing the design. In Warehouse Builder, it is important to understand that partitions and indexing has been moved into the logical design area, exposing the full editing capabilities within the editors on these objects and parts of the design.

In this tutorial, you get to learn how Warehouse Builder allows you to set up indexing and partitioning schemes to optimize your data storage and access paths. In this tutorial, you also get an introduction to dimensional modeling through the creation of a TIME dimension. You will also learn how Warehouse Builder helps you manage slowly changing dimensions enabling you to track changes in your dimensions.

Back to Topic List

Before starting this tutorial, you should:

1.

Complete the Setting Up the Oracle Warehouse Builder Project tutorial.

Back to Topic List

Creating a Time Dimension

Dimensions are the primary organizational unit of data in a star schema. Warehouse Builder uses dimensions to organize and index data for cubes. Examples of some commonly used dimensions are Customer, Product, and Time. A time dimension is a special type of dimension and is often used in any dimensional model. In this topic, you learn how to create, configure, and deploy a time dimension in Oracle Warehouse Builder.

Creating a TIME Dimension
Examining the TIME Mapping
Deploying the TIME Dimension

Back to Topic List

Creating a TIME Dimension

To create a TIME dimension, perform the following steps:

1.

You are logged in to the Design Center as owb/owb. In the Project Explorer panel, expand the Handson > Databases > Oracle> DP_TGT. Right-click the Dimensions node and select New > Using Time Wizard.

The Create Time Dimension Wizard is launched. Click Next on the Welcome Page.

 

2.

On the Name and Description page, enter TIMES as the name of the dimension. Click Next.

 

3.

On the Storage page, select Relational Storage (ROLAP) to specify relational implementation of the dimension. Click Next.

 

4.

On the Data Generation page, you specify the range of time data that is required for your warehouse. This information is used to generate a mapping that populates the time dimension. Within this mapping, the dates you enter are added as parameters, allowing you to rerun this mapping with dates at a later stage.

For Start Year, enter 2005 and for Number of Years enter 3. Ensure that Calendar is selected, as shown in the screenshot. Click Next.

 

5.

On the Levels page, select Normal Hierarchy and select all the levels: Calendar Year, Calendar Quarter, Calendar Month, and Day. Click Next.

 

6.

On the Pre Create settings page, review the details of the TIMES time dimension. Click Next.

 

7.

The progress of the dimension creation is displayed. On successful completion, click Next.

 

8.

On the Summary page, examine the details again and click Finish.

 

9.

Expand the Mappings, Tables, and Sequences nodes in DP_TGT node.

Observe that the wizard has generated four objects necessary for a fully functional TIME dimension. The four objects are the:

  • TIMES dimension object
  • TIMES_SEQ sequence that populates the surrogate ID of the time dimension levels
  • TIMES table to support the relational implementation of the time dimension that physically stores the time data
  • TIMES_MAP mapping to populate the time dimension.

 

Back to Topic

Examining the TIME Mapping

To examine the TIME mapping, perform the following steps:

1.

Double-click TIMES_MAP under the Mappings node to launch the Mapping Editor.

 

2.

From the View menu, select Auto Layout. You can see the expanded mapping. You can scroll or select the zoom options from the View menu to view the mapping appropriately. The TIMES_MAP mapping loads the TIMES time dimension and is generated by Warehouse Builder. Warehouse Builder creates this complex mapping automatically for you, there by enhancing the productivity and saving your time.

From the Mapping menu, select Close to exit the Mapping Editor. From the Design menu, select Save All to commit your work.

 

Back to Topic

Deploying the TIME Dimension

Deployment is the process of creating your target system from the logical design or model. The process includes generating scripts, such as DDL that creates data objects (such as tables, views, and dimensions). To enable the relational model for multidimensional querying, choose to deploy all metadata to the database. This extra metadata allows us to run the dimension specific data viewers.

To deploy the TIME dimension, perform the following steps:

1.

Right-click the TIMES dimension and select Configure.


The Configuration Properties window is launched.

 

2.

In the Configuration Properties window, select Deploy All from the Deployment Options drop-down menu.

Click OK and select Save All from the Design menu to commit your work.

 

3.

To create all objects in the database, you have to deploy the objects in the Control Center.

The Control Center Manager offers a comprehensive deployment console that enables you to view and manage
all aspects of deployment, including configuration and validation. In the Control Center Manager, you can view the deployment history of an object. The deployment history is also used to automatically determine the default deployment action. These options are not available if you deploy objects from the Project Explorer tree in the Design Center.

To deploy all objects, launch the Control Center Manager. From the Tools menu, select Control Center Manager.

The Control Center Manager is launched.

 

4.

Observe that in the Control Center Manager console, you can access the design objects based on their locations, because the navigation tree is organized on locations.

Note: On the right, in the Object Details panel, you will observe that you can see all the objects that you created during the creation of the TIMES dimension. Examine the design status, deploy status, and other details of all the objects. It is important to note that you may have other objects as a result of performing other tutorials. The objects from your previous tutorials can be disregarded during deployment in this tutorial.

 

5.

Expand DP_TGT_LOCATION >DP_TGT. Press Ctrl and select the highlighted objects (TIMES_MAP, TIMES, TIMES, and TIMES_SEQ) as shown in the screenshot. Click the Default Actions button.

 

6.

To deploy the selected objects, select File>Deploy>To Control Center. Note how the Deploy Action has now changed to Create.

 

7.

You can monitor the progress of deployment in the Control Center Jobs panel on the lower right of the console, as shown in the screenshot.

 

8.

Note that in the Control Center Jobs panel, the deployment status changes from "generate"
to "run" to "completed successfully" . Note that Deploy Status has changed to Success for all
the objects.

 

9.

You can view the generated code for the successfully deployed objects in the TIMES dimension. Double-click HANDSON in the Job column in the Control Center Jobs panel.

The Job Details window is launched.

 

10.

In the Job Details window, scroll down to TIMES under Time Dimensions in the DP_TGT node. Select TIMES. Click the Script tab in the right hand panel of the Job Details window.

 

11.

For each object generated, Warehouse Builder generates DDL scripts. To view the generated code for the TIMES dimension, select TIMES_REL.ddl script name. The View Code button is enabled. Click View Code.

 

12.

Scroll in the TIMES_REL.ddl window to view the generated code.

 

13.

From the Code menu, select Close to close the code window. Close the Job Details window.

 

14.

In the Control Center, right-click TIMES_MAP in the Mappings node and select Start.

 

15.

You can monitor the progress of the execution of the TIMES_MAP in the Control Center Jobs panel as shown in the screenshot.

 

16.

Close the Control Center when the execution has been completed successfully.

 

17.

In the Design Center window, right-click TIMES under the Dimensions node and select Data.

The Dimension Data Viewer window is launched.

 

18.

Examine the Dimension Data Viewer window to observe the drilling through the hierarchy. Close the Dimension Data Viewer window.

 

19.

Click the Save All icon in the toolbar.

 

Back to Topic

Back to Topic List

Configuring Objects

Warehouse Builder stores the definitions for your target schema in warehouse modules. These definitions can be created using the Warehouse Builder wizards or by importing them from external sources. A big part of any schema design is the use of indexes and constraints, partitions, and various other physical properties on the tables. This section shows you how to create a schema module, import objects, define indexes, constraints, and partitions.

Creating a SH Schema Module
Examining the Imported Objects
Defining Indexes and Constraints
Defining Partitions
Setting Configuration Properties

Back to Topic List

Creating a SH Schema Module

To create a SH schema module, perform the following steps:

1.

In the Project Explore panel, expand the HANDSON project. Expand the Databases node, right-click the Oracle node and select New.

The Create Module Wizard is launched. Click Next on the Welcome page.

 

2.

Specify SH in the Name field. Ensure that the default values of the following fields are maintained:

  • Select the module status: Development
  • Identify the module type: Warehouse Target

Click Next.

The Connection Information window is displayed.

 

3.

In the Connection Information window, you can provide the database link information necessary to import metadata into the warehouse module.

In the Connection Information window, click Edit and enter the information given in the tables.

Name SH_LOCATION
User Name SH
Password SH
Host localhost
Port 1521
Service Name orcl
Version 10.2

Click Test Connection. If the connection is successful, click OK.

 

4.

In the Connection Information window, review the details of SH_LOCATION.

Select the Import After Finish checkbox and click Next.

 

5.

The module definition is complete and you are now ready to import the metadata for the SH module.

Click Finish.

 

6.

The Import Metadata Wizard is launched.

Warehouse Builder enables you to import data object definitions into modules using the Import Metadata Wizard. These definitions assist you in modeling your target system. For Oracle target modules, you can import definitions for tables, views, external tables, sequences, advanced queues, and PL/SQL transformation packages.

Click Next.

 

7.

The Filter Information window is launched.

In this topic, you import tables, materialized views and views into the SH target module. In the Filter Information window, deselect all checkboxes except the Table, Materialized View, and View checkboxes.

Click Next.

 

8.

Expand the Tables node. Press Ctrl and select CHANNELS, PRODUCTS, PROMOTIONS, and SALES and click >.

 

9.

The Confirm Import Selection pop-up window is launched. Click OK.

 

10.

Ensure that the CHANNELS, CUSTOMERS, PRODUCTS, PROMOTIONS, SALES, and TIMES tables have been selected.

 

11.

Press the Ctrl key while you select Materialized Views and Views, and click >. Then click Next.

 

12.

In the Summary and Import window, review the details of the objects you have chosen to import.

Click Finish.

 

13.

The progress of the import is displayed.

 

14.

The Import Results window displays the results of the import process.

Review the information and click OK.

 

15.

The SH module has been created.

 

Back to Topic

Examining the Imported Objects

To examine the imported objects in the Data Object Editor, perform the following steps:

1.

Expand the SH module in Databases node. Expand the Materialized Views node and double-click CAL_MONTH_SALES_MV.

 

2.

The Data Object Editor is launched.

Review the information on the tabs and look through the options in the Configuration panel.

Note: If you are not able to view the Configuration panel, click Window from the Data Object Editor menu and select Configuration from the drop-down menu.

 

3.

Expand Views in the SH module in the Explorer panel of the Data Object Editor.

Select the PROFITS view in the SH module.

 

4.

Drag and drop the PROFITS view to the Canvas panel on the right.

 

5.

Review the PROFITS view.

Right-click the PROFITS view object and select Detail View. (Perform this only if you the View Details panel is not already open.)

 

6.

Views are used to simplify the presentation of data or restrict access to data. Often the data that users are interested in is stored across multiple tables with many columns. In Warehouse Builder, a view can be defined to model a query on your target data. This query information is stored in the Warehouse Builder repository.

In the View Details panel, click the Query tab. Click the right down arrow in the View Details panel and select Full Size.

 

7.

Review the code displayed in the Query tab. Click the right down arrow in the View Details panel and select Restore.

 

8.

Expand the Tables node in the SH module. Drag the SALES table into the Canvas panel to the right.

In the Data Object Editor menu, click View and select Auto Layout.

 

9.

Right-click the SALES table object and select Detail View.

 

10.

Click the right down arrow in the View Details panel and select Full Size.

 

11.

In the Table Details panel review the description for the Sales table. Click the Columns tab.

 

12.

Review the description for the columns in the SALES table. Click the Constraints tab.

 

13.

Review the information for the constraints in the SALES table. Click the Indexes tab.

 

14.

Click the + in front of each index name. Review the information for the Indexes in the SALES table. Click the Partitions tab.

 

15.

Review the information for the Partitions in the SALES table.

 

16.

Click the right down arrow of the Table Details panel and select Restore.

 

17.

In the Configuration panel, you can view the physical properties of the SALES table that have been imported.

Note: To obtain the same view as that shown in the screenshot below, click on the on the upper right down arrow of the panel and select Maximize to expand the Configuration panel.

Close the Data Object Editor.

 

Back to Topic

Defining Indexes and Constraints

Warehouse Builder supports the following types of indexes:

B-tree indexes
Bitmap indexes (auto-generated on a cube)

To define indexes and constraints, perform the following steps:

1.

In the Design Center, expand the DP_TGT module. Right-click the Tables node and select New.

The Data Object Editor is launched. Click the right down arrow of the Table Details panel and select Full Size.

 

2.

In the Table Details panel, specify the name of the table as T_IND_PART. Click the Columns tab.

 

3.

Create the following columns with the help of the information given in the tables.

Column Name

Data Type

Length

Precision

Not Null

Default Value
ID

NUMBER

 
0

Y

 
CODE
VARCHAR2

2

 

Y

 
MSTATUS
CHAR

1

 

Y

'N'

 

4.

Click the Constraints tab.

 

5.

To add a new constraint, click the Add Constraint button at the bottom of your screen.

 

6.

Specify the name of the constraint as T_IND_PART_PK1. Select Primary Key as the Type.

 

7.

Click the Local Column field and click the Add Local Column button at the bottom of your screen. Select ID from the Local Column drop-down menu.

 

8.

You will now create a check constraint.

Click the Add Constraint button and specify the name of the constraint as T_IND_PART_CK1. Select Check Constraint as the Type.

 

9.

Click Check Condition field. Click the button in the Check Condition field.

The Expression Builder is launched.

 

10.

In the Check Constraint Expression panel on the right, specify the check condition as MSTATUS IN ('Y', 'N', 'D', 'W'). Click OK.

The check constraint has been created.

 

11.

You will now create an index.

Click the Indexes tab. To create a new index, click the Add Index button at the bottom of your screen.

 

12.

Keep the default index name as T_IND_PAR_idx1. Select BITMAP as the index Type, GLOBAL as the Scope, and NONE as the Partitioning.

 

13.

Click the [+] in front of the index name and select MSTATUS in Key Columns.

 

14.

In the Data Object Editor menu, click Object and select Generate.

 

15.

The progress of the generation is displayed.

 

16.

The code is generated for the T_IND_PART table. Click the Script tab in the Generation window to view the generate code.

 

17.

Scroll through the code to ensure that the constraints added earlier in this section have been included in the code.

 

18.

Click the Validation Message tab.

Click Save All icon in the toolbar and exit the Data Object Editor.

 

Back to Topic

Defining Partitions

To define partitions, perform the following steps:

1.

Right-click the Tables node in the DP_TGT module and select New.

 

2.

The Data Object Editor is launched. Click the right down arrow in the Table Details panel and select Full Size. In the Table Details panel, specify the name of the table as T_PART.


 

3.

Click the Columns tab. Create the following columns with the help of the information given in the tables.

Column Name

Data Type

Length

Precision

Scale
ID

NUMBER

 

22

0
SHIPDATE
DATE

 

 

 
SHIPTOSTATE
VARCHAR2

2

 

 


 

4.

Click the Partitions tab. Select Range-List as Type.

 

5.

Click Tab in your keyboard.

The Partition Key, Subpartition Key, Partitions, and Subpartition Template fields become available.

 

6.

Select SHIPDATE as the Partition Key and SHIPTOSTATE as the Subpartition Key.

 

7.

Add three Partitions to the T_PART table with the help of the following information. Click the Add button at the bottom of your screen to add partitions after creating the first partition

  • M12006 < to_date(‘012006’, ‘MMYYYY’)
  • M22006 < to_date(‘022006’, ‘MMYYYY’)
  • M32006 < to_date(‘032006’, ‘MMYYYY’)
  • Leave the PDEFAULT value as is

 

8.

Add three subpartitions to the T_PART table using the Subpartition Template. Click the Add button at the bottom of your screen to add subpartitions after creating the first subpartition.

  • SHP_WEST = 'CA', 'OR'
  • SHP_MID = 'MN', 'TX'
  • SHP_EAST = 'NY', 'MA'

 

9.

Click Object in the Data Object Editor menu and select Generate.

 

10.

The Code window is launched. Scroll through the code and notice how all the partitions and subpartitions have been generated

 

11.

Click the Validation Message tab.

 

12.

Click the Save All icon and close the Data Object Editor.

 

Back to Topic

Setting Configuration Properties

To set configuration properties for tablespaces and for compression and parallel access, perform the following steps:

1.

In the Project Explorer panel, right-click the DP_TGT module and select Configure.

 

2.

The Configuration Properties window is launched.

Specify USERS as the Default Object Tablespace. Click OK.

 

3.

Right-click the T_IND_PART table and select Generate.

 

4.

The Generation Results window is launched. Click the Script tab.

 

5.

Select the T_IND_PART.dll script and click the View Code button.

 

6.

The Code is displayed.

 

7.

Select Close from the Code menu and close the Generation Results window.

 

8.

Right-click the T_IND_PART table in Design Center and select Configure.

 

9.

The Configuration Properties window is launched. Under Storage Space, for the Tablespace, enter DIFFERENT. Click OK.

 

10.

Right-click the T_IND_PART table and select Generate.

 

11.

The Generation Results window is launched. Click the Script tab.

 

12.

Select the T_IND_PART.dll script and click the View Code button.

 

13.

To configure compression and parallel access:

In the Project Explorer panel, right-click the T_IND_PART table and select Configure.

 

14.

The Configuration Properties window is launched.

Select Parallel as the Parallel Access Mode and specify 4 as the Parallel Degree.

 

15.

Select Compress as the Data Segment Compression. Click OK.

By configuring the compression and parallel access setting you have decreased the storage volume on your system. Additionally, you have enabled both query and ETL applications to make use of Oracle parallel capabilities.

 

16.

Click the Save All icon in the toolbar.

 

Back to Topic

Back to Topic List

Creating Slowly Changing Dimensions

In this topic you perform the following tasks:

Setting Type 2 SCD
Examining the SCD Settings

Back to Topic List

Setting Type 2 SCD

To set Type 2 SCD while creating a dimension, perform the following steps:

1.

In the Project Explorer panel of the HANDSON project, right-click the Dimensions node. Select New>Using Wizard.

The Create Dimension Wizard is launched. Click Next.


2.

Specify PRD_SCD_DIM as the name of the dimension. Click Next.

 

3.

Ensure that the ROLAP: Relation Storage radio button is selected. Click Next.

 

4.

The Dimension Attributes window is displayed.

Add three new attributes to the existing attributes list with the information described in the table below. After adding the three attributes, click Next.

Name

Data Type

Precision

Scale

PACKSIZE

NUMBER

0

0

PACKWEIGHT
NUMBER

0

0

CONTAINERSIZE
NUMBER

0

0

 

5.

The Levels window is displayed. Specify the following three levels:

  • TOTAL
  • PRD_GRP
  • PRD

Click Next.

 

6.

Note that the newly added dimension attributes have not been added to the TOTAL level.

 

7.

Select the PRD_GRP level. Note that the newly added dimension attributes have not been added to the PRD_GRP level either.

 

8.

Select the PRD level. Note that the newly added dimension attributes have been added to the PRD level. Click Next.

 

9.

The Slowly Changing Dimension window is displayed.

Select the Type 2: Store the complete change history radio button. Click Next.

 

10.

The Pre Create Settings window is displayed. Review the details of the dimension and click Next.

 

11.

The Progress of dimension creation is displayed. After the dimension creation has finished successfully, click Next.

 

12.

The Summary window is displayed. Review the information for the PRD_SCD_DIM dimension. Click Finish.

 

Back to Topic

Examining the SCD Settings

To examine the SCD settings in the Data Object Editor, perform the following steps:

1.

In the Project Explorer panel, right-click PRD_SCD_DIM in the Dimensions node. Select Open Editor.

 

2.

The Data Object Editor is displayed. Click the right down arrow in the Dimension Details panel and select Full Size from the bottom of your screen .

 

3.

Click the SCD tab.

 

4.

In the SCD tab, click the Settings button.

 

5.

The Type 2 slowly changing dimension settings are displayed. Click Close.

Note:

  • Warehouse Builder has generated an effective and an expiration date column, to keep track of the versions in the records.
  • SCD settings are only added to the lowest level, and within that level only to non-key columns.
  • You can specify whether to create historical records on a per-attribute level.

 

6.

Click the Save All icon in the Data Object Editor. Close the Data Object Editor.

 

7.

Click the Save All icon in Design Center.

 

Back to Topic

Back to Topic List

In this tutorial you learned to:

Create, examine, and deploy a time dimension.
Create a schema module.
Import tables, materialized views, and views into the schema module.
Define indexes, constraints, and partitions.
Set configuration properties for tablespaces, compression, and parallel access.
Create a Type 2 slowly changing dimension.

Back to Topic List

Place the cursor over this icon to hide all screenshots.