I am experiencing terrible performance with Power BI matrix visual. The issue occurs on the expand to next level operation. In my scenario I have Mulit Dimensional cubes as the source for Power BI. In the cubes I have a customers hierarchy that is 4 levels deep. When I add the hierarchy to the martix rows along with measures the top level of the hierarchy displays with the appropriate measure values with no issues. If I use the "Go to next level" option everything work as expected the next level down apprears with the measure values. However if I try to "Expand to Next level" the report sits and spins. When I look at the server CPU hovers around 98-100% CPU usage. After several minutes the results will appear, but the fact it takes several minutes makes it show stopper.
When I perform the same steps in excel pivot tables the results are instantanious. I dont think it is anything to do with the cubes but perhaps the way Power BI is querying the cubes. Has anyone else run into this issue? Were you able to find a solution?
Thank you for your assitance,
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.
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.
Any assistance you can provide is greatly apprecited.
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.
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?