Cube_Assessment Cube_Assessment

Introduction

The Assessment cube details the marks and grades students get on each of their module assignments.  In addition for Board Reporting purposes it holds module and stage marks.  These are accurate for individual students.  For higher level calculations Cube_Module and Cube_Stage should be used instead.

Shared Dimensions

Dimensions in the cube based upon Shared Dimensions are as below.  Details of these columns are in the Shared Dimensions section.

Shared Dimension Name Name in Cube Notes
Assessment  Assessment  Also uses assessment_statistics view, for additional statistics required for board reports.
Module Assessment Module Also uses module_statistics view, for additional statistics required for board reports.
Award Class

Assessment Confirmed Grade

Assessment Provisional Grade

Assessment Module Confirmed Grade

Assessment Module Provisional Grade

Assessment Award Confirmed Class

Assessment Award Provisional Class

 

Changes to field names to be unique & reflect assessment/ module/ award & grade or award.

Award Qualification

Assessment Award Confirmed Qualification

Assessment Award Provisional Qualification

Fields amended to reflect provisional/ confirmed awards.

 

Course  Assessment Course  
Member  Assessment Student  
Stage  Assessment Stage   
Personal  Assessment Personal 

Contains following fields:

Email Address Internal, First Name, Surname, Short Name 

 

Cube-Specific Dimensions 

Assessment Module Selection Status

See Dim_Module_Selection_Status in Warehouse Lookup tables for details.

Assessment Student with Open Assessment

Uses Board_Student_Module_Open_Reassessment view, indicates students with an open assessment in a module.

Assessment Mark Type 

See Dim_Assessment_Mark_Type in Warehouse Lookup Tables for details.

Assessment Other

This holds columns stored on the Fact Table used for analysis and reporting purposes. 

Column Name Data type Description Derivation
Assessment Latest Record char As field name Uses Board_Member_Latest_Assessment_Record view to assign.
Module Result Src char   smr_rslt
PGT Intermediate Action Required char Indicates PGT students to be flagged up in boards Uses Board_Member_Stage_Module_Mark_Under_50 to assign.

 

Assessment Attempt Current  integer  As field name 

sas_cura record on SAS for assessments,

sra_cura on SRA for reassessments.

Assessment Attempt Taken  integer  As field name

sas_coma record on SAS for assessments,

sra_coma on SRA for reassessments. 

Assessment_Due_Date_Src  date  As field name  sas_dued for assessments, sra_dued for reassessments. 
Assessment_Process_Status_Src  varchar  As field name sas_proc for assessments, sra proc for reassessments.
Confirmed_Progression_Code  varchar  As field name  spi_pit1 on SPI.
Module_Reassessment  varchar  Indicates whether the student was reassessed on the module which the assessment is linked to.  Uses DW.dbo.Board_Member_Module_Reassessment.  See Warehouse Views for details.
Provisional_Progression_Code  varchar As field name spi_pitc on SPI.
Reassessment_Exam_Type_Src  varchar  Just for reassessments. ast_code on SRA   
Reassessment_Percent_Src  integer  The percentage of the module mark attributed to the reassessment.  Note: Synoptic Reassessments are 100%.   Just for reassessments. sra_perc on SRA.
Module Credit or Result varchar As name  
Stage Action Required varchar Indicates if action is expected to be required at boards. Derived
Stage Assessment Mark 40 To 50 varchar   Yes if any assessment marks 40 to 50 in stage
Stage Complete Assessment Mark varchar Indicates if all assessments are complete  
Stage Contains Reassessment varchar   'YES' if there is an assessment in the stage.
Stage Incomplete Assessment Mark varchar Opposite of Yes if any assessment marks 40 to 50 in stage, kept for legacy reasons.  
Total Module Credit integer Total Module credits for the student, across all academic years.  

 

Measures

Note: The measures below are the visible ones - there are some other non-visible measures used for internal calculation purposes. 

The main source of the measures is view DW.dbo.Fact_Board_Assessment.

 
Cube Field Description/ Derivation 
Avg_Stage_Confirmed_Mark Average of stage_average_confirmed_mark
Avg_Stage_Progression_Mark Average of stage_average_progression_mark 
Avg_Stage_Provisional_Mark  Average of stage_average_provisional_mark 
Avg_Prev_Stage_Confirmed_Mark Average of prev_stage_average_confirmed_mark
Avg_Module_Confirmed_Mark  Average of confirmed_mark_std
Avg_Module_Provisional_Mark Average of provisional_mark_std 
Avg_Module_Initial_Mark Average of module_initial_mark_std
Avg_Assessment_Confirmed_Mark Average of assessment_confirmed_mark_std  
Avg_Assessment_Provisional_Mark Average of provisional_mark_std 
Max Assessment Attempt Current Maximum of assessment_attempt_current
Max Assessment Attempt Taken Maximum of assessment_attempt_taken
Count Assessments As name
Count Assessment Confirmed Marks  As name
Count Assessment Provisional Marks As name
Progression Confirmed Mark Count As name
Progression Provisional Mark Count As name
Stage Grade Credits ? should not be displayed ?
Prev Stage Average Confirmed Mark Count As name
Distinct Count Assessment Sk Number of different assessments.
Student Count Number of Students (uses member_sk, so actually based upon student/ course combination).
Student_Count_Anonymised Student Count if >= 5, else null
Avg_Stage_Credits As name