MEMBER (DIM_MEMBER) MEMBER (DIM_MEMBER)

The columns on Member 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_Member.  On the Data Marts it is given the names as below, for ease of understanding.

Cube Name
Cube_Application Applicant Applicant
Cube_Student Student
Cube_Award Award Student
Cube_Module Module Student

 

Column Name Data Type Description Derivation
member_sk integer This uniquely identifies the member record. The value increments each time a record is entered into the table.
ucas_personal_id char The UCAS identifier for the member srs_cap.cap_uci2 for SITS
member_id_src char

The source system identifier for the student record.

Note: For SITS this is the same as applicant_id_src.

ins_stu.stu_code for SITS
student_join_id_src char

The record linking a student with a course.

If a student attends more than 1 course at the UEA then they will have multiple student_join_Id_src records.

 

srs_scj.scj_code for SITS.

Applicants who don't come to the UEA won't have a value in this field.

hesa_id char The Higher Education Statistics Agency (HESA) reference for the student. ins_stu.stu_esid
source_system char The source system where the record originated from. 'SITS' for all applicants and students.
start_date date Date the record was created Not currently used
end_date date Date the record was replaced by another one. Not currently used
current_flag char Whether the record is the latest one for the student record. Currently 'YES' in all cases
applicant_status char Guide to the status of the member as an applicant dw.dim_member_status.member_status
applicant_status_group char Higher level grouping of the applicant status. dw.dim_member_status.member_status_group
student_status char Description of the current member's status Same source as for applicant_status
student_status_group char Groups similar student statuses together. Same source as for applicant_status_group
mode_of_attendance char How the member attends/ attended the UEA See Dim_Mode_Of_Attendance in Warehouse Lookup Tables
full_or_part_time char Whether the mode of attendance equates to full or part-time attendance. as mode_of_attendance
plc_cohort char Used exclusively by MED.  Holds details of member's job. ins_spr.cohc
applicant_decision_response_src char The incrementing code indicating the pattern of UEA decisions on applications and the applicants' responses.  srs_cap.cap_idrc
reason_for_transfer char Why the student transferred off the course See DW_Reason_For_Transfer in Warehouse Lookup Tables 
staff_status char current status of the staff member, if applicable Not in use
staff_status_group char group to which the staff_status belongs. Not in use
gender char Description of the gender ('MALE', 'FEMALE' or 'NOT KNOWN') See DW_Gender in Warehouse Lookup Tables
gender_hesa_id integer Number version of gender, as required by HESA. As gender
ethnicity char Description of member's ethnicity See DW_Ethnicity in Warehouse Lookup Tables 
ethnicity_hesa_id integer Number version of ethnicity, as required by HESA. As ethnicity
disability char Description of member's disability See DW_Disability in Warehouse Lookup Tables
religion char Description of member's religion   See DW_Religion in Warehouse Lookup Tables 
nationality char The member's nationality. See DW_Country in Warehouse Lookup Tables  
domicile char The member's home country. As nationality
fee_profile char The category used to establish the level of fees charged. See DW_Fee_Profile in Warehouse Lookup Tables  
applicant_category char Whether the student is 'Home', 'EU' or 'Overseas' See DW_Applicant_Category in Warehouse Lookup Tables   
age_at_entry integer The age of the member at entry to the UEA, using the member's date of birth.  Uses 31st August at the course start date, to fit in with HESA specifications.

See Dim_Age in Shared Dimensions.

source field for calculation - ins_stu.stu_dob 

age_band_at_entry char The age at entry banded up into groups (eg 16-20, 21-24) As age_at_entry 
under_21_at_entry char Whether the student is under or over 21 at entry('YES' or 'NO') As age_at_entry 
socio_econ_group char The socio-economic group the member is expected to come from. See DW_Socio_Economic_Group in Warehouse Lookup Tables  
home_postcode_std char The full standardized home postcode for the member.  ins_stu.stu_hapc.  Field is checked for validity, and only held for home students. 
current_postcode_std char The full postcode of the member whilst at the UEA. ins_stu.stu_capc.  Field is checked for validity. 
widening_participation_flag char Whether the member comes from an address with low participation in higher education

Uses home_postcode_std to derive

See DW_Widening_Participation in Warehouse Lookup Tables  

widening_participation_level char Level of participation in higher education of people from the member's home postcode. As widening_participation_flag
into_student char Whether the member attended INTO prior to attending the UEA. See DW_Into_Qualification in Warehouse Lookup Tables  
highest_qual_on_entry char Highest level of qualification when entering the UEA. See Dim_Highest_Entry_Qualification in Warehouse Lookup Tables.  
highest_qual_on_entry_hesa_code char HESA code associated with highest_qual_on_entry As highest_qual_on_entry  
highest_qual_on_entry_hesa_xqualent01_desc char Description linked to highest_qual_on_entry As highest_qual_on_entry   
course_acad_entry_year char Academic entry year member was due to begin their course, as coded on SITS. (Eg '2012/3') srs_scj.scj_ayrc
course_acad_award_year char ** TBD - IN HERE OR ON SEPARATE DIM ** cam_saw.ayr_code  
course_start_date date Date student started their course. srs_scj.scj_begd 
course_actual_end_date date Date student finished their course.  For current students this is set to '31/12/9999'. srs_scj.scj_endd 
course_expected_end_date date Date the course expected to finish. srs_scj.scj_eend 
adviser char The adviser/ supervisor for the student ins_spr.prs_code.  See Dim_Lecturer in Shared Dimensions. 
student_stage char The student/ applicant's current stage ins_spr.spr_levc for student.  1st character of srs_cap.cap_blok for latest application record for applicant. 
foundation_course char The department of the foundation course the student attended. Derived from Fact_Member_Over_Time.  If the student attended a foundation course then holds the department for that course.  
census_reportable char Whether the member should be recorded on the UEA census If the source fee_profile record ends with an 'X' then 'NO' else 'YES'.