Connecting Power BI to SAP: HANA, BW, and S/4HANA

SAP systems hold the most business-critical data in enterprise organizations — yet getting that data into dashboards has historically required expensive SAP BusinessObjects licenses, complex BEx queries, or custom ABAP programs. Power BI's native SAP connectors change this equation significantly. Organizations are now routing SAP HANA views, BW queries, and S/4HANA CDS views directly into Power BI dashboards without middleware or data replication.

This guide covers every SAP connector available in Power BI, when to use each, and how to configure secure, performant connections in production.

Key Takeaways

• Power BI offers four SAP connectors: SAP HANA, SAP BW (MDX), SAP BW (BAPI), SAP OData
• DirectQuery on SAP HANA leverages HANA's in-memory engine — queries return in milliseconds
• Import mode loads SAP data into Power BI's VertiPaq — enables rich DAX calculations
• S/4HANA CDS views expose clean, pre-modeled data ideal for DirectQuery
• SAP HANA SSO requires Kerberos delegation or SAP SSO setup — critical for user-level audit trails
• BW variables and prompts can be surfaced as Power BI parameters for interactive filtering
• On-premises data gateway is required for all on-premises SAP connections
• SAP BW OpenHub is the highest-performance path for large-scale data extraction

---

SAP Connector Overview

Power BI provides four distinct connectors for SAP systems:

Each connector has different performance characteristics, feature support, and setup complexity. Understanding which SAP data is available through which connector is the foundation of a successful integration.

---

Connector 1: SAP HANA

The SAP HANA connector is the most powerful option for organizations running HANA as their primary database (including S/4HANA on HANA).

Prerequisites

1. On-premises data gateway: Install on a Windows server with network access to HANA
2. SAP HANA ODBC driver: Install SAP HANA Client (includes ODBC driver) on the gateway server
3. HANA user account: Create a dedicated Power BI user in HANA with SELECT privileges on required views
4. SSL certificate: Export the HANA server's SSL certificate for secure connections

Connection Setup

In Power BI Desktop:

1. Get Data → SAP HANA Database
2. Enter server: hana-host:30015 (port = 3 + instance number + 15, e.g., 30015 for instance 00)
3. Select Data Connectivity mode: Import or DirectQuery
4. Navigate the catalog: Schemas → Views → Calculation Views

DirectQuery on SAP HANA

DirectQuery on HANA is Power BI's most powerful integration mode for real-time SAP data:

User opens dashboard
    ↓
Power BI generates SQL for each visual
    ↓
Query sent to HANA via ODBC
    ↓
HANA executes using in-memory engine (sub-second)
    ↓
Results returned to Power BI visual

Performance tip: HANA calculation views with Analytic Privilege-based access control align perfectly with Power BI's RLS requirements. Define data access in HANA, and Power BI passes the user context via SSO.

HANA Calculation Views

HANA Calculation Views are the recommended data access layer for Power BI. They provide:

• Pre-aggregated data: Reduce query complexity
• Joined tables: Hide HANA's column-store internals from Power BI
• Currency conversion: Built-in with HANA's currency conversion engine
• Analytic Privileges: Row-level access control at the HANA level

Connect to calculation views by navigating: Schema → _SYS_BIC → Calculation Views. Views prefixed with sap.xxx are SAP-delivered standard content.

Import Mode on SAP HANA

For complex DAX calculations and offline analytics, import mode loads HANA data into VertiPaq:

1. Build Power Query transformations on top of HANA views
2. Schedule incremental refresh (requires Power BI Premium or PPU) to load only new/changed rows
3. Define DAX measures for time intelligence that go beyond HANA's native capabilities

Incremental refresh configuration:

• Add RangeStart and RangeEnd parameters (Date/Time type) to your Power Query
• Filter the HANA query using these parameters
• Configure incremental refresh policy in the dataset settings (e.g., refresh last 2 days, keep 3 years)

---

Connector 2: SAP Business Warehouse (BW)

SAP BW stores decades of enterprise data in InfoCubes, DSOs, and BEx queries. Power BI's BW connector can expose this data directly to dashboards.

