Forum Discussion
performance optimization for direct query in power bi
Hi Friends,
I have power BI report direct query connection mode.my report is slow while rendering the visuals.used performance analyzer and came to know that calender slicer and high cardinality field used in slicer because of these performance is poor.please suggest me how can I optimise report performance.I would appreciate your support.
Two things to try first since the performance analyzer flagged the slicers.
For the calendar slicer, switch it from a Between or range style to a Dropdown, or split it into separate Year and Month slicers. The Between slicer fires two queries on every change and is one of the most common DirectQuery slowdowns.
For the high cardinality slicer, change it to a Dropdown with search instead of the List style, and consider replacing it with a hierarchical drill so users filter by a low cardinality field first.
Then go to File, Options, Current File, Query Reduction and turn on "Add an Apply button" for slicers and filters. That way several changes only fire one round of queries instead of one per click.
If you can use a composite model, set the dimension tables that feed your slicers to Dual storage mode. Slicer dropdowns then read from the in memory cache instead of hitting the source on every interaction.
If this works for you, kindly mark it as the solution and give a thumbs up.
Thanks,
Shai Karmani
4 Replies
What I would also recommend you do is to use the performance analyzer in Power BI Desktop and see which one of the visuals are taking time to query and return the results and then work with your DBA to see if there are some optimizations or aggregations that you can do to improve the performance.
Two things to try first since the performance analyzer flagged the slicers.
For the calendar slicer, switch it from a Between or range style to a Dropdown, or split it into separate Year and Month slicers. The Between slicer fires two queries on every change and is one of the most common DirectQuery slowdowns.
For the high cardinality slicer, change it to a Dropdown with search instead of the List style, and consider replacing it with a hierarchical drill so users filter by a low cardinality field first.
Then go to File, Options, Current File, Query Reduction and turn on "Add an Apply button" for slicers and filters. That way several changes only fire one round of queries instead of one per click.
If you can use a composite model, set the dimension tables that feed your slicers to Dual storage mode. Slicer dropdowns then read from the in memory cache instead of hitting the source on every interaction.
If this works for you, kindly mark it as the solution and give a thumbs up.
Thanks,
Shai KarmaniHi srividya182020 ,
Thank you for reaching out to Microsoft Fabric Community and Thanks to GilbertQ and Shai_Karmani for Sharing valuable insights.
Just wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.Best Regards,
Abdul Rafi.
Hi srividya182020 ,
Could you please confirm if the issue has been resolved? If not, feel free to reach out if you have any further questions.
Your update would be helpful for other members who may face a similar issue.
Best Regards,
Abdul Rafi
