12 Replies Latest reply on Dec 21, 2016 11:53 AM by Alexander Mou

    CROSS JOIN with Tableau's join dialog

    kettan

      Did you know that we can cross join tables with Tableau's join dialog?

      In case you didn't:

      This is done by adding dummy keys to both tables with one and the same value in all rows (such as 1=1).

       

      This gives us an all-to-all relationship of the equi-join – a new term just coined for easier understanding and comparison with similar terms such as a  many-to-many relationship and alike.

       

      Have you used Tableau's join dialog for cross joining two tables?

      If yes, what is your experience?

      If no, but like to, please share your findings.

       

      Example of a cross product of 140,000,000 rows ( data 700,000 x lookup 200 )

      The gif below shows how surprisingly well Tableau responds with a data table of 700,000 rows and lookup table of 200 rows!

      imageFile.gif

       

       

      Why

      1. As shown above: It is fast, very fast!

      2. No inflation of rows to store! The cross join is done on the fly by Tableau.

       

       

      How

      The guide below shows how. See also attached xlsx and twb file.

       

       

      Warning: Don't Extract!

      You can save the file as twbx without any issues (still fast, still no inflation of rows).

      But if you thereafter extract the twbx connection, you will get a full cross product!

       

       

      Step 1: Add dummy keys to both tables

      Add a dummy key containing one and the same value in both tables, such as CrossJoinKey seen below:

      imageFile.png

       

      The attached Excel file contains 50,000 rows, but can easily be expanded to more by copying the rows.

      The values will be random, because the columns [PeriodStart], [PeriodEnd] and [Amount] are populated with RANDBETWEEN formulas.

       

       

      Step 2: Connect to data and join tables

      imageFile.gif

       

       

       

       

       

      Step 3: Filter

      imageFile.gif

       

       

       

       

       

      Step 4: Build view

      imageFile.gif

       

       

       

       

       

      Step 5: Test performance with Quick Filter

      imageFile.gif

       

       

       

       

       

      Step 6: Test performance with calculation [Days in Period]

      imageFile.gif

       

       

       

       

       

      Step 7: Test performance with more demanding calculation [Amount in Period]

      imageFile.gif

       

       

       

       

      Attached Tableau Version: 9.0

      .

        • 1. Re: CROSS JOIN with Tableau's join dialog
          Shawn Wallwork

          Nice post Kettan. Interesting.

           

          --Shawn

          • 2. Re: CROSS JOIN with Tableau's join dialog
            kettan

            Thanks   I also find this interesting!

             

            Just so you know:

             

            1) I had issues uploading the attached files (Jive didn't like my file names). This is solved now.

             

            2) One extra information is added, see section titled Warning: Don't Extract!

            7 of 7 people found this helpful
            • 3. Re: CROSS JOIN with Tableau's join dialog
              Rody Zakovich

              Thanks for sharing kettan

               

              I usually do Cross Join via a View in the DB (Crossing a Lookup table to a transactional table), that way the "storage" is happening in Tableau, but the data is being populated via a Query (View)

               

              But, as not all Databases support Views, I think this is a great example of how to get around it in Tableau.

               

              Thanks again for sharing, I know I will reference this in future responses!

               

              Regards,

              Rody

              2 of 2 people found this helpful
              • 4. Re: CROSS JOIN with Tableau's join dialog
                Łukasz Majewski

                Hi Kettan,

                 

                I believe it is to some extent related question: why cannot a custom join be created with excel file? I mean operators other than '=' like '<=' or '<>'. It is allowed with 'legacy connection' - along with custom sql - but not with standard which to me seems to be kind of regress...

                • 5. Re: CROSS JOIN with Tableau's join dialog
                  kettan

                  I believe it is to some extent related question: why cannot a custom join be created with excel file? I mean operators other than '=' like '<=' or '<>'.

                  I agree it is related, even strongly related.

                   

                  Seeing how well a 1=1 cross join performs with unequal calculated sheet filters makes me wonder if any real issue hinders support of unequal operators in the join dialog or if its support only takes a relatively small effort !?

                  • 6. Re: CROSS JOIN with Tableau's join dialog
                    Michael Hesser

                    Okay... this is pretty gosh durn amazing for a number of ways-- first: thanks to kettan for again putting together such an incredible article. Those "moving pictures" are excellent.

                     

                    This was the "secret sauce" I needed to link otherwise unruly, disparate dates together and make them play nice.

                     

                    And if I may give a humble shout-out to the quick viewers: even if you don't think you might ever use this fancy-schmancy cross-joining thingamabob, take a real close look at what kettan snuck in steps 3-5: that responsive date filter is a very slick trick to add to your arsenal of vizeffects! (I'm sure there's articles dedicated just to that, but it didn't go unnoticed here)

                     

                    Well done!

                    --Michael

                    • 7. Re: CROSS JOIN with Tableau's join dialog
                      Brian Calvanese

                      Great post!

                       

                      Yes, I've used this a number of times... Particularly useful for creating 'filterable data scaffolds across multiple dimensions'.... Also helpful for generating the shape files for Sankey polygons... Currently using this for a financial model that uses most recent month's salaries by employee as a baseline from which to extrapolate a forecast, calc'ing Fx and burden rate impacts along the way... Have also used this to simulate a user defined (dare I say 'dynamic'?) parameter.

                       

                      The only drawback to using this technique is the multiplicative effect it will have on data size... but, definitely worth considering when data size doesn't become an issue!

                      1 of 1 people found this helpful
                      • 8. Re: CROSS JOIN with Tableau's join dialog
                        Hannah Jensen

                        I tried blending two unrelated data sources in this manner using a calculated field with value of 1 in each data source but Tableau didn't blend the data sources as it does in this cross join example.  I'm new to Tableau, so please forgive my lack of understanding of what is probably a key component of how blends vs joins function.  Can you please explain why this didn't work?

                         

                        I've attached the two workbooks.  Why am I able to see all item numbers in the cross joined workbook, but in the blended workbook I can only see an asterisk for my item numbers?  Why don't I get a table containing every machine name with every item number as I do when I use a cross join?

                         

                        Thanks in advance for your explanations.

                        • 9. Re: CROSS JOIN with Tableau's join dialog
                          Michael Hesser

                          Hi Hannah;

                          kettan is the expert here, but I think your issue is you're trying to blend on a calculation.

                          In your initial example, your CrossJoinKey was an integral part of your data; in your second example it was not.

                           

                          This link may help: Re: What does an asterisk "*" mean when blending data?

                          Check the Answer for detail in how to solve, courtesy Vladislav Grigorov.

                           

                          I'm confident someone with more Tableau-know-how will be able to explain (or explain away) this in more detail.

                           

                          --Michael

                          2 of 2 people found this helpful
                          • 10. Re: CROSS JOIN with Tableau's join dialog
                            kettan

                            A cross join generates rows, actually one table B of rows per row in table A!

                             

                            table A:  Machine Energy Use has 65 rows

                            table B:  Part Volumes has 34 rows

                            cross join:  returns 65 times 34 rows ( 2210 rows )

                             

                            I am not an expert in blending, but quite sure that it either returns one single (aggregated) result from blended data source or an asterix ( * ) as seen in workbook you shared.

                             

                            http://redheadedstepdata.io/when-to-blend/

                            • 11. Re: CROSS JOIN with Tableau's join dialog
                              Hannah Jensen

                              Thanks very much, kettan and Michael Hesser.  Your explanations and the links you both provided on asterisks in blended data and tips on when to use blending vs joining were very helpful!  Much appreciated.

                               

                              Hannah

                              • 12. Re: CROSS JOIN with Tableau's join dialog
                                Alexander Mou

                                Thanks to kettan for this great writeup!

                                 

                                I just finished this summary post on the same topic with new features in tableau 10.2

                                No more need to create the Cross Join Key in the tables, i.e., no need to reshape data.

                                Instead, one can use the "Join Calculation" feature to create join conditions.

                                Vizible Difference: 4 Approaches to Cross Join in Tableau

                                2 of 2 people found this helpful