Handling multiple dates within a table and from multiple tables
I was wondering which approach out of Canonical date, Date Island and Link Table would result in faster reload of data and charts for our data. I have attached an image of how our data looks like. I appreciate any inputs on this.
Or could you please point me to an article that compares the pros and cons of these approaches?
Example. I had many dates in 4 joined (keep) tables. Three I used canonical dates for and table 4 a date Island approach.
I removed the date island approach. It actually worked well but
- It can't be used with a dimension.
- It's different. And I forgot after a time what I had done when asked to adjust the App (this was starting out)
So I tried a Canonical Date for this table 4 (I was using it for the other tables) but it got confusing as there wasn't a clear finer grain table (table 3 vs table 4). So table 2 was missing calls from table 1. Likewise table 3 from table 2. This was overcome by making table 3 the finer grain table and adding the missing call numbers to table 2 and 3. But table 4 was missing calls from table 3 and table 3 likewise from table 4.
So I simple concatenated table 4 to table 3
My view is either use canonical dates alone or canonical dates after concatenating tables.