Project Description

AUTOMATED AND IMPROVED PROCESS

CREATING A ROBUST, SCALABLE &
FUTURE-PROOF DATA WAREHOUSE

BRIEF

One of our clients is an insurance provider that offers unique solutions in the areas of care, disability and absenteeism throughout the EU. They maintain close collaboration with partners who directly engage with the end-users. Given their extensive customer base, it’s crucial to make informed business decisions and ensure precise data insights! To meet this need, we assembled a team of Data Visualization Expert, Data Engineer, Solution Architect and Project Manager to help create a robust and future-proof data warehouse. Our team worked closely with their Data Analyst and stakeholders and supported them in areas such as data engineering, cloud development, project management, Power BI dashboard development and solution architecture.

This project includes an end-to-end solution; from collecting raw data to visualizing insights on a Power BI dashboard. This all to provide the client with the following added values:

  • Having a data warehouse with an automated dashboard that provides reliable and up-to-date insights whenever and wherever you need them.

  • Promptly identifying and addressing issues through rigorous data checks.

  • Generalizing and comparing data from diverse sources such as Excel and SQL, translating it into a single format.

  • Analyzing various time periods and trends to make predictions for the future.

THE CONCEPT

To establish a robust and effective data warehouse, it’s essential to maintain a well-structured data pipeline for extracting, transforming and preparing data. By creating an automated data pipeline, we eliminated the need for manual coding and data cleaning. Furthermore, a generic data model was devised to ensure the generation of consistent reports. These reports are subsequently visualized on a Power BI dashboard. Importantly, the entire data warehouse operates on Azure, making it future-proof and scalable.

THE CHALLENGE

Our client wants to have insights on all their data sources that often come in different formats, and they want to make sure to compare the right information. To accomplish this, we had to ensure that the central data warehouse automatically processes various data sources (like Excel files and SQL databases), translates them into one unified format, and prepares the data for reporting. For an insurance provider, accurate information is vital. Our client wants to be able to automatically generate reports that give them an overview of their expenses and income, such as how many premiums they should receive, how many claims they should refund, and whether an insurance claim from the previous year is valid or not. Therefore, it is necessary to eliminate manual data processing as much as possible because it can lead to human error. In addition, we had to ensure that numerous checks were made during the process to ensure a flawless claims reimbursement process and error-free administration for all partners.

AUTOMATED AND IMPROVED PROCESS

In the past, our client had to manually process the data source they received from their partners. Fortunately, this is no longer necessary as the process is automated as follows:

DATA UPLOAD

Upon receiving data, whether in the form of an Excel file, SQL database, or any other source, they simply upload it to a designated Sharepoint folder.

EXTRACTION, TRANSFORMATION, PREPARATION AND DATA CHECK

The uploaded data then enters an automated data pipeline, where it undergoes extraction, transformation, preparation, and data quality checks before being transmitted to a centralized data warehouse. These checks adhere to specific business rules (e.g., a claim type must have a duration of no more than 1 year). If errors are detected during these checks, they are flagged and made visible on the dashboard. This allows our client to use these instances as examples when reporting back to their partners.

REPORT PREPARATION

The central data warehouse brings together various data sources (Excel file, SQL database), translates them into a uniform format and provides data for reports.

VISUALIZATION

These reports are then visualized in a Power BI dashboard and are automatically updated each time.

MEDIAAN IN ACTION

During this project, we put together a team of Data Visualization Expert, Data Engineer, Project Manager and Solution Architect. We maintained a close collaboration with the client’s Data Analyst and engaged with their stakeholders, who represent the end-users of the dashboard.

  • Data Factory to build automated data pipelines using a simple interface.

  • Azure to host and manage access.

  • Power BI to visualize data in dashboards and allow the client to quickly get up-to-date overviews and dig into their data.

RESULTS

With a solid data warehouse, decision makers can gain insight into current and historical data. This includes obtaining an overview of expenses and revenues, such as expected premiums, expected claims reimbursements and the validation status of insurance claims from the previous year. In addition, we ensure the integrity of high-quality data through numerous controls integrated into the automated data pipelines. These controls are of various types and adhere to various business rules, such as ensuring uniform date notations, checking the presence of specific columns in each Excel file and enforcing restrictions such as a maximum claim duration of 1 year. Having a solid data warehouse enables our client to make informed decisions quickly and better understand their performance. Moreover, it allows them to implement other innovative and smart data science capabilities in the future.

WANT TO SEE MORE?

OTHER PROJECTS