6 Replies Latest reply on Jul 25, 2017 6:17 AM by Sourabh Dasgupta

    SQL Query building byTableau

    Sourabh Dasgupta

      Hello...I found something interesting with respect to how Tableau generates the SQL Queries. I connected to the sales data stored in my SQL Server database, and created a calculated field as SUM(Profit) / SUM(Sales). On the tableau sheet, I brought [Region] in the rows and the calculated field in the columns. The SQL Query that was generated by Tableau looks like below:

       

      SELECT REGION, SUM(Sales), SUM(Profit)

           FROM [Orders]

           GROUP BY REGION

       

      I thought the expression SUM(Profit) / SUM(Sales) would also be part of the SQL Query but it is not. I was wondering how & where is this evaluated if not at the database level.

      Even if we now change the expression to something like SUM(Sales) / SUM(Profit), no new SQL Query is generated.

       

      Logically it makes sense, as once I receive the individual components i.e. SUM(Sales) and SUM(Profit) from the database, the rest of the expression is evaluated locally. This is true irrespective of what database you connect to. But I wanted to know which "Tableau Process" is responsible for this. Any ideas?

        • 1. Re: SQL Query building byTableau
          Bora Beran

          Tableau has a query cache (more than one type of cache actually) to improve performance and reduce unnecessary requests from the database. It will compute things locally based on previously queried data if it can.

           

          Here is a Tableau Conference talk from a few years ago that covers this (slide 14 talks about query caches)

           

          Understanding Tableau's Visual Pipeline

          • 2. Re: SQL Query building byTableau
            Sourabh Dasgupta

            Thank you! I was aware of the Query Cache, but was not sure if the SUM(profit)/SUM(sales) will also be done at the cache level. Is it the Model cache that will hold this value? Also I believe its VizQL process is responsible for calculating this, right?

            • 3. Re: SQL Query building byTableau
              SANDIP SHARMA

              Hi Sourabh,

               

              If I am not wrong then VizQL is only responsible to draw your charts our output of your design or you can say for visualisation. As far as calculation concern Data Engine does this for Tableau because Data Engine loads data in its memory then perform calculation and send it back to VizQL.

              • 4. Re: SQL Query building byTableau
                Jonathan Drummey

                Hi,

                 

                One thing to keep in mind here is that in more complex views SUM(Profit)/SUM(Sales) could be computed multiple times at different levels for the same view, here are several examples that I can think of:

                 

                1) reference lines using the Total aggregation

                2) grand totals and subtotals

                3) the TOTAL() table calculation

                4) trend lines

                5) data blends where there are linking dimensions at a finer grain than the vizLOD

                6) Level of Detail expressions where Tableau determines it has the information needed to compute results without resorting to a subquery.

                 

                I'm not sure which module(s) are responsible for all of these variations on the calculation and I don't know know which one(s) might end up in one of the several levels of caching that Tableau does, plus this is an area of ongoing development for Tableau. Some of the caching features plus #5 and #6 are new since I started using Tableau and I'm sure there will be more, so whatever statement Bora might make about where a calculation is computed could very well change in the future. In my case I'm happy that Tableau has been (mostly) fast enough and is continuing to get faster while handling more complex scenarios.

                 

                Joanthan

                • 5. Re: SQL Query building byTableau
                  Bora Beran

                  The computation happens as part of VizQL process but this is an area of ongoing development, which is the main reason I don't want to give too much detail on as how we cache and find cache hits etc. will change in the not too distant future.