SAP to Power BI Integration: Complete Enterprise Guide for S/4HANA, BW, and HANA

SAP and Power BI represent two of the most dominant enterprise platforms in modern IT portfolios—SAP for transactional ERP and HANA-based analytics, Power BI for self-service BI and executive reporting. Connecting them cleanly is one of the most technically demanding integration scenarios that SAP technical leads face, primarily because SAP data access surface is fragmented across multiple protocols, authorization models, and connectivity layers. This guide covers every major integration path in depth: the SAP BW connector, SAP HANA DirectQuery, S/4HANA OData feeds, CDS view exposure, on-premises gateway configuration, extraction pattern selection, performance optimization, and the failure modes that derail most implementations. Our Power BI consulting team and Power BI architecture practice have deployed these patterns across manufacturing, utilities, and financial services organizations running S/4HANA, BW/4HANA, and SAP HANA 2.0.

Choosing the Right Integration Path

Before configuring any connector, the single most important decision is selecting the correct integration architecture for your workload.

**1. SAP BW Open Hub / BAPI-Based Extraction (Import Mode)** Appropriate when your reporting semantic model is BW-centric, data volumes are large (tens of millions of rows), and end-users can tolerate scheduled refresh latency. Data is extracted from BW InfoProviders into a staging area (Azure Data Lake Storage Gen2 or a SQL Server landing zone) and then loaded into Power BI via import. This decouples Power BI refresh from live SAP load and enables Microsoft Fabric OneLake storage for downstream reuse.

2. SAP BW DirectQuery Connector (MDX/BICS) Appropriate for BW-based reporting where sub-minute data freshness is required and query volumes are manageable. Power BI sends MDX or BICS queries to BW query objects. Every visual interaction triggers a live query against BW. This is the highest-risk path for performance—poorly designed BEx queries, missing aggregates, and high concurrency will saturate BW dialog work processes immediately.

3. SAP HANA DirectQuery Appropriate when your landscape is S/4HANA with embedded analytics or BW/4HANA where the HANA database is the analytical layer. Power BI sends SQL SELECT statements directly to HANA calculation views or CDS-based views. Performance is generally superior to BW DirectQuery because HANA columnar in-memory engine handles Power BI generated SQL efficiently.

4. S/4HANA OData / REST API Extraction Appropriate for operational reporting on S/4HANA business objects where the source of truth is the S/4HANA application tier rather than BW. Data is pulled via OData V4 services exposed through S/4HANA API Business Hub. Not suited for large-volume historical reporting—OData pagination at scale is slow and fragile.

SAP BW Connector: Configuration and Extraction Patterns

The SAP BW connector in Power BI Desktop connects to BW via the SAP .NET Connector 3.0 (NCo), which must be installed on every machine running Power BI Desktop and on every on-premises data gateway node handling BW refresh. The NCo version must match the SAP kernel bitness (64-bit for all modern deployments).

Gateway Node Requirements: - Windows Server 2019 or 2022 - SAP .NET Connector 3.0 for .NET 4.0, 64-bit, same version across all cluster nodes - SAP GUI 7.70 or later - Network line-of-sight to SAP message server and application server ports - Service account with SAP system user authorization (S_RFC, S_SYST, and BW-specific authorizations)

Configure the gateway cluster with at least two nodes for production refresh reliability. A single-node gateway is a silent single point of failure.

DirectQuery vs. Import in BW Connector: In DirectQuery mode, Power BI sends MDX expressions to BW. A report with 10 visuals executing in parallel will consume 10 dialog work processes per user. For a report with 20 concurrent users, this is 200 simultaneous dialog work process requests. Most production SAP systems do not have that many available. Implement Import mode with scheduled refresh unless your BW system is dedicated to reporting.

Open Hub Extraction for Large Volumes: For data volumes exceeding 5 million rows per fact table, bypass the BW connector entirely and use Open Hub Destinations (OHD). Configure an OHD pointing to a flat file or database table, schedule a BW Process Chain to extract to the OHD, and land the data in ADLS Gen2 or Azure SQL Database. Power BI then imports from the staging layer. This scales to hundreds of millions of rows.