SAP BW MDX Connector

The MDX connector queries BW using MDX (Multidimensional Expressions) protocol — the same protocol used by SAP BusinessObjects Analysis for Office.

Setup:

1. Install SAP BW Frontend Client on the gateway server
2. Connect to BW system: Application Server + System Number + Client
3. Browse BEx queries, InfoCubes, and MultiProviders
4. BW variables surface as Power BI query parameters

BW Variables in Power BI:

BEx queries with mandatory variables (selection screens) can be configured in Power BI as parameters. For example, a BW query requiring Fiscal Year selection shows a parameter dialog in Power BI. This is critical for queries that would time out without appropriate date filters.

Limitation: MDX connector reads BW's OLAP layer — performance is constrained by BW's MDX server. Very large InfoCubes can be slow.

SAP BW BAPI Connector (Recommended)

The BAPI connector uses SAP's RFC (Remote Function Call) protocol directly, bypassing the MDX layer. It delivers:

• Higher throughput for large data volumes
• Better performance for flat extractions
• Access to BEx query results via BAPI calls

For large-scale data extraction from BW, the recommended architecture is:

SAP BW OpenHub → Azure Data Lake / Blob Storage → Power BI

BW OpenHub allows scheduled delta extractions of InfoProvider data to flat files or database tables, which Power BI then loads via Import mode. This pattern handles billions of rows without impacting BW performance.

---

Connector 3: S/4HANA via OData and CDS Views

SAP S/4HANA exposes data through two key mechanisms for Power BI integration:

SAP OData Connector

S/4HANA's SAP Gateway exposes OData v2 services for every Fiori application. Power BI's OData connector can consume these services directly:

1. Get Data → OData Feed
2. URL: https://s4hana-host:443/sap/opu/odata/sap/API_SALES_ORDER_SRV/
3. Authentication: Basic or Windows (Kerberos) with SAP system credentials

Available S/4HANA OData APIs (most relevant for Power BI):

CDS Views for Power BI

Core Data Services (CDS) views in S/4HANA provide pre-modeled, semantically rich data access. The recommended approach for Power BI + S/4HANA:

1. Identify standard analytical CDS views: SAP provides 1,000+ analytical CDS views with embedded annotations
2. Expose via VDM (Virtual Data Model): Use CDS views with @Analytics.dataCategory: #CUBE annotation
3. Access via HANA DirectQuery: Connect Power BI to HANA, navigate to the CDS view schema

Example CDS view for sales analytics: C_SalesOrderItemCube — provides sales order item data with dimensions (customer, material, plant, time) and measures (quantity, value, discount).

---

Security: SSO and Row-Level Access

SAP HANA Single Sign-On

For user-level audit trails (who queried what in HANA), configure SSO between Power BI and HANA:

Kerberos SSO setup:

1. Configure constrained delegation on the gateway server's Active Directory account
2. Map Windows AD users to HANA database users (SPS 10+ required)
3. Enable "Use SSO via Kerberos for DirectQuery queries" in the gateway data source settings

With SSO active, Power BI queries execute in HANA under the authenticated end user's identity — not the gateway service account. HANA's analytic privilege system then controls exactly which calculation views each user can access.

SAP SSO (Kerberos + RFC): For BW connections, SAP SSO 3.0 with Kerberos token exchange is the standard. The gateway server requires the gsskrb5.dll library from SAP and Kerberos SPN configuration.

Row-Level Security Architecture

There are two places to implement RLS for SAP data in Power BI:

Option 1: RLS in HANA (Recommended):

• Define Analytic Privileges in HANA that restrict which rows users can read
• Power BI queries are executed under the user's HANA identity via SSO
• Security enforcement happens at the database level — cannot be bypassed from Power BI

Option 2: RLS in Power BI:

• Import SAP data into Power BI dataset
• Define RLS roles using DAX filter expressions
• Map Azure AD users/groups to RLS roles
• Suitable when SSO is not available or when security requirements allow dataset-level enforcement

---

Performance Optimization

