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.
You should consider to have a calendar dimension table (aka: date dimension) due to the following reasons:
- 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.
- Standardization – Using the calendar table will ensure that all reports contain similar date formatting, this brings us to the next point
- 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”).
- Calculations & Sorting – A calendar table will hold various attributes which are handy for calculations and sorting of data in your SQL or scripts.
- 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.
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).
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