OWB provides SCD Type 2 / Type 3 option(great feature) for a Dimension Operator. For a table operator, mapping needs to be manually created to handle SCD's. Working for one of my client , was assigned a task to generate mappings which would handle SCD TYPE 2 for table operator being used as Dimension.
Well, there were few dimensions with more than 120+ columns and 100 + SCD columns/Trigger History columns.
Yes, Dimensions with 120+ attributes and triggering history on more than 100+ columns.
Never thought would have to go through this but then generating mappings manaully and testing each and every column for SCD is not the best way to go ahead.
Creating an OWB Expert was the only way around it.
It took few weeks to create and test it but now its delievering the goods for the project. Its Smoking !!!!
Expert saved hours of effort on creating and testing all these mappings. :-)
You can download the expert from here. Import expert into your Design repository. Warning - No warranty with the expert, It works for me hope it does it for you.
Here is a quick demo of it ...
The expert executes in the context of a project. Start the expert
--Select the oracle target Module from the drop down list.
- Select target table (dimension) from the drop down list.
-- Select target table (dimension) from the drop down list.
-- Set the target details
1) Provide a mapping name
2) Select Surrogate Key
3) Select Effective date column
4) Select Expiration date column
5) Select Business keys (Natural keys)
6) Select SCD Attributes (Trigger History)
7) Select the sequence name
8) Insert Only Attributes ( Columns for which gets inserted but never updated)
Property “Load Column when updating row” is set to “No”
--Select Source type from the drop down list ( Source can be TABLES/VIEWS/EXTERNAL_TABLES/MATERIALIZED_VIEWS)
If there is no source you can select NO_SOURCE.
-- Select Source Oracle Module
-- Select Source Object
-- Expert will generate a new mapping. Open the new mapping , press the auto layout button. You can validate the mapping before deploying.