Description


COINS Analytics replicates COINS tables to SQL. Once a table has been setup for extraction, if Change Data Capture (CDC) is enabled, every change to that table in COINS will be replicated to SQL in near real time.

To ensure that the COINS and SQL tables remain in sync a reconciliation is performed every night. The results of this reconciliation are issued via email “Vistry Insights Monitoring: Reconciliation: COINS to Bronze”.

Sometimes this reconciliation will identify that the COINS table and the SQL table have fallen out of sync. i.e. the number of rows reported are different between COINS and SQL.

Alternatively, you may be made aware that there has been a direct Progress update, insert or delete performed against a COINS table. These types of updates do not trigger the Change Data Capture event required for CAOA to keep tables in sync.

When either of these events occur a Re-extract of the table to SQL can be performed to re-sync the two tables.


Process


To perform a re-extract of the table you’ll need to follow the process below which will drop the existing SQL table, re-create the SQL table and re-extract the data. To do this you must first identify the table which is out of sync. E.g. APVendor. This will be referred to as [Entity] below. 


1. Drop [Entity] SQL Table:

Coins --> System --> Extracts --> Extracts Maintenance --> Check "X_[Entity]" --> Select the "Drop SQL Table" function and click the arrow. 


2. Build SQL for [Entity]:

Coins --> System --> Extracts --> Entity Maintenance --> Check [Entity] --> Select the Build SQL function and click the arrow. --> Set Process to "Create Tables" --> Next. 


3. Set CDC for [Entity] (if not already set).

Coins --> System --> Extracts --> Entity Maintenance --> Check [Entity] --> Select the Set CDC function and click the arrow. --> Next. 


4. Extract Data for [Entity]:

Coins --> System --> Extracts --> Entity Maintenance --> Check [Entity] --> Select the Extract Data function and click the arrow. --> Ensure Companies is set to * --> Next. 


5. Confirm that the Extract Data Report ran successfully. 


6. Run the Extracts Reconciliation Report to confirm that the table is now in sync

Coins --> System --> Extracts --> Extract Reconciliation Reports --> Extracts Reconciliation --> Set Tables to [Entity] --> Next


7. On the Extracts Reconciliation Report, confirm that the figure in ERP+ and the figure in SQL are the same.