Handling rejected data

rsunny · Post by **rsunny** » Fri Jan 14, 2011 7:18 pm

Hi ,

I need a scenario where i need to create a job for the rejected data.

scenario is

If i have a source which has 2 business keys , and if i got 5 rows of reject data then i have to insert the data in a table in such a way that each business key has to have all the 5 rejected records i.e i need to insert 10 records in a table with each business key and if i have 3 business key i have to insert 15 records in the table and so on.

And then once i am done with that , if my source has 10 fields , then for each of the rejected data i need to check each field for each row and check which field has violated business rule such that it captured reject data. once i check each row for each field say for suppose if i am having 5 rejected records and 1st row is rejected because of 5 fields that arre violated the business rule out of 10 fields then i need to insert 5 records of each field in the table . so if 5 rejected records has each of 5 fields violated the business rules then i have to insert 25 records withe each field has a row in the table.

As i am stuck in the middle as i couldnt able to insert the records into the table.

can anyone please suggest what is the best way to solve this issue as i am not able to find any kind of solution.

thanks in advance

rsunny · Post by **rsunny** » Fri Jan 14, 2011 11:22 pm

Hi ,

I would like to know whether the above scenario is possible to design a job or it is not possible?

Please anyone suggest me how to solve this issue.

thanks in advance

ray.wurlod · Post by **ray.wurlod** » Sat Jan 15, 2011 4:56 am

What is the desired format of the target table that receives the rejected records and associated information (in particular, what are you using for its key)? Some more information about the "business rules" you need to test would probably be useful as well.

rsunny · Post by **rsunny** » Sat Jan 15, 2011 8:09 am

ray.wurlod wrote:What is the desired format of the target table that receives the rejected records and associated information (in particular, what are you using for its key)? Some more information about the "business ...

hi ray,

The key for the target table is surrogate id and the rows of the target table is based on the source natural keys which might be 1 or 2 or many.And based on the natural keys i have to insert the rows as per the above scenario that i have stated..

thanks in advance

ray.wurlod · Post by **ray.wurlod** » Sat Jan 15, 2011 4:15 pm

In that case you should have no difficulty inserting as many rows as you can generate. So it looks like the issue is generating the records, rather than actually inserting them.

Why not create a Transformer stage with one output per business rule, and bring these back together using a Link Collector stage?

nayanpatra · Post by **nayanpatra** » Sat Jan 15, 2011 9:26 pm

Please have a look at the following design-

Input rejected records -> Transformer1 -> Funnel1 -> Transformer2 -> Funnel2 -> Reject Table Load

I am taking the following records for example.

Input record Key1 Key2 Field1 Field2 Field3 Field4 Field5
Correct K11 K21 F11 F21 F31 F41 F51
Reject1 K12 K22 F12 F22 F32 F42 F52
Reject2 K13 K23 F13 F23 F33 F43 F53

Now as you can see in the above sample data, you have 1 correct record and 2 rejected records (Reject1 and Reject2) each having a pair of business keys - (Key1, Key2). Each records are having 5 attribute fields - Field1, Field2, Field3, Field4 and Field5. For the sake of assumption, lets take F12, F23 and F43 have violated the business rules.

Now, in our transformer1 we will split the business keys of the rejected records. Here, the transformer will be having 1 input link and 2 output links. Each output link will have the layout format as -

Key Field1 Field2 Field3 Field4 Field5

So the map all the attribute columns along with Key1 in first output link. And map all the attribute columns along with Key2 in the second one.

Link11 Key Field1 Field2 Field3 Field4 Field5
Reject1 K12 F12 F22 F32 F42 F52
Reject2 K13 F13 F23 F33 F43 F53

Link21 Key Field1 Field2 Field3 Field4 Field5
Reject1 K22 F12 F22 F32 F42 F52
Reject2 K23 F13 F23 F33 F43 F53

Funnel both these links. So in the output of Funnel1 you will have something like this

Link Key Field1 Field2 Field3 Field4 Field5
Reject1 K12 F12 F22 F32 F42 F52
Reject2 K13 F13 F23 F33 F43 F53
Reject3 K22 F12 F22 F32 F42 F52
Reject4 K23 F13 F23 F33 F43 F53

Now in transformer2 do a similar kind of splitting for the attribute columns. Here will be 5 output links. The only additional logic that you need to implement here is add a constraint to each output link wherein you will let pass only the rows which haven't satisfied the business rule.

Link21 Constraint(Y/N) Key Field Output(Y/N)
Reject1 Y K12 F12 Y
Reject2 N K13 F13 N
Reject3 Y K22 F12 Y
Reject4 N K23 F13 N

Link22 Constraint(Y/N) Key Field Output(Y/N)
Reject1 N K12 F22 N
Reject2 Y K13 F23 Y
Reject3 N K22 F22 N
Reject4 Y K23 F23 Y

Link23 Constraint(Y/N) Key Field Output(Y/N)
Reject1 N K12 F32 N
Reject2 N K13 F33 N
Reject3 N K22 F32 N
Reject4 N K23 F33 N

Link24 Constraint(Y/N) Key Field Output(Y/N)
Reject1 N K12 F42 N
Reject2 Y K13 F43 Y
Reject3 N K22 F42 N
Reject4 Y K23 F43 Y

Link25 Constraint(Y/N) Key Field Output(Y/N)
Reject1 N K12 F52 N
Reject2 N K13 F53 N
Reject3 N K22 F52 N
Reject4 N K23 F53 N

Now funnel all the above 5 links and you will get the output as -

Link Key Field
Reject1 K12 F12
Reject2 K22 F22
Reject3 K13 F23
Reject4 K23 F23
Reject5 K13 F43
Reject6 K23 F43

Now load this records in your reject table. Hope this solves your problem. You may use any other stages as well. It will all be the same until the functionality is fulfilled. Let me know if there are any other concerns.