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.
@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
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
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.