Greetings from a 30°C office, in the north of Belgium, Flanders. It’s late in the evening, a good time for some lighter philosophising before packing the suitcase.
The DATE dimension is a very simple flat table (although some designers and DBA’s prefer third normal form). It contains surrogate keys, the date, the name of the weekday, month, day number, week number, month number, quarter, trimester and semester number, IsLastDayOfMonthFlag and what have you. There will also be a IsAHolidayFlag somewhere and in case you only work in a few countries, you simply add a column per country.
In case you operate on a global scale you may want to snowflake a bit to support truly global analytics and compare apples with apples.
Example of a snowflaked Holiday model
Holidays can explain a lot
Imagine you are comparing last year’s June sales with June of this year. Last year, June had no holidays and 21 working days. This year it has 20 working days and one holiday, i.e. 19 working days. In case your revenue is directly linked to the number of working days, this explains for a revenue drop of no less than ten percent in June this year! And in case you sell holiday related products like ice cream or beer it may well run into the other direction explaining a sales increase of over twenty percent or more…
So, here’s my advice: enjoy your holiday but don’t forget to integrate it in your analysis.