This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Im working with some hefty tables and servers on the brink of death. The result is the workflow end to end can take between 2 hours and 8 hours to complete.
The problem is the server idle time is at 240 mins, so i'm getting the publish to tableau "error 401002 authentication" error. Which i understand is idle time.
the bulk of the workflow works quickly so i'm not concerned about that. What i need help with is what is the best way to stop the rest of the workflow running/ activating the tableau publish to server tool in stream 2 (or maybe even 1) until all of the server data loading is complete?
I was thinking block until done but it doesn't seem to be holding it due to the way i've built the flow, if anyone can run their eyes over it and suggest something i've missed or haven't tried that would be great. Image below of the start of the workflow i'm trying to block. (please note in the second image, ive currently replaced all the 5 publish to tableau tools with hypers to a network drive)
EDIT: forgot to add, that if i cache and run workflow and run all 5 it fails, however if i turn off all of the connectors using containers, cache and run , then push each one individually they all run fine.
The Publish to Tableau macro creates a local file and then splits it up to copy to Tableau Server ... this can take quite a long time, especially when you flag at the start that there's problems to begin with.
Are you doing an incremental load or full overwrite ? I'm guessing a full overwrite
The data is coming from SQL servers old ones that are constantly fighting for resource. As an example a query could take 5 secs to run, during parts of the day the exact query can actually take 12-16 mins.
The source query normally takes about 2 - 4 hours to run. If i run each part seperately once caching this initial data source pull. Each tool normally gets the job done pretty quickly, even the largest takes only 1 hour.
just trying to do the cache/ lockout of the rest of the data that would mimic the tool container being closed but without the manual intervention. and stage the tableau server pushing sequentually.
And yes a drop overwrite for the time period to stay a rolling 13 weeks.
OK, so the bottleneck is really the SQL Server. I would usually suggest converting some/all of the workflow to in-DB tools but it's your server that's killing you here.
Does the "old" data change ? So if you're doing a rolling 13 weeks, obviously this week will change but does last weeks data change at all ? Where I'm heading is that can you store some of the data in a yxdb for those "older weeks", pull the "newer weeks" from SQL Server and then combine ?
I think either way you're faced with rewriting the workflow to cater for the struggling database server. It's possible, but it can take a long time to do because there's a lot of trial & error.