Case Study
Real time Sales data enablement using Databricks for a Global Pharma company


Our client is a leading biopharmaceutical company, which specializes in medicinal discoveries and pharmaceutical insights. The client currently uses a homegrown .NET-Informatica-SQL Server based application that manages pharma sales data, such as territory allocation and sales goals, which are routinely used by sales reps, doctors and other authorized medical professionals. This data is reviewed periodically and securely updated in the system.

Solution Highlights

Leveraging the expertise of working on cloud components, Agilisium’ s experts suggested the following changes.

  • Replacing the existing Informatica tool with Databricks Unified Analytics Platform (UAP) and
    Mulesoft Customer Relationship Management (CRM).
  • Replacing SQL Server with Aurora Relational Database System.
  • Deploying AWS services such as AWS Lambda, AWS Secrets Manager, Cloud Watch Alerts, Aurora RDS, SNS, Amazon S3 and Glue, to finely integrate the requisite technical components.

The component functionality includes:

  • Veeva-CRM: A cloud-based specialized life sciences solution built on Salesforce, to optimize and handle pharmaceutical data as per health industry regulations.
  • Databricks: To integrate & transform business data from multiple applications and feed them into Salesforce CRM.
  • Mulesoft integrator: An API integrator tool to feed real time data into Salesforce CRM
  • Amazon Aurora: A cloud-based RDS, which is the single source of truth of data available to both Mulesoft and Databricks.
  • AWS Glue: A serverless, fully managed and cloud optimized ETL tool, to prepare and load sales and territory data for analytics and further processing.


System Functionality

From the client’s front-end application, the obtained CRM data is classified based on different categories i.e., sales representatives, customer classification and territories, call frequencies, territory hierarchies and so on, further stored into cloud storage services provided by AWS.

Through backend ETL processes, this massive CRM data is migrated from SQL Server to Aurora (a MySQL compatible RDS) and is processed using suitable algorithms. This data is transformed using Databricks ETL tool & AWS Glue, and eventually stored into AWS’s Storage Layer. Further ETL operations were triggered using AWS Lambda and integrated using Mulesoft API, from which the data is finally sent to Salesforce Cloud CRM.

Besides leveraging Salesforce for reporting, configuration and maintenance capabilities, the life sciences Veeva CRM enabled the following operations:

  • Real-time updates using Mulesoft API into Salesforce.
  • Batch processing operations using AWS Glue & Databricks, as push updates into Salesforce.

Real time availability: For the field sales workers to view real-time information and sales updates, a customized mobile application was created as a part of Salesforce, to fetch and display data provided from Databricks & Mulesoft. This mobile application provides near real time updates about territory allocations, sales reps, rep-alignment mappings and similar data, to the field users; and is then stored permanently into Salesforce.

Results and Benefits

The ETL integration processes, which initially consumed about 8-12 hours due to Informatica and MySQL, can now be entirely completed to 1-1.5 hours, including other essential operations such as auto-scheduling, auto-triggering and automatic failures handling. This was able to offer a quicker turn-around time and reduced latency.

The overall data availability factor has been improved to 90%.

The entire data migration processes from on-prem to cloud, along with ETL processing, was carried out in 3 months. In this, the first 45 days were spent only in the necessary requirements gathering and initiation, while the actual project implementation was completed in the remaining time.

The field sales team now have access to sales information and territory alignment updates “on the go”, which has increased their productivity and responsiveness due to the mobile application, gradually reducing all additional efforts on their routine tasks.