How a Pharma Company Replaced 200 Excel Reports with 12 Power BI Dashboards

The Challenge

This mid-size pharmaceutical company runs clinical trials, manufactures generic drugs, and operates a commercial sales team across 3 therapeutic areas. Each function had its own system: Oracle for clinical trial data management, SAP for manufacturing quality metrics and production scheduling, and Salesforce for the commercial sales pipeline. Three systems, three data silos, zero integration.

The reporting team maintained 200+ Excel workbooks. Every month, they'd pull exports from each system, paste them into workbook templates, apply formulas, check for broken references, and email the finished reports to stakeholders. The clinical ops team got a 47-tab workbook tracking trial enrollment, adverse events, and site performance. Manufacturing got a separate set of workbooks for batch yield, deviation rates, and equipment uptime. Sales got pipeline reports, territory performance, and quota attainment. None of these workbooks talked to each other.

The worst part: 30% of stakeholders' time in meetings was spent debating which version of a number was correct. The VP of Manufacturing would quote a batch yield number from last Tuesday's Excel report. The quality team would quote a different number from their own report pulled on Thursday. Both were "correct" at the time they were generated, but the underlying data had changed between pulls. Nobody trusted the numbers, so everybody maintained their own shadow spreadsheets.

FDA audit prep was the breaking point. The compliance team needed to assemble data packages covering trial data, manufacturing quality records, and adverse event reports. This required pulling data from all 3 systems, cross-referencing it manually, and producing audit-ready documentation. It took the 5-person compliance team 3 full weeks to prepare for each audit. With 2 audits per year, that's 6 weeks of senior staff time just assembling data packages.

What We Built

We built a Snowflake data warehouse with 3 dedicated schemas - clinical, manufacturing, and commercial - plus a shared conformed schema for dimensions that span business units (products, sites, time periods, and employee hierarchies).

Azure Data Factory handles the Oracle and SAP extractions. For Oracle, we used ADF's Oracle linked service with change data capture to pull only modified clinical records every 4 hours. For SAP, we used ADF's SAP Table connector - this is the native connector that reads directly from SAP tables without requiring a separate middleware layer like SAP BW. The SAP extraction pulls manufacturing batch records, quality deviations, and equipment maintenance logs on a 6-hour schedule.

Hevo Data handles the Salesforce integration. We chose Hevo over ADF's Salesforce connector because Hevo handles Salesforce's API rate limits and bulk query optimization more gracefully. Salesforce syncs every 2 hours, pulling opportunity data, account hierarchies, territory assignments, and custom objects for formulary tracking.

We created 12 Power BI dashboards organized by business function: clinical trial status (enrollment, site performance, protocol deviations), clinical safety (adverse events, serious adverse events, safety signal tracking), manufacturing yield (batch-level yield rates, trend analysis, OOS investigations), manufacturing compliance (deviation CAPA tracking, equipment qualification status), sales pipeline, territory performance, formulary wins/losses, and 5 executive summary dashboards. Each dashboard uses row-level security tied to Active Directory groups, so a clinical project manager only sees data for their assigned trials, and a regional sales director only sees their territory.

The FDA audit data packages now auto-generate. We built a set of Snowflake views that pre-join clinical, manufacturing, and commercial data in the exact format the compliance team needs. They run a parameterized Power BI report with date range and product filters, export to PDF, and the package is ready. What took 3 weeks now takes 2 days - and most of that time is review, not assembly.

Results

Tech Stack

What We Learned

• The SAP Table connector in ADF is underrated. Most teams default to extracting SAP data through SAP BW or HANA views, which requires SAP-side development. ADF's SAP Table connector reads directly from underlying tables, which means no SAP developer needed for new extractions. The gotcha: you need to understand SAP's table structure, which isn't intuitive. We spent a week just mapping the relevant tables for manufacturing batch records (AFKO, AFPO, JEST, and about 12 join tables).
• Row-level security in Power BI was harder to implement than expected. The org chart in Active Directory didn't match the reporting hierarchy the business wanted. The VP of Quality needed to see manufacturing data across all sites, but AD had them grouped under the headquarters OU. We ended up building a custom security mapping table in Snowflake that maps AD groups to Power BI RLS roles, with an admin UI for the IT team to manage exceptions.
• Killing Excel reports requires political will, not just better dashboards. We built the dashboards in week 6. The Excel reports didn't actually stop until week 10 because several directors kept requesting "just one more month of the old format." The CIO eventually set a hard cutoff date and removed access to the shared Excel folders. You can't sunset old reports gradually - people will cling to them as long as they're available.
• FDA audit prep improvement was the strongest ROI argument. The 200-to-12 dashboard consolidation is impressive, but what sold the CFO was the audit prep reduction. At loaded cost, the compliance team was spending $180K/year just on audit data assembly. Cutting that to 2 days saved roughly $150K/year in labor, and the team now spends that time on actual compliance review instead of data wrangling.