Mapping generation for Data Vault demo: part 4 (history area)

Now that the staging area tables and the mappings from source to staging are created, it’s time for the next step: the history area. In this step the source data is archived in a SCD2 way.

The source tcl file for the staging to history mappings can be downloaded here: 2_staging_to_history_generation. When run (source command!) the script will ask the familiar questions about replacing ETL metadata. The script executes the following steps, specific elements are highlighted and commented.

                                                                    
# Name:         OWB Staging Area generation TCL                         
# Author:       Roelant Vos                                          
# Purpose:     Generate history mappings                                                                                             

source C:/TCL/settings.tcl

puts “”
puts “nConnecting to OWB Metadata Repository, project: $projectn”

##################################
# General connection information #
##################################
OMBDISCONNECT
OMBCONNECT $OWB_repository/$OWB_password@$OWB_server:1521:$environment
OMBCC ‘$project’

set ModList “$StgMod”
puts “”
puts “Reading base module $ModList for processing into $HisMod”
puts “”

######################################
#  History Area altering options     #
######################################
puts -nonewline stdout “Do you want replace existing tables? (Y/N)  => “; gets stdin ReplaceExistingTables;
puts “”
puts -nonewline stdout “Do you want replace existing mappings? (Y/N)  => “; gets stdin ReplaceExistingMappings;
puts “”
puts -nonewline stdout “Do you want replace existing sequences? (Y/N)  => “; gets stdin ReplaceExistingSequences;
puts “”
 
