4 Replies Latest reply on Apr 7, 2016 7:26 AM by Larry Tsai

    SQL Preprocessing vs Tableau Processing

    Larry Tsai

      Hello,

       

      I've connected to my SQL database and created a custom query to pull specific project data. The data looks like this:

       

      Project Created DateProjectIDUser Sign
      Hours LoggedType of WorkLocation
      31/03/2016AUserA21locA
      31/03/2016AUserA12locA
      31/03/2016BUserC21locA
      31/03/2016BUserA41locA

       

      There are questions I want to answer with this single data source, average hours a single user logs, average hours for each project, average hours for each type of work.. etc. Now I know I can write SQL to shape the data to answer average hours per project however, doing so I would lose other information to aggregate the data.

       

      SELECT [Project Created Date], ProjectID, AVERAGE([Hours Logged]) AS [Hours Logged]
      From tableA
      GROUP BY [Project Created Date], ProjectID 
      

       

      Running the custom query above, I would lose the ability to analyze the other data like User Sign, Type of Work, Location. What solutions have you guys taken to work around this issue?

        • 1. Re: SQL Preprocessing vs Tableau Processing
          Bill Lyons

          I usually connect directly to the SQL tables, doing all the joins in Tableau, and let Tableau create the appropriate aggregation queries. If that is insufficient, such as when I need to join between databases, which Tableau doesn't allow, I build a view in SQL, or Custom SQL in Tableau, with just JOINS and WHERE clauses. I rarely need to do a GROUP BY in a view or custom SQL If I have performance issues, I either create an extract or pre-aggregate into staging tables in SQL and report from that in Tableau. That is usually the last resort, and only when I don't need the detail.

           

          I don't know if this answers your question, but it is at least another perspective.

          • 2. Re: SQL Preprocessing vs Tableau Processing
            Larry Tsai

            Hey Bill,

             

            Thanks for your reply. I'm not looking for a particular answer, just wanted to get an idea of what the community is doing when faced with a similar issues.

            • 3. Re: SQL Preprocessing vs Tableau Processing
              Joe Oppelt

              In my organization we usually let Tableau do the work.  (Or we let me, as the Tableau guy, write the calcs and table calcs that let Tableau do the work.)

               

              Sometimes aggregates and other calculations are already available from other programs within the organization, and at times we just grab a second data source with that  stuff and I just blend it in.


              And sometimes things are just way too expensive (especially time-wise to generate in a real-time user experience), so we generate that in its own data source and blend it in.  Look up "Rolling COUNTD" for an example of that.

               

              Tableau is pretty darned powerful, and people around here are ready to share ideas, experiences and examples of ways they've achieved things that others ask here.  There are a million corners in this tool, and different people have experience (sometimes VERY DEEP experience!) in different areas.

               

              For the record, in our operation just about every data source is a Tableau Data Extract.  Most get updated by scheduled runs daily.

              • 4. Re: SQL Preprocessing vs Tableau Processing
                Larry Tsai

                Thanks for your reply guys, I looked into LOD and I believe this is the best approach to solving the issue presented above.