Transformations
These are the transformations that are used to apply the business rules on the data to transform
from the source format to the target format or to check that the good data gets in to the next stage
and data cleansing and many more.
from the source format to the target format or to check that the good data gets in to the next stage
and data cleansing and many more.
Validation
Validation Query transformation is used to validate the data and allows the valid data to move
to the next phase of the job.
to the next phase of the job.
As per SAP AIO we use 3 types of validations to validate the data.
- Lookup validation
- Format validation
- Mandatory validation
Lookup validation is used to cross check the data from the lookup table. If the data exists in the
lookup table then it will be passed else it will be kicked off.
lookup table then it will be passed else it will be kicked off.
Here we are extracting the data from a flat file and doing a lookup validation on Cust number.
For this we need to have a lookup table. In this case we are using a permanent table stg_load1.
This is the permanent table that we are using for the lookup .
Right click on this table and click on view data.
Now you can see the actual data that is present in the lookup table.
Click on the validation in the above job. You will get the below screen
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 your lookup table exists and
select the lookup table and then the customer number field to do a lookup
Now we join the output pass to valid pass n 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.
Mandatory transformation is used to do a mandatory check on the data. This ensures that the
valid data will not have null records. And the failed table will carry the null records.Generally
we send the failed records back to the client to ensure the data and take a sign off from client.
valid data will not have null records. And the failed table will carry the null records.Generally
we send the failed records back to the client to ensure the data and take a sign off from client.
We are connecting the flat file to do a mandatory validation in the Query validate.
Click on the validation Here click on the cust num and under the validation rule
click on enable validation. Under condition select “ IS NOT NULL”. Come back and
connect the valid table’s output (fail to fail table and pass to pass table) to the tables.
Now execute to see the data in the output tables.
The null values will go to the fail table and pass table will have not null values.
The null values will go to the fail table and pass table will have not null values.
This is how we get the good data to move to the next stage of the project.
Format Validation is used to check the format of the data.
Sometimes even to segregate good data we does this format validation also.
Sometimes even to segregate good data we does this format validation also.
Click on the validation and open it. Select the cutnum and click on enable validation under the
validation rule tab. Now check custom condition and give “length(cutnum) <=10”
validation rule tab. Now check custom condition and give “length(cutnum) <=10”
Now go back and save and execute the job to see that the data is validated based on the condition
and moved to the target tables (Fail and Pass).
Now click on the magnifying glass symbol on the target tables to check the data.
and moved to the target tables (Fail and Pass).
Now click on the magnifying glass symbol on the target tables to check the data.
