3 Replies Latest reply on Oct 29, 2018 1:22 AM by mortenbodaugaard.jrgensen

    Tableau creating TEMP SQL tables when I group fields

    Fima Leshinsky

      I have created 3 field groups. Tableau creates a temporary SQL table for each group (3 separate queries). It then issues a 4th query against the 3 tables. This is an incredibly expensive way to retrieve the data. Am I doing something wrong?

       

      Tableau Version 2018.2.3

       

      Example of one of the create table queries:

      SELECT TOP 0 (
       CASE "companies"."employees_range" 
       WHEN 'A) 10,001+' THEN 'Enterprise' 
       WHEN 'E) 201-500' THEN 'Mid Market' 
       WHEN 'G) 11-50' THEN 'SMB' 
       ELSE "companies"."employees_range" 
      END) AS "employees range (group)", 
      "companies"."employees_range" AS "employees_range" 
      INTO TEMP"#tableau_40_2_group" 
      FROM "public"."companies" "companies"
      

       

      And the final query:

      SELECT "Group_1"."employees range (group)" AS "employees range (group)", 
        SUM("product_deployments"."spend") AS "sum:spend:ok" 
        FROM "public"."companies" "companies" 
        LEFT JOIN "public"."product_deployments" "product_deployments" ON ("companies"."company_slug" = "product_deployments"."company_slug") 
        INNER JOIN "#tableau_40_2_group" "Group_1" ON ("companies"."employees_range" = "Group_1"."employees_range") 
        WHERE ("Group_1"."employees range (group)" <> 'J) Employee data not available') 
      GROUP BY 1
      

       

      Logs from tabprotosrv:

      {"ts":"2018-10-24T20:08:37.979","pid":65592,"tid":"f4c002","sev":"info","req":"-","sess":"-","site":"-","user":"-","k":"end-query","v":{"cols":1,"elapsed":0.39,"is-command":false,"protocol-class":"redshift","protocol-id":40,"query-category":"NullCheck","query-hash":2713804525,"query-trunc":"SELECT TOP 1 \"companies\".\"employees_range\" AS \"employees_range\"\nFROM \"public\".\"companies\" \"companies\"\nWHERE (\"companies\".\"employees_range\" IS NULL)","rows":0},"ctx":{"client-type":"desktop","procid":"10641","tid":"15700455","version":"20182.18.1009.2120"}}
      {"ts":"2018-10-24T20:08:38.011","pid":65592,"tid":"f4bfb3","sev":"info","req":"-","sess":"-","site":"-","user":"-","k":"begin-query","v":{"is-command":true,"protocol-id":40,"query":"SELECT TOP 0 (CASE \"companies\".\"employees_range\" WHEN 'A) 10,001+' THEN 'Enterprise' WHEN 'E) 201-500' THEN 'Mid Market' WHEN 'G) 11-50' THEN 'SMB' ELSE \"companies\".\"employees_range\" END) AS \"employees range (group)\",\n  \"companies\".\"employees_range\" AS \"employees_range\"\nINTO TEMP\"#tableau_40_2_group\"\nFROM \"public\".\"companies\" \"companies\"","query-category":"TempTable","query-hash":2971100114},"ctx":{"client-type":"desktop","procid":"10641","tid":"15700456","version":"20182.18.1009.2120"}}
      {"ts":"2018-10-24T20:08:38.373","pid":65592,"tid":"f4bfb3","sev":"info","req":"-","sess":"-","site":"-","user":"-","k":"end-query","v":{"cols":0,"elapsed":0.361,"is-command":true,"protocol-class":"redshift","protocol-id":40,"query-category":"TempTable","query-hash":2971100114,"query-trunc":"SELECT TOP 0 (CASE \"companies\".\"employees_range\" WHEN 'A) 10,001+' THEN 'Enterprise' WHEN 'E) 201-500' THEN 'Mid Market' WHEN 'G) 11-50' THEN 'SMB' ELSE \"companies\".\"employees_range\" END) AS \"employees range (group)\",\n  \"companies\".\"employees_range\" AS \"employees_range\"\nINTO TEMP\"#tableau_40_2_group\"\nFROM \"public\".\"companies\" \"companies\"","rows":0},"ctx":{"client-type":"desktop","procid":"10641","tid":"15700456","version":"20182.18.1009.2120"}}
      {"ts":"2018-10-24T20:08:38.382","pid":65592,"tid":"f4bfb3","sev":"info","req":"-","sess":"-","site":"-","user":"-","k":"begin-query","v":{"is-command":true,"protocol-id":40,"query":"INSERT INTO \"#tableau_40_2_group\" (\"employees range (group)\", \"employees_range\")\nVALUES\t('Enterprise', 'A) 10,001+'),\n\t('Enterprise', 'B) 5,001-10,000'),\n\t('Enterprise', 'C) 1,001-5,000'),\n\t('Enterprise', 'D) 501-1,000'),\n\t('Mid Market', 'E) 201-500'),\n\t('Mid Market', 'F) 51-200'),\n\t('SMB', 'G) 11-50'),\n\t('SMB', 'H) 2-10'),\n\t('SMB', 'I) 1'),\n\t('J) Employee data not available', 'J) Employee data not available')","query-category":"CreateTable","query-hash":3170544785},"ctx":{"client-type":"desktop","procid":"10641","tid":"15700457","version":"20182.18.1009.2120"}}
      {"ts":"2018-10-24T20:08:38.782","pid":65592,"tid":"f4bfb3","sev":"info","req":"-","sess":"-","site":"-","user":"-","k":"end-query","v":{"cols":0,"elapsed":0.399,"is-command":true,"protocol-class":"redshift","protocol-id":40,"query-category":"CreateTable","query-hash":3170544785,"query-trunc":"INSERT INTO \"#tableau_40_2_group\" (\"employees range (group)\", \"employees_range\")\nVALUES\t('Enterprise', 'A) 10,001+'),\n\t('Enterprise', 'B) 5,001-10,000'),\n\t('Enterprise', 'C) 1,001-5,000'),\n\t('Enterprise', 'D) 501-1,000'),\n\t('Mid Market', 'E) 201-500'),\n\t('Mid Market', 'F) 51-200'),\n\t('SMB', 'G) 11-50'),\n\t('SMB', 'H) 2-10'),\n\t('SMB', 'I) 1'),\n\t('J) Employee data not available', 'J) Employee data not available')","rows":0},"ctx":{"client-type":"desktop","procid":"10641","tid":"15700457","version":"20182.18.1009.2120"}}
      {"ts":"2018-10-24T20:08:39.049","pid":65592,"tid":"f4bfb3","sev":"info","req":"-","sess":"-","site":"-","user":"-","k":"begin-query","v":{"is-command":false,"protocol-id":40,"query":"SELECT \"Group_1\".\"employees range (group)\" AS \"employees range (group)\",\n  SUM(\"product_deployments\".\"spend\") AS \"sum:spend:ok\"\nFROM \"public\".\"companies\" \"companies\"\n LEFT JOIN \"public\".\"product_deployments\" \"product_deployments\" ON (\"companies\".\"company_slug\" = \"product_deployments\".\"company_slug\")\n INNER JOIN \"#tableau_40_2_group\" \"Group_1\" ON (\"companies\".\"employees_range\" = \"Group_1\".\"employees_range\")\nWHERE (\"Group_1\".\"employees range (group)\" <> 'J) Employee data not available')\nGROUP BY 1","query-category":"Data","query-hash":746611398},"ctx":{"client-type":"desktop","procid":"10641","tid":"15700458","version":"20182.18.1009.2120"}} 
      
        • 1. Re: Tableau creating TEMP SQL tables when I group fields
          mortenbodaugaard.jrgensen

          This is expected behavior. Since the group does not exist in the data, Tableau needs a way to "create" said group.

           

          The real way to not have this issue is to have the group exist in the database.

          • 2. Re: Tableau creating TEMP SQL tables when I group fields
            Fima Leshinsky

            Thanks for the reply. What I don't understand is why, when I use a CASE statement in a calculated field to perform the exact same grouping, Tableau generates a single efficient SQL query.

             

            SELECT (CASE WHEN ("companies"."employees_range" IN ('A) 10,001+', 'B) 5,001-10,000', 'C) 1,001-5,000', 'D) 501-1,000')) THEN 'Enterprise' ELSE (CASE WHEN ("companies"."employees_range" = 'E) 201-500') THEN 'Mid-Market' ELSE (CASE WHEN ("companies"."employees_range" IN ('F) 51-200', 'G) 11-50', 'H) 2-10', 'I) 1')) THEN 'SMB' ELSE NULL END) END) END) AS "calculation_5298203543849082880",
              COUNT(DISTINCT "companies"."company_slug") AS "ctd:company_slug:ok",
              SUM("product_deployments"."spend") AS "sum:spend:ok"
            FROM "public"."companies" "companies"
              LEFT JOIN "public"."product_deployments" "product_deployments" ON ("companies"."company_slug" = "product_deployments"."company_slug")
              LEFT JOIN "public"."use_cases" "use_cases" ON ("companies"."company_slug" = "use_cases"."company_slug")
            GROUP BY 1
            

             

            Why would you ever group fields vs. using a calculated field to accomplish the same thing?

            • 3. Re: Tableau creating TEMP SQL tables when I group fields
              mortenbodaugaard.jrgensen

              Whether or not a TEMP table is better than a CASE statement I guess depends on the underlying data source. Some datasources like Oracle I believe have a max length to queries and I guess that is why Tableau chose to do it like that.

               

              I ALWAYS use CASE statements but they take longer time to type out compared to using the group functionality. But yes you can force the behavior you want by using CASE statements.