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 doThe 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 itI’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 upFishing 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:
- 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.
- 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).
- 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:
|Target||Start date (effective date)||[SE]|
|Target||End date (expiration date)||[ED]|