3 Replies Latest reply on Feb 4, 2012 9:55 PM by Robert Morton

    Calculated Field : How to avoid pushing down through Custom SQL connection?

    Michael Scharber

      Folks,

       

      I've noticed that the calculation of my calculated fields is getting pushed down to Sybase IQ via a wrapper around my Custom SQL.  This is wreaking havoc on my database performance and making me an unpopular guy these days.  Kidding aside, I was shocked to learn that the calculations of my derived fields (for labeling min, max, outliers, etc) is NOT happening in Tableau, but rather down in Sybase with some huge nasty SQL of several KB in length.

       

      Am I doing something heinously wrong here?  How can I avoid Tableau pushing this work down to Sybase?  I want the work of calculated field generation to happen AFTER the results come back from Sybase.

       

      Any advice is welcome.

       

      Thanks,

       

      Michael

        • 1. Re: Calculated Field : How to avoid pushing down through Custom SQL connection?
          Dimitri.B

          With my limited knowledge, this is how Tableau works. There are some cases when calculations are done locally, but most basic calculations are passed on to data source for processing.

           

          A few ways to avoid overloading database would be:

          • Use extract and schedule it to refresh during quiet (for database) time
          • Use one workbook with a plain custom SQL (no calculations) to make an extract and then use another workbook (with calculations) to use that extract as a data source, so all calculations will be done in the extract, not database.

           

          Wether this will work for you depends on your circumstances, of course.

          • 2. Re: Calculated Field : How to avoid pushing down through Custom SQL connection?
            Nathaniel Fitzgerald-Hood

            Yep, I think the first port of call is to create an extract. It'll materialise some of those as part of its optimisation process as well. At the very least it should be pretty quick and will not pass the calculation on.

             

            It's feature for the base datasource to be used for many of these aggregate calcs but like in this instance, having a little more option would be helpful.

            • 3. Re: Calculated Field : How to avoid pushing down through Custom SQL connection?
              Robert Morton

              Hi Michael,

               

              There are many posts on our forums discussing why Tableau generates queries wrapped around Custom SQL, and you can find additional information about the types of queries Tableau generates from this recorded presentation for our 2011 Customer Conference:

               

              Tuning Tableau and Your Database for Great Performance - Matt Higgins & Robert Morton (Video) (Files)

               

              The gist is that the wrapped SQL is necessary for proper functionality, and these computations are best performed close to the data, within the database. For databases which cannot perform well with nested queries, we generally recommend that you either consider creating a view to replace the Custom SQL (and connect Tableau to the view), or attempt to create the connection in Tableau as a single-table or multi-table connection with any necessary filters as global filters.

               

              Last, you should be careful not to include any unnecessary ORDER BY statements in your Custom SQL. This may introduce premature and unnecessary sorting of the data within a subquery, which can be very costly.

               

              I hope this helps,

              Robert