Fixing No-match Rows after synchronize (Oracle Warehouse Builder)

For a product we are using Oracle Warehouse Builder for building and maintaining the ETL process. From time to time changes are made to the source and target tables to meet the requirements of our customers.

To have our mappings still validate without warnings after table definitions in the repository have changed, mapping operators (source/target table, lookups, etc.) to these tables will have to be synchronized with the definition stored in the repository.

During some recent changes we noticed that after performing a synchronize action on a key lookup operator, the settings for ‘No-match Rows’ got cleared out.

So instead of opening every mapping hand-by-hand, I wrote a OMBPlus/tcl script to go through all of the mapping objects within a project and for each lookup operator within the mappings list the default values for columns and provides a warning message when for none of the columns an default value has been set. This validation will only be performed in the cases where the property Create No-match Row of the out group of a lookup operator has been checked.

Start either OMBPlus.bat or OMBPlus.sh, depending on your platform of choice.

Connect to your repository and the proper workspace

# Connect to OWB repository
OMBCONNECT <user>/<password>@<host>:<port>:<sid> USE WORKSPACE '<owner>.<workspace>'

and switch to the project that needs checking

# Switch context to project to be checked
OMBCC '<project>'

The following piece of OMBPlus/tcl code will produce the report (stored in c:/temp/report.txt, adjust for you environment). You can also get this source for the script using this link. 1

# Open output file
set outfile [open "c:/temp/report.txt" w]

# Initial message
puts "Busy..."

# Loop through all Oracle Module within current project
foreach modName [OMBLIST ORACLE_MODULES] {
    # Loop through all Mappings within current project
    foreach mapName [OMBLIST MAPPINGS '${modName}/.*'] {
        # Get lookup operators
        set operNames [OMBRETRIEVE MAPPING '${modName}/${mapName}' GET LOOKUP OPERATORS]

        # Output name for current mapping and traverse lookup operators
        if { $operNames != "" } {
            # Display module and mapping name
            puts $outfile "======================================================================"
            puts $outfile "Mapping: ${modName}/${mapName}"

            # Loop through all lookup operators within current mapping
            foreach operName $operNames {
                # Determine name of the outgroup
                set grpName [OMBRETRIEVE MAPPING '${modName}/${mapName}' OPERATOR '${operName}' GET OUTPUT GROUPS]

                # Get setting for 'No-match Rows'
                set propNoMatchRows [OMBRETRIEVE MAPPING '${modName}/${mapName}' \
                    OPERATOR '${operName}' \
                    GROUP '${grpName}' \
                    GET PROPERTIES( CREATE_NO_MATCH_ROW )]

                if { $propNoMatchRows } {
                    # Output name of lookup operator
                    puts $outfile "  Lookup = ${operName}"

                    # Counter for populated default values
                    set attrCount 0

                    # Loop through all output attributes of the lookup operator
                    foreach attrName [OMBRETRIEVE MAPPING '${modName}/${mapName}' OPERATOR '${operName}' GROUP '${grpName}' GET ATTRIBUTES] {
                        # Get default value for output attribute
                        set attrValue [OMBRETRIEVE MAPPING '${modName}/${mapName}' \
                            OPERATOR '${operName}' \
                            GROUP '${grpName}' \
                            ATTRIBUTE '${attrName}' \
                            GET PROPERTIES( DEFAULT_VALUE )]

                        # Report attribute and its default value (only the not null)
                        if { $attrValue != "NULL" && $attrValue != "{}" } {
                            incr attrCount
                            puts $outfile "    $attrName  =>  $attrValue"
                        }
                    }; # foreach attrName

                    # Output warning when none of the attributes has a default value
                    if { $attrCount == 0 } {
                        puts $outfile "    !!! WARNING: None of the attributes has been provided a default value!!!"
                    }
                }; # Check propNoMatchRows

                puts $outfile ""
            }; # foreach operName

            puts $outfile "======================================================================"
            puts $outfile ""
        }; # Check operNames
    }; # foreach mapName
}; # foreach modName

# Close the output file
close $outfile

# Final message
puts "Done."

This OMBPlus script will produce the following result

======================================================================
Mapping: DWH/DWH_SUP_STG_SUP
  Lookup = DWH_SUPPLIERS_1
    LVL_ID  =>  KEY_INPUTS.UNKNOWN

  Lookup = DWH_SUPPLIERS_2
    LVL_ID  =>  KEY_INPUTS.UNKNOWN

======================================================================

It the example above both lookup operators have an expression for the column LVL_ID, so this is what we expected to see. But for some operators we had something like the following report indicating that the lookup operator STG_CLAIM is missing some settings.

======================================================================
Mapping: STG/STG_CLA_STG_REL
  Lookup = STG_CLAIM
    !!! WARNING: None of the attributes has been provided a default value!!!

  Lookup = STG_RELATIONS
    REL_NR  =>  INGRP1.UNKNOWN

======================================================================

Using this script will help you spent your time on fixing the issues, instead of wasting your time finding the issues.