Forum Discussion
Matrix Performance Issues SSAS MD Drilldown Hierarchy
After further investigation and actually recreating the matrix visual I am now limiting this issue to sorting. While recreating the report I noticed that performance never took a hit until I tried to sort the matrix by one of the measure fields. When sorting is applied the matrix takes over 5 minutes to display. During the 5+ minutes CPU on the SSAS Server is pegged between 98-100%. It matters not which measure I sort by they are all slow. Sorting by dimension members does not seem to cause the same issue as measures. Writing the same query in MDX with sorting applied returns in less than 1 second.
I then turned to Profiler to see what the queries are doing. The DAX produced by the matrix causes the cube to query subcubes and non-cached data, much like the article by Chris Webb. Unlike the article turning off totals and subtotals do not resolve the issue. The duration of the query subcubes and non-cahed data tasks are hugh according to Profiler. Hense the slowness. The MDX query uses all cache and aggregations.
Has anyone else seen this type of behavior using the matrix? Were you able to over come the terrible performance with sorting in nested hierarchies?
Did this ever get resolved? We are seeing the same issues.
Thanks