Informatica Metadata Repository tables

Post date: 29-Jul-2010 14:17:13

While browsing through the Informatica rep and opb tables, we are usually stuck up as we do not understand what the widget_id and widget_types are for.

Below is the list of widget_types. This data would help us ease the handling of metadata.

[edit]

What are widget IDs and why do we require them?

Informatica maintains metedata regarding the mappings and its tranformations, sessions, workflows and their statistics. These details are maintained in a set of tables called OPB tables and REP tables.

The widget refers to the types of transformation details stored in these tables.

Port types in a transformation

As the above section details, widget is a transformation in metadata tables. To get the port type from repository table, below is the SQL snippet to use

     select a.widget_id, decode(a.porttype, 1, 'INPUT',                          3, 'IN-OUT',                          2, 'OUT',                          32, 'VARIABLE',                           8, 'LOOKUP',                          10, 'OUT-LOOKUP',                           to_char(a.porttype)) Port_Type      from opb_widget_field a; 

If you want to know the mapping name, then match the widget_id against the widget_id of opb_widget_inst and then pull the mapping_id which can be mapped against mapping_id in opb_mappings table. If you want to know the Folder name, then map the subject_id from opb_mappings to that of subj_id in OPB_SUBJECTS table to get the subject_name.

Expressions and SQL overrides in a transformation

OPB_EXPRESSION is the table that stores all the expressions in metadata. To associate an expression to a field in a transformation, OPB_WIDG_EXPR is the table to be used.

    select g.expression      from opb_widget_expr f,           opb_expression g     where f.expr_id = g.expr_id 

SQL overrides can be in Source Qualifiers and Lookup transformations. To get the SQL Override from metadata, check REP_WIDGET_ATTR.ATTR_VALUE column.