7 Replies Latest reply on Mar 7, 2013 10:20 PM by Joshua Milligan

    Removing a formula from a calculated Field

    Petchiappan R

      Hi All,

       

      I am working on a larger database with more a 150,000 records. I am using a lengthy and complex formula for computing a calcuated field and it takes a lot of time to finish.

       

      Everytime the calcuated field is pulled into the view for analysis, the query re-runs again and it is taking a lot of time to finish.

       

      Is there a way in Tableau to do a 'paste-special' like in Excel or remove/de-link the formula from the calculated field but retaining the values of it?

       

      Thanks

      Petchiappan

        • 1. Re: Removing a formula from a calculated Field
          Alex Kerin

          If you are using and extract, you can optimize the extract, which can do the equivalent of paste special, depending on the calculation (typically not a table calc or an aggregated calculation)

          1 of 1 people found this helpful
          • 2. Re: Removing a formula from a calculated Field
            Joshua Milligan

            Petchiappan,

             

            I was just about to suggest the data extract and noticed that Alex beat me to it!

             

            What is your data source?  Because another possibility that may work is to do the calculation ahead of time and store it at the data source level.

             

            Regards,

            Joshua

            • 3. Re: Removing a formula from a calculated Field
              Petchiappan R

              Hi Joshua & Alex,

               

              Thanks for your inputs. Could you please let me know about any links or help documents for optimizing data extract?

               

              Joshua,

               

              My data source is Excel. I have used a lengthy calculation which is not possible to run ahead in Excel and store at data source level.

               

              As of now I am doing the following as a workaround for this:

                   1) Export the calcuated values to a new Excel sheet

                   2) Integrate this with the original data source (Excel File that has raw input data)

                   3) Then refreshing the data extract in Tableau

               

              Though this works it takes a lot of time as Excel takes a lot of time to process 150000 records.

               

              Please let me know your thoughts on this.

               

              Thanks

              Petchiappan

              • 4. Re: Removing a formula from a calculated Field
                Joshua Milligan

                Petchiappan,

                 

                Is your data already extracted?  If not, then click on the "Extract Data" from the Data menu as shown below.  Then to optimize, you can select "Optimize" as shown.  If your data is already exracted, I would  not expect that a row-level calculation to impact performance as you describe.  Is it possible that the calculation is done at an aggregate level (does it include SUM, COUNT, AVG, or similar)?

                 

                Your approach sounds like it works, but I think I'm missing something in my understanding.  A couple of questions:

                1. What kind of calculation can be done in Tableau that cannot be done in Excel, but can be re-integrated back into the Excel data?  Perhaps you can post the calculation?

                2. Why does Excel have a hard time processing 150,000 records if it's not doing the calculation?

                 

                Capture.PNG

                • 5. Re: Removing a formula from a calculated Field
                  Joshua Milligan

                  Also, a couple of notes on calculations and efficiency:

                  1. Avoid string manipulation if possible.  Numeric calculations are always faster.  Aliases and formatting can be done to display numeric values as readable text in the view.

                  2. Row level calculations can be performed once and have results cached or stored in an extract.  They aren't always the correct calculation to use -- so make sure you understand whether the calculation should be done at a row level or aggregate level.

                  3. Aggregate calculations will have to be calculated every time you adjust the view in a way that changes the level of aggregation (though Tableau will cache the results as much as possible).

                  4. Version 8 (due for release this month) significantly optimizes calculations, especially the way strings are handled.  So that might actually be the answer for you (new versions fix everything, right? )

                   

                  Regards,

                  Joshua

                  • 6. Re: Removing a formula from a calculated Field
                    Petchiappan R

                    Hi Joshua,

                     

                    I tried optimizing the extract and now the performance has increased multiple times. Thanks a lot for the help.

                     

                    Actually I am trying to classify around 150000 records into 10 pre-defined categories based on specific keyword searches on 3 columns (Remarks, Comments & Description) of the database. The columns contains sentences typed in the by the user at the time of record creation.

                     

                    I am using the function 'Find' and nested if statements (50 in number) to perform this. As I understand, this calculation involves string operations and it is of row level and hence the performance has come down. However after optimizing the extract it works fine.

                     

                    Also I am eagerly awaiting the release of Tableau 8 to explore the advanced features of it.

                     

                    Also are there any data mining techniques and statistical representations like probability distributions, regression analysis e.t.c  present in Tableau?

                     

                    Thanks,

                    Petchiappan

                    • 7. Re: Removing a formula from a calculated Field
                      Joshua Milligan

                      Petchiappan,

                       

                      There are definitely statistical trend models, confidence intervals, standard deviation, etc... all built in to Tableau.  Version 8 will introduce some forecasting models.  I would definitely encourage you to search out the knowledge base articles on Tableau's website and also ask any specific questions on the forums! 

                       

                      Regards,

                      Joshua