Forum Discussion
Matrix Performance Issues SSAS MD Drilldown Hierarchy
Hi Matt,
1. How did you connect to SSAS cube? Using Direct Query or Import mode?
2. "Go to next level" and "Expand" are different. For example, the dates of 5 years. The first level is Year. When we go to the next level Quarter, there are only four calculations (four quarters). But when we expand to the next level, there could be 20 calculations (5 year multiply by 4 quarters).
Could you please share your report? At least the structure of the visual and the formulas of the measure. Maybe we can optimize the formulas.
Best Regards!
Dale
Dale
Thank you for your response. I wanted to provide more information on this issue in hopes of finding a solution.
I am connection Power BI to SSAS using direct query. SSAS is a Multi-Dimensional model. For the particual visual giving me issues it is a Matrix with a 2 level hierarchy on rows, Top level Channel has 7 members, next level down DSR has 24 members. Then to complete the Matrix I add 6 measures to the matrix, 3 regular measures and 3 calculated. There are no values on colums, other than the values of the measures. Below are the definition of the caluclated measures.
Prior Year Sales =(PARALLELPERIOD([DimDate].[CALENDAR DATE].[CALENDAR YEAR], 1, [DimDate].[CALENDAR DATE].CurrentMember),[Measures].[Sales Dollars])
Gross Sales + Open Order = [Measures].[Sales Dollars] + [Measures].[Open Sales Dollars]
MTD Variance = [Measures].[Sales Dollars] - [Measures].[Estimate Dollars]
I am starting to wonder if this is an issue similar to what Chris Webb blogged about a while back.
To show the issue below are screen shots of the Matrix in different states.
Initial ViewFrom initial view drilled to the next level. This happens very quickly and I see no delays.This is the nested view I want to use as the initial view. BUt this takes 4 minutes to load and my SSAS server CPU maxes out while computing.
Any assistance you can provide is greatly apprecited.
Thanks,
Matt
Hi mattkarriker,
Did you try the solution introduced in the blog? Since the design and the calculation of live connection of SSAS are carried out in the SSAS server, I would suggest:
1. Check the performance of the server if it's sufficient.
2. Try to change the live mode to import mode. Only import basic data, all calculation will be done in the Power BI Desktop.
3. Get help from here to optimize the SSAS model.
Best Regards!
Dale
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
