SAP HANA Direct Query for Shared Datasets

[rbrocks]

The company I work for has SAP HANA and has started to develop calculation views to consolidate ERP data for end user consumption and reporting. Then Power BI shared datasets are being created by connecting via Direct Query (relational mode) to these calculation views. The Power BI datasets are shared with Power Users for report development.

This approach has some pros.

1. The large amount of data is not stored in our Power BI capacity. 
2. Data replicated from our various SAP ERP instances can be displayed near real time in Power BI without a need for refresh

However, I am noticing a lot of cons to this approach.

• The datasets can be very granular to try and be flexible for a wide range of reporting needs. But as a result, the performance of the visuals is often slow on these large datasets. This is a huge sticking point for our power users, especially when they do not want near real time data.
• I have concerns with the number of queries that could end up hitting the HANA database via direct query as this approach grows and power users develop and distribute more reports. 
• There are DAX restrictions on functionality with Diect Query so less flexibility on the Power BI side and more reliability on IT to alter views as needed. 
• We are using relational mode which does not always aggregate non-additive functions correctly.
• I do not believe the new chaining functionality is supported with SAP HANA DQ (Direct query for Power BI Datasets and Analysis Services preview feature) so users are limited in ability to combine data with an outside source.

So long story short, I am looking for anyone else who has worked with Power BI and SAP HANA and what their experiences are. Has anyone else successfully used SAP HANA Direct Query in their self-service BI approach? Or in your experience, should we be looking at import mode for most items with Direct Query only being used when near real time data is needed. Any experiences or expertise shared is appreciated. 

[v-henryk-mstf]

Hi @rbrocks ,

To use SAP HANA, you must have the SAP HANA ODBC driver installed on the local client computer for the Power BI Desktop's SAP HANA data connection to work properly.

Because SAP HANA supports both DirectQuery and Import options, according to your description, I think you can use composite models to alleviate the limitations of slow query caused by dq mode.

One of its advantages is Storage mode, You can now specify which visuals query back-end data sources. Visuals that don't require a query are imported even if they're based on DirectQuery. This feature helps improve performance and reduce back- end load.

And you can also use Performance Analyzer, it helps us in understanding how the performance of the visuals can be optimized. Performance analyzer collects and displays the performance information of each visuals or DAX formula in real time.

For more details, you can read related document:

Use SAP HANA in Power BI - Power BI | Microsoft Docs

Use composite models in Power BI Desktop - Power BI | Microsoft Docs

Use storage mode in Power BI Desktop - Power BI | Microsoft Docs

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Consider the use of Aggregations. It is a mix of import and Direct Query, and may be able to satisfy both your audiences  (and put less pressure on HANA).