SAP HANA DirectQuery and CDS View Integration

SAP HANA DirectQuery is the preferred integration path for S/4HANA 1909 and later deployments. Power BI communicates with HANA via the SAP HANA ODBC Driver installed on gateway nodes. Performance is substantially better than BW DirectQuery because HANA SQL engine handles query execution natively.

HANA Calculation Views vs. CDS Views: HANA calculation views are the traditional analytical layer. Core Data Services (CDS) views are the strategic direction from SAP. For new integrations on S/4HANA, target CDS views with the annotation @Analytics.dataCategory that exposes them as analytical providers in the SAP VDM hierarchy.

**Row-Level Security Alignment:** This is the most frequently under-designed aspect. In fixed credentials mode, HANA sees only the technical user—you must enforce RLS entirely in Power BI DAX roles. In SSO mode (Kerberos delegation through the gateway), HANA applies the analytical privileges of the actual user HANA database user, enabling HANA-native RLS. SSO mode requires Active Directory Kerberos constrained delegation—a non-trivial configuration that typically requires 3-5 business days. Our data analytics practice regularly implements this delegation chain.

Performance Optimization: Common causes of degradation include calculated columns in Power BI (evaluated row-by-row, not pushed to HANA), complex DAX measures with nested CALCULATE (partial pushdown only), and relationships that cannot be expressed as single JOIN predicates. Move computation into HANA calculation views, use HANA Aggregation Views, and enable HANA query plan caching.

S/4HANA OData Integration and Fiori Embedding

OData V4 via S/4HANA API Business Hub: S/4HANA exposes hundreds of pre-built OData V4 services. Power BI native OData connector handles V4 protocol including server-side pagination and filtering. The critical limitation is volume: if your target dataset exceeds 500,000 records at full refresh, OData is not the correct extraction path.

For OData at sustainable volumes, implement incremental refresh in Power BI using the LastRefreshDate parameter pattern. This reduces per-refresh volume by 90% or more in steady-state operation.

Custom OData Services: For requirements not covered by standard services, custom OData services can be built using RAP (ABAP RESTful Application Programming Model), the strategic approach for S/4HANA.

**Embedding Power BI in SAP Fiori:** The most practical approach is a Fiori tile of type URL pointing to a Power BI report URL. Since Fiori Launchpad is typically federated to Azure AD, the user AAD token propagates to Power BI automatically. For tighter embedding, use the Power BI Embedded JavaScript SDK in a custom SAPUI5 component. See our Power BI architecture guide for embedded analytics scenarios.

On-Premises Data Gateway and Common Pitfalls

The on-premises data gateway is the single most common source of SAP-Power BI integration outages. Deploy a minimum of two gateway nodes in a cluster. Gateway nodes must be on the same version. Mixed-version clusters cause intermittent failures.

Network Requirements: The gateway initiates outbound HTTPS (port 443) and TCP (5671/5672 for Azure Service Bus). SAP connectivity requires outbound access to SAP application server RFC ports (33XX) and HANA ODBC ports (3XX15).

Common Pitfalls: - BEx Query Variable Handling: Mandatory variables require default values to function in DirectQuery mode. Power BI cannot present SAP variable input prompts. - HANA Analytical Privilege Gaps After Transport: When CDS views are transported from Dev to Prod, HANA privileges do not transport—they must be granted manually. - Gateway Connectivity During SAP Maintenance: SAP planned downtime causes all gateway connections to fail. Pre-communicate and pause scheduled refreshes. - Import Model Stale Data: Add a visible data freshness indicator to report canvas showing MAX(LastRefreshDate) to prevent business decisions on stale data. - BW Dialog Work Process Exhaustion: Monitor BW work process utilization after go-live of any DirectQuery report. If utilization exceeds 60% during peak hours, switch to Import mode.

Ready to design your SAP to Power BI integration architecture? Contact EPC Group for a technical assessment of your S/4HANA or BW/4HANA landscape and a recommended integration blueprint.