Transformations are there in BW to change the data based on some logic. These are also there in ETL tool like BODS. These can be used to apply any business rules or cleanse and format the data from source to target.
Query transformation is used to validate the data. There are three types of validation that can occur-Lookup, Format and Mandatory validation.
Lookup validation is used to cross check the data in the look up table and then apply some conditions on the source data.
In below example we will look up customer master table and will pass the records having customer number in the master table to Pass table and pass other records which don’t have the customer number in the master table to a fail table. Below is how the data flow will look like-
This data flow shows that an excel file is being loaded and validated against a permanent table.
The look up table and the look up condition can be selected by clicking on the validation box.
Click on the “customer number” in the schema in and under the validation rule tab, click on enable validation and click on Exists in table. Now click on the down arrow and select the data store in which the look up table exists and select the table and the customer number field to do the look up.
Now join the output pass to valid pass and fail to valid fail. Like this we are moving the pass records to valid table and invalid records to fail table.
Execute the job to see the valid data moving to the valid table and invalid data to the invalid table.
This is used to do a compulsory check on the data e.g. ensuring that a field in the file being loaded should not have null records. These null records will be transferred to a fail table. These failed records can be sent back to the client to make them aware of the data issues and avoid these in future.
The data flow will look similar as in lookup validation. In the validation rule, select the field-enable validation-and give a condition ‘IS NOT NULL’. Go back and connect the validation rule pass to pass table and fail to fail table. Execute the job and check the data in both the tables.
Thus we have now quality data to move to the next phase of the project.
This type of validation rule is used to check the format of the data. e.g. the customer number length should not be more than 10 characters.
The data flow will look similar as in lookup validation. In the validation rule, select the field-enable validation-and give a custom condition ‘length (custnum) <= 10’. Go back and connect the validation rule pass to pass table and fail to fail table. Execute the job and check the data in both the tables.