Create and Maintain Source to Target Map in OWB
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
SELECT entity_name, column_name, position , MIN(CASE WHEN property_name = 'UDP_a_AREA' THEN property_value END) AS AREA, MIN(CASE WHEN property_name = 'UDP_b_SRC_TBL' THEN property_value END) AS SRC_TBL, MIN(CASE WHEN property_name = 'UDP_c_SRC_TBL_COMMENT' THEN property_value END) AS SRC_TBL_COMMENT, MIN(CASE WHEN property_name = 'UDP_d_SRC_COL' THEN property_value END) AS SRC_COL, MIN(CASE WHEN property_name = 'UDP_e_SRC_COL_COMMENT' THEN property_value END) AS SRC_COL_COMMENT, MIN(CASE WHEN property_name = 'UDP_f_JOINS' THEN property_value END) AS JOINS, MIN(CASE WHEN property_name = 'UDP_g_SRC_COL_DATATYPE' THEN property_value END) AS SRC_COL_DATATYPE, MIN(CASE WHEN property_name = 'UDP_h_SRC_COL_LENGTH' THEN property_value END) AS SRC_COL_LENGTH, MIN(CASE WHEN property_name = 'UDP_i_TRANSFORMATION_RULE' THEN property_value END) AS TRANSFORMATION_RULE, MIN(CASE WHEN property_name = 'UDP_j_NULL_REPLACEMENT' THEN property_value END) AS NULL_REPLACEMENT, MIN(CASE WHEN property_name = 'UDP_k_TRG_TBL' THEN property_value END) AS TRG_TBL, MIN(CASE WHEN property_name = 'UDP_l_TRG_COL' THEN property_value END) AS TRG_COL, MIN(CASE WHEN property_name = 'UDP_m_TRG_COL_DATATYPE' THEN property_value END) AS TRG_COL_DATATYPE, MIN(CASE WHEN property_name = 'UDP_n_TRG_COL_LENGTH' THEN property_value END) AS TRG_COL_LENGTH, MIN(CASE WHEN property_name = 'UDP_o_ISSUES' THEN property_value END) AS ISSUES FROM ALL_IV_COLUMNS a JOIN ALL_IV_OBJECT_PROPERTIES b ON (a.column_id = b.object_id) WHERE a.entity_name LIKE 'D_%' OR a.entity_name LIKE 'F_%' GROUP BY entity_name, column_name, position ORDER BY entity_name, position |
You could use this to create a derived table in a Business Objects Universe to create your source to target map via Web Intelligence.
