Start Finally Using Your Data: 3 Real Use Cases of Insurance Company Making the Best Out of ETL / BI Reporting Tools. Part 1

undefined
6 min read
Client stories
Digital Transformation
ETL/ELT
Insurance

“Don’t you see what’s happening? Potter isn’t selling. Potter’s buying! And why? Because we’re panicking and he’s not. That’s why. He’s picking up some bargains.” George Bailey.  It’s a Wonderful Life.

Sounds thrilling, but such inspirational movie quotes are hardly ever spoken in real-life office spaces. 99,99% of successful corporate decisions aren’t guided by gut feeling alone, but rather rely on solid data. To make matters even worse for all us dreamers, information flows from multiple sources and goes a long way to be presented in an easily digestible form to guide the leader in a proper direction.

To be completely honest with you, the amount of data and its processing is a real headache even for large enterprises. To relieve symptoms they resort to ETL applications and BI Reporting tools.

If you are new to our blog, I’m Ivan Sokolov, a BI engineer at Symfa. Today I’d like to have one more honest talk with you about ETL and BI tools that we use for our big client – a major US-based insurance carrier, serving small and mid-size businesses. From this article, you’ll learn about on-premise and cloud ETL and Reporting instruments that make data manipulations easier and help our customer make strategic decisions through smart granular reports.

If you like stories like this, make sure to check more of my articles on Symfa blog here and here.

Table of Contents

  • Journey made by data: from primary sources to beautifully visualized graphs and charts
  • Use case 1: Symfa helped the client fine-tune the ETL processes for efficient insurance data management
  • Use Case 2: How we assisted the client in informative financial reports generation
  • Use case 3: How we helped the client revive their sales process through insightful marketing reports
  • To wrap it up

Journey made by data: from primary sources to beautifully visualized graphs and charts

To have a better grasp of how important data representation is, let’s have a closer look at how it is done at a company providing insurance services. 

  1. Any of them deals with vast amounts of data from a variety of sources, both internal and external, such as transaction systems, databases storing information about customers, multiple government agencies, and many, many more. Staying on top of such a mish-mash of digits and facts is easier said than done.
  2. Another hot-button issue is that processed data must be compliant with financial regulations, such as anti-money laundering (AML), Know Your Customer (KYC), and others.

These two reasons alone (let alone marketing and sales) are enough to put ETL solutions and BI Reporting tools into play. 

But how does the process go and which tools to use on the way? Let’s talk about it in more detail.

Part 1 Bi Reporting Tools

From the scheme above we can see that the entire data journey consists of two legs: ETL and BI Reporting. ETL – Extract, Transform and Load – is the first part of the process, during which:

  • data is extracted from primary sources
  • transformed into a consistent format through cleansing, validation, aggregation, merging, and normalization and
  • loaded into a centralized repository – Data Warehouse or Data Lake for future analysis. 

To make this journey possible for this client – huge insurance player operating globally – we leverage on-premise and cloud ETL tools, such as: 

  • SSIS (SQL Server Integration Service) on-premise tool automating data processing streams.
  • Astera Centerprise – ETL platform supporting data manipulation and transferring to Data Warehouse.
  • MS Azure Data Factory – twin brother of SSIS from the MS Azure family, allowing to build data processing streams in the Cloud.
  • MS Azure Synapse Analytics – a very close alternative to the above Data Factory and a great fit for data integration, data warehousing and big data analytics. What makes it stands out is that it supports PySpark (Python), Spark (Scala), Spark SQL .NET Spark (C#), SparkR for lightning-fast data manipulation and analysis.
  • MS Azure Data Lake – cloud-based platform providing scalable data storage.

So, what happens after data is extracted, cleansed, filtered, and loaded to the destination storage with the help of on-premise or cloud ETL tools? It needs to take some easy-to-digest shape (i.e. become readable and understandable) to bring maximum value for the client, right? And here starts the second part of this fascinating transformational journey, which is the BI reporting. 

As soon as our client’s data is loaded, we can visualize it, and build reports with BI Reporting tools. Those vary, as today’s landscape for Business Intelligence technologies is as diverse as ever. Yet, as we’re talking about the corporate client here, it’s no surprise we’ll stick to the Microsoft ecosystem of solutions. For the purpose of this topic, I picked the two below: 

  • SSRS (SQL Server Reporting Service) – on-premise tool for reports generation in the form of graphs or charts
  • MS Power BI – Microsoft BA Cloud-based tool for data visualization (can be used on-premise as well). 

Now as you know the theory, let’s see how it is applied in practice on real projects and – most importantly – what value we help generate for our client.

Use case 1: Symfa helped the client fine-tune the ETL processes for efficient insurance data management

A simple one for a start.

As I have already mentioned before, our US-based client provides insurance services to businesses of small and medium sizes and routinely deals with vast amounts of data. The customer was looking to transform the ETL processes and create a unified codebase to manage the inbound data in the most efficient way. 

How the client made the best of the BI tool: Our team works with data sent from third-party services in xlsx, csv, and txt formats. We manage the respective ETL processes through the Astera Centerprise ETL application.

After data is processed and filtered, it is uploaded to the database in compliance with the client’s business requirements. And vice-versa, if the client needs to send documents or reports, structured data is extracted from the database and shared with the stakeholders. 

Read the detailed description of this project in our portfolio.

Use Case 2: How we assisted the client in informative financial reports generation

In this case, our task was to optimize the data flow between the client's Oracle ERP system and their custom Data Warehouse (DWH) hosted on a SQL server. As the client operates globally and has about 10,000 employees, managing financial data is a tough job to do. 

How the client made the best of the BI tool: Here’s our approach. The client's ERP system acts as the intermediary, connecting the data sources and feeding raw data into the ETL tools. Given incoming data complexity, we use two on-premise ETL applications – Astera Centerprise and SSIS. Once the data is prepared, it is loaded to the data storage to be used by the financial analysts from the client’s side. Thus they are enabled to create custom financial reports with their available visualization tools – such as Power BI or simply Excel.

For a complete picture of our work on this project, check out our detailed case study, which provides deeper insights into the challenges we faced and the solutions we implemented.

Use case 3: How we helped the client revive their sales process through insightful marketing reports

The client’s marketing team utilizes policy data to drive sales and renewals. But before getting to the marketers' hands, the data must be transformed.

How the client made the best of the BI tool: Previously, a third-party agency was responsible for cleansing the sales data for the client. Our objective was to replicate their workflows and identify opportunities for optimization using the client's existing tools and infrastructure (Astera Centerprise ETL application).

With our help the client no longer needed the third-party vendor’s services on data cleansing and could reallocate the available resources to support other digital transformation initiatives.

This case study is also available in our portfolio. Don’t hesitate to click for more details on the project implementation!

To wrap it up

In this blog post, I told you about ETL and Reporting tools that the Symfa team uses on a range of projects for our insurance client. You may have noticed that I incorporated cases with on-premise instruments only, and it is no coincidence. In the next part of this story let’s explore the cloud ETL technologies that our team uses for this same insurance customer. Meet me same place – Symfa blog – I’ll be back with a new article for you.

Credits

Ivan Sokolov
Ivan Sokolov

Business Intelligence Developer

Ivan is an aspiring young leader of the corporate BI universe. He's constantly challenging himself with new approaches to data processing and experimenting with tools. Ivan is residing in Georgia with his wife and two kids.

Ivan is an aspiring young leader of the corporate BI universe. He's constantly challenging himself with new approaches to data processing and experimenting with tools. Ivan is residing in Georgia with his wife and two kids.

More Like This

BACK TO BLOG

Contact us

Our team will get back to you promptly to discuss the next steps