DirectQuery mode

A DirectQuery mode model provides the same semantic layer interface for users and contains the same metadata that drives model behaviors as In-Memory models. The performance of DirectQuery models, however, is dependent on the source system and how this data is presented to the model. By eliminating the import or refresh process, DirectQuery provides a means to expose reports and dashboards to source data as it changes. This also avoids the file size limit of import mode models. However, there are several limitations and restrictions to be aware of with DirectQuery:

• Only a single database from a single, supported data source can be used in a DirectQuery model.
• When deployed for widespread use, a high level of network traffic can be generated thus impacting performance.
  • Power BI visualizations will need to query the source system, potentially via an on-premises data gateway.
• Some DAX functions cannot be used in calculated columns or with role security.
  • Additionally, several common DAX functions are not optimized for DirectQuery performance.
• Many M query transformation functions cannot be used with DirectQuery.
• MDX client applications such as Excel are supported but less metadata (for example, hierarchies) is exposed.

Given these limitations and the importance of a "speed of thought" user experience with Power BI, DirectQuery should generally only be used on centralized and smaller projects in which visibility to updates of the source data is essential. If a supported DirectQuery system (for example, Teradata or Oracle) is available, the performance of core measures and queries should be tested.