Creating a Data Model with Canonical Date

[lucasdavis500]

I'm trying to create a new data model for my application that is robust. Currently I have loaded my master table twice, creating all my date time functions off of specific dates, but I cannot use a chart to sums, counts, against eachother across the same time frame.

I've been reading this post:

Canonical Date

about canonical dates, and believe this is a great method to accomplish this....but I'm a little fuzzy on how I do it.

Currently I have one concatenated table, that has three relevant dates, but no table with "a grain fine enough" like Henric suggests in the above reading..

Example Data:

SomeKey         SomeOtherKey           CreatedDate           DueDate          DateResolved

123                            -                           01/01/2016          01/31/2016          01/21/2016

456                            -                           01/05/2016          01/04/2016                  -    

-                                987                       01/02/2016          02/01/2016          01/14/2016

-                                654                       01/10/2016          02/09/2016          01/29/2016

This is a summation of my data. There will always be a Created and Due Date, but not always a DateResolved. Also, I have two keys because they are two different tables that cannot be joined.

How can I create a canonical date off of this? Would it be like the example data below?

Key       Date                DateType       KeyType

123       01/01/2016       Created         FirstKey

123       01/31/2016        Due              FirstKey

123       01/21/2016        Resolved     FirstKey

...

987       01/02/2016       Created        SecondKey

987       02/01/2016       Due              SecondKey

987       01/14/2016       Resolved     SecondKey

....

And this table would work as my "Data Bridge" to my "Master Table" where the master table has each "Master Calendar" for each Date Type branched off. The "Data Bridge" would then be used to make the "Canonical Date" Master Calendar

I'm not the best data modeler so any feedback would be appreciated...

[swuehl · Accepted solution]

Create a primary key in your example data table, either by using the two key fields, or by just creating a Row number uisng

LOAD

     RowNo() as RowID,

     ...

then use this key in your canonical date table (yes, it would look like this table, one record per date).

[lucasdavis500]

Then use the Pkey (i.e. RowNo()) as the link between the bridge and my master table?

[swuehl]

The master table is your calendar? Then the key field between master table and bridge would be Date field.

[lucasdavis500]

sorry, no, but the new Pkey would be the link from the "bridge" table to my "master table" and then the generic "date" field would be the link between my "bridge table" and the "Canonical Date" Calendar table..

[swuehl]

So, are there any open issues left?

[Anonymous]

Hi Stefan Wühl,

Could you place a diagram (image model) of the finished model?

(With masterCalendar and canonical date)

I have the same question as Duke and try to use your solution to resolve it.

Thank you!

[swuehl]

There are more informations and models in the blog post by HIC that is referenced in the original post.

or maybe have a look at

Linking to two or more dates