7 Replies Latest reply on Jul 28, 2016 10:53 AM by Vincent Sanchez

    [SOLVED] SQL does not reflect expression in Calculated Field

    Vincent Sanchez

      Hi Everyone,

       

         A newbie here with a team lead that is adamant on getting my training wheels off. I come to you with a question about the underlying SQL that Tableau uses to gather data from a database. In my sample work book, I have two Calculated Fields that I created that have the following expressions:

       

      • Budget Margin Average(AGG)
        • avg([Budget Margin])*100
      • Sum of Difference(AGG)
        • (sum([Profit])/sum([Inventory]))*100000

       

        Note that these are similar expressions that I'm using in my project but due to the nature of it, I can't share my other workbook. My team lead has requested that I check over the underlying SQL to ensure that the data is being calculated correctly. If not, the entire design is faulty. When I do check the SQL query through using the Performance Recording Tool, I get this (I've included line numbers for readability):

       

      1    Command

      2    SELECT IIF(IsNull(DATEPART('yyyy',Fix([factTable].[Date]))),NULL,CStr(DATEPART('yyyy',Fix([factTable].[Date])))) AS [none:Calculation_6300719152037418:nk],

      3    SUM(IIF(IsNull([factTable].[Inventory]),NULL,SGN([factTable].[Inventory])*Int(ABS([factTable].[Inventory])))) AS [TEMP(Calculation_6450727125049749)(2042856701)(0)],

      4    SUM([factTable].[Profit]) AS [TEMP(Calculation_6450727125049749)(631662408)(0)],

      5    COUNT([factTable].[Budget Margin]) AS [TEMP(Calculation_7490727133709152)(3976334922)(0)],

      6    SUM([factTable].[Budget Margin]) AS [TEMP(Calculation_7490727133709152)(890354727)(0)],

      7    MAX(IIF(IsNull(DATEPART('yyyy',Fix([factTable].[Date]))),NULL,CStr(DATEPART('yyyy',Fix([factTable].[Date]))))) AS [max:Calculation_6300719152037418:nk]

      8    FROM ( [factTable]

      9    INNER JOIN [Location] ON [factTable].[Area Code] = [Location].[Area Code] )

      10  INNER JOIN [Product] ON [factTable].[ProductId] = [Product].[ProductId]

      11  WHERE IIF(IsNull(((#07/19/2016 15:15:54# >= #06/01/2016 15:15:31#) AND (#07/19/2016 15:15:54# <= #07/19/2016 15:15:54#))),NULL,IIF(((#07/19/2016 15:15:54# >= #06/01/2016 15:15:31#) AND (#07/19/2016 15:15:54# <= #07/19/2016 15:15:54#)),-1,0))

      12  GROUP BY IIF(IsNull(DATEPART('yyyy',Fix([factTable].[Date]))),NULL,CStr(DATEPART('yyyy',Fix([factTable].[Date]))))

       

        I was expecting to see the calculations done that I specified within the SQL in the Calculated Fields but I don't see them. I also notice several different fields called "temp calculations" being set as the values of the measures I was using in my expressions. For example:

       

      • Budget Margin Average = (Line 6 / Line 5)*100
      • Sum of Difference = (Line 4 /Line 3)*100000

      Is this expected and if not, how can I verify that the expressions that I am using in my Calculated Fields are working correctly?

       

      Thank you all again for your help! I'm growing as Tableau developer with each of these challenges and your guidance.

        • 1. Re: SQL does not reflect expression in Calculated Field
          Jeff Strauss

          thanks for attaching the packaged workbook.  I get the same result as your performance recording.  My only thought is based on the database, there may be temp tables created and the calcs are happening somewhere internally related to the temp tables.  But perhaps somebody more knowledgable will chime in, I'm interested in the answer...

          • 2. Re: SQL does not reflect expression in Calculated Field
            Vincent Sanchez

            That is an interesting hypothesis. I'm thinking the same too. Somewhere behind in Tableau, the calculation must be done there. I'm just unsure what to tell my team lead. I might have to have Tableau Support on this.

            • 4. Re: SQL does not reflect expression in Calculated Field
              Dmitry Chirkov

              Interesting observation - looks like we decide to finish up these calculations locally even though MSAccess could totally do this for us:

               

              SELECT
                IIF(IsNull(DATEPART('yyyy',Fix([factTable].[Date]))),NULL,CStr(DATEPART('yyyy',Fix([factTable].[Date])))) AS [Year],
                AVG([factTable].[Budget Margin])*100 as [Budget Margin Average],
                SUM([factTable].[Profit])/SUM(IIF(IsNull([factTable].[Inventory]),NULL,SGN([factTable].[Inventory])*Int(ABS([factTable].[Inventory]))))*100000 as [Sum of Difference]
              FROM [factTable]
              WHERE (((NOT ISNULL([factTable].[Area Code])) AND (NOT ISNULL([factTable].[ProductId]))) AND IIF(IsNull(-1),NULL,IIF(-1,1,0)))
              GROUP BY IIF(IsNull(DATEPART('yyyy',Fix([factTable].[Date]))),NULL,CStr(DATEPART('yyyy',Fix([factTable].[Date]))))
              

               

              Overall, there's no guarantee that Tableau will offload ALL the work to a remote database engine and could opt out for doing some data processing by itself (just like in this case). You'd have to check resulting numbers if you'd like to verify correctness.

               

              p.s. I'll run this by our team to see if we can get more specific explanation as to why we opted for local compute

              p.s.s. someone on this threads mentioned "temp tables" - those have nothing to do with this particular case.

              • 5. Re: SQL does not reflect expression in Calculated Field
                Vincent Sanchez

                Thank you Dmitry! If the process is like you said where Tableau does the calculation locally (prior to visualization) the only other way that I can think that can show that the calculations are working is the process that you mentioned where we would have to do the calculation local (e.g. using excel for verification). I was just hoping there would be a more robust method for verification. Please let me know what your team says and thank you again!

                • 6. Re: SQL does not reflect expression in Calculated Field
                  Dmitry Chirkov

                  First of all - we really hope that we are no longer in the position (as a company with 10+ year old product) that we have to prove that our math and queries are robust.

                   

                  As for logic above - I verified - we bring in SUM and COUNT separately so there's a higher chance of answering future questions via cache hits since there's very high probability that your viz would need those two separately. We had this put in place many years ago as for several customers it was a very important performance optimization.

                   

                  Hope this helps!

                  2 of 2 people found this helpful
                  • 7. Re: SQL does not reflect expression in Calculated Field
                    Vincent Sanchez

                    Hi Dmitry,

                     

                       I definitely agree with you. Tableau has been such a versatile and dependable tool that I also feel that having to prove the calculations are correct seems unneeded. It does help know that Tableau brings in those queries separately for performance optimization. I can let my team lead know about this and have him figure out if he'd want me to do all the calculations again in excel for verification (which I don't want to do but who does haha) or trust Tableau (which I do). Thank you again Dmitry!