HANA DirectQuery Optimization

1. Use Calculation Views, not Column Tables directly: Calculation Views with aggregation nodes pre-summarize data at the HANA level
2. Enable query-level time-out: Set QueryTimeout in Power BI's data source settings to avoid hanging queries
3. Limit visual count: Each visual on a DirectQuery page generates a HANA query — keep pages under 20 visuals
4. Use HANA's aggregation behavior: Calculation Views with SUM, COUNT aggregations execute in parallel across HANA nodes

BW Import Performance

1. OpenHub extraction: Use SAP BW OpenHub to extract to Azure Data Lake, then import into Power BI via ADLS connector — much faster than MDX for large volumes
2. Query folding in Power Query: Ensure transformations fold to the BW/HANA level (avoid operations that break query folding)
3. Incremental refresh: For daily BW loads, configure incremental refresh to extract only delta records

---

Frequently Asked Questions

Do I need SAP licensing to connect Power BI to SAP systems?

Power BI's SAP connectors use standard interfaces (ODBC, RFC, OData) available in all SAP system licenses. However, SAP's licensing terms may require appropriate Named User licenses for the SAP accounts used by Power BI. Specifically, if Power BI queries read SAP data that would normally require an SAP Business Intelligence license (like BW BEx queries), SAP may argue those licenses apply. Consult with your SAP license manager before broad deployment.

What is the maximum data volume Power BI can handle from SAP HANA?

In DirectQuery mode, there is no theoretical limit — Power BI queries HANA for aggregated results, so even HANA tables with billions of rows return quickly if the query is optimized. In Import mode, Power BI Premium supports datasets up to 400GB (compressed VertiPaq format, equivalent to several TB of raw data). SAP HANA calculation views should be designed to return pre-aggregated results to keep response times under 2 seconds for dashboard visuals.

Can Power BI connect to SAP S/4HANA Cloud?

Yes — SAP S/4HANA Cloud exposes OData APIs and CDS views accessible to Power BI. The SAP HANA connector (via HANA Cloud) and SAP OData connector both work with cloud deployments. SAP recently introduced SAP Analytics Cloud integration with S/4HANA Cloud as the preferred embedded analytics path, but Power BI remains fully compatible through the OData and HANA Cloud interfaces.

How do I handle SAP BW variables in Power BI reports?

SAP BW variables that require user input (mandatory variables) surface as Power BI query parameters. Report authors configure default values for these parameters. For reports shared with end users, the parameters appear as filter controls. For fixed parameters (e.g., fiscal year always current year), use Power Query M expressions to generate the variable value dynamically based on today's date.

Is it better to use SAP HANA or SAP OData for Power BI integration?

SAP HANA connector (DirectQuery or Import) is generally superior for analytical workloads — it leverages HANA's in-memory engine and exposes rich calculation views. SAP OData is better for transactional data access (individual orders, customer records) where you need specific API-exposed business objects with built-in SAP authorization checks. For finance and operations dashboards, use HANA views. For application-specific data with SAP standard API coverage, OData is appropriate.

What happens if SAP HANA goes down — do Power BI dashboards break?

In DirectQuery mode, yes — Power BI visuals show an error if HANA is unavailable. This is the fundamental trade-off of DirectQuery: real-time data but no offline resilience. To mitigate this, use Import mode for critical executive dashboards (data refreshes on schedule, remains accessible during HANA outages) and reserve DirectQuery for operational dashboards where real-time accuracy is essential. You can also combine modes in a Composite Model.

---

Next Steps

Connecting Power BI to SAP is technically achievable but requires careful planning around connector selection, security architecture, and performance optimization. Organizations that get this integration right unlock real-time insights from their most business-critical data — without the cost of SAP BusinessObjects or custom ABAP development.

ECOSIRE's Power BI ERP integration practice specializes in SAP + Power BI deployments. We handle connector configuration, HANA view design, BW OpenHub setup, SSO security, and dashboard development as an end-to-end engagement.

Explore our Power BI ERP integration services or contact our team to discuss your specific SAP landscape and analytics requirements.