Oracle Warehouse Builder OWB 11g New Features Part 4!

This is a continuation from part 3. We will continue to explore some new features this time of OWB 11g. Last time we touched on the Siebel Connector and some general connectivity options.

First, you should know that there are a few new features in OWB 11g that were rolled out in the latest OWB 10g R2 patch version 10.2.0.3. As follows:

• Version support for type 2 SCDs.
• Set based Delete in mappings code generation.
• Availability of a merge optimization property for table operators.
• DML error logging to handle error tables.

So what are these and how are they 'new'?

Versioning of Type 2 SCDs (slowly changing dimensions)
Hierarchy versioning supports multiple hierarchies. When the non-leaf level (think of a tree or maybe a bush...the leaves are the furthest from the root or middle) of a dimension contains versioned attributes, the versioning of this non-leaf level results in the versioning of its corresponding child records if they have effective date and expiration date attributes and if you have enabled hierarchy versioning. In essence, what can happen to 'Dad' will happen to his kids too.

Hierarchy versioning is not enabled by default for type 2 SCDs (slowly changing dimensions). When you create a type 2 SCD by using the Create Dimension Wizard, hierarchy versioning is disabled. You must use the Data Object Editor to enable hierarchy versioning.

So what is a Type 2 Slowly Changing Dimension?

In Type 2 Slowly Changing Dimensions, a new record is added to the table to represent the new information. Therefore, both the original and the new record will be present. The new record gets its own primary key.

For example, say we have the following table:

Customer Key	Name	State
1001	Christina	Illinois

After Christina moved from Illinois to California, we add the new information as a new row into the table:

Customer Key	Name	State
1001	Christina	Illinois
1005	Christina	California

Advantages:

- This allows us to accurately keep all historical information.

Disadvantages:

• This will cause the size of the table to grow fast. In cases where the number of rows for the table is very high to start with, storage and performance can become a concern.
• This necessarily complicates the ETL process.

Usage:

• About 50% of the time.

When to use Type 2:

• Type 2 slowly changing dimension should be used when it is necessary for the data warehouse to track historical changes.

Set-based DELETE in Mapping Code Generation
Beginning in Warehouse Builder 10.2.0.3, you can design a mapping with the loading type set to DELETE and code generation set to SET BASED.

Merge Optimization for Table Operators
Beginning in Warehouse Builder 10.2.0.3, you can enable the Merge Optimization property for table operators. When set to true, this property optimizes the invocation or execution of expressions and transformations in the MERGE statement. There is QUITE a bit of information and tips and tricks out there on using the merge operator to the full extent.

DML Error Logging to Handle Error Tables
Prior to release 10.2.0.3, OWB already generated error-handling code for row-based mode.
Beginning in Warehouse Builder 10.2.0.3, DML error logging is available for set-based maps.

Thats is all for now, soon we'll move on to more new features and how OWB11g is easier to install and maintain.