The following is the detail explanation for I,B,D,U,X in the expressions of mappings,
1) This expression transformation evaluates the value of the "Update Flag", a port which is used to take actions (like Insert/Update) downstream in the mapping. The possible values that an Update Flag may take are (I = Insert new record), (B = Insert new record and mark for soft delete), (U = Update existing record), (D = Update existing record and mark for soft delete) and (X = Reject). For more information on the evaluation logic, please see the description of the port "UPDATE_FLG".
For existing records, a given set of columns are compared to decide whether or not to update the record. If you wish to add more columns to this list, you can extend the comparison (which happens in a variable port called "SYSTEMS_COLS_DIFF").
2) One lookup transformation in the mapping is responsible for getting the required columns from the target table which are compared against the corresponding staging area columns to detect new records or identify changed records. The SQL statement of this lookup transformation is overridden with a join with the staging area table to minimize the number of records to be cached by this lookup.
3) We use one filter transformation to filter out records that have an UPDATE_FLG value of 'X'.
4) One Update Strategy transformation we using is the place where actions are taken based on the value of the Update Flag, whether to Insert or Update the record into the datawarehouse. If the Update Flag is (I or B), records are inserted. If it is (U or D), records are updated. For all other values, records are rejected.
5) Description of some columns in the target tables,
DELETE_FLG: This flag indicates the deletion status of the record in the source system. A value of "Y" indicates that the record is deleted from the source system and logically deleted from the data warehouse; a value of "N" indicates that the record is active.
W_INSERT_DT: This column stores the date on which the record was inserted in the data warehouse table.
W_UPDATE_DT:This column stores the date on which the record was last updated in the data warehouse table.
DATASOURCE_NUM_ID:This column is the unique identifier of the source system from which data was extracted. In order to be able to trace the data back to its source, Siebel recommends that you define separate unique source IDs for each of your different source instances.
CREATED_ON_DT: Identifies the date and time when the record was initially created in the source system.
CHANGED_ON_DT: Identifies the date and time when the record was last modified in the source system.
AUX1_CHANGED_ON_DT: This column identifies the last modified date and time of the auxiliary table's record which acts as a source for the current table.
INTEGRATION_ID: This column is the unique identifier of a dimension or fact entity in its source system. In case of composite keys, the value in this column can consist of concatenated parts.
TENANT_ID: This column is the unique identifier for a Tenant in a multi-tenant environment. This would typically be used in an Application Service Provider (ASP) / Software As a Service (SOAS) model.(for our project, I set it as one invariable value ‘DEFAULT’)
