DirectQuery project candidates

Three common candidates for DirectQuery model projects are the following:

• The data model is larger than the current 1 GB file size limit or there's insufficient RAM for an in-memory SSAS model.
  • Power BI Premium is expected to raise this file size limit soon as well as support incremental data refresh
• Access to near real-time data is of actionable or required value to users or other applications such as with notifications.
  • For example, an updateable Nonclustered Columnstore index could be created on OLTP disk-based tables or memory optimized tables in SQL Server 2016 to provide near real-time access to database transactions
• A high performance and/or read-optimized system is available to service report queries such as a SQL Server or Azure SQL Database with the Clustered Columnstore index applied to fact tables.
  • Azure SQL Data Warehouse is not recommended for Power BI in DirectQuery mode

In summary, the performance and scalability of DirectQuery models is primarily driven by the relational data source. A de-normalized star schema with referential integrity and a system that's isolated from OLTP workloads is recommended if near real-time visibility is not required. Additionally, in-memory and columnar features available to supported DirectQuery sources are recommended for reporting and analytical queries.