Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mattkarriker
Helper I
Helper I

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 6
branah
Regular Visitor

Alguien logro solucionarlo? aún me esta sucediendo.

 

Gracias

-----

Did this ever get resolved?  We are seeing the same issues.

 

Thanks

v-jiascu-msft
Employee
Employee

@mattkarriker,

 

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 

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

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.

Chris Webb Blog.

 

To show the issue below are screen shots of the Matrix in different states.  

Initial ViewInitial ViewFrom initial view drilled to the next level.  This happens very quickly and I see no delays.From 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.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

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

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.