Introduction

A leading pharmaceutical company's Pharmaceutical Sciences and Small Molecules division needed to enhance their research capabilities through better data analytics. The division required automated tools to analyze compound solubility experiments and other research studies. Our team was engaged to develop analytical pipelines that could extract and process data from Electronic Lab Notebooks (ELNs) and present insights through intuitive dashboards for both researchers and executives.

Challenges

1. Manual Data Processing: Researchers spent significant time manually extracting and processing experimental data from ELNs.
2. Limited Data Visibility: Lack of automated analytics tools made it difficult to gain insights from experimental data.
3. Digital Traceability: Needed better tracking and documentation of experimental processes and results.
4. Diverse Stakeholder Needs: Different visualization requirements for researchers versus business executives.

Objectives

• Automate data extraction from Electronic Lab Notebooks
• Develop analytical pipelines for processing experimental data
• Create customized dashboards for different stakeholder groups
• Improve digital traceability of research experiments

Implementation

1

Data Lake Implementation

Tools Used: AWS S3 Terraform AWS IAM

• Data Lake Architecture: Implemented a scalable data lake using AWS S3 with distinct zones for raw, processed, and curated data
• Infrastructure as Code: Used Terraform to provision and manage AWS resources
• Access Control: Implemented fine-grained access controls using AWS IAM roles and policies
• Data Organization: Created optimal bucket structure for different laboratory data types

2

Data Processing

Tools Used: AWS Glue Python PySpark

• ETL Pipelines: Developed AWS Glue jobs for processing complex laboratory data files
• Data Cataloging: Utilized AWS Glue Data Catalog for metadata management
• Data Quality: Implemented data quality checks and validation rules
• Schema Evolution: Managed schema changes and versioning for laboratory data

3

Data Storage Optimization

Tools Used: AWS S3 AWS Glue

• Data Formats: Optimized storage using Parquet and other columnar formats
• Data Partitioning: Implemented efficient partitioning strategies for improved query performance
• Data Lifecycle: Configured data retention and archival policies

4

Visualization

Tools Used: Spotfire AWS Glue

• Analytics Platform: Integrated Spotfire for advanced analytics and visualization
• Custom Visualizations: Created specialized scientific visualizations for compound analysis
• Self-Service Analytics: Enabled researchers to create custom analyses and reports
• Data Connection: Established secure connections between Spotfire and AWS data lake

Results

• Improved Efficiency: Reduced data processing time by 75%, allowing researchers to focus more on analysis
• Enhanced Insights: Provided deeper understanding of compound behavior through automated analysis
• Better Traceability: Achieved complete digital documentation of experimental processes and results
• Stakeholder Satisfaction: Met diverse needs with customized dashboards for different user groups

Conclusion