Almost everything a data warehouse is asked to do involves dates: days, weeks, months, years, periods of interest, special days. These date details “are attached to virtually every fact table to allow [easy] navigation.” Data Vault has a different structure from the Star schema data warehouse. However, in the case of dates, the idea stays the same: “You would never want to compute Easter in SQL, but rather want to look it up in the calendar date dimension.” (Source for both quotes: Kimball Group)
There are no dimensions in Data Vault. It makes sense to implement Calendar as a reference ensemble. It makes even more sense to make a stand alone reference ensemble so that other Data Vault ensembles don’t need to be linked with Calendar. Each entity could have multiple dates associated with it, and it is even worse in terms of a number of dates when it comes to transactions. Data Vault models become too congested and unreadable if you try to link each date with the Calendar ensemble. Using a stand alone calendar with the date as a business key allows you to join any date with the Calendar in Data Vault queries.
We have developed a calendar ensemble for ODE Data Vaults. ODE is an open source Data Warehouse automation tool which uses Data Vault methodology. You can read more about ODE here. If you use ODE, you can download our Calendar ensemble code from GitHub, install it by following the simple instructions and start using it. We know that you need one anyway, so you can save time by not creating one from scratch. Data for the Calendar is generated with stored procedures, you can adjust them if you don’t like our defaults. Our calendar includes information about day, month, quarter and week day in multiple formats, also fiscal months and quarters.
ODE Calendar also includes New Zealand public holidays and regional anniversary days. Even if you don’t use ODE or Data Vault at all, you can still use our SQL functions for holidays calculation to integrate with your data warehouse solution. It is easy to find algorithms to calculate Easter or the British Queen’s nominated birthday on the internet. But if you are from New Zealand, I believe you will appreciate regional anniversary days algorithms which we confirmed with our fellow developers at Ministry of Business, Innovation and Employment.
The installation code also includes a piece for creating a view. This view puts all the calendar information together. It could be useful because the main calendar and holiday ensembles have different granularity. In rare cases, there’s more than one holiday on the same date, meaning the holiday ensemble will have two records. View puts multiple holidays together in one field to match the date grain, with holiday names separated by a comma.