14 Replies Latest reply on Jan 7, 2019 5:09 AM by Toby Erkson

    Limit the Total Number of Tasks on a Refresh Schedule

    Rene Salinas

      Hello Server Community!

       

      Does anyone know if there's a way to set a limit on the total number of extracts that run on a refresh schedule in Tableau Server?

       

      I don't mean serial vs. parallel. I'm looking to tell Server, "This 5am CST extract schedule is only allowed to run a total of ### extracts, and no more".

       

      Thank you.

        • 1. Re: Limit the Total Number of Tasks on a Refresh Schedule
          Carisa Chang

          Hi Rene,


          That's an interesting idea! Tableau Server doesn't have a configuration option for limiting the number of extracts on a given schedule today, but I encourage you to post it on the Ideas forum for consideration:

           

          Ideas

          1 of 1 people found this helpful
          • 2. Re: Limit the Total Number of Tasks on a Refresh Schedule
            Toby Erkson

            As Carisa said, it's not possible.

             

            I'm curious as to why you want to do this.

            • 3. Re: Limit the Total Number of Tasks on a Refresh Schedule
              Rene Salinas

              Hi Toby,

               

              It's to help reinforce our governance. We have a multi-tenant environment and we're trying to space out when extracts can be scheduled. Additionally, there are a few extracts (and I really do mean just a few) that need to run close to peak usage hours. I don't want to create a schedule that would be open to hundreds of publishers to use during that hour.

              • 4. Re: Limit the Total Number of Tasks on a Refresh Schedule
                Rene Salinas

                That's a great idea. Thanks for the tip, Carisa!

                • 5. Re: Limit the Total Number of Tasks on a Refresh Schedule
                  Matt Coles

                  What would you want Server to do once the number of tasks on a given schedule met the limit? Remove the schedule from the dropdown? What if a new, really important extract needed to run at a given time each day?

                   

                  One option is to use data from the Tableau Server postgreSQL database to determine how many tasks run on a given schedule, and set up a dashboard for yourself to monitor and/or alert. You could then shift tasks to different schedules to balance the load, or at least have a conversation with the extract owner to determine how important their content was.

                   

                  Another thing this would let you do is determine who is refreshing data that really isn't being used much, or at least not enough to justify it running on as frequent a basis, and de-prioritize, shuffle schedules, or get the owner to remove it altogether. We do that weekly on our Server. In fact, just this morning I helped one such person reduce their extract from 2GB to 50MB, because it came up in our alert--they just needed to hide unused fields and use an extract filter!

                   

                  Bottom line for me is that there are lots of ways to help mitigate this problem without a new product feature.

                  1 of 1 people found this helpful
                  • 6. Re: Limit the Total Number of Tasks on a Refresh Schedule
                    Rene Salinas

                    Thanks for the tips Matt. Much appreciated.

                     

                    But we're already monitoring task reports from data out of postgreSQL, shifting tasks, conversations with the extract owners, removing unused extracts, encouraging usage of managed data sources. The concern is that we can never get ahead of the curve with these issues. They are ongoing, and always will be. By baking in limits and functionality, it will help with our efforts. I'm really looking at this through the same lense as setting a site's max users and max storage.

                     

                    I will definitely take a look at the pre-built content you included in your response. I'm always open to ideas.

                     

                    What would Server do once the number of tasks on a given schedule me the limit? Prompt the user to select a different schedule. If they do have new, really important extract that needs to run, chances are that they will evaluate what they already have scheduled at that time, and remove what is unnecessary. If there's nothing they can remove, then they can work with the Server administrator to either increase the limit on the schedule, or the administrator can remove unused extracts where possible.

                     

                    Thank you again for the response, tips, and the link to the tools.

                    • 7. Re: Limit the Total Number of Tasks on a Refresh Schedule
                      Matt Coles

                      Good to hear you're already doing that! I have to confess I don't know what it's like to run a multi-tenant server deployment. Would there be a quota for the number of tasks each Site could run on given schedule? Or just a server-level max tasks? Or would you measure it by total duration of all tasks...or of the task delays, since your real concern is with server resources. I like the idea of allowing a user to pick any custom schedule they want (unlimited by static choices in a drop-down menu), and the UI dynamically informing them as to some of the "consequences" of their choice. Like "Oh, you want the 6am schedule? You and everybody else. You can pick it, but you're looking at having this thing refreshed by around 9am, because things are really busy around that time". Or "You've picked a time that is too busy according to the thresholds your admin has set. Please pick a new time or contact your admin (email hyperlink) to request further help".

                       

                      While we wait for the Nirvana feature, another "soft" option is to use the data sources I linked with a tool I wrote called VizAlerts, the combination of which would allow you to detect a new task added to a "full" schedule, and automatically email the owner of the workbook/datasource and yourself, instructing them to switch schedules, and warn them that if they didn't, it would be deleted from the existing schedule. If they didn't, you'd still have to go and delete it, but it should cut down quite a bit on manual work.

                      • 8. Re: Limit the Total Number of Tasks on a Refresh Schedule
                        Anuj Bansal

                        I am also looking same solution, so please let me know if you found something on it. In our case, all most everyone is trying to schedule extracts at 8AM or 9AM which is causing issues. So I want people choose some other time as well. It can possible if we put some limit on schedules or they should appear some waning before choosing same.

                        • 9. Re: Limit the Total Number of Tasks on a Refresh Schedule
                          Toby Erkson

                          I understand server admins have constraints to deal with in balancing hardware and licensing.  In the spirit of self-service and quick/agile flow (workbook design, build, test, & publish) for the end user I recommend using your information -- resource constraints vs. user growth & needs -- as the argument to add more cores/RAM/drive space.  This is what I've done to go from 8 cores to 12 to 24.  If you're forced to be restricted to X amount of hardware with no hope of expanding then Tableau could ultimately be just like any other BI tool not allowed to reach its potential service.  It's particularly frustrating for me to be denied or that I must provide additional justification because I've witnessed firsthand how a hardware upgrade improves performance and user experience, so I can speak from experience that it does make a difference.

                           

                          If users are restricted to when/how they can use the Tableau Server and/or disappointed with performance, and the Tableau Server hardware environment is not allowed to grow accordingly to handle the demand, then management is to blame, not the software!  When I am in the situation where my customers are unhappy with performance I tell them to complain to my manager, not just to me.  A manager or the person who deals with the BI budget needs to hear the pain directly from your end user community, not always from the server admin.  This feedback makes frustration "real" and provides them with additional information to drive budget allocation for hardware/licensing upgrades.

                           

                          I will say this:  If you have a lot of extracts and/or subscriptions then additional Backgrounders are a must and if you can put them on their own node (i.e. multi-node environment) then that's even better.  Forcing restrictions/constraints just leaves end users unhappy and with a false perception of Tableau as incapable

                          • 10. Re: Limit the Total Number of Tasks on a Refresh Schedule
                            Anuj Bansal

                            I am totally agree with this notion that we should also focus on increasing the hardware/license capacity but this can't be true every time. We should have some mechanism and processes by which we can streamline the existing stuff  and sometime restrict the users for doing such activities which have some alternate best solutions. In my opinion, though Tableau is great tool for developers but we have very less and limited functionality for Tableau Server Administrator.

                             

                            My example, We have 500+ Site admin/publishers and they scheduled their 120+ tasks between 8AM to 9AM. Everybody want to refresh their data before starting the business, and I am pretty sure all of them not require for the same time. They can choose some other time in whole day, like 6AM to 8AM but how Server Admin team can put some automated check on it? As of now we keep chasing people over ph calls and email.

                            We have almost doubled our hardware capacities and purchased more server licenses recently but we are still in same situation.

                             

                            So Tableau should focus on adding more and more such functionality for Server Admins so that they can use it fullest and  manage it efficiently.

                            1 of 1 people found this helpful
                            • 11. Re: Limit the Total Number of Tasks on a Refresh Schedule
                              Toby Erkson

                              Over 500 Site admins?    Uhm...that's a LOT! and very curious about such a huge number...but that's a topic for another thread.

                               

                              Odd that everyone thinks -- or must -- extract during that time period.  Without knowing the environment very well I can only recommend working with them to better understand their options.

                              For example:

                              1)  If the db data is batch refreshed at night then refresh those extracts after the db batch update.  Or run incremental refreshes, maybe even at other times in the day, as those should execute faster and thus use less server resources.  Once a month a full refresh would happen during off-hours, like a Sunday night, or every other week, or once a week...it would depend.

                               

                              2)  Are live connections possible for smaller and/or faster data sources?

                               

                              3)  Can one data source feed multiple workbooks?  That way the published data source would only refresh once and feed more than one workbook (that may not need refreshing).  This can dramatically reduce refreshes & resource usage.

                               

                              So in other words, look for and encourage alternatives to refresh schedules.  It looks like it's not a problem that will go away nor be fixed given the current desire, as it appears it will continue.  I think Matt's suggestions are the most helpful in this situation.

                              • 12. Re: Limit the Total Number of Tasks on a Refresh Schedule
                                Toby Erkson

                                Anuj Bansal wrote:

                                 

                                ...

                                We have almost doubled our hardware capacities and purchased more server licenses recently but we are still in same situation.

                                 

                                So Tableau should focus on adding more and more such functionality for Server Admins so that they can use it fullest and manage it efficiently.

                                 

                                All of this conversation is more about managing the users.  I have had conversations with multiple authors who complain about their extracts failing or being delayed but when I finally figure out what's going on I can then educate them on how their actions are negatively affecting others (consuming resources that don't need to be, slowing down other author's workbooks, etc.) and how we can fix it.  For example, one author thought they needed an extract every 15 minutes but after talking with them it turns out they only need it during certain hours on weekdays -- so I make them a schedule that fits their needs.  As you can see below, here are some of our schedules to accommodate various end user needs (zAlert_...is for VizAlerts):

                                 

                                 

                                Limiting the number of extracts per schedule isn't managing the server, it's simply putting a road-block in front of the end users.  If end users are unable to get fast, timely reports then Tableau will fall to the side of the road like any other BI tool-set as users become frustrated, and then tired, of working with something that doesn't fit their needs.

                                 

                                Also, one needs to look at it this way:  It's not necessarily the number of refreshes or subscriptions being run but how long they take.  For example, if one has 8 backgrounders and 20 refreshes that take about a minute to execute then everything will be fine for the end user as they will all be completed in less than 3 minutes.  However, if those 20 refreshes were to take 20 minutes each to complete one is now looking at almost an hour for all of them to finally complete.  If a restriction of 20 refreshes were placed on the schedule it wouldn't make sense for the first scenario.  While it may appear to make sense for the second scenario, what about the occasional refresh that needs to be added that takes only a minute?  Or what if a few of the long-running refreshes were fixed to be more efficient, then allowing more resources to be freed during that schedule...so more extracts could be added?

                                 

                                The other problem with a scheduling limit would also be maintenance.  Can the limit be raised because old, long running extracts are now gone?  Because workbooks are now more efficient and properly designed?  Should it be lowered because now there are some longer running extracts?  For me it would just be another admin thing to use up my time that could be better used elsewhere.

                                 

                                I think modifying the original post to be an alert would be a better idea.

                                • 13. Re: Limit the Total Number of Tasks on a Refresh Schedule
                                  Anuj Bansal

                                  Thanks for these suggestions, we are already working on few things to tackle these scenario like:

                                  1. Creating more schedules. Earlier we had n hourly basis, now we created for every 15min interval so that users can choose wide range.

                                  2. Discuss with owners/site admin whose tasks are running more than 30min. We have identified few issues(database replication, db latency, complex long db queries in extracts)  on some tasks and trying to resolve them.

                                  3. Instead of publishing multiple workbook with embedded extract, we are asking users to publish extract once and point all workbooks to same extracts.

                                   

                                  I know, putting limitation on the ability of choosing schedules for users is not ideal, but sometimes having these kind of features make life easy for Admin. Above tasks are continuous and you have to keep chasing people and guide them.

                                  • 14. Re: Limit the Total Number of Tasks on a Refresh Schedule
                                    Toby Erkson

                                    Anuj, it sounds like you are on the right track   I understand the "chasing people" bit...just think of it as slice of job security