3 of 3 people found this helpful
It is impossible to do such a thing in Tableau, ditto any other tool.
Your beautiful Galaxy schema is a logical one,
but for a Tableau data sourse you would like
to build a physical schema, which translates nicely
into a valid SQL code. As of my understanding,
circular joins don't translate well (if any) into SQL.
If you're trying to build a two-fact-table-with-common-dims
as a single datasource, the common approach would be
to union your facts and join dimensions to common Keys.
Hope it could help.
My approach would be concatenate the two facts and link dimensions directly.
You can flag a Source field to identify the slices.
I am not an expert in Tableau's join dialog, but curious to understand how it works.
Would JOINs in SQL look like this?
INNER JOIN DIMENSIONB ON DIMENSIONB.KEYB = FACTB.KEYB
INNER JOIN DIMENSIONC ON DIMENSIONC.KEYC = FACTB.KEYC
INNER JOIN DIMENSIONX ON DIMENSIONX.KEYX = FACTB.KEYX
INNER JOIN FACTC ON FACTC.KEYB = DIMENSIONB.KEYB AND FACTC.KEYC = DIMENSIONC.KEYC
INNER JOIN DIMENSIONY ON DIMENSIONY.KEYY = FACTC.KEYY
If KEYB and KEYC is available in FACTB and FACTC, couldn't you just join them directly on both keys?
INNER JOIN FACTC ON FACTC.KEYB = FACTB.KEYB AND FACTC.KEYC = FACTB.KEYC
Ps. I am neither an expert in joining two fact tables (many-to-many relations) and wonder when these are useful. If this is a one-to-many relation, I do understand.
Thank you both for your answers and your support. What a pity the SQL interpreter is unable to deal with this kind of datamodel. I'm going to concatenate the fact tables to create a STAR-schema.