-
I would throw extreme caution when creation a relationship between two fact tables(more so when they are sharded).
I would first question if there a possibility to create a common dimension table between the two facts. If yes, I would create common dimension table/s between the facts and create a FK-PK constraint from both facts to the common dimension. This would be a better option than creating a relationship between two fact tables..
If you are using the relationship between two fact tables, it will always be a cross join between the two tables, which can lead to terrible performance, incorrect results due to double counting, amongst others..
However, if you know all of these and still want to go ahead with it, here's the link and an example below..
An example from our docs page: https://docs.thoughtspot.com/5.3/reference/sql-cli-commands.html
ALTER TABLE "wholesale_buys" ADD RELATIONSHIP "REL_fruit" WITH "retail_sales" AS "wholesale_buys"."fruit" = "retail_sales"."fruit" AND ("wholesale_buys"."date_order" < "retail_sales"."date_sold" AND "retail_sales"."date_sold" < "wholesale_buys"."expire_date");
-
Thanks Rajiv and Damian,
Reason i asked this question, while defining the relationship between 2 sharded tables in TQL, it was throwing error that atleast 1 table must be non sharded.
Hence in case of extreme requirement situation, how to overcome this error.
I am using Thoughtspot 5.0.3 version.
-
sanyam if you join on the columns your sharded on it should work
-
Marc Price. Point noted. Thank you I will check it out.
