How do you model multiple column relations to the same dimension table?
I have a fact table with 3 different "Associate ID" columns for different purpose and I want each of them to join to my associate master dimension table so that their name, job code, years of service, etc... could be referenced for each of the 3 "Associate ID" columns. How do you handle this in ThoughtSpot database and/or worksheet design?
Greg Brown ,
This is what we call Multi Join Path. The system will let you create the additional joins using TQL orby using the front-end's Create Relationship capability.
Once the joins are created, you can add the name field or the job code field multiple times to the worksheet. Each time you add, you will be asked to choose which join you would like to use. Just remember to name the field accordingly. So for e.g. if one Associate ID represent the creator and the other represents the Modifier, upon adding the Name field twice, you might want to call the first one Creator Name and the second one Modifier Name.
For fields added in such a way to the worksheet, a chain link icon will show up next to it, that can be used for changing the join path at any time.
I mentioned associate values in my original post, but I tried a test with a new worksheet using existing tables where I have "Store Number" and "SWGO Store Number" in my fact table and I want them each to join to our Store Dimension table to get "Store Name". I created the relationships as mentioned and I can see those now on the table script.
ALTER TABLE "helzberg"."falcon_default_schema"."TS_INTERNETSALELN" ADD CONSTRAINT FOREIGN KEY ("STORE_NUMBER" ) REFERENCES "helzberg"."falcon_default_schema"."DMSTORE" ("STORE_NUMBER");
ALTER TABLE "helzberg"."falcon_default_schema"."TS_INTERNETSALELN" ADD CONSTRAINT FOREIGN KEY ("SGWO_STORE_NUMBER" ) REFERENCES "helzberg"."falcon_default_schema"."DMSTORE" ("STORE_NUMBER");
I then followed how you describe for adding to the worksheet and it works for adding the first "Store Name" field and I chose the first join path, but then it won't allow me to add it a second time choosing the second join path. Clicking "+ Add Columns" on the same field again is non-responsive. I also tried it the other way around where I add "Store Name" using the second join path and renamed the column "SWGO Store Name", but then it won't let me add "Store Name" again to choose the first join path.
Siva Singaram which join condition (of the three) would be chosen if search involves attributes and measures from both of these tables?
Fact Columns: projectId1, AssocId1, AssocId2, budget, dept_key Associate Dimension: AssocId, assoc_name, Job_code, Joined_on Department Dimension: Id, dept_name
Relationships are as follows:
- Assoc1 - AssocId (Developer)
- Assoc2 - AssocId (Manager)
- dept_key - Id (Department info)
And the question goes like: "budget" "dept_name" "assoc_name"
If you are asking a question of the base tables, it will ask you to choose which of the AssocId relationships you want to use when you add in the assoc_name.
If you are asking a question of a worksheet, when you created the worksheet it would ask you which join to use at this point. This allows someone who understands the model to pre-define which joins are used for which columns and ensure that end users don't have to worry about any of this.
I have the same concern as @Monique. There are multiple date columns within the fact table which correspond to various dates for a single fact. I would like each of these date fields to point to the Date table for reference. Currently using 5.1 and not able to have multiple fk's related to the same pk.
Please see attached sample including the TQL file (schema). I have two tables, a fact table with two date keys and a dimension table with a date key and a date. I created two foreign keys from the fact table to the dimension table. Then when I created the worksheet I was prompted to pick the date FK I wanted to use. I was able to use both dates in a search.
I'm running on 5.2, but should work the same on earlier versions.