0 Kudos

Going to a Visualisization 'Table' using Drillthrough crashes my SQL Server

Status: Delivered
by johuys Visitor on ‎07-06-2018 06:20 AM

Our set-up:

- Windows Server 2016 DataCenter - SQL Server / Analysis Server 13.0.1742

- On-premisis Gateway 14.16.6724.2 (june 2018)

- Power BI Desktiop 2.59.5135.781 64-bit (june 2018)

 

Our power bi reports are  using the data (cubes) from the analysis server. 

 

If we are usng the visualisation "Table" and add mutliple fields from tables (combination of measures and normal fields) then very quickly we get an performance issue (crash) when visualising a lot of records.

 

The same performance issue happens when we use the "Table" as the result of a "DrillThrough" functionality. Even if the result of the DrillThrough should show only a few records - it crashes.

 

No data is displayed, we keep seeing the 'hourglass' for minutes - memory on the sql server is going up through the roof - when out of memory we get an error in Power BI - it even happens that our  Windows Server 2016 hangs and needs to reboot.

 

Very strang for such (from my point of view) simple functionality.

 

If I copy/paste the page in the report and replace "Table" with "Grid from MAQ Software" then everything works correct and quick. So my conclusion is that the visualisation "Table" is not ok.

 

Please advise how to proceed.

 

 

Status: Delivered
Comments
by Moderator v-qiuyu-msft
on ‎07-08-2018 10:51 PM

Hi @johuys,

 

I'm not able to reproduce the issue with live connection to SSAS cube for drill through feature in table visuals. 

 

In your scenario, is there any error message occurs? Would you please enable the desktop trace log and repeat steps to reproduce the issue, share log files with us? Please click on Refresh button to see if table can load data.

 

Best Regards,

Qiuyun Yu 

by johuys Visitor
‎07-09-2018 12:29 AM - edited ‎07-09-2018 12:44 AM

Hi @v-qiuyu-msft

 

After a while (few minutes) I get "Can't display the visual. See details".

 

When clicking on 'See details' I get this message :

Couldn't load the data for this visual

Server : The operation has been cancelled because there is not enough memory available for this application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.

 

This is on our development machine with default 16 GB memory, we get the same message even when increasing the memory to 64 GB.

 

I reproduced the error when tracing was active. Also I clicked on the Refresh button. Same result.

 

You can find the logfile here.

 

by Moderator v-qiuyu-msft
on ‎07-12-2018 07:12 PM

Hi @johuys,

 

From the desktop trace log files, I find error:

 

An error occurred running the query.Details: Message=Failed to execute the DAX query., HResult=0x80004005, ErrorCode=QuerySystemError, Language=en-US, ProviderErrorCode=0xC11C0005, ProviderErrorMessage=[\\u003cpi\\u003eServer: The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.\\u003c/pi\\u003e], ProviderGenericMessage=[Server: The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.]

 

As you mentioned in your initial post, the table visual contains some measures and normal fields, do you create these measures on SSAS project or in Power BI desktop? Please try to remove measures one by one, check if the issue related to one specific measure. 

 

Best Regards,
Qiuyun Yu 

by johuys Visitor
on ‎07-13-2018 02:44 AM

Hi @v-qiuyu-msft,

 

All measures are created in the SSAS project. 

 

There is only 1 measure added, if I remove the measure then everyhting works fine. If I add the same or another measure I get the error again. The issue is not related to a specific measure.

 

This is not limited to this query, if I choose another combination of labels and measures of other (large) tables big change that I will get the same issue using the visualisation "Table". I have this issue on almost all my reports (orders, invoices, CRM phone calls,...) at the moment that I add measures.

 

Friendly reminder :

  • If I use "Grid from MAQ Software" then it works always.
  • we upgraded the memory from 16GB to 64GB --> same issue.

 

 

 

by Moderator v-qiuyu-msft
on ‎07-14-2018 02:25 AM

Hi @johuys,

 

Thanks for your detail information. As I can't reproduce the issue on my side, I would suggest you create a support ticket to let Microsoft engineer look into your real environment. 

 

Support Ticket.gif

 

Best Regards,
Qiuyun Yu 

by Vicky_Song Established Member
on ‎07-14-2018 02:25 AM
Status changed to: Delivered
 
Idea Statuses