Summary

A global pharmaceutical company automated its reporting pipeline using AWS, Python, and Power BI, enabling daily insights, reducing manual workload, and cutting licensing costs by 50%.

Challenge

The Swiss division of a global pharmaceutical company relied on manual, error-prone reporting processes. Reports were generated monthly, requiring manual data downloads from multiple sources, which consumed significant time and resources. These inefficiencies limited the company’s ability to make timely decisions and maintain data accuracy.

The objective was to automate and streamline the reporting process to reduce manual workload, improve accuracy, and increase the frequency of data refreshes. A scalable and flexible solution was needed to integrate multiple data sources and support daily reporting.

Solution

Marlabs developed a comprehensive, automated reporting solution using AWS services, Python, and Power BI.

Data collection and ingestion

In the first phase, the team automated data extraction from various sources including Salesforce, IQVIA, SFTP, emails, and S3. This ensured reliable and scalable ingestion of data into the system. Connectivity setups were established for each source, and initial validation checks were implemented to ensure data integrity from the start.

Data processing and storage

The second phase focused on organizing and storing data in structured layers—bronze, silver, and gold—within a PostgreSQL database hosted on AWS RDS. This layered approach enabled consistent data transformation and storage. The team also optimized the database for performance and scalability, ensuring it could handle growing data volumes.

Reporting and automation

In the final phase, automated reporting was implemented using Power BI and its REST API. Reports were refreshed daily, providing timely insights with improved accuracy. The team developed and validated Power BI dashboards, automated data refreshes, and set up monitoring to ensure consistent performance and reliability.

Results

The AWS-based solution delivered measurable improvements:

• Daily reporting enabled: Increased reporting frequency from monthly to daily, providing timely insights.
• Manual workload eliminated: Automated data extraction and processing reduced repetitive tasks.
• Data accuracy improved: Centralized and automated handling minimized errors.
• Cost efficiency achieved: Reduced licensing and operational costs by 50%.
• Scalable cloud solution: AWS services supported integration of new data sources and growing volumes.

Impact

The automation initiative delivered lasting benefits:

• Enabled daily reporting for timely insights
• Eliminated manual data handling
• Improved accuracy across reports
• Achieved 50% cost reduction
• Supported scalable data integration
• Streamlined analytics workflows