Axtria Insights

Axtria Insights

CASE STUDY
Weekly Processing of High Volume Data for Multiple Downstream Processes

Situation

  • Global Pharma company with total field force size of ~2,000 and 6 brands
  • Axtria was chosen as partner for providing end to end sales operations analytics services
    • Sales force sizing
    • Alignment
    • Call Planning
    • Incentive Compensation
    • Cloud based platform for online reporting
    • Adhoc analytics
  • A team of 30 Pharma analytics professionals spread across US and India

Challenges

  • Each process has varying data requirement at different frequency. A centralized datamart was required to ingest data at weekly, monthly, quarterly and annual basis to be consumed by all downstream processes
  • Total weekly input data size : ~ 120GB, Total output data size: ~1TB
  • Current time to deliver reports and incentives to field was delayed due to inefficient processes and archaic technology used by current vendor
  • Some key input files : (i)Plan level sales for 6 markets (weekly) (ii)Non Retail data for 3 markets (weekly) (iii)Physician demographic files (iv)Alignment files (v)Claims data
  • Total time taken for ETL and creating data mart for downstream processes using SQL database was close to 24 hours which was sub-optimal considering weekly dashboard was to be published

Approach

  • Axtria’s data management experts did a thorough review of existing processes and systems
  • Two options were explored – (a)Optimization of current infrastructure and processes (b)Shift to Amazon REDSHIFT
  • Axtria shifted underlying technology from conventional Oracle/SQL to Amazon Redshift
  • Separate layers were used for storage only (S3), ETL (Redshift) and Datamart (RDS) for reporting and other downstream processes
  • Used pay per use model to release resources as soon as data processing is complete to reduce total cost of ownership for the Client

Result

  • There was significant improvement in timeline and total cost  of ETL and data preparation for reporting and other downstream sales operation processes
  • ~50% reduction in on-going operating infrastructure cost
  • Cycle time improved from 24 hours to 9 hours. FTE effort reduced proportionately

High Volume Data Processing for Multiple Downstream Processes