Hello @akiko_lim,
I would like to highlight some of the facts as far as speed and performance is concerned -
-
Upon Importing the Data, the Power BI ensures the queries are loaded quickly and all changes to the visual are reflected immediately whereas while using the DirectQuery option, queries are sent to the underlying data source to retrieve the necessary data. The time taken to refresh the visual will depend upon the performance of the underlying data source.
-
DirectQuery can prove to be a very useful/viable option only if your data is changing frequently and a near real - time reporting is required and can also be helpful in applying data sovereignty restrictions.
-
If refreshing each visual takes just a few seconds then the user experience would be reasonable but if the slowness of the source causes individual visuals to take longer than tens of seconds, the experience becomes extremely poor and queries may even time out.
-
When you use multiple data sources, it’s important to understand how data is moved back and forth between the underlying data sources, and the security implications it brings.
-
Similarly, there are limitations in the data transformations that can be applied within Query Editor. With imported data, a sophisticated set of transformations can easily be applied to clean and reshape the data before using it to create visuals whereas those transformations are more limited in the DirectQuery.
-
When in DirectQuery mode, it’s not possible to use the family of DAX PATH() functions that generally handle Parent-Child structures and also it’s not possible to use the Clustering capability, to automatically find groups.
-
A time-out of four minutes is applied to individual queries in the Power BI service. Queries taking longer than that shall fail.
-
If the data in the underlying source is changing, there’s no guarantee that each visual shows the data at the exact same point of time.To guarantee this consistency would require the overhead of refreshing all visuals whenever any visual refreshed, in tandem with the use of costly features like Snapshot Isolation in the underlying data source.
-
While it’s possible to switch a model from DirectQuery mode to use import mode, all the necessary data must be imported. It’s also not possible to switch back, primarily because of the set of features not supported in DirectQuery mode.
-
And lastly it’s not good or recommended to ‘materialize’ calculated data in SQL or database since will have an adverse impact on the overall performance of the report and that would not be the great user experience. Therefore, @sam.mckay has always recommended to rely most of the times on measures in order to achieve diverse analysis.
Here’s also the link provided for the reference exclusively relating to the DirectQuery in Power BI.
Hoping this be helpful to you in achieving the desired analysis.
Please feel free to write back in case I’ve missed out on anything.
Thanks & Warm Regards,
Harsh
[About using DirectQuery in Power BI] -
(https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about)