7.2 Data Movement (DM) Utility

The Data Movement (DM) utility is used to transfer data from one Oracle data source to another Oracle data source.

This utility can be used for moving the data from the landing area to the consolidation area. And, then consolidation area to UI (KDD Case tables, example KDD_CASE_CUSTOMER, KDD_CASE_ACCOUNT, and so on.

  • Data movement across the source and target tables residing in two different databases. For example, the source table on database1 and target table on database2.
  • Data movement across the source and target tables residing in two different schema in the same database. For example, source table on schema1.table1 and target table on schema2.table2.
  • Data movement across the source and target tables residing in the schema in the same database. For example, source table on schema1.table1 and target table on schema1.table2.

The following data transfer modes are available:

  • DI: In this mode, the Utility fetches the data from the source table/s based on the metadata available in the FCC_DM_DEFINITIONandFCC_DM_MAPPINGtables.Data is removed from the target is based on its PK/UK. Then the data is moved into the source table.
  • IS: In this mode, Utility inserts the data from the selected table of the source totarget.
  • MI: In this mode, Utility performs insert or update operations. If data is not available in the target table, then Insert operation is performed. If data is available in the target table, then Update operation is performed.

DM Metadata Tables

  • FCC_DM_DEFINITION: Stores the definition of SQL conditions that is used to fetch the data from the source database.
    The structure of the DM definition table is as follows:

    Table 7-1 FCC_DM_DEFINITION (Metadata Table)

    Column Name Primary Key Column Type Nullable
    DM_GROUP_ID * NUMBER(10) No
    DM_ID NUMBER(10) No
    DM_CODE VARCHAR2(100) Yes
    DM_DESCRIPTION VARCHAR2(4000) Yes
    V_SOURCE_DATASET CLOB Yes
    V_TARGET VARCHAR2(30) Yes
    V_SRC_FILTER VARCHAR2(4000) Yes
    V_TARG_FILTER VARCHAR2(4000) Yes
    V_TARGET_DATASET CLOB Yes
    V_SELECT_HINT VARCHAR2(500) Yes
    V_PARALLEL_DEGREE VARCHAR2(3) Yes
  • DM_GROUP_ID: grouping code of DM definition. DM definitions can be grouped to pull the data together.
  • DM_ID: unique identification ID of DM definition.
  • DM_CODE: unique name of DM definition.
  • DM_DESCRIPTION: description of DM definition.
  • V_SOURCE_DATASET: name of the Source table. It can contain the join conditions with multiple source tables and conditions associated with it. All source tables must be put under the curly bracket ‘{’. For example: {EMP_PHON}
  • V_TARGET: name of Target table.
  • V_SRC_FILTER: source filter that contains the filter condition for the source database.
  • For example:
    EMP_PHON.DATA_DUMP_DT = $MISDATE AND EMP_PHON.PRCSNG_BATCH_NM IN (SELECT FCC_BATCH_DATAORIGIN.V_DATA_ORIGIN FROM FCC_BATCH_DATAORIGIN WHERE FCC_BATCH_DATAORIGIN.N_RUN_SKEY = $RUNSKEY)
  • V_TARG_FILTER: filter condition in the target database.
  • V_TARGET_DATASET: contains the join condition with multiple target tables and filter condition associated with it.
    For example:
    INNER JOIN FCC_EMPLOYEE_LOOKUP ON FCC_EMPLOYEE_LOOKUP.EMP_INTRL_ID = [EMP_- PHON].EMP_INTRL_ID

    Table 7-2 FCC_DM_DEFINITION (Metadata Table 1)

    DM_GROUP_ID DM_ ID DM_CODE DM_DESCRIPTION V_SOURCE_DATASET V_TARGET V_SRC_FILTER V_TARG_ FILTER V_TARGET_DATASET
    1 1 BD_EMP_PHON T2T_FCCM_PROD_EMP_PHON {EMP_PHON} EMP_PHON.DATA_DUMP_DT = $MISDATE AND EMP_PHON.PRCSNG_BATCH_NM IN (SELECT FCC_BATCH_DATAORIGIN.V_DATA_ORIGIN FROM FCC_BATCH_DATAORIGIN WHERE FCC_BATCH_DATAORIGIN.N_RUN_SKEY =$RUNSKEY) INNER JOIN FCC_EMPLOYEE_LOOKUP ON FCC_EMPLOYEE_LOOKUP.EMP_INTRL_ID =[EMP_PHON].EMP_INTRL_ID
  • FCC_DM_FIELD_MAPPING:stores the field-to-field mapping details of data from the source to the target table.
    The structure of the DM field mapping table is as follows:

    Table 7-3 FCC_DM_Field_Mapping (Metadata table)

    Column Name Column Type Nullable
    DM_ID NUMBER(10) No
    V_ENTITY_NAME VARCHAR2(50) Yes
    V_FIELD_NAME VARCHAR2(50) Yes
    V_SRC_DATA_TYPE VARCHAR2(50) Yes
    V_FIELD_FORMAT VARCHAR2(50) Yes
    F_IS_NULL_ALLOWED CHAR(1) Yes
    V_SQL_EXPRESSION VARCHAR2(4000) Yes
    V_TARGET_ENTITY_NAME VARCHAR2(30) Yes
    V_TARGET_FIELD_NAME VARCHAR2(50) Yes
    V_SQL_FUNCTION VARCHAR2(500) Yes
    V_NULL_IF VARCHAR2(50) Yes
    V_DEFAULT_IF VARCHAR2(50) Yes
    V_TARG_DATA_TYPE VARCHAR2(50) Yes
    V_EXECUTION_SPACE VARCHAR2(5) Yes
  • DM_ID: DM ID from FCC_DM_DEFINITION table.
  • V_ENTITY_NAME: Name of Source table.

    Note:

    It can contain expression and target table if source value is populating from any SQL expression or a particular column from target table.

    For example: EXPRESSION, CM_EMP_SEQ.NEXTVAL.

  • V_FIELD_NAME: Name of Source field

    Note:

    It can contain target filed name if the value is coming from the target table.
  • V_SRC_DATA_TYPE: Data type of Source field.
  • V_FIELD_FORMAT:Data type format of the source field. Example: mm-dd-yyyy
  • F_IS_NULL_ALLOWED: Set this flag as yes if is Null allowed.
  • V_SQL_EXPRESSION: Type of SQL expression.

    For example: Case statement, Sequences, and so on. It can contain direct variable from the application interface.

    For example: $MISDATE (MIS date passed from the external interface for source filter).

  • V_TARGET_ENTITY_NAME: Name of Target table
  • V_TARGET_FIELD_NAME: Name of Target field.
  • V_TARG_DATA_TYPE: Data type of target field.
    For example:

    Table 7-4 Sample Table

    DM_ID V_ENTITY_NAME V_FIELD_NAME V_SRC_DATA_TYPE F_IS_NULL_ALLOWED V_SQL_EXPRESSION V_TARGET_ENTITY_NAME V_TARGET_FIELD_NAME V_TARG_DATA_TYPE V_EXECUTION_SPACE
    1 EXPRE SSION

    DATA_ DUMP_DT

    		 DATE Y $MIS-DATE FCC_EMP_PHON MIS_DATE DATE Trg
    1 EMP_PHON EMP_I NTRL_ ID VARCHAR2(200) Y FCC_EMP_PHON EMP_INTRL_ID VARCHAR2( 200) Src
    1 EXPRE SSION

    EMP_ PHON_SEQ_ID

    		 NUMBER(22,0) Y CM_EMP_PHON_ SEQ.NEXTVAL FCC_EMP_PHON EMP_PHON_SEQ_ID NUMBER(2 2,0) Trg
    1 EMP_PHON

    PHON_EXT_NB

    		 VARCHAR2(2 0) Y FCC_EMP_PHON PHON_ EXT_NB VARCHAR2( 20) Src
    1 EXPRE SSION PHONE_TYPE VARCHAR2(2 0) Y ‘Business’ FCC_EMP_PHON

    PHONE_TYPE

    		 VARCHAR2( 20) Src

DM Audit and Error Details Tables

FCC_DM_AUDIT: stores the execution order of each run and SQL execution in source and target. FCC_DM_ERROR_DETAILS: stores all the errors that occurred in the source or target database.