Forum Discussion
Matrix Performance Issues SSAS MD Drilldown Hierarchy
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,
Matt
6 Replies
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
Alguien logro solucionarlo? aún me esta sucediendo.
Gracias
-----
Did this ever get resolved? We are seeing the same issues.
Thanks
