1 2 3 Previous Next 30 Replies Latest reply on May 12, 2016 11:11 AM by John Kuo

    Too many Tableau Extracts - How to optimize?

    Satish Kikani

      Hello Everyone,

       

      I am sure you all faced this problem and found a best solution. I tried searching through forum but could not find anything useful.

       

      Here is my situation.

      We have 150+ dashboards which has 177 extracts. Many of the extracts needs to be refreshed every hour.

      Since we have all of these connecting to 1 sql server and they are all on 1 site, extracts are getting delayed due to long queue.

       

      Is there any way i can distribute load or can you point me to some articles that can help me ?

       

       

      Thanks in Advance,

      Satish kikani

        • 1. Re: Too many Tableau Extracts - How to optimize?
          Zack Gorman

          Hi Satish,
          If your extracts are all connecting to similar data, I would recommend publishing up Tableau Data Sources to your sever (more info here Data Sources ) These would ideally be inclusive files that would address the needs of a wide number of your users' reports. The benefit is that you only need to have one data source and one extract hitting your SQL Server, so even if you need to create dozens of these you'll save a whole load of resources compared to the 150+ extracts you have now.
          There's plenty of different ways to go about this problem, but this would certainly be my first recommendation.
          Best of Luck,
          Zack Gorman
          InterWorks

          • 2. Re: Too many Tableau Extracts - How to optimize?
            Toby Erkson

            There are many things that can be done and there's no one single answer, especially with the lack of details in your question.  For example, you can:

            Build better workbooks (hint: performance recordings can help here).

            Build better extracts.

            Refresh extracts less often.

            Use live connections.

            Add more cores.

            Add more RAM.

            Add more nodes.

            Use physical machines, not virtual.

            Use the latest version of Server.

            ... etc. ...

            1 of 2 people found this helpful
            • 3. Re: Too many Tableau Extracts - How to optimize?
              Satish Kikani

              I thought about that but that is not possible. making 1 source would make query way more complex and also it gives me some bad data too.

               

              I tried that already. may be i can merge 2-3 extracts for different workbooks but it will not make any difference overall.

              • 4. Re: Too many Tableau Extracts - How to optimize?
                Matt Coles

                Been there! There are a million ways to approach this problem. To add a few ideas to the other good ones others have proposed:

                 

                 

                1. Identify workbooks whose extract refresh frequency / usage can't be justified based on the number of views they get, then retire them or decrease the extract frequency. To identify more easily, you'll want a Custom Admin View. I have one I use for this that I can share if you need it.

                 

                2. Ask authors to ensure they check the "read uncommitted data" checkbox on all of their SQL Server connections in all their workbooks. This prevents locking, and therefore potentially delays, on the SQL Server side.

                 

                3. Ensure that all workbooks with extracts have used the "hide unused fields" feature, and that they are using Extract filters to limit the data to the bare minimum of what they need.

                 

                3.5. Create schedules that run hourly but are offset from each other on the start, quarter, half, and three-quarters hour. This may help SQL Server deal with the load, and will at least make your extract refreshes a bit more predictable if they're all kicking off on the start of the hour, then your backgrounders are idle at the end of the hour.

                 

                4. I see that you said Datasources won't work due to complexity and the like. I would suggest, rather than trying to retrofit existing workbooks into a new Datasource, that you instead work with the publishers to identify sets of data that would make sense for a decent set of reporting needs, then build a Datasource for it like this. Enlist others to help market it to everyone. This strategy may not be a short term fix, but will massively help you as you scale.

                 

                5. If people don't get on board with making things more efficient, it may be worth considering some kind of chargeback situation to incentivize departments to put some effort into reducing the resources they consume.

                 

                6. If you've already done everything you can to make your content efficient and useful, and it's still not enough, it may be time to bite the bullet to price out some options for expanding. This might look like investing in an analytics-oriented querying platform with ETL, or perhaps a hardware / core license expansion for Tableau Server, or both.

                8 of 8 people found this helpful
                • 5. Re: Too many Tableau Extracts - How to optimize?
                  Toby Erkson

                  Wow, nice suggestions Matt!

                  Regarding #2, I have a .tdc on the Servers to insure this happens.

                  2 of 2 people found this helpful
                  • 6. Re: Too many Tableau Extracts - How to optimize?
                    Matt Coles

                    Good point Toby--we actually use one too. It's the only way our SQL Server is able to survive our pummeling!

                    • 7. Re: Too many Tableau Extracts - How to optimize?
                      Satish Kikani

                      Hello Matt,

                       

                      Thank you for detailed reply. I really appreciate it. At our company I am the only one developing dashboards so I keep in mind to optimize workbooks as much as I can.

                       

                      point 1 is already done. we refresh most of workbooks at night and i have deleted around 10+ unused dashboard.

                       

                      Point 2 & 3 are something I should check into to optimize workbooks further.

                       

                      Point 3.5 I am doing that right now. But we have around 40 dashboards that needs to refresh hourly and we are going to add 25 more soon. That is why I am looking for options before I run out of time and anything breaks.

                       

                      Point 4 is something I am trying to do but It is turning out to be very complex and time consuming but yes I am trying for that also.

                       

                      Point 5 can leave to me being laid off. Jokes apart, I tried that earlier and that is how we removed few unwanted dashboards.

                       

                      Point 6. yup you are right. I think time to bite bullet is very near. Lets see.

                       

                       

                      I would greatly appreciate if anyone else has any other solution in mind.

                      • 8. Re: Too many Tableau Extracts - How to optimize?
                        Satish Kikani

                        Hello Toby,

                         

                        Thanks for suggestions. Here is what we do. I am the only one developing dashboards so i can answer most of them.

                         

                        we got 1 tableau server, 1 db server.

                         

                        Build better extracts. - I think I am doing best I can. There are few point raised by Matt and I am going to make sure I have done that on all workbooks

                        Refresh extracts less often. - Done wherever we can

                        Use live connections. -  I have nearly 30 workbooks already on live connection. I do not want many live workbooks as it increases load on DB server.

                        Add more cores. - Can push for that but I have to research more on that. Matt suggested it too.

                        Add more RAM.- Can push for that but I have to research more on that. Matt suggested it too.

                        Add more nodes. Not sure what do you mean by adding more nodes. It would be great if you can link me to something.

                        Use physical machines, not virtual. - we already do that.

                        Use the latest version of Server. - Yes that is what I am pushing too. to upgrade from 8.3 to 9. But I am trying to find how it can help in this scenario.

                         

                        Also please let me know what kind of details  can help more.

                         

                        Thank you,

                        Satish Kikani

                         

                         

                        2 of 2 people found this helpful
                        • 9. Re: Too many Tableau Extracts - How to optimize?
                          Matt Coles

                          Interesting...just you, huh? Well, that will at least make doing some of this easier since you have control over the content. Yes, definitely check out 2 & 3...it can drastically reduce extract sizes while keeping your dashboards working fine. Very "low-hanging fruit" so to speak.

                          1 of 1 people found this helpful
                          • 10. Re: Too many Tableau Extracts - How to optimize?
                            Satish Kikani

                            Yup one man army

                             

                            Point 2,3 & 4 are what I am focusing on for short term.

                             

                            Point 6 is something i need to really push may be.

                            • 11. Re: Too many Tableau Extracts - How to optimize?
                              John Kuo

                              You can also manipulate background task prioritization to give the more important ones priority.

                              Background Task Prioritization

                               

                              Also, double-check tasks are scheduled to run parallel instead of serial.

                              http://onlinehelp.tableau.com/current/server/en-us/help.htm#schedule_manage_create.htm

                               

                              Best,

                               

                              John

                              3 of 3 people found this helpful
                              • 12. Re: Too many Tableau Extracts - How to optimize?
                                Justin D'Cruze

                                Yikes, that sounds like a nightmare...

                                Another thing to address the queuing would be to increase the number of backgrounder processes, although I would definitely recommend checking your current CPU/RAM utilisation first to make sure your server isn't going to crawl if you add more. Generally you don't want to go overboard and run too many, but it can help reduce queuing in some situations.

                                2 of 2 people found this helpful
                                • 13. Re: Too many Tableau Extracts - How to optimize?
                                  Eric Axelrod

                                  "177 extracts... connecting to 1 sql server and they are all on 1 site,"

                                   

                                  Sounds to me like you probably have a LOT of unnecessary data sources.

                                   

                                  As a general rule for building a semantic layer (in any BI tool, not just Tableau) you should have 1 star-schema model per subject matter.  In the Tableau world, this means 1 datasource per subject matter/functional area.  So 1 datasource for Sales, 1 for Accounts Receivable, 1 for Procurement, etc.  There may be exceptions, but usually this is the perfect model.

                                   

                                  Under this model this means you would have 177 different subjects in the same single node SQL Server box.  I wouldn't say this is impossible... but it's highly unlikely.

                                   

                                  Rarely do I find a system with more than 30-40 different subject areas, most are 20 or less.  This leads me to believe that there is a massive amount of overlap between datasources.

                                   

                                  Priority #1 should be identifying the subject matter in your longest-running extracts and looking for opportunities to consolidate datasources.  You will probably find lots of them.  Then you won't spend time editing a bunch of datasources that you are just going to delete.

                                   

                                  Eric Axelrod

                                  President & Chief Architect

                                  DIGR

                                  www.digr.io

                                  3 of 3 people found this helpful
                                  • 14. Re: Too many Tableau Extracts - How to optimize?
                                    Satish Kikani

                                    Hello John,

                                     

                                    Thanks for pointing it out. We are already doing that, but I double checked too.

                                    1 2 3 Previous Next