cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mattkarriker Regular Visitor
Regular Visitor

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
v-jiascu-msft Super Contributor
Super Contributor

Re: Matrix Performance Issues SSAS MD Drilldown Hierarchy

@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.
Highlighted
mattkarriker Regular Visitor
Regular Visitor

Re: Matrix Performance Issues SSAS MD Drilldown Hierarchy

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.  

Top Level.pngInitial ViewLevel 2.pngFrom initial view drilled to the next level. This happens very quickly and I see no delays.Nested.pngThis 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

v-jiascu-msft Super Contributor
Super Contributor

Re: Matrix Performance Issues SSAS MD Drilldown Hierarchy

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.
mattkarriker Regular Visitor
Regular Visitor

Re: 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? 

Binny New Member
New Member

Re: Matrix Performance Issues SSAS MD Drilldown Hierarchy

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

 

Thanks

branah Visitor
Visitor

Re: Matrix Performance Issues SSAS MD Drilldown Hierarchy

Alguien logro solucionarlo? aún me esta sucediendo.

 

Gracias

-----

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

 

Thanks

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 25 members 903 guests
Please welcome our newest community members: