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?

10replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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.

    Reply Like
  • 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.   

    Reply Like
  • If you are in ver 4.5 of Thoughtspot, there is a bug that doesn't allow this. We will be having a fix in our next version.

    Reply Like 1
  • Greg Brown - I believe the fix for that issue will be in 4.5.1.1 - will keep you posted on the exact release date. Should be very soon.

    Reply Like
  • 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:

    1. Assoc1 - AssocId (Developer)
    2. Assoc2 - AssocId (Manager)
    3. dept_key - Id (Department info)
    And the question goes like: "budget" "dept_name" "assoc_name"
    Reply Like
  • Umang,

    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.

     

    Stuart

    Reply Like
  • Hi all

    I am experiencing the same issue as Greg Brown  and the client is running on TS 5.0. Once I have added the first join path, it won't allow me to add the second. The only difference in my example is, I am trying to join on the date dimension.

    Is there a fix for this?

    Thanks

    Reply Like 1
  • 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.  

    Reply Like
  • 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.  

    Reply Like
  • Thank you for sharing this.  I had mostly the same syntax, but in my copy/paste process or adding Alter table statements to my TQL script I never changed the constraint synonyms (e.g. Fk_sales_date) and therefore it wouldn't load multiple pk/fk relationships. 
    Thanks!

    Reply Like
Like Follow
  • Status Answered
  • 4 mths agoLast active
  • 10Replies
  • 442Views
  • 8 Following