Hey does anyone know if there’s a way to perform a sql cross join in a data cube? I have 2 sources already in DBI that I want to cross join but I can’t seem to find a way.
I can back out and make a new cube that cross joins the data before it hits DBI, but I’d prefer to do it internally if possible.
While the built-in join doesn’t support this, you can actually do this using Rank. After each select, you’ll want to add a Rank transform and just connect it to the select – no need to configure the Rank transform.
Then you’ll want to join the two ranks on the Rank field.
This should effectively give you a cross join, as seen here:
Thanks, creative solution! Would this work if one table is longer than the other, though? One of my tables is 20 rows, the other is… 20k-ish. Won’t they only cross join for the first 20 rows?
Yes it would still work, because the the Rank transform if not configured would just return 1 for all rows. Basically the same as using the calculated element transform -> adding a new field that returns 1.
Join result:
8*25 = 200
Wow great topic, thanks for this one!
