6 Replies Latest reply on Nov 22, 2016 4:11 PM by Jamieson Christian

    Tableau Executes Query Twice

    Louisa Lei

      Hi,

      I want to know how to avoid Tableau running the same query twice.

      In my dashboard I have some calculations that aggregates the total sales within a certain period of time, ranks the individuals by the total sales (with rank() calculation), and then displays the top n individuals.

      The whole process took about 8 mins. I got the backend query from the performance recording and ran it in SQL Server but it only took 4 mins.

      Somehow Tableau ran the query twice and I want to know why and how to avoid it.

       

      I am connecting to an MDX cube. Don't know if this might have something to do with the solver orders or "run before SSAS calculation" selection.

      Did anyone have this situation before and know how to optimize the run time?

      I can specify more if anyone needs other info.

      Thank you!

       

      Here is a screenshot of the performance recording. Two green bars have the exactly same query to aggregate the sales. The orange one should be the rank() table calculation.

      performance.PNG

        • 1. Re: Tableau Executes Query Twice
          Dmitry Chirkov

          Do you mind attaching packaged performance recording workbook?

          • 2. Re: Tableau Executes Query Twice
            Jamieson Christian

            Dmitry Chirkov I'm a little late to the party, but I may have a scenario with the same root cause as the OP's. My scenario involves the creation of an extract. Also, my redundant queries are not exactly the same — the first one includes a calculated field that the second one does not.

             

            I can't post my performance recording here due to sensitive info in the queries, but I have opened case # 02513731 with a performance recording. I would love to get some insight as to what would be causing the query to execute twice. Thanks very much.

             

            Redundant-query-performance-recording-redacted.png

            • 3. Re: Tableau Executes Query Twice
              Dmitry Chirkov

              Jamieson Christian, could you also send logs for this operation, please? I'd like to look at the execution context. (Sending Tableau Desktop Log Files | Tableau Software)

              Workbook as well, if possible. (TWB only, no need for data)

              • 4. Re: Tableau Executes Query Twice
                Jamieson Christian

                Dmitry Chirkov, I just uploaded the logs to case # 02513731. Let me know if you need any additional information. Thanks!

                • 5. Re: Tableau Executes Query Twice
                  Dmitry Chirkov

                  I see the issue.


                  Tableau has a retry logic in case it's unable to compute some of the calculations remotely. If "full query" fails we'll query for individual components so we could compute results locally.

                   

                  In your case there's a calculation that Oracle is not happy with (DATE("2016-01-15") which becomes TRUNC(CAST(N'2016-01-15' AS DATE)))

                  That fails with ORA-01861: literal does not match format string. Since it's a sub-query from complex Custom SQL - it takes 400s+ for query to fail. Right after we run the query once again but without this calculation which runs fine (for another 400s+).

                   

                  To get rid of extra query you have to eliminate the error - you have to fix date string to match format your Oracle server expects.

                  Run this query (you can use Custom SQL in Tableau) to see the format:

                      SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT'

                   

                   

                  Server I have, for example, needs DD-MON-RR which is 15-JAN-16 so my calculation would look like DATE("15-JAN-16").

                   

                  Try this out and let me know how it goes.

                  2 of 2 people found this helpful
                  • 6. Re: Tableau Executes Query Twice
                    Jamieson Christian

                    Dmitry Chirkov, that was it! I replaced the DATE() function with a date literal — which, for some reason, I didn't even realize I could do until now — and then Tableau subsequently presented it to Oracle as TO_DATE('2016-01-15','YYYY-MM-DD'), something that Oracle could work with regardless of server native date format.

                     

                    Thanks very much! Not only did I learn more about how to avoid the myriad date handling pitfalls, I learned a little something about reading the logs, too. I don't know if this very specific scenario helps with the OP's situation any, but I really appreciate your helping me understand what Tableau is doing behind the scenes that might cause it to run a query more than once.