Direct Query performance issue

[vs_7]

Hi Everyone,

I'm currently utilizing Direct Query Mode in Power BI for our dashboards. However, we're experiencing performance issues with SAP when multiple users are accessing the reports simultaneously.

Are there any alternative methods to alleviate the load on the database and reduce system consumption?

Thank you in advance.

@amitchandakplease suggest.

[anupamj]

Hi @vs_7 , did you consider caching the data in a repository  (ADLS / Onelake/ others)?

1. Microsoft recommended architecture for PowerBI / Fabric integration with SAP (See the cached method): 

https://techcommunity.microsoft.com/t5/azure-data-factory-blog/integrate-microsoft-fabric-with-sap-d...

1. PowerBI can schedule the refreshes from SAP as per this guide: https://youtu.be/TA0-tya44K0

Questions? please reach out.

Anupam

[suparnababu8]

Hi,
Pls follow some strategies to help alleviate the load on your database and reduce system consumption:

• Query Reduction: Enable query reduction options in Power BI settings to minimize the number of queri...
• Avoid Complex Queries: Simplify Power Query transformations to ensure each query maps directly to a ...
• Analyze Performance: Use the Performance Analyzer in Power BI to identify and optimize slow-running ...

Implementing these strategies should help improve the performance of your Power BI reports and reduce the load on your SAP database

[v-linyulu-msft]

Thanks for the reply from danextian , please allow me to provide another insight:

Hi, @vs_7 

Regarding the issue you raised, my solution is as follows:

1.First, you can use aggregation tables: create one or more aggregation tables that contain pre-computed summary data. This reduces the amount of computation when querying.

Here's the documentation:
Aggregation to speed up the performance of a Power BI report even if all IMPORTED - RADACAD
 

2.Second, use automatic aggregations: Power BI provides automatic aggregation capabilities that automatically create and maintain aggregation tables based on query patterns to optimize performance.

Here's a screenshot of the documentation:
 

Automatic aggregations overview - Power BI | Microsoft Learn
 

3.Finally, there are official links related to performance optimization, I hope it will be helpful to you:

Optimization guide for Power BI - Power BI | Microsoft Learn
Troubleshoot report performance in Power BI - Power BI | Microsoft Learn

Of course, if you have any new ideas, you are welcome to contact us.
 

Best Regards,

Leroy Lu

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

[danextian]

Hi @vs_7 

Several things to consider:

• Is direct query necessary? You might just need a combination of import and direct query (composite) or just plain old import.
• Look into user-defned aggregations (aggregation table).
• This one is very important: Where in SAP is the report getting data from? it shouldn't be from the same database the operation/production is using but from a data warehouse or a database replica.

[vs_7]

Hi ,
Thanks for the response.
I'm using the SQL statement to fetch data for 2 years from SAP PRD Database in direct query mode, and we are using the PowerBI Pro license.
The Power BI Dashboard is shared with more than 150+ users, and the SAP Database is getting slow when users access the single report on the same day.
I cannot use the import mode because the data I'm fetching has more than 20 lacs of records for two years, and it takes time to load the data.
How to resolve the issue?

[danextian]

Hi @vs_7 ,

It isn't advisable to be querying the same database the production is using. It is a best practice to use a replica or a data warehouse.

[anupamj]

Hi @danextian, ref to the advice "It isn't advisable to be querying the same database the production is using. It is a best practice to use a replica or a data warehouse."

This used to be best practice a few years ago, but SAP (& many other applications) are now merging OLTP & OLAP engines, driving towards embedded intelligence.

HANA is the platform for inbuilt intelligence within the transaction engine & even Fabric / PowerBI support realtime queries with SAP.

[danextian]

Hi @anupamj 

Thank you for the info. I think the question now is wether OP's  SAP is running on HANA as OP didn't mention so.