Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I want to understand this scenario a little better from you experts...
I have a simple dashboard with 3 tables. 2 out of those 3 are hardcoded value tables [Enter Data in Power Bi] and having 4 and 6 rows respectively, with 2 columns in each. Finally last table is from a SQL query that is used as "DirectQuery" to SQL Server 2016.
This query is on a SQL Server View and then having a where clause [Where ExecutionDate = Cast(Getdate() as Date)].
In SSMS this runs in 0 sec (ok.. less than 1 sec.).
MeterialDelivery [DirectQuery From SQL View and a Where clause Date Filter] - Every time returns 120 Rows for that day
BoxedBy [Enter Data - Only 4 Names]
ReceivedBy [Enter Data - Only 4 Names]
But my power bi performance is horribly slow. When checking performance analyzer as well as queries in SQL Profiler, something that I noticed is - PBI is pretty much sending 10 queries for my 10 visuals.
~ i.e. For example... 1 visual shows last_Meterial_Arrival_Time (i.e Lastest Arrival_Time). another visual shows number of meterials received already on current day. (count meterial receipt id). etc.
Now for each and every such visual - PBI is sending pretty much same query to SQL, where as my expectation was, that in one round - PBI will send query 1 time and then it will do derivation for above visuals with in power bi.
While I indeed want PBI to fetch real time/latest data from SQL via directquery, I do not want that at same time for each visual it will send similar query in SQL and eventually run 15 - 20 queries in SQL. Is there any way to improve performance?
Hi @RKM ,
In DirectQuery, whenever you click something in the report, a query is sent to the underlying data source. If you change a slicer, there will be sent a new query for every visual on the current page. You click on a bar on the bar chart, all the visuals send a query to the data source. You deselect the bar chart, all visuals send again a query to the data source.
Best Regards,
Jay
Hi @amitchandak
Thanks... but no bi-directional join. and Index where? Its a view.
@RKM , If you have a few view in the SQL server, You need to check it is on how many tables. Those tables have indexes in the join column.
Also, need to check how complex the view is
Hi @amitchandak
It is just one view, using 4 tables. View complexity is moderate.
But above all as I said, when running the source query in SQL - it is within fraction of second rerturnin result.
In Power Bi its slow because power bi not executing that one query one time, rather for each visual it is executing that query. That's where I am having a problem.
If I convert everything to import mode, then my PBI is lightening fast and refresh time is also very quick. But I can not really have it in Import mode, because we want to have near real time visibility and that's why I had to go for DirectQuery Approach
@RKM , Do you have bi-directional joins.
If yes, Try to avoid that.
Second, make sure you have index in the join columns
Please share sample data model
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
76 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |