Back to Blog

Power BI DirectQuery with SAP HANA - Multi-Dimensional vs Relational Approaches

April 24, 2026•8 min read•Michael Ridland

If your organisation runs SAP HANA, you've probably already been asked to get that data into Power BI. Maybe it's the CFO wanting real-time dashboards. Maybe it's the ops team tired of waiting for overnight extracts. Either way, Power BI's DirectQuery connector for SAP HANA is the tool Microsoft gives you for this job, and it comes with a choice that affects everything downstream - do you treat HANA as a multi-dimensional source or a relational one?

This is a decision most teams make once and live with for a long time. I want to walk through what each option actually means in practice, because the official documentation is thorough but doesn't quite convey how different the two experiences are.

The Two Connection Modes

When you connect Power BI to SAP HANA via DirectQuery, you pick one of two approaches in the Options dialog under DirectQuery settings.

Multi-dimensional (the default) treats your SAP HANA analytic or calculation view like an OLAP cube. Power BI sees it as a structured, pre-aggregated source - similar to how it connects to SAP BW or Analysis Services. You pick one view, and all its measures, hierarchies, and attributes show up in your field list. That's it. No Power Query, no joins, no reshaping.

Relational treats SAP HANA more like a SQL database. You get the flexibility to write queries, add calculated columns, bring in data from other sources, and build your semantic model in Power BI. But you take on the responsibility of making sure aggregations work correctly.

Here's the thing that catches people out: this setting applies to new connections only. Existing reports keep whatever mode they were created with. And you can't switch an existing report from one mode to the other. So choose carefully.

When Multi-Dimensional Makes Sense

For most SAP HANA reporting scenarios we encounter with Australian enterprises, multi-dimensional is the right starting point. Here's why.

SAP HANA calculation views are designed to handle aggregation logic. Non-additive measures like ratios, counters, and weighted averages get calculated by HANA itself, not by Power BI. This matters because Power BI's aggregation engine doesn't always know the right way to roll up a measure that the SAP team has carefully designed. With multi-dimensional mode, HANA does the maths. Power BI just displays the results.

We worked with a manufacturing client who had complex cost allocation measures in their HANA views. These measures used specific aggregation logic that wouldn't make sense if Power BI tried to re-aggregate them at different granularities. Multi-dimensional mode ensured the numbers matched what their SAP team expected to see.

The trade-off is flexibility. You lose quite a lot of Power BI's modelling capabilities:

  • No calculated columns. This means no grouping, no clustering, no derived fields in Power BI. Everything needs to exist in the HANA view.
  • No relationships between views. You pick one view and that's your model. You can't join in a customer master table from a different view.
  • No Table View in Desktop. You can't inspect row-level data in the modelling interface.
  • Limited DAX. Some aggregate functions over tables won't work.
  • Visual restrictions. You can't change column aggregations on a visual, and dragging a column to the filters area uses the underlying data values only.

These restrictions sound harsh, and they are. But they exist because multi-dimensional mode guarantees that what you see in Power BI is what HANA calculated. That guarantee is worth a lot when finance teams are comparing numbers between systems.

When Relational Mode is the Better Fit

Relational mode opens up the full Power BI modelling experience. You can add calculated columns, combine data from multiple sources, define relationships, and write whatever DAX you need. It feels more like normal Power BI development.

The risk is aggregation. When you treat HANA as a relational source, Power BI handles the aggregation logic. If you've got a measure that counts distinct customers, Power BI might double-count when it shouldn't, or miss records when it re-queries at different granularities. Non-additive measures are the biggest danger zone here.

Relational mode also loses SAP HANA hierarchies. If your organisation relies on SAP hierarchies for organisational structure, cost centres, or product categories, you'll need to recreate that structure in Power BI somehow - or accept a flat view of the data.

