When connecting to a data source, Power BI reports can use one of two different connection modes. This article describes the differences between them.
When a report operates in Import mode, data is queried from the data source and stored in a cache on a scheduled basis. The report must have a corresponding refresh schedule in order to update the data in the cache.
The vast majority of Davidson's reports operate in Import mode. It is the mode best-suited for most datasets, with the exception of very large datasets that encompass tens of millions of rows. Report performance is almost always quickest in Import mode because the report does not need to re-query data from the data source every time the user interacts or changes how the data is filtered. Instead, the report simply filters the cached data, resulting in much more rapid interactions.
When a report operates in DirectQuery/Live mode, data is queried live from the data source. There is no caching involved, and thus there is no requirement to maintain a dataset refresh schedule. While this may sound ideal, it actually carries significant downsides, and thus is rarely used in Davidson's reports.
Chiefly, using DirectQuery/Live mode in reports often results in a dramatic slowdown in how report interactions perform for the end user. For example, if a user changes the filter on a DirectQuery report, the report needs to send that command down to the data source to retrieve the data, filter it, and send the filtered result back to be rendered in the body of the report. Depending on the size and complexity of the dataset, this can result in a delay of several seconds while these operations are performed.
It is generally recommended to only use DirectQuery/Live connections when dealing with very large datasets that could pose storage issues, or with volatile datasets that are frequently changing.