13 Replies Latest reply on Apr 24, 2018 11:53 PM by Bång Anders Thorburn Norén

    Filter out fields/columns from data extract

    Jonathon Stewart

      Hello,

       

      I have created a workbook that analyzes survey data.  The database that houses the survey data includes lots of data, so I am using an extract to help speed up the workbook.  However, the workbook is still pretty slow - takes about 1min to load in a browser from Tableau server.  The survey database includes a bunch fields that I actually don't use in my workbook.  I'm thinking that if I can remove these fields it may help improve performance. Is there any way to remove fields when creating an extract in Tableau?

       

      Cheers,

      Jon

        • 1. Re: Filter out fields/columns from data extract
          Joshua Milligan

          Jon,

           

          Any hidden fields will not be included in an extract and you are exactly right -- it can greatly increase performance to exclude those fields.  You can hide a field by right-clicking it and hiding it in the Dimensions or Measures pane.  But even easier is to click "Hide All Unused Fields" in the Extract Data interface.  That will hide (and then exclude from the extract) any field you are not using in a view.

           

          Hide Unused.PNG

          2 of 2 people found this helpful
          • 2. Re: Filter out fields/columns from data extract
            Jonathon Stewart

            Josh,

             

            Thanks for you response - worked great.  I didn't realize that hide actually removed those fields from the extract, but instead thought it just didn't show them in the dimensions/measures panes.  It reduced the size of the workbook from 11MB to about 4MB, and decrease load time by about 15sec.  Now I need to see if we have any calculated fields that may be slowing it down.

             

            Cheers,

            Jon

            • 3. Re: Filter out fields/columns from data extract
              Joshua Milligan

              Jonathon,

               

              As far as calculated fields go, string manipulation is intensive.  So simple things like aliasing numeric values instead of calculating a string representation and using custom formatting for a field instead of a calculated field (e.g. format a value as # days instead of calculating STR([# Days]) + " days") for use in labels or row headers can go a long way in reducing processing time.

               

              A couple of other things you might do:

              1. Look at aggregating the extract (the option for Aggregating Data for Visible Dimensions).  This can dramatically reduce the size and increase performance.

              2. Check the log files to see what is consuming most of the time before a render is complete.  Is it calculated fields or queries or something else?

               

              Good Luck!

              2 of 2 people found this helpful
              • 4. Re: Filter out fields/columns from data extract
                Jonathon Stewart

                Thanks for your suggestions.

                 

                Tried the "Aggregating Data for Visible Dimensions" option without much luck in this case

                 

                Just calculated fields.  The data is raw survey data and we are using several calculated fields to aggregate data.  My guess is that these calculations are slowing down the workbook and perhaps we could improve them.  Are there log files within each workbook that I can check, or do I need to check the logs on Server.  I'm not as familiar with server, but hopefully can figure it out.

                 

                Cheers,

                Jon

                • 5. Re: Filter out fields/columns from data extract
                  Joshua Milligan

                  Jon,

                   

                  Desktop Log Files are located in        \Documents\My Tableau Repository\Logs

                      

                  For Server Log Files, see documentaiton here: http://onlinehelp.tableausoftware.com/v7.0/server/en-us/logs_loc.htm

                   

                  The log files can be a little cumbersome, but you'll be looking for any task that is taking up the bulk of time.  The calculations will mostly be done in the queries themselves, so look at those queries to see if they are poor performing.  You might find that the queries using the calculations are taking a long time and that it is specifically the calculation that is requiring a lot of processing.  On the other hand you might find that it is a query for a quick filter or the rendering of the visualization (do you have a lot of marks or just a few?) that takes a long time.

                   

                  Joshua

                  • 6. Re: Filter out fields/columns from data extract
                    Jonathon Stewart

                    Josh,

                     

                    I was able to find the "View Performance History" report on the Server Analysis page of Tableau Server.  Very interesting.  It breaks down performance by load time and compute time.  Are you familiar with this report?  I'm assuming load time is about query time, while computer time is about Tablea calcs but am not 100% sure.

                     

                    Cheers,

                    Jon

                    • 7. Re: Filter out fields/columns from data extract
                      Joshua Milligan

                      Jon,

                       

                      I'd be curious to look at exactly what you are seeing.  I do occasionally use that function and it is very helpful.  If I recall correctly, query time should be very easy to identify and you should even be able to click on the Gantt chart to see the query itself.

                       

                      Best Regards,

                      Joshua

                      • 8. Re: Filter out fields/columns from data extract
                        philip.watkinson

                        Hi there Joshua

                         

                        Where might I find this "Extract Data" interface in Tableau 10.2.1?

                         

                        Thanks.

                        • 9. Re: Filter out fields/columns from data extract
                          Joshua Milligan

                          Philip,

                           

                          Either right click the data source or use the Data menu and you should be able to navigate to the extract option:

                           

                          Alternately, it is a radio button option on the Data Source screen.

                           

                           

                           

                           

                          A few data sources do not support extracts (e.g. OLAP), but most do.

                           

                          Hope that helps!

                          Joshua

                          1 of 1 people found this helpful
                          • 10. Re: Filter out fields/columns from data extract
                            Sinaasappel

                            Hey,

                             

                            am I correct that the suggested solution doesn't remove/delete but really "only" hides the columns/fields?
                            I tried hiding columns and the extract the data and I also tried the "hide unused fields" options but in both cases if I open the extract in Tableau I just click "show hided fields" and back they are...

                             

                            Any suggestens on how to really delete a colum/field when extrcting data?

                             

                            If deleting is not possible, solving following problem would also help me:
                            I have the problem that if I rename columns/fields and then extract the data, and then if I open that extract and join another table to the extract the names of the extract columns are lost/reverted... (This may only only occour if the "native" column names of the extract and those of the table to join are the same.). Any hints on that?

                             

                            Thank you

                            • 11. Re: Filter out fields/columns from data extract
                              David Lanza

                              Yes, please tell us how to REALLY remove these fields because just hiding them is not actually removing the data from the .tde.  The fields in question are not being used in any calculation.  Perhaps during testing they were temporarily brought into a view, but I am building a data source so there are no fields in the workbook so removing unused fields checkbox is not an option for this scenario.

                              • 12. Re: Filter out fields/columns from data extract
                                Kevin Taylor

                                David,  After you hide the unused fields or hide individual fields, have you recreated your extract?  This may resolve your issue and actually remove them from the extract itself.

                                 

                                If you hide fields in the data connection window, before you create the extract, this should work as well

                                • 13. Re: Filter out fields/columns from data extract
                                  Bång Anders Thorburn Norén

                                  I have a similar problem, When I hide in the "workbook" view things seem to stick and when I hide in the data source view they seem to disappear. Is this by design or am I just interpreting something that cant be?

                                   

                                  I have a data source on our server, I create a local copy and have build a visualization. since the visualization contains a few different data sources I would like to block out things not needed so I tried the above suggestion. Then a med sure I remade the extract.

                                   

                                  I can however still unhide dimensions and measures calculated and not without creating a new extract.

                                   

                                  How can I resolve this?

                                   

                                  //Bång