Postgraduate Research Reporting Postgraduate Research Reporting

Introduction

Postgraduate research reporting utilises a variety of warehouse objects:

Cubes

Cube_Student - This is generic to all students, and has additional columns on Student (based on the Member dimension) which are the result of PGR requirements:

  1. Absence Days Total
  2. Research Council Student
  3. Period of Study End Date
  4. Thesis Submission Date
  5. Transfer Panel Date
  6. Viva Date
  7. Years To Complete
  8. Years To Complete Excl Absence.

Cube_Application - This holds details of all applications.

Cube_Award - This holds details of the awards achieved by PGR students.

Cube_Module - This holds details of modules taken by PGR students.

Views 

The PGR-specific views in the warehouse dataset DW are as follows.  These are made available via Excel and other client tools as Excel_<<Viewname>>:

 Research_Sponsorship

This view is based mainly on records on the Student Sponsorship (SSP) and Sponsor  SPO (Sponsor/ Employer) forms.  In addition it uses a view Fact_Student_Academic_Year, as detailed in the Warehouse Views section which has just one record per student per academic year matching the Sponsorship granularity.

 

Column Name

Data Type

Description

Derivation

member_sk 

integer 

Unique key for Member 

ssp_stuc used to derive. 

sponsor_academic_year 

varchar 

The academic year the money was for. 

ssp_ayrc 

sponsor_code_src 

varchar 

Internal code for the sponsor 

spo_code 

sponsor_amount 

decimal

Amount provided by sponsor 

ssp_amnt 

sponsor_percent 

decimal 

Percent of financial support provided by sponsor.

Note: Either sponsor_amount or sponsor_percent is

populated, not both. 

ssp_perc 

sponsor_sequence_num

integer 

Sequence number of sponsor 

ssp_seq2 

sponsor_ref_num 

varchar 

Reference number of sponsor 

ssp_sref 

sponsor_name 

varchar 

Sponsor name 

spo_name 

sponsor_debtor_code 

varchar 

Sponsor debtor code 

spo_dbtc 

sponsor_org_type_code 

varchar 

Sponsor organisation type code 

spo_otyc 

record_id 

integer 

Internal unique identifier to link to the Fact_Member_Over_Time record.  No business meaning 

Automatically incremented 

course_sk 

integer 

Surrogate key for course, to enable linking to Course dimension 

Fact_Student_Academic_Year.course_sk

census_reportable 

varchar 

Enables reporting based upon records which should be in the census. 

Fact_Student_Academic_Year.census_reportable 

student_status_sk 

integer 

Enables link to Dim_Member_Status where required. 

Fact_Student_Academic_Year.student_status_sk 

student_stage_sk 

integer 

Enables link to Dim_Stage where required 

Fact_Student_Academic_Year.student_stage_sk 

 

Research_Supervisor

This is largely based on the RDX form,  

Column Name

Data Type

Description

Derivation

member_sk 

integer 

Unique internally generated record for student

 

student_join_id_src 

varchar 

SITS unique identifier for student on course. 

 

begin_date_num 

integer 

Number representing start date, to allow linking to Dim_Date for context.

 

begin_date 

date 

Start date in date format 

Based upon rdx_endd. 

begin_acad_year 

varchar 

Academic year when study started. 

 

actual_end_date_num 

integer 

Number representing actual end date, to allow linking to Dim_Date for context. 

 

actual_end_date 

date

Actual end date in date format  

Based upon

rdx_endd

 

actual_end_acad_year 

varchar

Academic year when study finished.  

 

expected_end_date_num 

integer 

Number representing expected end date, to allow linking to Dim_Date for context.  

 

expected_end_date 

date

Expected end date in date format.   

Based upon rdx_eend 

percent_weighting 

Integer 

Percentage of supervision supervisor is responsible for.  

Based upon

rdx_perc.  Note: Where value is not populated it has been set to 100.

 

examiner_type_code 

varchar

Examiner type code.  Indicates supervisor role. 

Based upon rdx_extc

unit_of_assessment_code 

 varchar

Based upon rdx_uoac

 

lecturer_sk 

 integer

Internal unique identifier for supervisor, to allow other columns for Dim_Lecturer to be retrieved. 

 

lecturer_short_name 

 varchar

Short name for lecturer.