Note

This option is only available when running an Oracle data warehouse.

By default, a load fails if a source column that is to be loaded does not exist. This default action can be modified by using the Allow Missing Source Columns feature of a load table. When this feature is enabled the load process will check all source columns, and if any are found to be missing will replace those missing columns with a Null value (which can be changed, see below).
On the Source tab of a Load table's Properties there are two check boxes and a drop-down list that we will cover in this section. See the following example:

Fields

Description

Override Source Column/Transformation

  • When this is enabled, the Source Columns edit window is enabled. With this enabled, a load table uses the contents of the Source columns to specify which columns are being extracted during a table load.
  • When this is unselected the load process builds up the statement to select from the source table(s) by looking at the two fields source table and source column and any transformations associated with each column in the load table. If a transformation is present then the transformation overrides the contents of source table/source column. See the following section for more information on transformations.

Note

This box must be unselected to enable Allow Missing Source Columns support.


Allow Missing Source Columns

When this is enabled, the load process will examine every column in the load table. Based on the source table/source column fields associated with each column, it will check to see if the column exists in the source database. If the column exists normal processing will occur. If the column does not exist then a Null will be substituted for the column, and the load will proceed.
If one or more columns are found to be missing the load process reports this situation. The status level of this reporting can be set via the Exit status drop-down. See the following topic. In all cases the load will be deemed to have been successful provided that no other errors occur.
Often Null values are not desirable in a data warehouse. This Null value can be replaced by some other value by means of a During or After transformation. For example, a During transformation, as shown below, set on a missing column called 'State' will replace the Null with the value 'N/A'.
NVL(state,'N/A')

Exit Status When Missing Columns

If columns are found to be missing as a result of a Non Mandatory check then a message is recorded in the Audit trail and against the task if running in the Scheduler. The drop-down list of the same name allows the choice of action in the event of a missing column or columns. The choices are:

ChoiceImpact
SuccessThe message informing that columns are missing uses an information status.
WarningThe message issued is a warning. The table load will be identified as successful but with warnings.
ErrorThe message issued is an error. The table load will still complete successfully albeit with an error message.

This drop-down list is only available if the Non Mandatory Source Columns check-box is ticked.

Limitations of Missing Column check

The check for missing columns will only examine and check those columns that are recorded in the source table and source column fields of a load table's column Properties. Therefore if a source column is used in a transformation or join, but is not recorded in the two fields mentioned above it will not be checked.
If a column is used in a transformation, it should have the same usage (name and case) as is found in the source column and source table fields.
This check has no upper limit on the number of missing columns. All columns can be missing and the load will still succeed.


  • No labels