RealTime DWH

The scope of delivery included:

1. Implementation of a PoC for online data synchronization of a selected subset of tables, within which
   • a Log-based CDC source connector was delivered for the IBM Informix database, ensuring the capture of changes made in the SIRAEL source database,
   • a JDBC Sink connector was configured for writing data to the MS SQL DWH database,
   • a mechanism was created for the initial population of synchronized tables using IBM Informix External Tables and TSQL Bulk Insert,
   • a mechanism was prepared to ensure the processing of captured transactions (Commit, Rollback) on the MS SQL database side,
   • a method was designed for historization of changes in synchronized tables in MSSQL.
2. Implementation of the entire online data synchronization solution, within which
   • the solution was extended to the entire set of synchronized data (more than 300 tables), with the set of synchronized tables managed by metadata stored in configuration tables in MS SQL,
   • definition and tuning of operational processes,
   • installation and configuration of the operational Apache Kafka cluster,
   • installation and setup of monitoring tools (Prometheus, Grafana),
   • initial initialization of the DWH database on MS SQL.

The project achieved the following:

• Built a platform for online data synchronization via Apache Kafka.
• Connected the DWH directly to the production database of the SIRAEL core system without the need to create a daily copy of the source database on IBM Informix.
• Enabled online reporting on the DWH side, thereby reducing the operational load on the core system.
• Ensured a reliable data source containing information about all changes made in the SIRAEL core system, thus enabling the implementation of optimized data pumps that process only modified data during time-critical processing.