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?

       

      Capture.PNG