Introduction

A leading pharmaceutical company faced significant challenges in ensuring compliance with regulatory standards and maintaining up-to-date dashboards for leadership review. The company needed a comprehensive solution to streamline data ingestion, transformation, and presentation. James along with other Planisware experts were engaged to develop an end-to-end pipeline that would provide real-time, accurate insights across regions, vendors, and functional lines.

Challenges

1. Data Silos: The company had data stored in multiple silos, making it difficult to consolidate and analyze.
2. Compliance Monitoring: Ensuring compliance with various regional regulations was complex and time-consuming.
3. Outdated Dashboards: Leadership lacked real-time insights due to outdated and manually updated dashboards.
4. Scalability Issues: Existing systems were not scalable to handle increasing data volume and complexity.

Objectives

• Integrate disparate data sources into a single, coherent data warehouse.
• Automate compliance monitoring to ensure adherence to regional regulations.
• Develop real-time dashboards to provide leadership with up-to-date metrics.
• Create a scalable data pipeline to accommodate future growth.

Solution Components

Solution

1

Data Ingestion

Tools Used: PowerShell Python OpenText Alloy

• Data Sources: Collected time reporter data stored in a shared drive (NAS) from their time tracking and roster systems and some data from Planisware using OpenText Alloy.
• Data Ingestion: Developed PowerShell batch scripts to automate the data ingestion process from these sources.

2

Data Transformation

Tools Used: PowerShell Python SQL

• ETL Process: Designed an Extract, Transform, Load (ETL) process using Python to handle data processing efficiently.
• Data Cleansing: Applied data cleansing techniques to remove duplicates, correct inconsistencies, and fill missing values.
• Compliance Checks: Implemented automated compliance checks during the transformation phase to ensure data adhered to regional regulatory standards.
• Data Modeling: Structured the data into a star schema to facilitate efficient querying and reporting.

3

Data Warehousing

Tools Used: Oracle AWS RDS

• Data Storage: Used Oracle databases hosted in AWS for scalable and high-performance data storage.
• Data Integration: Integrated transformed data into a centralized data warehouse, ensuring seamless access and analysis.

4

Dashboard Development

Tools Used: Spotfire

• Dashboard Design: Created intuitive and interactive dashboards using Spotfire.
• Real-time Updates: Configured the dashboards to pull data in real-time from the data warehouse, ensuring leadership always had access to the latest metrics.
• Customization: Developed custom views to cater to different stakeholders, including regional managers, compliance officers, and executive leadership.
• Metrics: Key metrics included compliance status by region, vendor performance, functional line efficiency, and overall operational health.

Results

• Improved Compliance: Automated compliance checks reduced the time and effort required by as much as 90% to ensure adherence to regulations, minimizing the risk of non-compliance.
• Real-time Insights: Leadership gained access to real-time dashboards, enabling more informed decision-making and quicker response to portfolio changes.
• Enhanced Data Quality: Data cleansing and transformation processes improved overall data quality, leading to more accurate reporting. It also uncovered issues that the client didn't know they had in their processes.
• Scalability: The scalable data pipeline supported future growth, ensuring the company could handle increasing data volumes and complexity.

Conclusion