Excel export on "Entire View" crashes server

[ken (Ken Stanton)]

My users like to export tables to Excel sometimes. So they hit share and select Excel, then run it. Problem is, it crashes the server. They even see a 504 error after a few minutes of trying:

It’ll run the cpu and memory up:

But the report never finishes. The cpu does eventually calm, but the memory never comes back down:

Anyone else experience this? If you change the Selection to “All Visible” or “Selected Items” it exports quickly. I’ve told them to use that, but it isn’t always what’s desired.

Dash has a large dataset behind it, does have a layer, is running off a Postgres database, and on an Ubuntu server, if you think any of that matters!

[dinara (Dinara Sharipova)]

Hi Ken,

Can you, please, open the Development Tools in your browser (F12), go to “Network” tab and see which specific request gives that 504 error? The 504 is a gateway timeout, so it’s a web error that Excel file should not affect. We need to understand what is happening on the server with the requests in details (headers, response/preview).

Update 1: you can also try increasing the time-out on your web server from standard 60 to 600 (or more), and let us know if it makes a difference. This article may help - https://ubiq.co/tech-blog/increase-request-timeout-nginx/.

Update 2: You’ve mentioned that “All Visible” or “Selected items” work faster, that may mean that you have a hidden visualization that it exporting large amount of data. Also, can you let us know how long it takes until the 504 error shows up? We’re assuming it’s 60 seconds as it’s a standard time-out setting.

Thanks!

[ken (Ken Stanton)]

Hi Dinara -

Thanks for this reply. It took me a few days to get back to this, but here’s a screenshot of the network tab.

There IS a hidden layer, with a significant but not huge data table. I wish that the export defaulted to “All Visible” instead of “Entire View” too, as my users don’t know to change that when coming in to export what they’re seeing.

Finally, I can look into changing the NGINX time but I’d have to put a request in to my server team and that may take some time.

[dinara (Dinara Sharipova)]

Hi Ken,
Thanks for more details. We would still advise you to increase that time-out in NGINX. I’m seeing on the screenshot that this failing “export” call is timing out after 1 minute which is the standard time-out time. Due to the large volume of your report, NGINX doesn’t see a response coming in within those 60 seconds and throws an error.

Let us know if the increasing time-out helps!

[ken (Ken Stanton)]

Hey Dinara, I’m working with my admin now and he said it was actually set to 86400 already. It’s obviously some kind of setting, since it goes 504 at exactly 60 seconds, but neither of us can figure out what. And it’s not limited to my computer/browser since my users are getting the same thing, so it has to be server… but where?! haha

[dinara (Dinara Sharipova)]

Hi Ken,

Have you also increased the time-out in the Data Cube that this report is based on? They have their own time-outs on SQL Select element or Manual Select. Please, let us know what value do you have there. Thanks!

[ken (Ken Stanton)]

Thanks. They were set to 60 seconds, so I increased them to 600 for testing. It did not change the result. And since this is a 504, the 5 signifies it is a server issue so we’re betting that it is a timeout that is associated with the load balancer on our server. We will test tonight after work hours.

[ken (Ken Stanton)]

OK! The 504 was indeed from a server setting - the load balancer. It had a timeout of 60 seconds and moving it up to just 120 was enough to complete the export.

And now that I can see WHAT it was trying to export the whole time, I see why it was taking so long. The “Entire View” option really shouldn’t be the default one when a user initiates an export. It pulls all kinds of hidden items and if you have multiple layers you’re going to have a huge export.

And therein lies the rub - the advice when making dashboards is to create layers rather than script/filters when the going gets tough. So, the default export option “Entire View” will bring that weakness to the surface like oil in water. I think this is something Dundas should consider, especially on the Professional Services side (which is who really pushed me to make all of these things into layers). We burned a LOT of hours trying to diagnose and fix this.

[dinara (Dinara Sharipova)]

Hi Ken, I’m glad the issue was resolved!

Regarding the default option of “Entire View” - I’ll let my team know.
Thanks!