Multiple Validation Transforms

dataservices job design
1

Hi All,

From SAP Data Migration perspective - I have seen it as a best practice to utilize multiple Validation Transforms - each performing a set of Mandatory, Lookup or Miscellaneous validations. The Pass and Fail records are merged and a Select Distinct query used for the Pass path to eliminate duplicates. Please check the image attached below.

But my concern is that even if one validation is successful we get a record in the Pass table (although the same record failed the other two validations). Is this as expected or is there a possibility(Logic within the same DF) to eliminate the record from reaching the Pass Table.
OR please let me know if i am missing the point of using multiple VAL transforms.

TIA.

Kind Regards.
RandomJob.jpg

MaveriK (BOB member since 2012-02-26)

2

Use the output of Merge_Fail to filter out the records that didn’t pass all validation by left joining Query_Distinct to Merge_Fail and passing records where there was no matching record from Merge_Fail.

-Adam

avandeweghe :canada: (BOB member since 2012-04-25)

3

I don’t use the Validation transform all that often so I could be wrong here. It seems to me that if you use multiple transforms and each one doesn’t pass the record on to the next if the validation failed then you could have a situation where a record has multiple problems but you’re only reporting the first one.

For this reason, I think using a single Validation transform might be a better practice. Plus, it simplifies the Dataflow.

eganjp :us: (BOB member since 2007-09-12)