Maintain historical data on target database

[Ramaraju98]

We have a setup with Oracle database as the source for replication and target as Azure SQL database. We have a use case to maintain the historical data on the target database. whenever we do a full reload on target, it deletes the tables on target and re-creates a new set of tables. But, we want to maintain the previous data without deleting it.

1. Are there any configuration changes required to be set on the QLIK replication task that can avoid deleting existing data on the target DB?

2. Consider a scenario, where there is a change in source schema (a column is added or deleted). We do a full reload of the target table. In this use case, how do we preserve the historical data of the table on the target DB?

3. Consider a scenario, when there is a failure on the source table redo logs. We do a full reload of the target table. How do we preserve the historical data for this use case?

[SachinB]

Hello @Ramaraju98 ,

Thank you for reaching out to the Qlik community!

We can maintain the historical data, But you need to select "Do Nothing" option while performing next reload occurrence. Existing data and metadata of the target table will not be affected. New data will be added to the table. But this will have duplicate data in it.

Please refer the below link for more details:

Full Load option 

If the provided above information helps please "Accept as Solution".

Regards,

Sachin B

[merotda]

Metrics are pretty cool, but (I've found) they work quite differently to how you might have setup your KPIs in your reports. I guess it depends on the scale. If it's a single KPI metric, and you're happy with how Power BI creates everything for you, then this could definitely be an option. But if you're going to be storing multiple historic changes for multiple KPIs, it might be better to build a solution that's a bit more scalable.

[Alan_Wang]

Hi @Ramaraju98 

You can also consider using Change tables as a way to store historical data separate from the base tables. Changes tables are a new set of audit tables that will individually track and log CDC transactions of each insert, update, or delete operation. 

https://help.qlik.com/en-US/replicate/May2023/Content/Replicate/Main/Change%20Tables/use_change_tabl...