1 2 3 Previous Next 32 Replies Latest reply on Nov 17, 2015 9:00 AM by James Carter

    Tableau & SQL Group

    Shawn Wallwork

      This is now a All-Things SQL thread. Anything to do with SQL...

       

      • Multi-Table Joins
      • Custom SQL Joins
      • Joins w/ Sub-queries
      • SQL Syntax Tableau supports
      • SQL Syntax Tableau does not supports

       

      etc..

       

      --Shawn

       

      Message was edited by: Shawn Wallwork

        • 1. Re: Joins Group
          Matt Lutton

          Oh god, I would love this if it would help me learn more about the topic.  I'm a newbie though, and don't know if that is the intended demographic, but I'd join even if it seems over my head

          • 2. Re: Joins Group
            Shawn Wallwork

            Being an SQL newbie myself, I'm hoping the discussion will be a reach, and a way to learn the ****** (finicky) syntax.

             

            --Shawn

            • 3. Re: Joins Group
              Cristian Vasile

              Shawn,

               

              i shall suggest you to rename the thread as "SQL Joins Group", joins is a little bit confusing

               

              sql_join.jpg

               

              Regards,

              Cristian.

              • 4. Re: SQL Joins Group
                kettan

                CROSS JOIN — the most under-valued join

                I like very much to use cross joins for dynamic date data creation, data reshaping, and advanced date calculations.

                 

                 

                LESSON 1: Data Reshaping with Cross Join

                 

                Although we can reshape data with tools like the Tableau Add-In for Reshaping Data in Excel, we can often take a much simpler route using cross join to reshape data on the fly. This technique is particularly valuable for reshaping live data, where the intermediate step of reshaping data isn't possible.

                 

                See how in the step-by-step guide below and attached spreadsheet & packaged workbook.

                 

                CROSS JOIN dynamic data reshaping - Data.png

                Click on picture below to enlarge it so it becomes readable:

                CROSS JOIN dynamic data reshaping - Step by Step.png

                1 of 1 people found this helpful
                • 5. Re: SQL Joins Group
                  Jim Wahl

                  Johan,

                   

                  Nice post. Steps 1 to 4 are a bit hard to read. Could you post or attach a higher-res version of the image (or embed them in the workbook).

                   

                  Jim

                  • 6. Re: Re: SQL Joins Group
                    Jonathan Drummey

                    Cross joins are also called cross products and cartesian joins, I'm adding them here so search engines can pick this up.

                     

                    Also, here's an alternative: the way I typically do this sort of reshaping is by doing N UNION queries where N is the number of columns to merge. This can be faster to set up because the queries are simpler (getting the # of parentheses right in nested IIF statements is an Achilles heel of mine). I also find the UNION easier to read and Also, for most of my work I'm using Tableau data extracts, so the performance hit imposed by multiple queries isn't a problem because they are only run at extract creation time.

                     

                    SELECT [Dimension 1] AS [Dimension1],

                      [Measure 1] AS [Measure Value],

                      "Measure 1" AS [Measure Name]

                    FROM [source$]

                    WHERE NOT ISNULL([Measure 1])

                    UNION ALL

                    SELECT [Dimension 1] AS [Dimension1],

                      [Measure 2] AS [Measure Value],

                      "Measure 2" AS [Measure Name]

                    FROM [source$]

                    WHERE NOT ISNULL([Measure 2])

                    UNION ALL

                    SELECT [Dimension 1] AS [Dimension1],

                      [Measure 3] AS [Measure Value],

                      "Measure 3" AS [Measure Name]

                    FROM [source$]

                    WHERE NOT ISNULL([Measure 3])

                    1 of 1 people found this helpful
                    • 7. Re: SQL Joins Group
                      kettan

                      Jim,

                       

                      The image is supposed to enlarge to full size by clicking on it, and tested to work in Google Chrome and IE 10. I have added a comment so it is easier to know that one need to click on it. I have also embedded the image in the workbook and attached it as a file.

                       

                      Johan

                      • 8. Re: SQL Joins Group
                        Allan Walker

                        Good thread Shawn,

                         

                        Maybe not limit it to joins - basic SQL functions would be good.

                         

                        Also, can we add unions to the discussion.

                         

                        Thanks!

                         

                        Best Regards,

                         

                        Allan

                        • 9. Re: SQL Joins Group
                          kettan

                          Thanks for sharing. It is good to have various options to choose between.

                           

                          I share your feelings towards IIF parenthesis. I don't use Excel as data source in my daily work, but had to use IIF to share the cross join technique in "Excel SQL". In my daily work with Microsoft SQL Server and Oracle, I would have used a simple CASE sentence with no parenthesis.

                           

                          Ps. I intend to share a few more CROSS JOIN lessons in near future and am curious to observe how many of them technically also can be solved with UNION and thus learn more about the similarities and differences between CROSS JOIN and UNION.

                          • 10. Re: Re: Re: SQL Joins Group
                            kettan

                            A Recent Experience with Union vs Cross Join

                             

                            My fondness of CROSS JOIN got a boost when I recently rewrote a UNION query to a CROSS JOIN query.

                             

                            I needed to transfer a view to Tableau Server that has 8 union queries, but couldn't, because it timed-out after 2 hours. The query was likely slow because I joined tables with the view to add needed information.

                             

                            BENEFITS WITH THE CROSS JOIN

                             

                            Performance

                            Changing the view to a cross join query boosted performance from more than 2 hours (timeout) to 7 minutes!

                             

                            Readability

                            The query became much easier to understand and debug.

                             

                            Simplicity

                            It became much easier to add and remove tables and columns.

                             

                            Errability

                            It is easier to avoid errors when the same information is only added once (rather than 8 times as in this case).

                             

                            Comparison

                            Here are some statistics showing how CROSS JOIN improved this query.

                             

                            (Click picture to read comparison in normal size)

                            cross join vs union comparison.png

                            1 of 1 people found this helpful
                            • 11. Re: Tableau & SQL Group
                              Shawn Wallwork

                              I just worked my way through Ashley Ohmann's excellent presentation to the ATUG in June. (The PowerPoint and Excel workbooks are included in Andy Piper's note.) She presented several useful SQL techniques...

                               

                              The original basic Excel connection looks like this:

                               

                              SELECT ['Region Sales$'].[Division] AS [Division],

                                ['Region Sales$'].[Duracell] AS [Duracell],

                                ['Region Sales$'].[Duracell LY] AS [Duracell LY],

                                ['Region Sales$'].[Energizer] AS [Energizer],

                                ['Region Sales$'].[Energizer LY] AS [Energizer LY],

                                ['Region Sales$'].[Region] AS [Region]

                              FROM ['Region Sales$']

                               

                              RESHAPING DATA WITH SQL

                              This could work for some visualizations, but getting the Duracell and Energizer data combined into single fields (restructuring the data) could make other visualizations possible/easier. Here's the code she used to do this:

                               

                              SELECT

                                 ['Region Sales$'].[Division] AS [Division]

                                ,['Region Sales$'].[Region] AS [Region]

                                ,"Duracell" AS [Battery Type]

                                ,['Region Sales$'].[Duracell] AS [Sales TY]

                                ,['Region Sales$'].[Duracell LY] AS [Sales LY]

                              FROM ['Region Sales$']

                              UNION

                               

                              SELECT

                                 ['Region Sales$'].[Division] AS [Division]

                                ,['Region Sales$'].[Region] AS [Region]

                                ,"Energiizer" AS [Battery Type]

                                ,['Region Sales$'].[Energizer] AS [Sales TY]

                                ,['Region Sales$'].[Energizer LY] AS [Sales LY]

                              FROM ['Region Sales$']

                               

                              Note that she:

                                   1) Separated Energizer and Duracell into two different tables,

                                   2) Created two shared fields ([Sales TY] & [Sales LY])

                                   3) Added a new field (Battery Type) so the values could be differentiated in Tableau

                               

                              CALCULATIONS USING SQL

                              Ashley also demonstrated how you can do calculations in the SQL, which can save you time by avoiding doing IF/THEN calcs in Tableau:

                               

                              SELECT

                                 ['Region Sales$'].[Division] AS [Division]

                                ,['Region Sales$'].[Region] AS [Region]

                                ,"Duracell" AS [Battery Type]

                                ,['Region Sales$'].[Duracell] AS [Sales TY]

                                ,['Region Sales$'].[Duracell LY] AS [Sales LY]

                                ,['Region Sales$'].[Duracell LY]*1.15 AS [Quota]

                              FROM ['Region Sales$']

                              UNION

                               

                              SELECT

                                 ['Region Sales$'].[Division] AS [Division]

                                ,['Region Sales$'].[Region] AS [Region]

                                ,"Energiizer" AS [Battery Type]

                                ,['Region Sales$'].[Energizer] AS [Sales TY]

                                ,['Region Sales$'].[Energizer LY] AS [Sales LY]

                                ,['Region Sales$'].[Energizer LY]*1.1 AS [Quota]

                              FROM ['Region Sales$']

                               

                              Note that she multiplied Duracell by 1.15 and Energizer by 1.1.

                               

                              There were several revelation for me (a SQL newbie) in Ashley's presentation and I hope posting some of her work here will help others.

                               

                              --Shawn

                               

                              Ashley thanks for the presentation! And Andy thanks for organizing it.

                              1 of 1 people found this helpful
                              • 12. Re: Tableau & SQL Group
                                kettan

                                Reversed LIKE Search

                                 

                                Almost always, we use the LIKE operator as Field LIKE Value, but sometime it is helpful to use it as Values LIKE Field.

                                Attached workbook shows two examples of the reversed LIKE search, one SQL and one Calculated Field:

                                 

                                SQL

                                SELECT [State or Province], [Region], [Sales]

                                FROM [Orders$]

                                WHERE <Parameters.Region Parameter> LIKE '%' + [Region] + '%'


                                Calculated Field

                                Region Filter = CONTAINS([Region Parameter], [Region])

                                1 of 1 people found this helpful
                                • 13. Re: Tableau & SQL Group
                                  Matt Lutton

                                  I had a case where I needed to use the opposite of CONTAINS last week, and didn't realize the keyword NOT was supported.  This may be well known to others, but thought it was worth posting here since I did not realize it just a few days ago.  So, when wanting the opposite of CONTAINS, it may help others to note you can write calculated fields like:

                                  NOT CONTAINS([FieldName], "whatever")

                                   

                                  Nothing at all related to SQL, and not related to the functionality mentioned above by Johan, so I can delete this post if you like.

                                  • 14. Re: Tableau & SQL Group
                                    Shawn Wallwork

                                    No need to delete. Or should I say NOT (delete). I find this sort of basic "syntax" discovery interesting. When I first entered the Tableau space and delved into the field calculations, I was surprised there wasn't a basic dictionary of supported syntax. Since the Knowledge Base folks have been busily working on this sort of thing of late, there may be one out there now. [KB Folks please post a link, we Forum Folks will happily link to your work.]

                                     

                                    Note: Now that the IPO has successfully launched, can we make the assumption that Tableau has moved from "start-up" to state-of-the-art software, and all the documentation that implies/deserves?

                                     

                                    Matthew, thanks for contributing to this thread.

                                     

                                    --Shawn

                                    1 2 3 Previous Next