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.
|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|
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|
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'.|