WAREHOUSE LOOKUP TABLES WAREHOUSE LOOKUP TABLES

Add details of each lookup table used in the warehouse, together with equivalent SITS lookup tables and source fields used in matching.

DW_* tables are stored in the DW_Interfaces database. Dim_* tables are stored in the DW database. 

All tables have an equivalent C_*_Lkp table.

Any static tables where the table which it is based on is changed will need to be manually changed on the warehouse tables.

Note: D:\Scripts\LoadStaticLookupTables.sql holds the code to refresh static lookup tables.  The statements in here should be amended prior to

updating the records on test and live.  

Table/ View Description Fields Data
Source
Fields
Matched
Against
Table
Dim_Application_Agent (View) Agent used in application for UEA various.

Dynamic Load using SITS.SRS_AGN

Uses agn_code as key
Dim_Application_Category Category of country where applicant comes from. application_category Static table cap_apcc
Dim_Application
_ Core_Margin
This indicates whether an applicant was 'CORE' - A Level equivalent marks 'BBB' or below or 'MARGIN' - A Level equivalent marks 'ABB' or above. application_core
_margin,
application_core
_margin_code
Static
Table
SRS_CAP.CAP_AABS 
Dim_Application_Decision Decision made on application application_decision    
Dim_Application
_Entry_
Route
Indicates how the applicant applied for entry, through UCAS, directly or via the Teaching route (GTTR). application_entry
_route 
Static Table  First Char of SRS_CAP.CAP_MCRC. 
Dim_Application
_Qual_Status
This indicates how the grades the applicant achieved compared to the expected level.  application_qual
_status_code,

application_qual
_status,

application_qual
_status_group

Static Table  SRS_CAP.CAP_UDFC 
Dim_Application_Response Applicant's response application_response Static Table  
Dim_Assessment_Mark_Type Whether 1st attempt, reassessment or DFS assessment_mark_type/ code    
Dim_Assessment_Process_Status Holds details of the process status of the students' assessments.

assessment_process
_status_code

assessment_Process
_status 

Static Table  
Dim_Award_Status Status of award award_status, award_status_code Static Table CAM_SAW.SAW_AWS
Dim_Clearing
_Student 
Indicates whether the prospective student applied via clearing.  clearing_student Static Table  SRS_CAP.CAP_CLYN 
Dim_Highest
_Entry_Qualification
Holds highest qualification on entry categories. highest_entry_qual,
highest_entry
_qual_hesa_code,

hesa_xqualent01
_code,

hesa_xqualent01
_desc

Static Table SRS_SCJ.SCJ
_QENC if populated, else INS_STU.STU
_HQLC
Dim_Mode_Of
_Attendance 

Whether the member is part-time or full time. 

Based upon MOA table in SITS.

mode_of_attendance,
full_or_part_time 

Static Table  INS_SPR.MOA_CODE 
Dim_Module
_Selection_Status 
Whether a module was a core module, or optional. 

module_selection
_status,

module_selection
_status_code,

module_selection
_status_category.

Note the category field is a higher level categorisation of the status field. 

Static Table SMR_SESC, SMC_SECS. 
Dim_Reason
_For_Transfer

Holds reasons for transfer to other courses.

Based upon RFT Form
(Reason For Transfer and Withdrawal). 

reason_for_transfer, continue_dropout - indicates whether the student continues the course or drops out. Static Table  SRS_SCJ.SCJ_RFTC 
Dim_UCAS
_Adjustment 
An indication of whether the student applied late as a consequence of their gaining 'ABB' or above and then being able to apply to other Universities.  ucas_adjustment
_code,
ucas_adjustment 
Static Table  SRS_CAP.CAP_ADJU 
DW_Application
_Category

Holds details of the type of student:

Home EU or International.Based on APC form records on SITS.

application_category Static Table SRS_CAP.CAP_APCC
DW_Assessment
_Type 

Holds the type of assessment used.

Based on AST form records on SITS. 

assessment_type
_code,
assessment_type,
assessment_category 
Static Table CAM_MAP.AST_CODE 
DW_Building 

Holds details of the UEA buildings.

Based upon the BLD form records in SITS 

building_code,
building_name,
building_location
_code 
   
DW_Careers_Student_Activity_Status Indicates whether a student attended an activity.  Allows for fine-tuning of participation numbers in activities. student_activity_status    
DW_Country 

Holds country information.  Based upon NAT and COD Forms in SITS.

Records held in DW_CountryData.txt and C_Country_LkpData.txt files.  These should be amended if a change is required. 

country,
country_2_char_code
Static Table 

INS_STU.STU
_CODC - Domicile

INS_STU.STU
_NATC - Nationality 

DW_Course_Level  Holds information about the level of the course.  Based upon the SCH Form in SITS, but with Postgraduate split into Postgraduate Taught and Postgraduate Research.   course_level,
course_level_code
Static Table 

First Character of VAR.VAR_ROUC of 'R' matches to 'PGR', 'T' matches to 'PGT.

If not then use MCR.SCR_SCHC to get value.

DW_Dept

Holds information about the department linked to the course/ module.

Based upon the DPT form in SITS.

Also holds faculty_sk to link to DW_Faculty to get associated faculty.

dept_name,
dept_code.
Static Table  SRS_MCR.
MCR_DPTC for Course Department.CAM
_MAV.DPT_CODE for Module Department.  
DW_Disability 

Holds disability categories.

Based upon the DSB Form in SITS.

disability

Static Table   INS_STU.STU_DSBC
DW_Ethnicity 

Holds Ethnicity categories.

Based upon the ETH form in SITS 

ethnicity,

ethnicity_hesa_id,

ethnicity_group

Static Table  INS_STU.STU
_ETHC&
DW_Event
_Attribute_Type 

