User Procedure Language - Complex Data Translations

Document ID : KB000055655
Last Modified Date : 14/02/2018
Show Technical Document Details

INTRODUCTION

During the transformation of data from operational data stores to analytical ones, it may necessary to select, summarize, and convert source data values in order to produce the desired target data. It is rare that the source data can be simply moved to the target "as-is".

InfoRefiner provides such data conversion capabilities in addition to other transformation processing. Several "canned" conversion capabilities are provided to address the more common translation needs. For example, the "NINES-COMPLEMENT" translation can be used to compute the nines complement value of a source field. Another capability provided is the CONVERSION TABLE, used to translate individual source field values to corresponding model field values.

THE PROBLEM

But, on occasion, the data conversion requirements for a data migration can become quite complex. Conditional logic may be necessary to determine the target field value. Or, possibly the target field value must be calculated from the value of other fields. The standard conversions supplied by InfoRefiner cannot satisfy complicated translations such as these.

THE SOLUTION

UPL, or User Procedure Language, was created to satisfy these complex data translation requirements. UPL is a COBOL-like language, allowing the user to define advanced data translations as part of a data migration.

There are two types of UPL available: MFT (Model Field Translation) and SFT (Source Field Translation). A Source Field Translation derives a model field value from the source field values of a single source record. A Model Field Translation derives a model field value from other model field values. This capability enables the use of source field values from multiple sources, provided they are mapped to model fields.

UPL may be Internal (defined within the Dictionary) or External (created outside the Dictionary). Both are implemented as load modules invoked during InfoRefiner processing. For the Internal UPL, InfoRefiner itself manages all source code, compilation, linking, object and load modules. For External UPL however, the user is responsible for all source code and load module management.

Consider the following example. An inventory application stores an on-hand quantity. The user would like a status field on the target which is set based on the on-hand quantity. If the on-hand quantity is 10 or less, the status should be "Reorder". If the on-hand quantity is between 11 and 20, the status should be "Stocked". If the on-hand quantity is 21 or more, the status should be "Overstocked". The UPL to accomplish this translation is shown below.

IF SRCREC-ONHAND-QTY <= 10
    MOVE 'Reorder' to RESULT
ELSE IF SRCREC-ONHAND-QTY <= 20
    MOVE 'Stocked' to RESULT
ELSE MOVE 'Overstocked' to RESULT.

IMPLEMENTATION

To define a SFT from the Source Field Mapping Definition panel, use the "I" line command to add a new model field, and enter the Name, Type, Length and Null Type. To specify an external SFT, scroll the line to the right and simply enter the name in the field provided. To specify an internal SFT, select the line and use the PROC primary command to bring up an edit window where the UPL can be entered. When through, END back to the Source Field Mapping Definition panel. Scroll to the right to view the name assigned to the UPL under the Internal Procedure column.

To define a MFT from the Model Maintenance panel, use the MFLD primary command to access the Model Field Definition panel. Use the ADD command to add a new model field, enter "P" for the Field Type, and then supply the Name, Type, Length and Null Type. When through, END back to the Model Field Definition panel. To specify an external MFT, simply enter the name under the External Procedure heading. To specify an internal MFT, select the line and press ENTER to bring up a window where the UPL can be entered. When through, END back to the Model Field Definition panel. View the name assigned to the UPL under the Internal Procedure column.

During the Validate of a model, any User Procedures are generated into the product UPSLIB, and then linked into the product UPLLIB. The User Procedures are then ready to be used in processing the associated model.

CONCLUSION

In many cases, the standard translations provided by InfoRefiner are sufficient to satisfy the data transformation requirements for a model. But, in those cases where more complex translations are required, UPL provides the power and flexibility necessary to accomplish the conversion. And, because InfoRefiner manages the source code and load modules for Internal UPL, it is much easier to use than traditional user exits.