August 15, 2017
This article was originally posted March 28, 2016. Version 9.0 of Tableau was used.
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!
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.
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:
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
Step 3: Filter
Step 4: Build view
Step 5: Test performance with Quick Filter
Step 6: Test performance with calculation [Days in Period]
Step 7: Test performance with more demanding calculation [Amount in Period]
Attached Tableau Version: 9.0