One of my client's requirements is that if we put some auto fix validations like name.replaceall() etc. in some data transformation step, then he should be able to visually see all data errors that were fixed automatically so that he can approve and/or reject and/or rollback.
Will this be possible to achieve?
Never tried, but I think it could be done with this:
tOracleInput --> tMap --> tFlowToIterate --> (iterate) --> tMessageBox
--> (if Yes) --> Commit
--> (if No) --> Rollback
The tMessageBox should have options Yes/No and could contain the modified record that needs to be approved.
The If statement should look like: ((String)globalMap.get("tMsgBox_1_RESULT")).equals("0")
Where 0== Yes and 1 ==No
To build this kind of a system, ideally you would want to push the corrected records to a table where you maintain a additional column for flagging approved, rejected or rollback records.
If the client wants to view the data visually, then you may have to build a UI on top of the table or see if you can use Talend's native MDM UI.
I don't think you should use Talend's messaging or logging components to display data to the client esp. when the load is in progress.
Well it looks good by reading the description of the solution. However, since I am pretty new to Talend, I don't know how to go about it. Any similar/samle job which I can refer?
What you're describing is two-stage load process. While we don't have all the parameters here, my assumptions line up with @ArvinRapt in that you'll likely want to stage your data in a table, provide a means to review changes, then provide a mechanism to push out approved changes. This scenario would not be a single Talend job, but several tasks orchestrated into a process.
Ya you are right in the sense that it would be a 2-stage process. I can store the data in a staging table alright but how to as you said 'provide a mechanism to push out approved changes.' I cannot envision of this job design because of my very limited exposure to the tool.
Thanks anyways for your answer. Let's see if I get more insights on this.
There's couple of ways to do this.
1. Assuming you are going to build a UI on top of the staging table, you can have a web app that runs the UI update the flag column in the staging table as 'approved'. Then once the user clicks on a button in the UI, the web app should trigger the Talend job using the TAC api that would push all the records in staging table with flag as 'approved' to the target table.
2. If there are no plans for a UI, then we need to figure out how to 'show' the data to the client. Is the client comfortable with viewing the data in the database directly and updating the flag manually? Or will you be sending a flat file to the client which can then be used as an input for another talend job.
I think these are design decisions that need to be finalised before we can come up with the actual talend job design.
Of the two strategies you told, the 2nd one seems feasible to me. But first I want to know that how to know which columns were auto fixed by some rule out of say 100 records? (which we will put in some staging table). Then the client can see and then mark manually let's say is Approved column and then we can use it as input to another Job which will put it in Production table. Kindly suggest.
First thing that came to my mind was that you could use tJavaRow component to check if the value has changed after you applied the fix.
So you would need to store the result of the fixed value in a temporary variable and compare it with the incoming value to see if it has changed.
It would look something like this,
I think the screenshot does a better job of explaining things. Hope this helps.