To increase query performance while working with large Fact Tables, some optimizations should be applied in DirectQuery mode in Power BI:
Use Aggregations: Create summary tables that are pre-aggregated to reduce the number of rows that Power BI needs to query. This means that instead of querying the full fact table every time, aggregations allow Power BI to hit smaller, optimized tables.
Optimize SQL Indexing: Make sure that the fact table and the key columns used for relationships and filtering are properly indexed in the source database. Use clustered indexes for primary keys and non-clustered indexes on columns that are filtered frequently.
Reduce Query Complexity: Avoid excessive joins, intricate calculated columns, and inefficient DAX expressions. Allow for native SQL transformations in the data source instead of Power Query when applicable.
Limit the Data Volume: Use filters on the server side to restrict the data sent back to Power BI in DirectQuery mode. Instead of bringing in the entire dataset, consider partitioning or using query parameters to load only those necessary records.
Optimize Relationships and Cardinality: High cardinality relationships should be avoided as they slow down the queries. Use integer-based keys for the relationships in lieu of text values and avoid unnecessary bi-directional relationships when applicable.
Set Enable Query Reduction Settings: In Power BI options, set Reduce the number of queries sent and turn off Auto Date/Time to stop extra background queries that affect performance.
Utilize Composite Models: Use composite modeling to mix Import and DirectQuery when possible. Keep the more frequently used tables in Import and use DirectQuery only for larger, seldom-accessed data.
