Pharmaceutical Company: Change Data Capture with MuleSoft

The Challenge

One of the biggest challenges faced by the business operations team is to gather and report on granular changes i.e., every field change on a record needs to be captured, stored and made available for reporting. Change Data Capture (CDC) techniques allow operations to store data in a data warehouse in an optimal way. CDC techniques also drastically reduce data storage costs while maintaining optimal compliance with data retention policies. The business operations team will use data from the data warehouse to analyze current and long-term trends and will also be instantly alerted to opportunities and appeals.

Business
Overview

The client is a leader in distributing pharmaceuticals and providing health information technology, medical supplies, and care management tools. Hospitals, doctors, and patients become eligible to receive services when they enroll with the client. Once enrolled, the information must become available in salesforce and be automatically updated. All enrollment information needs to be stored in a data warehouse for audit purposes and for reports on real time trends.

The Solution

We created MuleSoft applications that integrate data between the four layers of data storage:

• Data storage layer — Saves a replicated copy of daily information for data processing.
• Staging layer — Used to transform data into history and fact layers.
• History layer—Maintains a record for every enrollment change.
• Fact layer—Contains current data for enrollment and audit reports.

MuleSoft applications were developed for easily loading data from Salesforce to the data warehouse.
This was achieved with reusable Mule templates.

A data warehouse was built in Oracle using Mule applications that load data to the four data layers.

The Mule application supports:

• Creation of reports by the end-user.
• Ad-hoc reporting and analysis.
• Dynamic presentation using dashboards with drill-down capability.
• Support for regulations, operational processes, and data mining.

Outcome

All data is automatically populated and stored, preparing the client for potential audit and giving them the ability to track all real-time trends with advanced reporting functionality.

The client benefitted from this design in the following ways:

• Ensured audit readiness and data retention compliance.
• Drastically reduced redundant data, saving the client from unnecessary data storage costs without compromising data retention compliance.
• Dashboards show all past and current enrollments, allowing the client to flag errors and ensure that no enrollments are missed.