Common Date Field Across Multiple Data Sources

I have three fact tables with client transactions.  I have defined RI between the three with a master dimensional client table.  I am trying to perform a date search but the issue is each data set has its own separate date field.  I've created a master date formula(each date field name is unique):

 

if(sourcesystem = 'A') then start_of_month(dateinvoice )else if (sourcesystem = 'B') then start_of_month(billdate) else if(sourcesystem = 'C') then start_of_month(invoicedate )else null 

 

This gets me close to my desired outcome however I get three lines for each month/ year:

 

Sept 2019

Sept 2019

Sept 2019

Oct 2019

Oct 2019

Oct 2019....

 

I've tried a combination of nested 'ifnull' formulas but this does not seem to work either.  I would like to use a formula if possible but feel that a dimension/aggregate table for dates may need to be added to my data model.  Please let me know if I can utilize a formula to get my desired result.

 

Thanks.

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • I would recommend a date dimension in the data model 

    do a count on one table, then bring in a column from the other table... does the number get bigger? if so you have a chasm trap overcounting problem

    Reply Like 1
  • Agreed, the way to model this is to create a common date dimension table, join that to the date columns in the facts and expose the date column from the dimension table for search.  By doing this you are creating a chasm trap across the facts, which ThoughtSpot will detect and ensure correct results are returned without any over/double counting that would normally be caused if you directly join the fact together.

    Reply Like 1
  • Thanks Marc Price and Paul Froggatt

    I did a count and it did not change when adding a field from one of the other tables.  I will create the date dimension table per your suggestions.

    Thanks again.

    Reply Like 1
Like Follow
  • Status Answered
  • 3 wk agoLast active
  • 3Replies
  • 13Views
  • 3 Following