Impressive Remodeling of Legacy BI System

Home
Case Studies
Impressive Remodeling of Legacy BI

Customer location

Project duration:

1 year (ongoing)

Our client is an insurance company headquartered in the US and employing over 6,000 talents worldwide, approached Symfa for a legacy modernization project.

Overall, the customer has a bunch of ETL processes running within the company for various needs. This BI application and the underlying ETL system is currently serving a very limited client circle, namely consisting of the customer’s contractors distributing their services. As of now the system can support only a few selected data formats used by the active platform users.

As the client is an international insurance player, they sell their cession portfolios and keep track of their sales using the custom BI application for report generation system. ETL is used to load data from client’s databases and transform it into graphs, tables, metrics in Power BI.

After a not so successful partnership with the previous vendor, the carrier turned to us to support the application, resolve bugs, and integrate the application with the client’s domain. The Symfa team was also to streamline the system operation, which grew too complex in time, and make the reports generation process more efficient.

The customer was expecting us to improve the system’s design in order to make it a fully-fledged BI product ready to hit the international market of insurance software solutions.

This was a project with a story to it, so before plunging into the development processes we scanned it for the bottlenecks. The major issue was the system complexity. ETL projects aren’t the most demanding in terms of technical execution, as this is a mere process of transferring data from one database into another. Yet, mastering ETL tools can be a challenge, as it was the case for the previous vendor who tried to move the whole ETL process to the cloud and got stuck in between. The solution lacked clarity and clear structure.The system was a mix of the monolith and microservice approach, with several stand-alone services each having their own code base and running on a separate server but sharing one database.

For now, our team has stabilized the system operation by unifying its logic where it was a reasonable choice.

Knowledge transfer process was hampered, too, as the previous vendor understandably wasn’t exceptionally willing to help after getting a cold shoulder from the client. The documentation was inadequate to the team’s needs.

The project data is complex, denormalized and poorly structured. The Symfa team gets their portion of the data from the source which is extremely denormalized for a different kind of reports. To get those denormalized data fit for our purposes required much self-studying and commitment from our team, which our guys showed before and continue to do so each day of the ongoing project.

Solution

For this project, an in-house customer team does primary data processing in the local database. After this step, the Symfa team carries out secondary data processing and generates the necessary reports.

The architecture of the solution has both an on-prem and the cloud part. The process starts on the local physical servers. From here the Symfa team takes the data stored in the SQL database. Using the SSIS package the team transfers the data to the SQL database in the Azure cloud. The BI application is deployed in the Azure Application Services.

Work done

The system logic was scattered across multiple servers, which made the excessively decoupled software hardly manageable. Throughout the year that the Symfa team was engaged on the project, we unified the logic. The resulting system is much easier to test and maintain, let alone grow new functionality.

With code base alone and no proper help from the previous vendor, our team got the ins and outs of the system only in a month’s time. Whenever the code base couldn't answer our questions, we worked closely with the customer’s Business Analyst. Together we managed to get the project back on the right track.

Azure
.NET Core
SSIS
TypeScript

Our deep ETL engineering and BI development expertise won us the project in the first place, but it’s the deliverables that the client sees that make the ongoing cooperation possible. Almost all the team members of the distributed project team come from the Symfa side, apart from the BA capacities and another Tech Lead/PM of the client’s.

The Symfa team works in strict compliance with the client’s procedures. The reputation we gained over the year of fruitful partnership helps easily settle all and any project challenges, be it infrastructure issues, access to the client’s internal resources (which is normally restricted to the in-house team) or the solution design. Daily syncups and detailed reporting guarantee ultimate transparency, which is a must for a highly-regulated insurance industry.

The BI solution has seen so much improvement, the client is planning to offer it as a stand-alone insurance product for other carriers globally in the future.