Hi everyone, I have a question regarding performance. More specifically, between DirectQuery and Live connection to a PBI Service Dataset. I'm going to share as much details as possible. Sorry in advance if the post becomes lengthy.
Thus far, I have been creating reports in PBI Desktop which would use multiple DirectQueries (some for fact tables, some for dimension tables and so on) to fetch underlying data. The source for direct queries is SQL Server (cloud). However, we noticed that for some visuals there are millions & millions of rows being fetched which is causing significant delay in rendering of the visual.
What I have done so far is - I published the report to PBI Service, then deleted it from there but kept the dataset in PBI Service. My question is if I create an empty report in PBI Desktop and create a Live connection to the aforementioned dataset in PBI Service, will that improve the performance ?? The reason I am concerned is I think even the dataset in PBI Service consists of DirectQueries to SQL Server. So, if live connection improves performance, where exactly is the latency reduced ?
I think this question is very specific and therefore I have not found the answer to it (yet). I figured asking on community would be faster. Also, in the scenario of Live connection, does scheduled refresh (and incremental refresh -- Is this an option here ??) play any important role. if you're still here, thanks in advance for reading patiently. Any information/links would be helpful.
