How does the Dimensions get their ROW_WID(s), which are the dimensions PKs? Well it is not done by the database systems. Instead, the PKs are controlled by Informatica using Sequence transformations. These Sequence transformations are within the mplt_SIL_XXX(s) within the SIL mappings. Remember in my prior post on mapplets defined in OBIA, these *_SIL_* mapplets are used for lookups for any FKs for the target table and also for getting and setting the PK(s) or ROW_WID(s). These mapplets are usually after any filter transformations and before the update strategy transformation within the SIL mappings. Even though these transformations are set as reusable does not mean that all the mappings are using only one and the same sequence transformation. Each dimension and updatable facts will have their own sequence transformation and they do not share. However, if the same mapping is being run in parallel for some reason they will use the same sequence transformation and avoid any duplicate PKs in the target table.
These sequences are kept and maintained in the Informatica Repository. In order to find or see them, you can use the following query which also breaks them into their subjects (aka folders):
SELECT OS.SUBJ_NAME
, OM.MAPPING_NAME
, RWI.INSTANCE_NAME
, OWA.ATTR_VALUE
FROM REP_WIDGET_INST RWI
INNER JOIN OPB_MAPPING OM ON (RWI.MAPPING_ID=OM.Mapping_id)
INNER JOIN OPB_WIDGET_ATTR OWA ON (RWI.widget_type=owa.widget_type AND OWA.widget_id=RWI.widget_id )
INNER JOIN OPB_SUBJECT OS ON (OS.SUBJ_ID = OM.SUBJECT_ID)
WHERE 1=1
AND RWI.widget_type_name like ‘Sequence%’
AND OWA.attr_id=4
ORDER BY OM.mapping_name
attr_id in the predicate above is for the transformation’s properties. To find these you would open up designer and edit the sequence transformation->properties. The attr_ids are read top down. Since the “Current Value” is the 4th down from the top it has attr_id = 4.
1 – Start Value
2 – Increment By
3 – End Value
4 – Current Value
5 – Cycle
6 – Number of Cached Values
7 – Reset
8 – Tracing Level
Remember if you copy over a mapping into your custom folder and it includes a sequence transformation, then make sure when you finally setup the DAC and run the new custom mapping, that the sequence transformation is updated with the correct/current value for the target table. Or you may create records in the target with duplicate row_wids! Obviously, full loads will not have this problem as the target table is truncated.
