The columns on the Date-based dimensions are as below. In each Data Mart just the columns required are available for retrieval.
On the Data Warehouse this is referred to as Dim_Date. On the Data Marts it is referred to based upon the date column in the Fact table.
For example the application date column within the Applicants Data Mart would be visible as the 'Application Date' dimension, and contain some of the columns below.
|Column Name||Data Type||Description||Derivation|
|date_pk||integer||This uniquely identifies the date record.||Dates are held in integer form as YYYYMMDD. 'NOT KNOWN' and 'INVALID' dates have negative values.|
|full_date||date||This holds the 'normal' date, and enable matching to source system supplied dates|
|academic_year||char||The academic year linked to the date, as stored on 'SITS'. Eg 1st Dec 2012 would be '2012/3'. This changes on 1st August.|
|academic_year_start_date||date||1st August for the academic year. Enables date reporting for last X years in reports.|
|academic_year_num||integer||First part of academic year,||Eg. 2016 for "016/7|
|academic_semester_flag||NOT USED YET|
|academic_semester||NOT USED YET|
|admissions_year||integer||Year linked to date, based upon Admissions. Eg 1st Dec 2012 would be '2013'. The year changes on 1st October.|
|admissions_week||integer||Number from 1-53 indicating weeks after 1st October for that year. The week is based upon increments of 7 days from 1st October, and might start on any day of the week. Eg 3rd Oct is always 1, and 8th Oct is always 2.|
|calendar_month||integer||3 character code for month|
number of month from 1-12.
|calendar_week||integer||Calendar week (1-52/ 53).|
|day_of_week||char||As name||Monday, Tuesday ..|
|day_of_week_num||integer||Monday is 1, through to Sunday 7.|
|day_of_month_num||integer||Month date from 1-31.|
|date_desc||char||Character representation of date in form 'DD MON YYYY'. Also holds 'NOT KNOWN' and 'INVALID' to allow these to be easily distinguished.|