In this post I will describe why you should highly consider to have a calendar dimension table in your reporting layer. I provide my own suggestion on creating the table in Oracle, and lastly I explain why you should pay special attention to ISO week.

Why?

You should consider to have a calendar dimension table (aka: date dimension) due to the following reasons:

1. Ease of use – A calendar dimension table will allow users who are not SQL savvy to easily retrieve all required date attributes without using any SQL functions or formatting.
2. Standardization –  Using the calendar table will ensure that all reports contain similar date formatting, this brings us to the next point
3.  ISO Week – ISO week is the week number in the year according to the ISO standard.
   I suggest to use it in your reporting. Please note that a year can have 52 or 53 weeks. When displaying ISO week it is important to display next to it the ISO Year, otherwise if we would display “Normal Year & ISO Week” for the date ‘2016-01-01’, it would display: ‘2016-53’ , instead of the expected value: ‘2015-53’ (based on:”ISO Year & ISO Week”).
4. Calculations & Sorting – A calendar table will hold various attributes which are handy for calculations and sorting of data in your SQL or scripts.
5. Create complex logic– The table allows to easily create complex logic. For example joining a date to future dates, in order to allow creating a forecast analysis.

How?

Below I illustrate my suggestion to creating a calendar dimension table. This is intended for execution on an Oracle DB. Please note I am populating a calendar for the range: ‘1970-01-01’ until ‘2099-12-31’.

I used a start date of ‘1970-01-01’ due to it representing the epoch time (Unix) and the date being far back from any dates used in my system, therefore it is useful as a default value for missing dates. However you should choose your range according to your needs.

I used an end date of ‘2099-12-31’ due to it being far in the future. This date is also useful as a default for open Datawarehouse records (ex: UntilDate) or for any records without an end date (ex: an open business contract might have an empty ContractEndDate value which can be replaced with ‘2099-12-31’).

Once the table is created I suggest to mainly store in your reporting tables the Julian dates. This will allow you to easily join your tables to Dim_Date and retrieve all date attributes.(join: Fact_Table.DATE_J = Dim_Date.DATE_J).

Creation

SQL code:

CREATE TABLE DIM_DATE
as
(
 select
  TO_NUMBER(TO_CHAR(DATE_D,'j')) as DATE_J,
  DATE_D,
  TO_CHAR(DATE_D,'YYYY-MM-DD') as DATE_V,
  TO_NUMBER(TO_CHAR(DATE_D,'YYYY')) as YEAR_NUM,
  TO_NUMBER(TO_CHAR(DATE_D,'Q')) as QUARTER_NUM,
  TO_NUMBER(TO_CHAR(DATE_D,'MM')) as MONTH_NUM,
  TRIM(TO_CHAR(DATE_D,'Month','nls_date_language=english')) as MONTH_DESC,
  TO_NUMBER(TO_CHAR(DATE_D,'IW')) as ISO_WEEK_NUM,
  TO_NUMBER(TO_CHAR(DATE_D,'IYYY')) as ISO_YEAR_NUM,
  TO_NUMBER(TO_CHAR(DATE_D,'DD')) as DAY_OF_MONTH_NUM,
  TO_NUMBER(TO_CHAR(DATE_D,'D')) as DAY_OF_WEEK_NUM,
  TRIM(TO_CHAR(DATE_D,'Day','nls_date_language=english')) as DAY_OF_WEEK_DESC,
  (CASE WHEN TRIM(TO_CHAR(DATE_D,'Day','nls_date_language=english')) IN ('Saturday','Sunday') THEN 'Weekend' ELSE 'Weekday' END) as DAY_TYPE_DESC
 from
 (
 select
  to_date('1969-12-31','YYYY-MM-DD')+ROWNUM as DATE_D
 from
  dual
  connect by level <= to_date('2099-12-31','YYYY-MM-DD')-to_date('1969-12-31','YYYY-MM-DD') --Range of calendar
 )
);

Additional optional columns:
TO_CHAR(DATE_D,'YYYY-Q') as YEAR_AND_QUARTER,
TO_CHAR(DATE_D,'YYYY-MM') as YEAR_AND_MONTH,
TO_CHAR(DATE_D,'IYYY-IW') as ISO_YEAR_AND_WEEK


Result