Validation Transformation in SAP BODS
- Validation transform is used to filter or replace the source dataset based on validation rules to produce desired output dataset.
- This transform is used for NULL checking for mandatory fields, Pattern matching, existence of value in reference table, validate data type, etc.
- The Validation transform can generate two output dataset Pass, Fail.
- The Pass Output schema is identical with the Input schema. The Fail Output schema has two more columns, DI_ERRORACTION and DI_ERRORCOLUMNS.
- In this scenario source table1 have legacy values as shown in Figure 1 but we want SAP values. We are getting SAP values from lookup tables 1,2 and 3 as shown below in Figures 1, 2,3.
- In lookup function, we are getting null values because of not matched records available in source table and lookup table but we don’t want null values in our target table.
- To avoid this problem we have to use validation transformation. The Validation transform can generate two output datasets Pass, Fail based on conditions.
- In pass target table we have clear output and In fail table we have history of failed records. Below are shown the sample figures 5 and 6.
|Legacy GL Account||Legacy Cost center||Legacy Profit Center||Amount|
Figure 1: Sample Source Data 1
|Legacy GL Account||SAP GL Account|
Figure 2: Sample Lookup File 1
|Legacy Cost Center||SAP Cost Center|
Figure 3: Sample Lookup File 2
|Legacy Profit Center||SAP Profit Center|
Figure 4: Sample Lookup File 3
Figures 7, 8 and 9 shows the object hierarchy for validation transformation job, ETL job flow and the way we define the validation rules respectively.
In Validation Transformation:-
Exists in Table option:-
- Exists in table option is used to specify that a column’s value must exist in another table’s column.
- Click the drop-down arrow to opens the window and select column in the provided window.
- This option uses the LOOKUP_EXT function. Define the NOT NULL constraint for the column in the LOOKUP table to ensure the Exists in table condition executes properly.
- Figure 9 shows the validation rules in validation transformation