Forum Discussion
Direct Query performance issue
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.
8 Replies
Hi Anonymous , did you consider caching the data in a repository (ADLS / Onelake/ others)?
- Microsoft recommended architecture for PowerBI / Fabric integration with SAP (See the cached method):
- PowerBI can schedule the refreshes from SAP as per this guide: https://youtu.be/TA0-tya44K0
Questions? please reach out.
Anupam
Hi Anonymous
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.
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?Hi Anonymous ,
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.
Thanks for the reply from danextian , please allow me to provide another insight:
Hi, Anonymous
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.
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 queries sent to the database
- Avoid Complex Queries: Simplify Power Query transformations to ensure each query maps directly to a single relational database source table or view
- Analyze Performance: Use the Performance Analyzer in Power BI to identify and optimize slow-running queries
Implementing these strategies should help improve the performance of your Power BI reports and reduce the load on your SAP database
