In my dataset, customers apply for a course, have an assessment date, and a course start date if they are successful and then end dates each step. That's 6 dates in total - application receipt and process date, assessment start and completion date, and then course start and end date. During the course, there are regular reviews that are each associated with a single course record. Thus the course to review relationship is a one to many.
I am relatively confident in creating the canonical date for each of the start dates but two stumbling blocks I'm hitting are:
The start dates and end dates exist in the same table. When I add both into the canonical calendar, it creates a synthetic key as they share two columns. If my understanding of the canonical calendar is correct, I need to link each date that I want on the calendar. It is not sufficient to have just the start date connected to the calendar because it means that filtering by the canonical date will only include assessments, for example, that start on that date, not those that also end on that date. If so, I would appreciate guidance on preventing synthetic keys without reconfiguring the source data table.
One piece of guidance in the advice of developing a canonical calendar is to select the most granular record to develop your calendar around so you have a single date per record. The example given in my first link creates one table with all the dates. However, I would like to avoid doing this with my dataset as joining the reviews onto a application - assessment - course table would make it grow exponentially and duplicating a lot of data from a storage perspective with little gain in analysis terms. I can't see a reason why the canonical calendar won't work by just joining distinct tables to the directly to the date bridge rather than joining everything into one table but I am a novice, is there something I'm missing or can I just join my review dates directly to my date bridge?
Hopefully the above make sense but if any further information is required, please do shout! Appreciate any support anyone can provide.
Hi, it would be much better if you can share a snapshot of your data model and script you are trying to implement.
Based on your description:
You should create a separate table for each date type, even if dates exist in same source table. For example, for Start Date and End Date, you would create two separate resident loads and then concatenate them into date bridge table. This helps avoid synthetic keys and allows each date type to link properly to the canonical calendar.
I’m not completely sure what you mean in the second point, but even when joining tables, you should join based on the most granular record. Otherwise, you risk duplicating records and inflating your data model.
Regards, Vighnesh Gawad Connect with me on LinkedIn | GitHub
Thank you for this! Really helpful regarding my second point. I think, if my understanding is correct, the guidance basically says the ID in the calendar needs to act as the link to all the dates. I think I realise what I am trying to do is have a couple of IDs to link to the date calendar. However this runs into the same problem as my first point: synthetic keys.
Do you know if there is similar resource for when you have multiple dates in the same table? Or how to avoid the synth key issue? I recognise I could be trying an incorrect approach so I welcome any correction there too!
I could imagine to skip the canonical-approach and further/other bridge-tables and just using a single date within an appropriate transformed fact-table. This follows the idea that there is always only a single date - one for each step on the journey from the start to the end - the dates are differentiating only to (n) states and/or key-fields.
Therefore I would concatenate all dates/events into separate records within the fact-table, like:
ID
Category
Date
DateType
with n load-statements against the source-tables to extract the relevant information. Afterwards the records would be sorted, numbered, counted ... maybe also extended to from-to against the relevant predator, their offsets calculated, clustered, scored ... maybe extended to further intervalmatch-stuff and so on ...
Such logic creates a rather small and long table - whereby the usually created canonical/bridge tables are seldom shorter and are needing the same efforts - without going this detour else doing it at the beginning in the right place respectively getting a clean star-scheme without all the trouble to link multiple facts with any bridges.
