Data Warehousing and Business Intelligence

28 August 2015

3 Things in Date Dimension

Filed under: Analysis Services — Vincent Rainardi @ 6:16 pm

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.

2 Comments »

  1. I certainly agree with keeping the table narrow.

    Also I have been thinking whether it is necessary to have future dates in the dimension. I could let the end date be today’s date and have the script run in the package on a daily schedule, i.e. include it in the daily package. This way the date dimension in the cube will never show more than the necessary dates.

    I’m pretty sure that I don’t have fact tables looking up future dates. Would there otherwise be issues with this approach?

    Comment by Marcus Henningsen — 5 October 2015 @ 9:17 am | Reply

    • It is unusualy approach but I don’t see any issue* with it Marcus.
      A more common approach is the select distinct from the (one of the) fact table(s) to provide good members of date dimension in the AS cube or Qlikview book.
      *check dimension as well, i.e. Renewal_Date_Key in Dim_Policy is in the future.

      Comment by Vincent Rainardi — 5 October 2015 @ 6:31 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: