Direct Query from SSAS Cube - Performance

[shuhn1229]

Hi,

I have a report connect to a SSAS cube using direct query. I have noticed some poor performance - some visuals can take 5-10 seconds to load, making it a poor user experience. I'm wondering if I created a calculated tables - filtering down some data in the model (eg only from 2 year range, which would cut the data in half if not more) would help speed things up. Just wanted to ask here before I went through with this because I'd like need to do this with >6 tables and rebuild the connections in the model.

Thanks

[Anonymous · Accepted solution]

Hi @shuhn1229 

1.You can consider to set the  partition in your data source, it can optimize the performance, you can refer to the following link.

Create and Manage Tabular Model Partitions | Microsoft Learn

How to partition an SSAS Cube in Analysis Services Multidimensional (sqlshack.com)

2.You can create the calculated table, but you need to ensure it only contains the necessary column and value.

3.You can also consiser to change the direct mode to composite mode, you can refer to the following link to get more information to optimize the direct query mode.

DirectQuery model guidance in Power BI Desktop - Power BI | Microsoft Learn

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.