7 Replies Latest reply: Dec 13, 2016 9:01 AM by Adam Davies RSS

    Multiple Facts with Multiple Dates- Best practice?

    Adam Davies

      Dear All,


      I've been working with a large fact (22m) with a number (7) of dates.

      The canonical date structure has been working brilliantly for me, however I now need to add a second fact.


      This fact needs to be linked to the original fact, and also needs to be linked to the master calendar in its own right.


      A picture speaks a thousand words so below is my current DM. Missing from the picture is the fact that %job_id = JobEntryID so the two FACTS can be linked.


      This works OK for showing a summary level for a date i.e. I can pick a date (in this case the last month) and get my job info and my timesheet info.


      However, what I can't do is select say a regional director and see the timesheet values.


      Thinking about it a little more, I also need to make sure that I don't limit one way or the other, so for example a job might have its 7 dates within April but some work is done in May. If I was looking at my May report, I wouldn't expect the job to show up in the counters but I would expect my timsheet detail to


      Is there any way to work this new fact into the existing canonical date structure?


      I am guessing I can't join the tables as %job_id is the unique ID in the jobs_fact, but this is a one to many with the timesheet fact, but also sometimes a one to none.


      I know I could scrap this method and build multiple calendars and link to a floating master but that means re-working all my expressions.


      Any suggestions on the best way to approach this?