Date-Range Join/Relationship Definition



I have a dimension table which explains when a piece of marketing reached my customers (in-home date aka ih_date :: date). We typically realize our sales cycle to be 45 days (ih_date45 :: date). These fliers have a call-to-action in the form of a toll-free number (tfn :: bigint, attribute). Additionally, we have a fact table for all of the responses that come in over one of our contact platforms. This table has timeseries data on when we received contact (call_start_time :: datetime) from a given toll free number (tfn :: bigint, attribute). These two tables are not strongly relateable at this moment in time; no unique keys can be found among either other table.


Naturally, my Users want to search "show me all the calls that came in for a given marketing piece".

There is an implicit relationship between the marketing fliers dimension table and the contact platform table on tfn, and a range of dates. We do not typically model the ih_date45 column, but I understand however that ThoughtSpot does not allow for expressions in the RELATIONSHIP construct so it is no problem to leave the range as part of the model. In production today, we have an MS SQL view which uses the syntax ...

FROM OfflineMktgVersion AS o
JOIN ContactCenterCalls AS i
     ON i.tfn = o.tfn
        AND i.datetime BETWEEN o.ih_date AND DATEADD(DAY, 45, o.ih_date)

... to join the two datasets.


How would I model this relationship in ThoughtSpot? My current attempt (below) works fine in the Search UI in terms of functionality - when typing "calls", the dimension table does not gray out - but the query "calls tfn" produces NO DATA.


Further debugging shows that if I take off the 2nd clause of the relationship (the date range), I get data but it is not a valid dataset. It naturally includes calls that come in on the tfn BEFORE the target_ih_date, which we understand is not possible and calls coming in on the tfn AFTER day 45, which is outside of the semantic.


CREATE TABLE "tbl_fct_ContactCenterCalls (
    call_start_time DATETIME,
    tfn BIGINT,
    call_date_id INTEGER,
    PRIMARY KEY (call_record_id),
    FOREIGN KEY (call_date_id) REFERENCES "tbl_dim_DateDim" (id),

CREATE TABLE "tbl_dim_OfflineMktgVersion" (
    tfn BIGINT,
    ih_date_avg DATE,
    target_ih_date_id INTEGER,
    ih_date_avg_id INTEGER,
    ih_date_avg45_id INTEGER,
    PRIMARY KEY(targetid),
    FOREIGN KEY(target_ih_date_id) REFERENCES "tbl_dim_DateDim" (id),
    FOREIGN KEY(ih_date_avg_id) REFERENCES "tbl_dim_DateDim" (id),

ALTER TABLE "tbl_dim_OfflineMktgVersion"
  ADD RELATIONSHIP "rel_MTM_calls_cycle" WITH "tbl_fct_ContactCenter"
   AS "tbl_dim_OfflineMktgVersion"."tfn" = "tbl_fct_ContactCenter"."tfn"
      AND ("tbl_dim_OfflineMktgVersion"."ih_date_avg_id" >= "tbl_fct_ContactCenter"."call_date_id"
           AND "tbl_dim_OfflineMktgVersion"."ih_date_avg45_id" <= "tbl_fct_ContactCenter"."call_date_id");


I hope I've demonstrated the desired output and situation here, please let me know if you require additional information to help in my data model.



Nick Cooper

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Wait .... I think I found my error, and it's definitely a `UserError`. :) The joinpath should simply be ...

    ALTER TABLE "tbl_dim_OfflineMktgVersion"
      ADD RELATIONSHIP "rel_MTM_calls_cycle" WITH "tbl_fct_ContactCenter"
       AS "tbl_dim_OfflineMktgVersion"."tfn" = "tbl_fct_ContactCenter"."tfn"
          AND ("tbl_fct_ContactCenter"."call_date_id" >= "tbl_dim_OfflineMktgVersion"."ih_date_avg_id"
               AND "tbl_fct_ContactCenter"."call_date_id" <= "tbl_dim_OfflineMktgVersion"."ih_date_avg45_id");

    woof, I think I need more coffee. With re-ordering the 2nd clause, and some very basic data validation, it looks like things are pulling correctly.


    Thanks for playing along! Hah.

    Like 1
  • Nicholas Cooper Glad you figured it out Nicholas! I blame it on Mondays! 😂☕

Like Follow
  • Status Answered
  • 6 mths agoLast active
  • 2Replies
  • 21Views
  • 2 Following