We've used relational mode successfully when clients needed to blend SAP HANA data with other sources. A retail client wanted to combine their HANA sales data with Google Analytics data from a separate database. Multi-dimensional mode can't do that. Relational mode let us build a composite model that brought both sources together. But we had to be very careful about which measures we pulled from HANA and how we handled aggregation on the Power BI side.

My honest advice: if you're considering relational mode, keep your views simple. Few columns, minimal joins, and stick to additive measures where possible. If you need complex measures, consider recreating them as DAX measures in the Power BI semantic model rather than relying on Power BI to correctly fold the aggregation back to HANA.

Variables and Parameters

One feature that works well in multi-dimensional mode is HANA view variables. If your calculation view has input parameters - things like fiscal year, company code, or currency - Power BI prompts you for those values when you connect. You can change them later through Transform Data > Edit Parameters.

This is genuinely useful. Instead of building complex slicers and filters in Power BI to restrict your dataset, you set the scope at the connection level. For large HANA datasets, this can make a real difference to performance because HANA only processes the data that matches your variable values, rather than filtering after the fact.

Practical Setup Tips

A few things I wish someone had told me the first time we set up a SAP HANA DirectQuery connection.

Test with your actual views, not sample data. The behaviour differences between multi-dimensional and relational mode only become apparent when you hit real-world complexity. A simple view with three measures and four dimensions won't show you the aggregation problems or the performance differences.

Get your SAP HANA team involved early. Whichever mode you choose, the quality of your reports depends on the quality of the underlying HANA views. With multi-dimensional mode, you're entirely dependent on the view structure. With relational mode, you need to understand what each column actually represents. SAP developers and Power BI developers need to work together. This sounds obvious but it's surprising how often these teams operate in silos.

Performance depends on the view, not the mode. I've seen people assume that relational mode is slower because Power BI sends more queries. That's sometimes true, but a poorly designed calculation view will be slow regardless of which mode Power BI uses. HANA query performance is largely about view design, indexing, and how much data you're asking for.

Watch the aggregation option carefully. In the Power BI Options dialog, the "Treat SAP HANA as a relational source" checkbox applies globally to new connections. If you're working on multiple reports, some connecting to views that suit multi-dimensional and others that suit relational, you'll be toggling this setting. It's easy to accidentally create a connection with the wrong mode, and there's no way to change it after the fact.

Attributes and Labels in Multi-Dimensional Mode

There's a specific behaviour in multi-dimensional mode that's worth understanding. SAP HANA lets you define one attribute as the label for another. For example, a Product attribute might have key values like 1, 2, 3 and a ProductName label attribute with values like Bike, Shirt, Gloves.

In Power BI, this appears as a single field called Product that displays the label values but sorts and groups by the key values. There's also a hidden Product.Key column if you need to access the raw keys. This works well and mimics how SAP BEx Analyzer handles the same scenario, but it can confuse Power BI developers who aren't used to hidden sort columns.

Similarly, attributes that participate in hierarchies are hidden by default in the field list. You can show them through the context menu (View hidden), but the default behaviour is to expose hierarchies rather than their constituent attributes. This is sensible - it prevents report authors from accidentally breaking drill-down paths - but it means your field list might look sparse compared to what you see in HANA Studio.

Our Recommendation

For most organisations we work with through our Power BI consulting practice, we start with multi-dimensional mode. It's safer. The aggregation guarantees matter when you're building reports that finance teams and executives will rely on for decisions. The flexibility restrictions are real, but they usually push you toward better data modelling practices in HANA rather than working around them in Power BI.

If you genuinely need to combine SAP HANA data with other sources, or if your reporting requirements need calculated columns and custom relationships, then relational mode is there for you. Just go in with your eyes open about the aggregation risks. Test thoroughly. Compare numbers against your SAP reports before going live.

If you're working through a SAP HANA to Power BI migration and want experienced people looking over your shoulder, get in touch with us. We've done this enough times to know where the gotchas hide, and we can help you avoid the mistakes that waste weeks.