###########################
# History Area generation #
###########################
foreach ModName $ModList {

      puts “Working on: $ModName”
      puts “”
      puts “The staging prefix is set to: $HistoryPrefix”
      puts “”

       ####################
      # Table generation #
      ####################

      # Change context to module <$ModName>
      OMBCC ‘$HisMod’

      set AllTablesListHis [ OMBLIST TABLES ]
      set AllMappingsListHis [ OMBLIST MAPPINGS ]
      set AllSequencesListHis [ OMBLIST SEQUENCES ]

      OMBCC ‘..’
      OMBCC ‘$ModName’

      # Process all the Staging-tables in module <$ModName>
      set TabList [ OMBLIST TABLES ‘$StagingPrefix.*’ ]

      foreach TabName $TabList {
         puts “$project/$ModList/$TabNamer”

         set TabShort [string range $TabName 2 end]
         set TgtTable $HistoryPrefix$TabShort

        if { [ lsearch $AllTablesListHis $TgtTable ] >= 0 && $ReplaceExistingTables == “N” || $ReplaceExistingTables== “n” } {
           puts “Table: $TgtTable already exists –> SKIPPEDn”
        } else {
            puts ”   —> $project/$HisMod/$TgtTabler”

            OMBCC ‘..’
            OMBCC ‘$HisMod’
           
            if { [ lsearch $AllTablesListHis $TgtTable ] != -1 } {
               OMBDROP TABLE ‘$TgtTable’
               puts ”   —> … dropped”
            }
           
            OMBCREATE TABLE ‘$TgtTable’
            puts ”   —> … created”

Here the primary key for the history table is created, its just a meaningless key.

            ## Create a primary key for the history table (HISTORY_ID)
            OMBALTER TABLE ‘$TgtTable’ ADD COLUMN ‘HISTORY_ID’ SET PROPERTIES (DATATYPE)
                     VALUES (‘NUMBER’)

            OMBCC ‘..’
            OMBCC ‘$ModList’

          set columnList [ OMBRETRIEVE TABLE ‘$TabName’ GET COLUMNS ]

In order for the target history tables to be created we still need to retrieve all the data dictionary information including datatype, lenght and precision. This information is used to create the target table which essentially has the same structure as the source one.

          foreach columnName $columnList {
             puts ”   —> … added $columnName to $TgtTable”
             set columnDataType [ OMBRETRIEVE TABLE ‘$TabName’ COLUMN ‘$columnName’ GET PROPERTIES (DATATYPE) ]
             set columnDataNull [ OMBRETRIEVE TABLE ‘$TabName’ COLUMN ‘$columnName’ GET PROPERTIES (NOT_NULL) ]
             set columnDataLength ‘N/A’

             if {“$columnDataType” == “VARCHAR2”} {
                set columnDataLength [ OMBRETRIEVE TABLE ‘$TabName’ COLUMN ‘$columnName’ GET PROPERTIES (LENGTH) ]
                OMBCC ‘..’
                OMBCC ‘$HisMod’
                OMBALTER TABLE ‘$TgtTable’ ADD COLUMN ‘$columnName’ SET PROPERTIES (DATATYPE, LENGTH, NOT_NULL)
                  VALUES (‘$columnDataType’, $columnDataLength, $columnDataNull)
             }

             if {“$columnDataType” == “NUMBER”} {
               set columnDataPrec [ OMBRETRIEVE TABLE ‘$TabName’ COLUMN ‘$columnName’ GET PROPERTIES (PRECISION) ]
             set columnDataScale [ OMBRETRIEVE TABLE ‘$TabName’ COLUMN ‘$columnName’ GET PROPERTIES (SCALE) ]
               OMBCC ‘..’
               OMBCC ‘$HisMod’
               OMBALTER TABLE ‘$TgtTable’ ADD COLUMN ‘$columnName’ SET PROPERTIES (DATATYPE, PRECISION, SCALE, NOT_NULL)
                 VALUES (‘$columnDataType’, $columnDataPrec, $columnDataScale, $columnDataNull)
             }
           
             if {“$columnDataType” == “DATE”||”$columnDataType” == “MDSYS.SDO_GEOMETRY” } {
               OMBCC ‘..’
               OMBCC ‘$HisMod’
               OMBALTER TABLE ‘$TgtTable’ ADD COLUMN ‘$columnName’ SET PROPERTIES (DATATYPE) VALUES (‘$columnDataType’)
             }
             OMBCC ‘..’
             OMBCC ‘$ModList’
           }

           OMBCC ‘..’
           OMBCC ‘$HisMod’

           OMBALTER TABLE ‘$TgtTable’
             ADD COLUMN ‘START_DATETIME’ SET PROPERTIES (DATATYPE) VALUES (‘DATE’)
             ADD COLUMN ‘END_DATETIME’ SET PROPERTIES (DATATYPE) VALUES (‘DATE’)
             ADD COLUMN ‘ACTUAL’ SET PROPERTIES (DATATYPE, LENGTH) VALUES (‘VARCHAR2’,30)

           puts ”   —> … added START and END datetime valid to $TgtTable”

           OMBCC ‘..’
           OMBCC ‘$ModList’

In this step the source primary key is retrieved so it can be made part of a ‘marking’ unique key constraint (disabled / undeployable). This can be used later on to determine the original source key.

           set PrimaryKeyCols “”
           set PrimaryKeyFinal “”
           set PrimaryKey [OMBRETRIEVE TABLE ‘$TabName’ GET PRIMARY_KEY]

           if {$PrimaryKey !=[] } {
              set PrimaryKeyCols [OMBRETRIEVE TABLE ‘$TabName’ PRIMARY_KEY ‘$PrimaryKey’ GET COLUMNS]
              foreach KeyPart $PrimaryKeyCols {      
                 set PrimaryKeyFinal “$PrimaryKeyFinal ‘$KeyPart'”         
              }
     
              set PrimaryKeyCols [string trim $PrimaryKeyFinal]
              set PrimaryKeyCols [string map {” ” ,} $PrimaryKeyCols]
              set PrimaryKeyCols [string map {, “, “} $PrimaryKeyCols]           
           }

           OMBCC ‘..’
           OMBCC ‘$HisMod’

If there is  a primary key it will be implemented to a composite unique key together with the end datetime.

           if {$PrimaryKey !=[] } {
             puts “”
             puts ”        Add primary key to table $TgtTable”
             set newpk [string range PK_$TgtTable 0 29]
             OMBALTER TABLE ‘$TgtTable’ ADD PRIMARY_KEY ‘$newpk’ SET REF COLUMNS (‘HISTORY_ID’)
             puts ”   —> … added Primary Key to $TgtTable”
            
             puts ”        Add unique key to table $TgtTable”         
             set newuk [string range UK_$TgtTable 0 29]
             set UniqueKeyCols “$PrimaryKeyCols, ‘END_DATETIME'”
             OMBALTER TABLE ‘$TgtTable’ ADD UNIQUE_KEY ‘$newuk’ SET REF COLUMNS ($UniqueKeyCols)
             OMBALTER TABLE ‘$TgtTable’ MODIFY UNIQUE_KEY ‘$newuk’ SET PROPERTIES (DEPLOYABLE) VALUES (‘FALSE’)
             puts ”   —> … added Unique Key (columns: $UniqueKeyCols) $newuk to $TgtTable”
             puts “”    
             OMBCOMMIT   
           }              
        }

      #######################
      # Sequence generation #
      #######################

A sequence is created for every table.

      set SequenceName [string range SQ_$TabShort 0 29]

      if { [ lsearch $AllSequencesListHis $SequenceName ] >= 0 && $ReplaceExistingSequences == “N” || $ReplaceExistingSequences == “n”} {
         puts “Sequence: $SequenceName already exists –> SKIPPEDn”
      } else {
         OMBCC ‘..’
         OMBCC ‘$HisMod’
               
         if { [ lsearch $AllSequencesListHis $SequenceName ] != -1 } {
           OMBDROP SEQUENCE ‘$SequenceName’
            puts ”   —> … sequence $SequenceName dropped”
         }
                       
      OMBCREATE SEQUENCE ‘$SequenceName’
      puts ”   —> … sequence $SequenceName created”
      }

      ######################
      # Mapping generation #
      ######################

     set MappingName M_$TgtTable
      set MappingName [string range $MappingName 0 29]

     if { [ lsearch $AllMappingsListHis $MappingName ] >= 0 && $ReplaceExistingMappings == “N” || $ReplaceExistingMappings == “n”} {
        puts “Mapping: $MappingName already exists –> SKIPPEDn”
     } else {
        puts “Mapping: start generation of $MappingName”
         OMBCC ‘..’
         OMBCC ‘$HisMod’
         set TgtTable_N [string range $MappingName 0 27]
         set TgtTable_N $TgtTable_N_N
         set TgtTable_C [string range $MappingName 0 26]
         set TgtTable_C $TgtTable_C_C

         OMBCC ‘..’
         OMBCC ‘$ModList’

         set PrimaryKey [OMBRETRIEVE TABLE ‘$TabName’ GET PRIMARY_KEY]

         if {$PrimaryKey !=[] } {
            set PrimaryKeyCols [OMBRETRIEVE TABLE ‘$TabName’ PRIMARY_KEY ‘$PrimaryKey’ GET COLUMNS]
         }

         OMBCC ‘..’
         OMBCC ‘$HisMod’

         # Basic mapping

The main mapping is created.
         
         if { [ lsearch $AllMappingsListHis $MappingName ] != -1 } {
           OMBDROP MAPPING ‘$MappingName’
        }
         puts “Mapping: create basics for $MappingName”
         OMBCREATE MAPPING ‘$MappingName’
          SET PROPERTIES (DESCRIPTION)
          VALUES (‘History Area mapping for loading the source table $TabName’)
          ADD TABLE OPERATOR ‘$TabName’
              BOUND TO TABLE ‘/$project/$ModName/$TabName’
          ADD TABLE OPERATOR ‘$TgtTable_N’ SET PROPERTIES (LOADING_TYPE,MATCH_BY_CONSTRAINT) VALUES (‘INSERT’,’NO_CONSTRAINTS’)
              BOUND TO TABLE ‘/$project/$HisMod/$TgtTable’
          ADD JOINER OPERATOR ‘JOINER’
          ADD TABLE OPERATOR ‘$TgtTable’
              BOUND TO TABLE ‘/$project/$HisMod/$TgtTable’
          ADD FILTER OPERATOR ‘FILTER_ACTUAL_RECORDS’
          ADD TABLE OPERATOR ‘$TgtTable_C’ SET PROPERTIES (LOADING_TYPE,MATCH_BY_CONSTRAINT) VALUES (‘UPDATE’,’$newpk’)
              BOUND TO TABLE ‘/$project/$HisMod/$TgtTable’
          ADD CONSTANT OPERATOR ‘ACTUAL_CONSTANTS_HISTCLOSE’
             SET PROPERTIES (DESCRIPTION)
             VALUES (‘Value for column ACTUAL in $TgtTable_C’)
          ADD ATTRIBUTE ‘ACTUAL’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘ACTUAL_CONSTANTS_HISTCLOSE’
             SET PROPERTIES (DATATYPE, LENGTH, EXPRESSION)
             VALUES (‘VARCHAR2′, 1, ”’N”’)
          ADD CONSTANT OPERATOR ‘ACTUAL_CONSTANTS_HISTNEW’
             SET PROPERTIES (DESCRIPTION)
             VALUES (‘Value for column ACTUAL in $TgtTable_N’)
          ADD ATTRIBUTE ‘ACTUAL’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘ACTUAL_CONSTANTS_HISTNEW’
             SET PROPERTIES (DATATYPE, LENGTH, EXPRESSION)
             VALUES (‘VARCHAR2′, 1, ”’Y”’)
          ADD CONNECTION FROM ATTRIBUTE ‘ACTUAL’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘ACTUAL_CONSTANTS_HISTNEW’
             TO ATTRIBUTE ‘ACTUAL’ OF GROUP ‘INOUTGRP1’ OF OPERATOR ‘$TgtTable_N’
          ADD CONSTANT OPERATOR ‘PROCESS_CONSTANTS’
             SET PROPERTIES (DESCRIPTION)
             VALUES (‘Process run information for mapping $MappingName’)
          ADD ATTRIBUTE ‘PROCESS_CODE’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘PROCESS_CONSTANTS’
             SET PROPERTIES (DATATYPE, LENGTH, EXPRESSION)
             VALUES (‘VARCHAR2′, 30, ”’$MappingName”’)
                     ADD ATTRIBUTE ‘SYSTEM_DATE’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘PROCESS_CONSTANTS’
                        SET PROPERTIES (DATATYPE, EXPRESSION)
                        VALUES (‘DATE’, ‘sysdate’)
                     ADD ATTRIBUTE ‘DATUM_VERRE_TOEKOMST’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘PROCESS_CONSTANTS’
                        SET PROPERTIES (DATATYPE, EXPRESSION)
                        VALUES (‘DATE’, ‘to_date(”31-12-9999”,”DD-MM-YYYY”)’)
                     ADD ATTRIBUTE ‘WORKFLOW_ID’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘PROCESS_CONSTANTS’
                        SET PROPERTIES (DATATYPE, EXPRESSION)
                        VALUES (‘NUMBER’, ‘”FNC_GET_WORKFLOW_ID”()’)
                    ADD PREMAPPING_PROCESS OPERATOR ‘PROC_MD_PROCESS_MAPPING_START’
                      BOUND TO PROCEDURE ‘/$project/$IntMod/PROC_MD_PROCESS_MAPPING_START’
                    ADD POSTMAPPING_PROCESS OPERATOR ‘PROC_MD_PROCESS_MAPPING_END’
                      BOUND TO PROCEDURE ‘/$project/$IntMod/PROC_MD_PROCESS_MAPPING_END’
                   ADD SEQUENCE OPERATOR ‘$SequenceName’ BOUND TO SEQUENCE ‘/$project/$HisMod/$SequenceName’
                 ADD SPLITTER OPERATOR ‘SPLIT_NEW_OR_HISTORY’
                     SET PROPERTIES (DESCRIPTION)
                     VALUES (‘Flow is split into new or history records’)
                 ADD EXPRESSION OPERATOR ‘EXPRESSION_DETERMINE_CHANGES’
                     SET PROPERTIES (DESCRIPTION)
                     VALUES (‘This expression checks the new and existing information in order to determine changes’)
                 ADD CONNECTION FROM GROUP ‘OUTGRP1’ OF OPERATOR ‘PROCESS_CONSTANTS’
                     TO GROUP ‘INGRP1’ OF OPERATOR ‘SPLIT_NEW_OR_HISTORY’
                 ADD CONNECTION FROM ATTRIBUTE ‘SYSTEM_DATE’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘SPLIT_NEW_OR_HISTORY’
                     TO ATTRIBUTE ‘START_DATETIME’ OF GROUP ‘INOUTGRP1’ OF OPERATOR ‘$TgtTable_N’
                 ADD CONNECTION FROM ATTRIBUTE ‘DATUM_VERRE_TOEKOMST’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘SPLIT_NEW_OR_HISTORY’
                     TO ATTRIBUTE ‘END_DATETIME’ OF GROUP ‘INOUTGRP1’ OF OPERATOR ‘$TgtTable_N’
                 ADD CONNECTION FROM ATTRIBUTE ‘PROCESS_CODE’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘PROCESS_CONSTANTS’
                     TO ATTRIBUTE ‘INPUT_MAPPING_NAME’ OF GROUP ‘INGRP1’ OF OPERATOR ‘PROC_MD_PROCESS_MAPPING_START’
                 ADD CONNECTION FROM ATTRIBUTE ‘PROCESS_CODE’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘PROCESS_CONSTANTS’
                     TO ATTRIBUTE ‘INPUT_MAPPING_NAME’ OF GROUP ‘INGRP1’ OF OPERATOR ‘PROC_MD_PROCESS_MAPPING_END’
                 ADD ATTRIBUTE ‘CHANGED_FLAG’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘EXPRESSION_DETERMINE_CHANGES’ SET PROPERTIES (DATATYPE, LENGTH)
                     VALUES (‘VARCHAR2’, 1 )
                 ADD ATTRIBUTE ‘NEW_FLAG’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘EXPRESSION_DETERMINE_CHANGES’ SET PROPERTIES (DATATYPE, LENGTH)
                     VALUES (‘VARCHAR2’, 1)
                 ADD ATTRIBUTE ‘HISTORY_ID’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘EXPRESSION_DETERMINE_CHANGES’ SET PROPERTIES (DATATYPE)
                     VALUES (‘NUMBER’)

          OMBALTER MAPPING ‘$MappingName’
            ADD CONNECTION FROM GROUP ‘INOUTGRP1’ OF OPERATOR ‘$TabName’
            TO GROUP ‘INGRP1’ OF OPERATOR ‘JOINER’

          OMBALTER MAPPING ‘$MappingName’
            ADD CONNECTION FROM GROUP ‘INOUTGRP1’ OF OPERATOR ‘$TgtTable’
            TO GROUP ‘INOUTGRP1’ OF OPERATOR ‘FILTER_ACTUAL_RECORDS’

          OMBALTER MAPPING ‘$MappingName’
            ADD CONNECTION FROM GROUP ‘INOUTGRP1’ OF OPERATOR ‘FILTER_ACTUAL_RECORDS’ 
            TO GROUP ‘INGRP2’ OF OPERATOR ‘JOINER’
      
          puts “Mapping: setting up outer join for $MappingName”
         # Determing natural key join
          set FinalJoinString “” 

The source key information is used to determine the join condition

         if { [llength $PrimaryKeyCols] == 1 } {
            set FinalJoinString “INGRP1.$PrimaryKeyCols(+) = INGRP2.$PrimaryKeyCols(+)”
          } else {
            foreach KeyPart $PrimaryKeyCols {
             set FinalJoinString “$FinalJoinString INGRP1.$KeyPart(+)=INGRP2.$KeyPart(+) ANDn” 
            }
            set FinalJoinString [string trim $FinalJoinString]
            set FinalJoinString [string range $FinalJoinString 0 [expr [string length $FinalJoinString] -4] ]
          } 

          OMBALTER MAPPING ‘$MappingName’
            MODIFY OPERATOR ‘JOINER’ SET PROPERTIES (JOIN_CONDITION) VALUES (‘$FinalJoinString’)

          OMBALTER MAPPING ‘$MappingName’
            ADD CONNECTION FROM GROUP ‘OUTGRP1’ OF OPERATOR ‘JOINER’ 
            TO GROUP ‘INGRP1’ OF OPERATOR ‘EXPRESSION_DETERMINE_CHANGES’

          OMBALTER MAPPING ‘$MappingName’
            ADD CONNECTION FROM GROUP ‘OUTGRP1’ OF OPERATOR ‘JOINER’ 
            TO GROUP ‘INGRP1’ OF OPERATOR ‘SPLIT_NEW_OR_HISTORY’

          OMBALTER MAPPING ‘$MappingName’
             DELETE CONNECTION FROM ATTRIBUTE ‘HISTORY_ID’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘JOINER’
             TO ATTRIBUTE ‘HISTORY_ID’ OF GROUP ‘INGRP1’ OF OPERATOR ‘SPLIT_NEW_OR_HISTORY’

          OMBALTER MAPPING ‘$MappingName’
            ADD CONNECTION FROM ATTRIBUTE ‘HISTORY_ID’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘EXPRESSION_DETERMINE_CHANGES’ 
            TO ATTRIBUTE ‘HISTORY_ID’ OF GROUP ‘INGRP1’ OF OPERATOR ‘SPLIT_NEW_OR_HISTORY’
            ADD CONNECTION FROM ATTRIBUTE ‘NEW_FLAG’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘EXPRESSION_DETERMINE_CHANGES’ 
            TO GROUP ‘INGRP1’ OF OPERATOR ‘SPLIT_NEW_OR_HISTORY’
            ADD CONNECTION FROM ATTRIBUTE ‘CHANGED_FLAG’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘EXPRESSION_DETERMINE_CHANGES’ 
            TO GROUP ‘INGRP1’ OF OPERATOR ‘SPLIT_NEW_OR_HISTORY’

          OMBALTER MAPPING ‘$MappingName’
            ADD CONNECTION FROM GROUP ‘OUTGRP1’ OF OPERATOR ‘SPLIT_NEW_OR_HISTORY’
            TO GROUP ‘INOUTGRP1’ OF OPERATOR ‘$TgtTable_N’ BY NAME

          OMBALTER MAPPING ‘$MappingName’
             DELETE CONNECTION FROM ATTRIBUTE ‘HISTORY_ID’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘SPLIT_NEW_OR_HISTORY’
             TO ATTRIBUTE ‘HISTORY_ID’ OF GROUP ‘INOUTGRP1’ OF OPERATOR ‘$TgtTable_N’

          OMBALTER MAPPING ‘$MappingName’
             ADD CONNECTION FROM ATTRIBUTE ‘NEXTVAL’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘$SequenceName’
             TO ATTRIBUTE ‘HISTORY_ID’ OF GROUP ‘INOUTGRP1’ OF OPERATOR ‘$TgtTable_N’

          # Reorder double attributes
          OMBALTER MAPPING ‘$MappingName’
             DELETE CONNECTION FROM ATTRIBUTE ‘PROCESS_CODE’ OF GROUP ‘INOUTGRP1’ OF OPERATOR ‘$TabName’
             TO ATTRIBUTE ‘PROCESS_CODE’ OF GROUP ‘INGRP1’ OF OPERATOR ‘SPLIT_NEW_OR_HISTORY’

          OMBALTER MAPPING ‘$MappingName’
             ADD CONNECTION FROM ATTRIBUTE ‘PROCESS_CODE’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘PROCESS_CONSTANTS’
             TO ATTRIBUTE ‘PROCESS_CODE’ OF GROUP ‘INGRP1’ OF OPERATOR ‘SPLIT_NEW_OR_HISTORY’

          # Rename incoming attributes from source
          set AttList [OMBRETRIEVE MAPPING ‘$MappingName’ OPERATOR ‘$TabName’ GROUP ‘INOUTGRP1’ GET ATTRIBUTES]
          set AttListExpr [OMBRETRIEVE MAPPING ‘$MappingName’ OPERATOR ‘EXPRESSION_DETERMINE_CHANGES’ GROUP ‘INGRP1’ GET ATTRIBUTES]

         # Comparison, depending on multiple key part
         puts “Mapping: start attribute comparison for $MappingName”
         set comparison “CASEn  WHEN”  

 The heart of the history mapping is the SCD2 comparison. This bit of code creates a OWB expression which compares every attribute. The end result is a fully dynamic comparison object between the source and the history table.

         if { [llength $PrimaryKeyCols] == 1 } {
            set comparison “$comparison INGRP1.$PrimaryKeyCols IS NULL AND  INGRP1.$PrimaryKeyCols_1 IS NOT NULL THEN ”D”n  WHEN INGRP1.$PrimaryKeyCols IS NOT NULL AND INGRP1.$PrimaryKeyCols_1 IS NULL THEN ”N””
          }  else {
             foreach KeyPart $PrimaryKeyCols {
               set comparison “$comparison INGRP1.$KeyPart IS NULL AND INGRP1.$KeyPart_1 IS NOT NULL ANDn” 
             }
          set comparison [string trim $comparison]
          set comparison [string range $comparison 0 [expr [string length $comparison] -4] ]
         set comparison “$comparison THEN ”D” n  WHEN “

          foreach KeyPart $PrimaryKeyCols {
            set comparison “$comparison      INGRP1.$KeyPart IS NOT NULL AND INGRP1.$KeyPart_1 IS NULL ANDn” 
          }
          set comparison [string trim $comparison]
          set comparison [string range $comparison 0 [expr [string length $comparison] -4] ]
         set comparison “$comparison THEN ”N””
        }
                
        foreach AttName $AttList {
           OMBCC ‘..’
           OMBCC ‘$ModList’
           set AttrDataType [ OMBRETRIEVE TABLE ‘$TabName’ COLUMN ‘$AttName’ GET PROPERTIES (DATATYPE) ]

           OMBCC ‘..’
           OMBCC ‘$HisMod’

           set AttNameNew $AttName
           set positie [lsearch $AttList $AttName]
           set AttNameHis [lindex $AttListExpr [expr $positie + [llength $AttList] + 1]]

           OMBALTER MAPPING ‘$MappingName’
           MODIFY ATTRIBUTE ‘$AttName’ OF GROUP ‘INGRP1’ OF OPERATOR ‘EXPRESSION_DETERMINE_CHANGES’ RENAME TO ‘$AttNameNew’
          
           if {$AttName!=”PROCESS_CODE” && $AttName!=”LOAD_DATE” && $AttName!=”SOURCE_SYSTEM” && $AttName!=”START_DATETIME” && $AttName!=”END_DATETIME” && $AttName!=”WORKFLOW_ID”} {

            if {$AttrDataType == “NUMBER”} {
              set comp_new_att “nvl(INGRP1.$AttNameNew,-1234554321)”
              set comp_old_att “nvl(INGRP1.$AttNameHis,-1234554321)”
              set comparison_add “n  WHEN $comp_new_att != $comp_old_att THEN ”Y””
             set comparison $comparison$comparison_add
           } elseif {$AttrDataType == “VARCHAR2”} {
              set comp_new_att “nvl(INGRP1.$AttNameNew,”<empty>”)”
              set comp_old_att “nvl(INGRP1.$AttNameHis,”<empty>”)”
              set comparison_add “n  WHEN $comp_new_att != $comp_old_att THEN ”Y””
               set comparison $comparison$comparison_add
            } elseif {$AttrDataType == “DATE”} {
               set comp_new_att “nvl(INGRP1.$AttNameNew,to_date(”31-12-9999”,”DD-MM-YYYY”))”
               set comp_old_att “nvl(INGRP1.$AttNameHis,to_date(”31-12-9999”,”DD-MM-YYYY”))”
               set comparison_add “n  WHEN $comp_new_att != $comp_old_att THEN ”Y””
                set comparison $comparison$comparison_add 
            } elseif {$AttrDataType == “MDSYS.SDO_GEOMETRY”} {
              set comparison_add “n  WHEN compare_sdo_geometry_objects(INGRP1.$AttNameNew,INGRP1.$AttNameHis) <> ”EQUAL” THEN ”Y””
             set comparison $comparison$comparison_add
              } else {
              set comp_new_att “INGRP1.$AttNameNew”
              set comp_old_att “INGRP1.$AttNameHis”
              set comparison_add “n  WHEN $comp_new_att != $comp_old_att THEN ”Y””
             set comparison $comparison$comparison_add
           }
          }
        }

 set comparison “$comparisonnELSE ”N”nEND”

  puts “Mapping: add process metadata for $MappingNamen”
  OMBALTER MAPPING ‘$MappingName’
     ADD CONNECTION FROM ATTRIBUTE ‘WORKFLOW_ID’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘PROCESS_CONSTANTS’
                    TO ATTRIBUTE ‘WORKFLOW_ID’ OF GROUP ‘INOUTGRP1’ OF OPERATOR ‘$TgtTable_C’
     ADD CONNECTION FROM ATTRIBUTE ‘SYSTEM_DATE’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘PROCESS_CONSTANTS’
                    TO ATTRIBUTE ‘END_DATETIME’ OF GROUP ‘INOUTGRP1’ OF OPERATOR ‘$TgtTable_C’
     ADD CONNECTION FROM ATTRIBUTE ‘ACTUAL’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘ACTUAL_CONSTANTS_HISTCLOSE’
                    TO ATTRIBUTE ‘ACTUAL’ OF GROUP ‘INOUTGRP1’ OF OPERATOR ‘$TgtTable_C’
     ADD CONNECTION FROM ATTRIBUTE ‘HISTORY_ID’ OF GROUP ‘OUTGRP2’ OF OPERATOR ‘SPLIT_NEW_OR_HISTORY’
                    TO ATTRIBUTE ‘HISTORY_ID’ OF GROUP ‘INOUTGRP1’ OF OPERATOR ‘$TgtTable_C’

   OMBALTER MAPPING ‘$MappingName’
        MODIFY ATTRIBUTE ‘CHANGED_FLAG’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘EXPRESSION_DETERMINE_CHANGES’ SET PROPERTIES (EXPRESSION)
          VALUES (‘$comparison’ )
        MODIFY ATTRIBUTE ‘HISTORY_ID’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘EXPRESSION_DETERMINE_CHANGES’ SET PROPERTIES (EXPRESSION)
          VALUES (‘nvl( INGRP1.HISTORY_ID, -999)’ )
      # New record determination, depending on multiple key part
      set FinalNewString “CASEn  WHENn” 

      if { [llength $PrimaryKeyCols] == 1 } {
    set FinalNewString “$FinalNewString       INGRP1.$PrimaryKeyCols_1 IS NULLn  THEN ”Y”n  ELSE ”N”nEND”

      } else {
             foreach KeyPart $PrimaryKeyCols {
      set FinalNewString “$FinalNewString      INGRP1.$KeyPart_1 IS NULL AND n” 
             }
             set FinalNewString [string trim $FinalNewString]
             set FinalNewString [string range $FinalNewString 0 [expr [string length $FinalNewString] -4] ]
      set FinalNewString “$FinalNewString n THEN ”Y”n ELSE ”N”nEND”
       } 

      OMBALTER MAPPING ‘$MappingName’
      MODIFY ATTRIBUTE ‘NEW_FLAG’ OF GROUP ‘OUTGRP1’ OF OPERATOR ‘EXPRESSION_DETERMINE_CHANGES’ SET PROPERTIES (EXPRESSION)
          VALUES (‘$FinalNewString’ )
      OMBALTER MAPPING ‘$MappingName’
        MODIFY GROUP ‘OUTGRP1’ OF OPERATOR ‘SPLIT_NEW_OR_HISTORY’
          SET PROPERTIES (SPLIT_CONDITION) VALUES (‘INGRP1.NEW_FLAG =”Y” or (INGRP1.NEW_FLAG =”N” AND INGRP1.CHANGED_FLAG =”Y”)’)
        MODIFY GROUP ‘OUTGRP2’ OF OPERATOR ‘SPLIT_NEW_OR_HISTORY’
          SET PROPERTIES (SPLIT_CONDITION) VALUES (‘INGRP1.CHANGED_FLAG =”D” or (INGRP1.NEW_FLAG =”N” AND INGRP1.CHANGED_FLAG =”Y”)’)
        MODIFY OPERATOR ‘FILTER_ACTUAL_RECORDS’
          SET PROPERTIES (FILTER_CONDITION) VALUES (‘INOUTGRP1.ACTUAL = ”Y”’)

      OMBALTER MAPPING ‘$MappingName’
        MODIFY GROUP ‘INGRP1’ OF OPERATOR ‘SPLIT_NEW_OR_HISTORY’ RENAME TO ‘INPUT_GROUP’
        MODIFY GROUP ‘OUTGRP1’ OF OPERATOR ‘SPLIT_NEW_OR_HISTORY’ RENAME TO ‘NEW_GROUP’
        MODIFY GROUP ‘OUTGRP2’ OF OPERATOR ‘SPLIT_NEW_OR_HISTORY’ RENAME TO ‘HISTORY_GROUP’
        MODIFY GROUP ‘INGRP1’ OF OPERATOR ‘EXPRESSION_DETERMINE_CHANGES’ RENAME TO ‘INPUT_GROUP’
        MODIFY GROUP ‘OUTGRP1’ OF OPERATOR ‘EXPRESSION_DETERMINE_CHANGES’ RENAME TO ‘OUTPUT_GROUP’

      OMBALTER MAPPING ‘$MappingName’
        MODIFY ATTRIBUTE ‘END_DATETIME’ OF GROUP ‘INOUTGRP1’ OF OPERATOR ‘$TgtTable_C’ SET PROPERTIES (MATCH_COLUMN_WHEN_UPDATING_ROW) VALUES (‘NO’)

      OMBALTER MAPPING ‘$MappingName’
        MODIFY ATTRIBUTE ‘END_DATETIME’ OF GROUP ‘INOUTGRP1’ OF OPERATOR ‘$TgtTable_C’ SET PROPERTIES (MATCH_COLUMN_WHEN_DELETING_ROW) VALUES (‘NO’)
       
      OMBALTER MAPPING ‘$MappingName’
        MODIFY ATTRIBUTE ‘END_DATETIME’ OF GROUP ‘INOUTGRP1’ OF OPERATOR ‘$TgtTable_C’ SET PROPERTIES (LOAD_COLUMN_WHEN_UPDATING_ROW) VALUES (‘YES’)       

       puts ”   —> … mapping $MappingName generated”
       puts “”
      }
   }
}

puts “Generation ready”

OMBSAVE
OMBDISCONNECT

 
Roelant Vos

Roelant Vos

You may also like...

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.