Hi @soldstatic
If the query result is too large, you can try below some best practices to optimize your model:
- Remove unused tables or columns, where possible.
- Avoid distinct counts on fields with high cardinality – that is, millions of distinct values.
- Take steps to avoid fields with unnecessary precision and high cardinality. For example, you could split highly unique DateTime values into separate columns – for example, month, year, date, and so on. Or, where possible, use rounding on high-precision fields to lower cardinality – (for example, 13.29889 -> 13.3).
- Use integers instead of strings, where possible.
- Be wary of DAX functions, which need to test every row in a table – for example, RANKX – in the worst case, these functions can exponentially increase run-time and memory requirements given linear increases in table size.
- When connecting to data sources via DirectQuery, consider indexing columns that are commonly filtered or sliced again. Indexing greatly improves report responsiveness.
For more information on optimizing data sources for DirectQuery, see DirectQuery in SQL Server 2016 Analysis Services.
Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
