Web Content Display Web Content Display

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_year integer Calendar year  
calendar_month integer 3 character code for month  
calendar_month_num integer

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.