DirectQuery performance

• DirectQuery models generate outer join SQL queries by default to ensure that measures return the correct value even if there's not a related dimension.
• However, you can configure DirectQuery models to send inner join queries via the modeling window's Assume referential integrity setting (see Figure 32).
• Along with source system resources, this is one of the top factors contributing to the DirectQuery model's performance.

• Of course, you should ensure that there is referential integrity in the source before enabling this setting as incorrect results could be returned.
• The design of the source relational schema and the hardware resources of this system can, of course, greatly impact the performance of DirectQuery models.
• A classic star-schema design with denormalized tables is recommended to reduce the required join operations at query time.
• Optimizing relational fact tables with column store technologies such as the Clustered Columnstore Index for SQL Server 2016 and table partitions will also significantly benefit DirectQuery models.
• Not all DAX functions are optimized for DirectQuery. It's important to utilize the optimized functions when possible when defining DAX measures.