WAREHOUSE VIEWS WAREHOUSE VIEWS

Introduction

The Data Warehouse contains a wide range of views.  Details of process-specific views are contained within that area.  The views enable simpler, more consistent reporting.

Board Reporting Views

View Name Description Derivation
DW.dbo.Board_Award_Stage_And_
Module_Result
Holds details of award, stage and module information.  Uses Fact_Award, Fact_Stage and Fact_Module, as well as a variety of dimensions.  
DW.dbo.Board_Member_Module_
Component_ Reassessment_Mark 

Holds students' reassessment marks, where the reassessment was for a particular component.  Used in the 

USP_Load_Fact_Stage procedure to load part of the Fact_Stage table.

Uses

Fact_Module_Assessment_Result and

Board_Member_Module_Reassessment

DW.dbo.Board_Member_Module_
Reassessment 
Holds details of the modules for a student where a reassessment was required. 

Contains member_sk and module_sk, where on

Fact_Module_Assessment_Result

the assessment_attempt_current > 1. 

DW.dbo.Board_Member_Module_
Synoptic_Reassessment_Mark 
Holds students' reassessment marks, where the reassessment carried out was synoptic (module level).  Used to load part of the Fact_Stage table. Uses Fact_Module_Assessment_Result, where there is a synoptic reassessment mark type.
DW.dbo.Board_Module_And_Assessment_
Item_Result 
Holds assessment, module and stage results, together with associated dimension records.

Uses

Fact_Module_Assessment_Result, Fact_Module_Result and Fact_Stage, together with various dimensions.

DW.dbo.Fact_Board_Assessment  Used in Cube_Assessment, for module and progress boards. 

Based upon

Board_Module_And_Assessment_Item_Result fact table columns.  Only brings back records where the student has completed the assessment.

DW.dbo.Fact_Board_Module  Used in Cube_Module, for final boards.  Based upon Board_Award_Stage_And_Module_Result fact table columns.

 

Other Views

View Name Description Derivation
DW.dbo.Accommodation_* Various views used for Accommodation reporting.  
DW.dbo.Dim_Bursary_Income_Band Indicates income band for student's family, where the student receives a bursary Static Data.
DW.dbo.Careers_* Various views used for Careers reporting.  
DW.dbo.Congregation_Student Holds details required for the congregation planning team. 

Note: DW.dbo.Excel_Congregation_Student is created based upon this view,

and used for viewing using Excel.  This is because of issues with cross-database permissions.  

Uses SCY, CER, SAW,

DPT, CMY & PWY data, plus Congregation_Student_Prize.

DW_Interfaces.dbo.Congregation_Student_
Prize
Internal view, used by Congregation_Student view to display prizes a student has been awarded.  Based upon SPZ and PRZ data.
DW.dbo.Dim_Country  Holds country information based upon Nationality and Domicile records, together with extra contextual information.  Uses DW_Interfaces.dbo.DW_Country as the basis.  See Warehouse Lookup Tables for details. 
DW.dbo.DQ_* Various views set up for holding records with data quality issues.  These feed into cube_data_quality Derivation specific for each view.
DW.dbo.Fact_Course_Entry_Qualification    
DW.dbo.Fact_Room_Usage     
DW.dbo.Fact_Student_Academic_Year  Holds a record for each student for each academic year they are on a course.  Used in PGR reporting to enable links between supervisors, sponsorship and annual changes.  Based upon the Fact_Member_Over_Time table, which in turn is based upon SCE.  Uses the latest SCE record, based upon start date, for the academic year.  
DW.dbo.Fact_Student_Attendance     
DW.dbo.Graduate_Returner    
DW.dbo.HESA_* Various views used for HESA reporting.  
DW.dbo.JACS_Course_Mapping    
DW.dbo.Library_* Various views used for Library use.  
DW.dbo.Member_A_Levels    
DW.dbo.Member_Qualification_Rank     
DW.dbo.Member_UCAS_Points     
DW.dbo.MSc_Good_Honours_Extract     
DW.dbo.Student_Starter_Returner  Indicates whether the academic year is the first one (Starter) or a subsequent one (Returner).   
DW.dbo.Tariff_Countable_Application     
DW.dbo.UCAS_Choice_Comparison    
DW_Interfaces.dbo.Course_JACS_Proportion    
DW_Interfaces.dbo.JACS_Course_Proportion    
DW_Interfaces.dbo.ONS_SOC_Group    
DW_Interfaces.dbo.UCAS_Choice