0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- Pin It Share 0 0 Flares ×

Validation Transformation

Validation Transformation in BODSSummary:-

  • 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 PassFail.
  •  The Pass Output schema is identical with the Input schema. The Fail Output schema has two more columns, DI_ERRORACTION and DI_ERRORCOLUMNS.

Example Scenario:-

  • 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 PassFail 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

1000

C1 P1

300

Figure 1: Sample Source Data 1 

Legacy GL Account SAP GL Account

1000

100000001

Figure 2: Sample Lookup File 1 

Legacy Cost Center SAP Cost Center
C1 C100

Figure 3: Sample Lookup File 2 

Legacy Profit Center SAP Profit Center
P1

1000

Figure 4: Sample Lookup File 3

Validation Transformation in SAP BODS
Figure 5: Sample Target Data1 (PASS)
Validation Transformation in SAP BODS

Figure 6: Sample Target Data2 (FAIL)

 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.

Validation Transformation in BODS 1
Figure 7: Validation Transformation Object Hierarchy
Validation Transformation in SAP BODS
Figure 8: Validation Transformation ETL Job Flow

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

 

Validation Transformation in SAP BODS
Figure 9: Validation Transformation Defining Rules

that’s it.

Enjoy 🙂

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- Pin It Share 0 0 Flares ×