Back to Blog

Connecting Power BI to SAP BW with DirectQuery - What Actually Works

April 20, 2026•8 min read•Michael Ridland

SAP Business Warehouse is still the data backbone for a lot of large Australian organisations. Mining companies, manufacturers, retailers with long SAP histories - they've got years of financial, operational, and supply chain data sitting in BW cubes that they need to report on. Power BI's DirectQuery connector for SAP BW is the way Microsoft expects you to connect these two worlds, and it works. Mostly. With some caveats that you really need to understand before you start building reports.

I'm going to be blunt about this one: the SAP BW DirectQuery experience in Power BI is functional but it has rough edges that catch people off guard. The official documentation lists the limitations, but it doesn't give you a feel for how those limitations actually hit you in practice. That's what this post is about.

Why DirectQuery for SAP BW?

The alternative is importing data from SAP BW into Power BI's in-memory model. For small datasets, that's fine. But SAP BW cubes tend to be large and complex - we've worked with BW environments where a single InfoCube has billions of rows across dozens of dimensions. Importing that into Power BI is either impossible or impractical.

DirectQuery lets Power BI send MDX queries directly to the SAP BW server. No data duplication, no scheduled imports, and the data is always current. For organisations that need their Power BI reports to reflect what's in SAP right now, DirectQuery is really the only option.

How the Connection Works

This is where the first surprise hits. If you're used to connecting Power BI to SQL Server or Azure SQL, the SAP BW experience is different in a fundamental way.

With relational sources, you use Get Data or Power Query Editor to define which tables and columns you want. You can write custom queries, join tables, filter data - the full Power Query experience.

With SAP BW, there's no Power Query Editor. You connect to the SAP server, select an InfoCube or BEx Query, and that's it. All the key figures and dimensions from that cube appear in your field list. You can't filter at the connection level, you can't join in other data sources through Power Query, and you can't reshape the data before it hits your report.

This means your data modelling happens in SAP, not in Power BI. If the BEx Query or InfoCube isn't structured the way you need it for reporting, you need to go back to your SAP team and fix it there. This is a workflow shift that SAP shops need to plan for.

The Restrictions You Need to Know About

Microsoft lists these in the documentation, but let me translate them into what they actually mean for report authors.

No Calculated Columns

You can't create calculated columns in Power BI when using DirectQuery against SAP BW. This is a bigger deal than it sounds. Calculated columns are how a lot of Power BI developers create groupings, categorisations, and derived fields. Without them, you're relying entirely on what SAP provides.

The workaround is to create those derived fields in BW itself - add characteristics, create hierarchies, or build calculated key figures in the BEx Query. This works, but it means Power BI developers need SAP BW developers involved for what would normally be simple report-level changes.

Limited DAX Support

Not all DAX functions work against SAP BW. Aggregate functions over tables, for instance, aren't supported. You can write measures, but you need to stay within the subset of DAX that can be translated to MDX queries. In practice, this means sticking to straightforward calculations - sums, averages, ratios, and basic conditional logic. Complex time intelligence or table manipulation won't work.

No Custom Relationships

In a normal Power BI model, you define relationships between tables. With SAP BW, the relationships are whatever's defined in the InfoCube or BEx Query. You can't add new relationships in the Power BI model. This is usually fine because SAP BW cubes already have their dimension relationships built in, but it means you can't augment the model with additional reference data tables from other sources.

Visualisation Constraints

Some visual interactions don't work the way you'd expect. Cross-highlighting (clicking a bar in one chart and having other charts respond) is limited. Multi-selecting data points from different columns is disabled. Measure filtering is turned off. These aren't bugs - they reflect what the SAP MDX interface can and can't do.

The Numbers Might Not Match SAP

This is the one that causes the most pain, and I want to be direct about it.

BEx Queries in SAP often include local calculations - running sums, cumulated values, special aggregation rules. These calculations are applied at the BEx Analyzer level but aren't exposed through the public MDX interface that Power BI uses. The result is that Power BI might show different numbers than what your SAP users see in their BEx Analyzer reports.

This isn't a Power BI bug. It's a fundamental limitation of how SAP's public API works. But try explaining that to a finance team that sees different numbers in two reports and doesn't care about API architecture.

Currency handling is another pain point. SAP BW might have values in multiple currencies, and BEx Analyzer knows how to display currency-specific formatting or suppress meaningless cross-currency totals. Power BI doesn't get that formatting information through the MDX interface. You might see a total that sums Australian dollars and US dollars together as if they're the same unit. BEx Analyzer would show an asterisk in that scenario. Power BI just shows the (wrong) number.

How to Handle This

First, document which numbers might differ and why. Get your SAP team and your Power BI team in the same room before you start building reports and agree on what the expected differences are.

Second, use BEx Queries that minimise local calculations. The simpler the query, the closer the Power BI numbers will match. If you need complex aggregation logic, consider doing it in a dedicated SAP BW query designed specifically for Power BI consumption rather than repurposing an existing BEx Query.

Third, add clear labels and documentation to your Power BI reports. If a measure might differ from what users see in SAP, say so in the report. A tooltip that says "This total sums all currencies - see SAP for currency-specific breakdowns" saves a lot of confused support tickets.

Practical Recommendations

Start with simple cubes. Your first SAP BW DirectQuery report should use a well-structured InfoCube with straightforward key figures. Don't start with a complex BEx Query that has calculated members and exception aggregation rules. Get comfortable with the workflow first, then tackle harder scenarios.

Invest in SAP BW query design. Since Power BI can't reshape the data, the quality of your reports depends on the quality of your BEx Queries. Work with your SAP team to create purpose-built queries for Power BI consumption. This is genuinely the most important success factor we see on these projects.

Test numbers early and often. Build a reconciliation process that compares Power BI totals against SAP for a known set of test cases. Do this before you show anything to business users. Number discrepancies discovered in a stakeholder demo are much harder to recover from than ones found in testing.

Consider composite models for enrichment. While you can't add relationships to the SAP BW DirectQuery model directly, you can use Power BI's composite model capability to bring in additional import-mode tables alongside your DirectQuery source. This lets you add reference data, lookup tables, or supplementary information that doesn't exist in SAP.

Performance matters. DirectQuery means every visual interaction sends an MDX query to SAP BW. If your BW server is slow, your report will be slow. Work with your SAP Basis team to ensure the BW server is properly sized and that the queries Power BI generates can execute in a reasonable time.

Who This Is For

SAP BW DirectQuery in Power BI is best suited for organisations that have significant data in SAP BW, need near-real-time reporting, and are willing to invest in the SAP-side query design work that makes it all hang together. If you've got a small SAP dataset, just import it. If you need pixel-perfect SAP report reproduction, stick with BEx Analyzer or SAP Analytics Cloud.

But if you want to give business users the ability to build their own reports and dashboards on top of SAP data using a tool they already know - and you're willing to work within the constraints - DirectQuery is a viable path.

Our Power BI consultants have worked on several SAP BW integration projects across manufacturing, resources, and financial services. If you're considering this approach, we can help you evaluate whether it's the right fit and design the SAP-side queries that make it work properly. For broader questions about data strategy across SAP and Microsoft platforms, our Microsoft Fabric consulting team can help you plan how these pieces fit together.