Replacing 200 Excel Reports: SAP, Oracle and Salesforce into Snowflake with Power BI

Last updated: October 2026

When each business function keeps its own system and its own spreadsheets, meetings turn into arguments about which number is right. In the project described here, the VP of Manufacturing would quote a batch yield from Tuesday's report and the quality team a different figure from Thursday's, and both were correct when they were generated because the data had changed between pulls.

This article describes the platform we built for a mid-size pharmaceutical company, covered in our pharma reporting case study. Clinical trial data lived in Oracle, manufacturing quality data in SAP and the sales pipeline in Salesforce, and the reporting team maintained more than 200 Excel workbooks. Preparing data packages for each FDA audit took a five-person compliance team three weeks. Project details come from that engagement. The code is a simplified illustration written for this article.

One warehouse, a schema per function, shared dimensions

We built a Snowflake warehouse with three dedicated schemas, clinical, manufacturing and commercial, plus a shared schema of conformed dimensions that span business units: products, sites, time periods and employee hierarchies. Shared dimensions allow a report to place a product's clinical, manufacturing and sales figures side by side, because every schema uses the same product and site keys.

SAP: the ADF SAP Table connector

Manufacturing batch records, quality deviations and equipment maintenance logs came from SAP every six hours through Azure Data Factory's SAP Table connector, which reads SAP tables directly without middleware such as SAP BW. It runs on a self-hosted integration runtime, version 3.17 or later, with the 64-bit SAP Connector for Microsoft .NET 3.0 installed, and by default it reads through the function module /SAPDS/RFC_READ_TABLE2. Large tables can be split into partitions on an integer, a calendar year, month or date, or a time column, and calendar dates use the eight-digit YYYYMMDD format that SAP stores. Microsoft recommends setting the number of partitions to a multiple of the copy activity's parallel copies.

"source": {
  "type": "SapTableSource",
  "rfcTableFields": "AUFNR, GSTRP, GAMNG",
  "rfcTableOptions": "GSTRP GE '20260101'",
  "partitionOption": "PartitionOnCalendarDate",
  "partitionSettings": {
    "partitionColumnName": "GSTRP",
    "partitionLowerBound": "20260101",
    "partitionUpperBound": "20261231",
    "maxPartitionsNumber": 8
  }
},
"parallelCopies": 4

New extractions do not need an SAP developer, because the connector reads the underlying tables. They do need someone who understands SAP's table structure, which is not intuitive, and we spent a week mapping the tables behind manufacturing batch records, AFKO, AFPO, JEST and about a dozen join tables, before any data moved.

SAP Note 3255746, which restricts third-party use of the ODP RFC interface, is also relevant. Microsoft's documentation says the resulting patch blocks calls from ADF's SAP CDC connector, which Microsoft no longer recommends, pointing to Microsoft Fabric options instead. Microsoft does not say whether the note affects the SAP Table connector, so we recommend confirming your approach with your SAP account team.

Oracle: watermark-based incremental loads

Modified clinical records were pulled from Oracle every four hours. ADF's native change data capture does not support Oracle as a source, so the pattern for Oracle is what Microsoft calls customer-managed delta extraction: a watermark table holds the last value loaded, one Lookup activity reads it and another reads the current maximum from the source, the copy query selects the rows between the two, and a stored procedure writes the new value back after the copy succeeds. Our guide to incremental loading in ADF walks through it in detail.

SELECT *
FROM clinical.subject_visits
WHERE last_updated > TO_TIMESTAMP(
        '@{formatDateTime(activity('LookupOldWatermark').output.firstRow.WATERMARK_VALUE, 'yyyy-MM-dd HH:mm:ss')}',
        'YYYY-MM-DD HH24:MI:SS')
  AND last_updated <= TO_TIMESTAMP(
        '@{formatDateTime(activity('LookupNewWatermark').output.firstRow.NEW_WATERMARK, 'yyyy-MM-dd HH:mm:ss')}',
        'YYYY-MM-DD HH24:MI:SS')

Use version 2.0 of ADF's Oracle connector for new pipelines; Microsoft lists version 1.0 as being at the removal stage.

Salesforce through a managed connector

Salesforce data came through Hevo Data, chosen over ADF's Salesforce connector because it handled Salesforce's API rate limits and bulk queries more gracefully. It synced every two hours, covering opportunities, account hierarchies, territory assignments and custom objects for formulary tracking. Hevo's current Salesforce source uses Salesforce's Bulk API 2.0, and for teams created after Hevo's Release 2.21 it syncs every six hours by default, with a minimum of 30 minutes.

Row-level security from a mapping table

The 12 Power BI dashboards cover clinical trial status, clinical safety, manufacturing yield, manufacturing compliance, the sales pipeline, territory performance, formulary wins and losses, and five executive summaries. Each one applies row-level security, so a clinical project manager sees only their assigned trials and a regional sales director only their territory.

Row-level security was harder to implement than expected, because the Active Directory structure did not match the reporting hierarchy the business wanted. The VP of Quality needed manufacturing data for every site, but Active Directory placed that role in the headquarters organisational unit. We built a security mapping table in Snowflake that maps Active Directory groups to Power BI RLS roles, with an admin interface for the IT team to manage exceptions.

Teams building something similar today can also use what Microsoft documents as dynamic row-level security, where one role filters a user-mapping table in the model to the signed-in user and relationships carry that filter to the dimensions. The example below shows the general pattern, not the configuration from this project.

-- Role filter on a user-mapping table with one row per user and site.
-- In the Power BI service, USERPRINCIPALNAME() returns the signed-in user's UPN.
[UserPrincipalName] = USERPRINCIPALNAME()

Microsoft's guidance adds a few rules worth following. Filter dimension tables, not fact tables, and remember that filters only travel along active relationships. Role members can be Microsoft Entra security groups, distribution groups or mail-enabled groups, but not Microsoft 365 groups. And row-level security restricts only users with Viewer permission; workspace Admins, Members and Contributors see all the data in the model.

Audit packages and switching off Excel

FDA audit data packages are now generated from Snowflake views that pre-join clinical, manufacturing and commercial data in the format the compliance team needs. They run a parameterised Power BI report with date range and product filters and export it to PDF. Preparation that took three weeks now takes about two days, most of it review. At loaded cost the compliance team had been spending about $180,000 a year assembling audit data. The change saves roughly $150,000 a year in labour, and that saving persuaded the CFO more than the dashboard consolidation did.

The dashboards were built by week six, but the Excel reports did not stop until week ten, because several directors kept asking for one more month in the old format. The CIO eventually set a hard cut-off date and removed access to the shared Excel folders. People keep using old reports for as long as they are available, so we recommend agreeing the cut-off date at the start of the project. The company now has one source of truth across its three business units, and meeting time fell by 30%.