Thursday, December 11, 2008

OWB Type II Slowly Changing Dimension Expert

The SCD II Expert is an OWB expert that creates mappings. These mappings implement a SCD II load strategy as described in the whitepaper you will find here. To understand what the SCDII Expert does, you can read SCDWhitepaper.pdf. The generated mappings use a loading strategy described in chapter TYPE II SLOWLY CHANGING DIMENSIONS WITH WAREHOUSE BUILDER.

Why should you use the SCDII expert?

It will save you lots of time and money. It creates a plain vanilla OWB working mapping, nothing happing behind the scenes, that you can edit twist to satisfy your needs. No additional licenses needed, contrary to OWB 10g Release 2 and above SCD II option that is very expensive. You need to buy the Advanced ETL option for your development, test and production etc database to use the OWB SCD II functionality.

What does it do

The OWB expert will create a mapping that implements a Type II loading strategy for you. You will save lots of time using it compared to manually constructing the mapping. The expert will guide you through all necessary steps like a wizard and you will have a mapping with all Type II complexity in it in a minute. You can edit the mapping afterwards adding transformation logic if you need to do so.

How to use it

I’ll describe how to use the expert using a sample run. The sample tables etc used in the sample can be downloaded here.

Setting it up

Fishing for a good time starts with throwing in your line (Tom Waits), you will have to import the mdl into your repository. The import will install the expert as a public expert. Before you can run the expert, you need to have in your OWB repository:
  1. A source table. The source table needs to have:
    • A business key (at least one column) that identifies a record in he source table
    • At least one column that triggers history.
  2. A target table. The target table needs to have:
    • A numeric natural/warehouse key that identifies a record in the target table. This key is being fed by a sequence in the SCD II generated mapping.
    • An business key
    • An effective date column
    • An expiration date column.
    • An optional current record indicator (char 1).
  3. A sequence.

Running the expert

To make your life easier, consider these optional steps that add metadata tags to the OWB repository. These tags will be picked up by the expert. You have to put the metadata tags in the columns Description. These tags can be used:
SourceBusiness key[BK]
TargetWarehouse key[WK]
TargetHistory trigger[HT]
TargetStart date (effective date)[SE]
TargetEnd date (expiration date)[ED]
TargetCurrent record[CR]