Holds what the event attribute holds - Student, Room, Staff or Facility.

Based upon the 

event_attribute
_type_src,

event_attribute
_type 

Static Table  CAM_EVA.
EVA_TYPE
DW_Event
_Type 
Holds the type of event being held.  

event_type_src,

event_type

Static Table  CAM_EVE.
EVE_TYPE 
DW_Faculty

Holds a list of the current faculties.

Based upon the FAC form in SITS

faculty_code,

faculty

Static Table

CAM_MCR.MCR
_FACC for Course.

In Dim_Module derived from associated Dept record.

DW_Feeder
_School_Size
Holds categories of sizes for feeder schools (schools attended prior to applying at the UEA). feeder_school
_size
Static Table  SRS_SCL.
SCL_ROLL 
DW_Gender  Holds categories of genders. Based upon GEN form in SITS.  gender, gender
_hesa_id
Static Table INS_STU.
STU_GEND
DW_Into_
Qualification
Holds details of the qualifications obtained at INTO. into_qualification Static Table

SRS_SQE.
SQE_EQEC.

Only INTO qualifications matched.

DW_Library_Availability_Status Holds details of the status of the item (available or if not why not) availability_status_src, availability_status Static Table

Aleph records:

z30_item_process_status (for item)

z36_process_status (for current loans)

z36h_process_status (for previous loans)

 

DW_Library_Classmark Holds letters and descriptions for classmarks of library items.

classmark_level,

classmark_level_1_code,

classmark_level_1_desc,

classmark_level_2_code,

classmark_level_2_desc,

classmark_level_3_code,

classmark_level_3_desc

Static Table

On Aleph: Letters at start of z30_call_no.

Matches field against level 3 code, then 2, then 1 to get best match possible. 

DW_Library_Collection Holds details of the library collections collection_src, collection_desc Static Table

On Aleph:

z30_collection

DW_Library_Loantype Holds details of the type of loans which can be made for each item

loantype_src,

loantype

Static Table

On Aleph:

z30_item_status

 

DW_Library_Sublibrary Holds details of the sublibraries

sublibrary_src,

sublibrary_desc

Static Table

On Aleph:

z30_sublibrary (for item)

 

DW_Module
_Credit_Type 
Specifies how the module credit is achieved.

module_credit
_type_code,

module_credit
_type

 Static Table   ?? NOT SURE WHERE USED??
DW_Module_Period 

Holds details of when the module runs.

Based on PRD (Academic Period) form in SITS

module_period_code,

module_period 

Static Table CAM_MAV.PSL_CODE 
DW_Progression
_Status 

Holds the students progression status. 

Based upon PIT form in SITS.

progression_status
_code,

progression_status

 

Static Table 

CAM_SPI.SPI_PITC - Provisional Progression Status.

CAM_SPI.SPI_PIT1 - Confirmed progression status  

DW_Qualification
_Level 

Holds Qualification Level based on HESA categories.

Based on HESA Guide XQLEV1003_1 2 3.pdf.

hesa_qualification
_level_10_way,

hesa_qualification
_level_6_way,

hesa_qualification
_level_5_way, 

hesa_qualification
_level_3_way

Static Table

INS_EQA.EQA_HESA for the course.

Uses SRS_VAR.VAR_AWDC for Course linked via INS_AWD to get to INS_EQA.

DW_Religion

Holds religion categories.

Based upon RLG form in SITS. 

religion Static Table INS_STU.STU_RLGC
DW_Research_Council 

The research councils who fund the research students.

Not based upon a lookup table.

research_council Static Table SRS_RDS.RDS
_RCST
DW_Room_Size
_Banding

The size bands associated with UEA rooms.

room_banding,

room_size
_min_seats,

room_size
_max_seats

Static Table Based upon
CAM_ROM.ROM_SEAT.
DW_Room_Type  The type of room. Based upon RTY form on SITS.

room_type_code,

room_type

Static Table CAM_ROM.ROM
_TYPC
DW_Socio_Economic
_Group
Socio-economic group linked to student. socio_econ_group Static Table INS_STU.STU
_SECL
DW_Term_Time
_Accommodation

Indicates type of accommodation the student is staying in. 

Based upon TTA form on SITS.

term_time
_accommodation
Static Table

INS_STU.STU
_TTAC.

Note: 'OWN HOME/RENTED', no longer in use, has been mapped to 'OWN RESIDENCE'.

DW_Widening
_Participation

Holds Widening Participation categories.

 People flagged as 'WP' (Widening Participation') have home postcodes where people are less likely to go on to Higher Education. 

C_Widening
_Participation_Lkp holds details of every postcode, together with their widening participation level.

widening_
participation
_flag,

widening
_participation
_level.

Static Table Uses
HEFCE_Number
_Control_Exception_Criteria
?? Not sure what this is used for??      
HEFCE_Polar3
_Post_Codes
Source table used to populate C_Widening
_Participation_Lkp.
  From File See
LoadOtherLookupTables.sql
for load/ derivation details.
INFOLAB_Area
/ District/
Postcode/ Sector 
Holds details of postcode areas/ districts/ postcodes/ sectors, together with their longitude and latitude for ??geomapping??   From File  See Postcode Data Load under Technical Documentation for details.
JACS_Principle
_Subject
    From File  See JACS Mapping for details
JACS_Subject
_Area
    From File See JACS Mapping for details
League_Table
_Label
    From File See JACS Mapping for details
League_Table
_Mapping 
    From File  See JACS Mapping for details
ONS_Country
/ County/
MSOA/ NUTS3/
Postcode/ Region 
Holds Office for National Statistics data at different geographic levels.    From File  See Postcode Data Load under Technical Documentation for details.