Create and Maintain Source to Target Map in OWB

Uli Bethke ETL, Oracle Warehouse Builder

One of the tasks during the ETL design process is to create a Source to Target Map. This maps fields from the source system to fields in the target data warehouse. Very often this is done in Excel.

This approach has two disadvantages. First of all the definitions in the source to target map need to be transfered to your target data warehouse. At best this is a script based effort that takes the definitions in your map and automatically creates target tables, columns, indexes, comments/transformation rules etc. in your data warehouse. The other disadvantage is that all the source to target meta data is stored externally to the warehouse in Excel. Typically the source to target map is not updated once the target tables have been created in the DW.

User Defined Properties (UDP) in Warehouse Builder come to the rescue. UDPs allow you to extend the OWB repository, i.e. you can define additional properties on the base objects in Warehouse Builder.

owb udp

As per documentation: "To define custom objects and properties in Warehouse Builder, you must use the scripting utility Oracle Metabase (OMB) Plus. After you specify the UDOs and UDPs through scripting, you can then create instances of those objects either through scripting or using the graphical user interface (GUI)."

For the source to target map you can create custom properties on the column object, e.g. the source of the column, the source data type, transformation rules etc..

This is done via OMB+, e.g. to define a new property on the column object named Source Table you issue the following command

OMBSWITCHMODE SINGLE_USER_MODE
to switch to single user mode


OMBREDEFINE CLASS_DEFINITION 'COLUMN'
ADD PROPERTY_DEFINITION 'UDP_b_SRC_TBL' SET PROPERTIES
(TYPE, BUSINESS_NAME) VALUES
('STRING', 'Source Table')

OMBSAVE
to persist your changes in the OWB repository

OMBSWITCHMODE MULTIPLE_USER_MODE
to go back to multi user mode.

You can now enter values for your source to target map through the Design Center: Right click on a table > Properties > User Defined tab > Expand Columns > Click on Column... et voila, enter your values. The values you enter are stored in the OWB repos and can also be retrieved from there.

In order to retrieve the Source to Target Map from the OWB repository I have written some SQL that you can adapt to your needs.

You could use this to create a derived table in a Business Objects Universe to create your source to target map via Web Intelligence.