2 Replies Latest reply on Feb 26, 2016 8:38 AM by J Frueh Branched from an earlier discussion.

    Very slow "executing query" when trying to filter based on calculated field and/or LOD

    J Frueh

      I am attempting one last thing in order to close the loop on my task.

       

      I am essentially trying to set a flag that will allow me to filter down to only show the records when the change first happened.

       

      I have successfully set up a field that shows the date of that change (based on a few LOD calculations).

      I think the best way to only show the records that are from that snapshot date is to use another LOD calculation as below, then set the filter to 1.

      Like this:

      {fixed [Opportunity ID]: sum(if [Stage or Forecast Change First Date]=[Snapshot Date] then 1 else 0 end) }

       

      I also thought about just a simple Calculated field that can be filtered to 'First'

      Like this:

      if ([Stage or Forecast Change First Date]=[Snapshot Date]) then 'First' else 'Not' end

       

      HOWEVER, any attempt to drag either of these two fields into the Filters section makes Tableau sit there running a query for hours that I have to eventually cancel after ~3 hours.

       

      What am I doing wrong?

        • 1. Re: Very slow "executing query" when trying to filter based on calculated field and/or LOD
          Steve Mayer

          Every LOD calculation is likely generating a sub-select statement (you can check Tableau Desktop logs to see the actual SQL), so it could very well be that the generated query is bringing your DB to a crawl. The only way to truly figure that out is to capture the SQL in the Tableau Desktop logs and have a DBA analyze the query for performance. If you multiple LOD calcs, I'm sure that query can become rather....gnarly.

           

          One idea... have you tried creating a Tableau extract of your data? LOD calcs definitely perform better using TDE extracts, so worth a try if you can live with an Extract for this particular analysis.

           

          -Steve

          • 2. Re: Very slow "executing query" when trying to filter based on calculated field and/or LOD
            J Frueh

            Thanks for the reply Steve.  I am looking at the log files now and am blown away by how crazy the sql is on this.  I hadn't really considered what is driving the stuff Tableau was doing. 

             

            Now that you mention it, I'm thinking the best solution would be to write these calculated fields into the sql that's creating the view in the first place so they are already there.  Now I just need to get the permission to do that, and figure out the best way to write these things into sql.