8 Replies Latest reply on Oct 7, 2013 1:11 PM by Scott Wei

    How to optimize a workbook. Slow Performance

    Scott Wei

      Dear Community,

       

      I was wondering how I could optimize my workbook and make it faster. Currently I am mimicking and internal data query tool. I was able to replicate the filters exactly with quick filters and using all relevant values. The real issue now so that the performance is very slow and even worse when I publish it to the server.

       

      I am only using a sample data set of 100,000 imported. But my real data set will be approximately 250 Million Records which makes me nervous.

       

      I was wondering what are some best practices and perhaps when I went wrong in designing the workbook.

        • 1. Re: How to optimize a workbook. Slow Performance
          Scott Wei

          Also it seems like every time I open the workbook somethings the filters are missing. Are there limitations with quick filters and how many you can use?

          • 2. Re: How to optimize a workbook. Slow Performance
            Matt Lutton

            Not sure what all you've looked at already, but you may find these helpful:

            Performance Tips

            Optimizing Tableau Server Performance | Tableau Software

            Why is my Tableau viz slow? - The Information Lab

            Improving Database Query Performance | Tableau Software

             

            The sheer number of filters present in your workbook, and the fact that most (if not all) are set to "only relevant values", is going to impact performance in a negative way.    You may look at: Cascading Quick Filters in Tableau - The Information Lab and see if you can utilize the concepts shown there.

            2 of 2 people found this helpful
            • 3. Re: How to optimize a workbook. Slow Performance
              Scott Wei

              Thank you Matthew for once again being a great resource. I found the first article performance tips but did not find the others. Excellent reads and some absolutely some great points.  It seems that I have both issues in both my data-source and design so back to the drawing board I go.

               

              My first issue is that I am pulling off of an excel sheet since this was more of a trail than anything else but I will pull from the database.

               

              Matthew I noticed that it is recommended. to import multiple tables(that are indexed correctly) and then let Tableau perform the joins. Do you know exactly what Tableau does when it exacts the data. We created a view of what our data-source should look like in SQL since we rather not touch the physical table itself but it that will help performance it is something else I should look at.

               

               

              Also what is the typical data set that user with Tableau work with? I found some articles on large data but seems my data set is a bit bigger than most. Approx 250 Million records with 105 Columns.

              • 4. Re: How to optimize a workbook. Slow Performance
                Jonathan Drummey

                The suggestion to use a multiple tables connection is for using a live connection, with proper indexes Tableau will perform join culling and only query the necessary tables. When you are using a Tableau data extract, when Tableau creates or refreshes or appends incremental records to the extract it "flattens" the data from the connection into a columnar format for fast access.

                 

                One thing of note that many new Tableau users do is try to create the "mother of all data sources" to go with the "mother of all workbooks". (I've been guilty of this myself). I think part of this is an artifact of how much effort it can be to create an analytics ready data source, how much effort it can be in other tools to build or modify anything (so we think we have to anticipate every user need in advance), and how easy it is in Tableau to start duplicating worksheets and putting tons of them on dashboards. We're really better off taking some time to think about what's going to be analyzed and from that create different data sources at the right levels of detail. In the case that you do actually have users who need to exercise the set of 17 different filters, you might consider giving them Tableau Desktop. Then they can build their own analyses tuned to their needs, and avoid the one-size-for-all-that-fits-none approach.

                 

                In terms of size, people are all over the place. Occasionally I'll touch a million records, most of my work is with data sets that are less than 20K records by the time they get to Tableau. Other folks are regularly into hundreds of millions or even billions of records, here's a useful thread: http://community.tableau.com/thread/131863.

                 

                Jonathan

                1 of 1 people found this helpful
                • 5. Re: How to optimize a workbook. Slow Performance
                  Scott Wei

                  Thank you Jonathan. I love how responsive and helpful the community is.  Its nice to see that others are working with datasets of a similar size along with seeing what the best practices are with this tool

                   

                  Some of the tabs on my workbooks were a proof of concept and from the articles I can see that there is more than 1 issue with my workbook.  Haha like 13 quick filters all with relevant values only, pulling from excel which is not indexed, also plotting essentially 100000 marks on a graph just to name a few.  As far as analytic ready data I understand that it should be more of a aggregation rather than row specific data like mine. This is for most analytics and reports. 

                   

                  Unfortunately we are trying to mimic and replace a currently tool (more of a data query tool with a ui) that we are using. (Hopefully  and ultimately eliminating our old cube structure.) The ability to filter is necessary and the ability to down all the data points is must for the legacy users.  

                   

                  I think my first step is to fix my design, perhaps with actions filters or cascading quick filters rather than just quick filters. Then work on the database end to connect. Additional suggest are always greatly appreciate.

                  • 6. Re: How to optimize a workbook. Slow Performance
                    Toby Erkson

                    Uhm, regarding your users need for "...all the data points...", is this because they're using the reporting tool as a way to get the data so they can then create their own reports [typically in Excel]?  I see this a LOT, extremely common misuse of a reporting tool.

                     

                    Also, though it would be additional work, you could mimick the legacy reports AND then create real reports that get the same info, maybe from several vizes instead of the one mega-viz, and by using specific extracts instead of the 'fits all, fits none' extract(s) or live connection(s).  Not knowing how the reports are used, what specific users are looking for, etc., it's hard to say how to make the better ones.  I'd recommend doing it to a handful of the popular ones, not all of them.  Introduce the new ones along with the legacy style ones and see what the users think after a period of time -- really, really encourage them to play/use them since they will give the same results anyway.

                    • 7. Re: How to optimize a workbook. Slow Performance
                      Scott Wei

                      Toby you hit the nail on the head. In a very strange and convoluted manner that's exactly what they are doing. Either they have an access db or an excel with a macro. Essentially the are using this tool as a data-dump to generate their own reports.

                       

                      They tool will have to be a parallel approach, where can 1) show them that they will not receive anything less. 2) show them the tool is actually better than what they have 3) generate their end reports and then 4) finally decommission their own system.

                      • 8. Re: How to optimize a workbook. Slow Performance
                        Scott Wei

                        My next steps after design is partition the tables by year and perhaps even 1 more level and  to properly index all the tables. This will take me a while but I will report the reports once I do.