Assessment (Dim_Module_Assessment) Assessment (Dim_Module_Assessment)

This holds details of the assessments which the students take.  The assessment records are taken from the SITS Module Assessment Body form (MAB).  In addition records from Student Reassessment (SRA) are included, where the record is a synoptic reassessments.

Column Name Data Type Description Derivation
module_assessment_sk integer This uniquely identifies the assessment record.  It has no business meaning, and shouldn't be used in reporting. The value increments each time a record is entered into the table.
source_system varchar The source system the records come from. 'SITS'
module_sk  integer  The unique record for the module, which the assessment forms part of.  See Shared Dimension Module 
 module_assessment_pattern varchar   As field name. 

mab.map_code for assessment,

sra_mapc for reassessments.

module_assessment_seq_num  integer  The sequence number for the module.  Note: Formative assessments have a sequence number beginning 'FM'.  This has been replaced with 99, to enable it to be unique.  For example 'FM1' is translated to 991.

mab_seq for assessments,

sra_seq for reassessments. 

module_assessment_seq_num_src varchar Based upon mab_seq as held on SITS, required for Board Reports mab_seq
module_assessment_name  varchar  As field name 

mab_name for assessments,

'Synoptic Reassessment' for reassessments. 

percent_module_mark decimal  As field name. 

if mab_tsha = 0 then 100,

else (mab_perc * 100)/mab_tsha 

assessment_type_src  varchar  Source code value for assessment type

mab_code for assessment,

null for reassessment.

assessment_type varchar  Assessment type description  See  DW_Assessment_Type in Warehouse Lookup tables
assessment_mark_scheme_src varchar  Source code value for mark scheme 

mks_code on MAB for assessment,

mks_code on SRA for reassessment 

assessment_submission_week_src  integer Academic week assessment is due 

mab_dweek for assessment,

NULL for reassessment. 

assessment_submission_day_src  integer  Academic day of week assessment is due. 

mab_dday for assessment,

NULL for reassessment 

reassessment_flag  varchar Indicates whether the assessment item is for a reassessment. 

WHEN mab_rasi IS NOT NULL THEN 'YES' ELSE 'NO' for assessment.

'YES' for reassessment.  

module_assessment_group  varchar As field name

mab_agrp for assessment,

NULL for reassessment.

electronic_marking varchar Indicates if assessment is marked electronically mab_udf6
colour_printing varchar Indicates if assessment should be colour-printed mab_udf5
return_date date Date item should be returned mab_udf4 (Note: Check for valid date format in record)
method_of_return varchar As name mab_udf3
electronic_submission varchar As name mab_elog
submission_date date As name mab_udfd
assessment_pass_fail   Indicates Pass/ Fail items If assessment_mark_scheme_src IN ('ADPF', 'AN00DP', 'AN00FM', 'AN00PF', 'APF') then 'YES'
submission_medium varchar Indicates which medium should be used for submission

If electronic_marking = 'B' then 'BLACKBOARD'

Else if electronic_submission = 'N' OR electronic_submission IS NULL then 'PAPER'

Else 'EVISION'

assessment_marked_date date Date assessment marked Based upon date Blackboard marks for assessment set to be 'visible'.