How to replicate data with audit trail for each tables from the source

[harikesh_1991]

Hello @john_wang @Dana_Baldwin @DesmondWOO 

I have a requirement to replicate a set of tables from the source to the target, however I need to setup audit trail for each tables. We have to track each changes of a record. Who changed it, what changed and when?
How can this be implemented? I need each changes whether its insert/delete/update to be captured.
Can this be implemented only via the store changes task? That means the __ct table will hold the history of changes? Also for this do we have to enable on update: Store after image only/Store before and After image.
Also for applying the changes to the actual target table, we can select apply changes as well in the same task? or do we need to 2 separate tasks for it?

Could you please advise on how to configure the target tables like it will only be inserts , as there will multiple changes to the same record?

just wanted some suggestions on the best practices or approach for this requirement.

Regards,
Harikesh OP

[john_wang]

Hello Harikesh OP, @harikesh_1991 

You are correct — both Apply Changes and Store Changes can be enabled at the same time. In this case, Qlik Replicate will apply the changes to the target table while also storing audit information in the  "__ct" table.

The only consideration is capturing the user who made the change (“who changed a record”). For this, you may need to add a column using a Global Transform Rule. If you can share the source database type, we can help confirm the best approach.

Good luck,

John.

[harikesh_1991]

Hello @john_wang ,

The source database type is Oracle and the target database type is also Oracle endpoint.


Regards,
Harikesh OP

[harikesh_1991]

Hello John,

So how do we capture the user info of "who is doing the change in the source tables" in my task?
As you said we need to add a column via global rules, maybe something like : changed_by.
But how will this capture the user info?

Regards,
Harikesh OP

[john_wang]

Hello Harikesh OP, @harikesh_1991 

For an Oracle source to Oracle target task, you can add a new column (for example, userName) and set its expression to $AR_H_USER, as shown below.

This will allow the target table to capture the user name that performed the update on each row.

Good luck,

John.

[harikesh_1991]

Hello @john_wick ,
Thanks a lot for your insights on this. I had used $AR_H_USER to map CHANGED_BY for my task already and I see that it keeps on populating "APPS" in it. I don't find any username apart from it.
I have attached the screenshot of it . Is this normal. The source is an Oracle EBS system. 

Regards,
Harikesh OP

[john_wang]

Hello Harikesh OP, @harikesh_1991 

I’m not completely certain, but it seems reasonable that the application is using the APPS account to connect to the source Oracle database and perform the data changes.

You may want to confirm this with the source team or DBA. Alternatively, you can run LogMiner sessions to verify the user information recorded in the redo logs.

Regards,

John.

[DesmondWOO]

Hi @harikesh_1991 ,

It appears that the customer is using Oracle E-Business Suite. EBS standard tables include “Who Columns” such as CREATED_BY and LAST_UPDATED_BY, which record the actual EBS user ID responsible for the change. There’s no need to add a specific column like $AR_H_USER to capture user information, as it won’t help in this case.

Regards,
Desmond