In this article I would like to write about 3 things in Date Dimension, i.e. a) minimize the number of columns, b) minimize the number of rows, c) last business day / month end indicator.
Minimize the Number of Columns
This month I saw a date dimension with only about 15 columns. In the past I have seen many date dimensions, and they all have a lot of columns. Probably about 40-50 columns. Now that I’ve seen the slim version, I’ve been asking myself whether those 40-50 columns are really required. Fiscal week, fiscal period, fiscal quarter, fiscal year: do we use them? System date (char 10), SQL date (datetime), Julian date (int): do we use them? Day (1-7), day of the week (long and short), month name (long and short), do we use them? System columns such as date created, date updated, do we use them? Week number, week start date, week end date, day of the month, day of the quarter, day of the year: do we use them? Week day flag, week end flag, last day of the month flag, last day of the quarter flag, last day of the period flag, last working day of the month, bank holiday flag (which country?): do we use them?
Most of the those columns are created just because we can, not because we use them. If we are being minimalist, I think we could probably get away with just 4 columns: date SK, date (date type = date, not datetime), month (int, YYYYMM), year (int, YYYY).
Minimize the Number of Rows
Also this week I saw for the first time a date dimension which is up to year 2400. I think it is not right, and it is unnecessary. I think there are fixed income securities with expiry date of 2050, but expiry date is not being keyed and linked to date dimension. It is usually stored as an attribute of security/instrument dimension, in date format e.g. DD/MM/YYYY (or MM first in the US).
So what we need to store as rows in Date Dimension is the oldest transaction date (2005? 2010?) and further 5 years from now (2020). Span of 10-15 years should be enough.
Last Business Date in the Month (Month End Date Flag)
If you work for a local company (local being defined as 1 country), then the last business date in the month aka Month End Date flag is very useful. It’s not the last day of the month (e.g. 28/2, 31/3, 30/4, etc) but the last business day in the month. For example, this month is August 2015. In the UK, Monday 31st August is a bank holiday. So if you work for a UK company (local), then the month end date is 28th August (today). I found that this flag is useful (Month End Date flag). It enables us to retrieve month end data from the fact table.