Client and Business Goals
A US-based prime small-business insurance provider contracted AIS Novations to implement a series of legacy modernization projects.
The complex software enabling work of over 5,000 employees has long been asking for an overhaul. For a year now, the distributed team composed of the AIS and client’s engineers has been doing monolith decoupling, legacy support, and a bunch of ETL, data management and analytics projects.
Solution
The AIS team embarked on the project to streamline the data flow between Oracle ERP and client’s custom made DWH sitting on the SQL server. The ERP connects to the data sources feeding raw data. The data is then extracted and transformed using the ETL tools of the client’s choice – one for the extraction and one for the transformation jobs, given the complexity and volume of the client’s data. The transformed cleansed data is loaded into the data warehouse where financial analysts of the client’s can get it to build the reports using paid/custom made visualization tools or simply Excel.
Challenge
The fundamental principles of any ETL project are pretty simple – the data is extracted from a source (sources), transformed to fit the requirements of a business model and loaded into the target storage. The challenge rises with the complexity of data, and being a global carrier, our client’s sources generate a huge amount of complex data.
The data may differ in format drastically and may need to be extracted from the source for which a standard component does not exist. Such is the Oracle’s ERP case. Oracle ERP returns .csv files which the SQL storage doesn’t welcome. Thus, the ETL processes that we developed include the following steps:
Besides the necessity to deal with the millions of rows of complex data, the distributed team was to establish a reliable connection between the Oracle ERP and the client’s ETL tool. The custom data warehouse lacks a standard connector, so the team made a custom connector from scratch. However, to make it a perfect fit for the complex financial reports that the client’s analysts generate, it required a bit of an extra polish during several development iterations that followed to guarantee 100% data integrity, safety and quality. The resulting solution provides a stable connection, data synchronization and data checks so no data is lost or duplicated when it reaches the target.
Technologies
Results
With data being at the core of the modern insurance business, ETL projects recently rose to prominence in the client’s company, being the cornerstone of data-driven culture. This ETL project sets a reliable data flow between Oracle ERP and the client’s DWH to supply the data for top-notch analytics.
After a successful onboarding phase, the AIS team was entrusted with all the extraction jobs. Our engineers leave the data for the client’s team in the data staging area from where they take it to proceed with the transformation (verification, counting, deduplication) and load parts of the ETL process. Both teams have their own areas of responsibility and use their respective data management tools. Such a cooperation model in no way isolates the team members – both teams enable an organic data flow and provide each other with extra support whenever it is needed. The ready-to-go data is then sorted out and stored in the data warehouse where data analysts of the client’s may get it to create custom financial reports. Thus, the engineers established a strong custom connection to synchronize high importance financial data.
