1 2 Previous Next 20 Replies Latest reply on Jul 18, 2017 7:06 PM by Bill Lyons

    CROSS JOIN with Tableau's join dialog

    kettan

      August 15, 2017

       

      This article was originally posted March 28, 2016. Version 9.0 of Tableau was used.

       

      Dynamic Cross Join - Join (Insurance).pngFrom release 10.2 it is possible to use dummy values 1 = 1 in Tableau's join dialog and thus not necessary to create dummy columns in data itself.

       

      The screenshot on the right side is copied from  Split Periods Into Rows Dynamically  which basically is about the same except it uses a more flexible lookup table strategy.

       

       

       

       

       

      March 28, 2016

       

      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 2 Previous Next