EFFECTIVE  USE OF VALIDATION FUNCTION

How can we call a Validation function inside a Validation transformation?

Description:

           If we want to  get the list of products, which are stored more than 6 months in a warehouse, for example product received date is greater than 6 months, those can be considered as  expired products and should be replaced with new ones. And we need to send the list of expired products into a file/ table to respective department.

            We will create a rule to check the condition and generate the output based on the defined rule.

Validation function is used to Filter or Replace the source data set by writing script in validation function under custom function tab and call the same function in validation transformation and produce desired output data set.

How it is implemented in BODS using validation transform and validation function in step by step process?

Use Case:

Created a rule  to check the product storage period and sending to OLD_PRODUCTS to Pass condition and loading into a table, if not we will be moving the new products that are less than 6 months to Fail condition and moving  to other product table. We can use variable/parameter to pass custom values.

In this scenario we are validating the data from the database table by using “validation function” instead of “column Validation” in “validation Rules”.

Source table: DEMO_PRODUCTS

STEP:1

Creating a validation function which is used to validate the list of products to check expiry date.

Go to custom functions tab, and we can see validation functions inside it.

Validation functions:

 Imported from Information Steward:

These functions were created in Information Steward and imported; they are not editable in Data services.

Locally Created: 

We can create reusable Custom functions and validation functions in Data services. 

Right click on validation function, then NEW, we will provide required information like name(‘VF_PRODUCT_EXPIRY_VALDATION’), type and description for the function in the popup window.

·         Name of the function ‘VF_PRODUCT_EXPIRY_VALDATION’ and click on “Next” button.

Then you will see “Smart Editor”, where we can write the script to check our conditions, create variables/parameters as shown below.

Below are the list of parameters/variables and data types:

1.  ‘$p_prod_rec_dt’ date
2.   ‘$v_month_count’ int
3.   ‘$v_expire months ’ int

Defining parameter/variables:

Right click on parameter and click on insert and give name of the parameter and required data type and input parameter type.

Next create a local variable named as ‘v_month_count’ and here local variable is used to check the input date that the product received date is expired or not.

Script to check the condition based on the provided data from variables and parameters

Information provided by variables/parameters and result of script:

# $p_prod_rec_dt is used to capture the PROD_REC_DT data

# $v_month_count (variable) is used to check the input date

# $v_expire_months this variable is used to check the given expire months.

#Return 1 means will evaluate the input data and

#if the above condition is satisfied

#then return the data into prdouct_expiry table.

$v_months_difference=6;

$v_month_count=date_diff($p_prod_rec_dt ,sysdate( ),'MM');

PRINT($v_month_count);

if($v_month_count>$v_months_difference)

Return 1;

else

Return 0;

STEP 2:

Next we can create Job, Workflow and Data flow as usual.

Drag Source table and Valid transformation into Data flow.

Double click on validation transformation and go to Add to provide details.

Give name and select required validation function and pass the parameter and select Argument means give Hard coded value or specified field.

Next Enable the check box.

STEP 3:

Add a Target table in the dataflow & give the connection between Validate Transform and Target table and choose the Validation condition as ‘PASS’.

Drag the tables and give the Connection to the ‘Fail’ and ‘Rule violation’ condition.

·         Validate the job, execute and check the input data and output data after executing the job.

Input:

In the above figure you can see in the highlighted rows in the input, where the date is not having more than 6 months (value assigned to $v_expire months variable) with current month count.

Output for “Pass” condition:

Output for “Fail” condition:

Output for “RuleViolation” condition:

Summary:

We can define our own Validation functions and rules based on our requirement.