Complete Institutional Research Data Warehouse Conversion Notes
Last Updated 5/21/2013
OVERALL CHANGES:
Modification to each table, where codes are used and Lookup tables are required, add description column to pull in description and remove LookUp table requirements
Convert GLINK codes that were changed to new ones in Datatel to the newer values 1:1 ratios, to improve all historical snapshots data integrity across semesters. Suzi has provided all LookUp Table information and Snapshot data
For all snapshots beginning with 999 or 777 look at session and use that plus ET to recode the snapshot data.
1. Student_Session Table
a. School: convert all old code values of 0 to CC, 1 to CE for data comparison
b. Remove Session column
c. Modify Enr.Status Column to look at SHAP/??? Data instead of XCASB table
d. Remove Preferred Campus column
e. Make sure that Current Units column is using Term Units
f. Prior Units column should be all completed units, however code is inaccurate, look into what is wrong here
g. Sem_Units_Att use Albert’s data he utilizes for transcripts
h. Sem_Units_Comp use Albert’s data he utilizes for transcripts
i. Sem_Grade_Points use Albert’s data he utilizes for transcripts
j. Cum_Units_Att use Albert’s data he utilizes for transcripts
k. Cum_Units_Comp use Albert’s data he utilizes for transcripts
l. Cum_Grade_Points use Albert’s data he utilizes for transcripts
m. Add Sem_GPA column and use Albert’s data he utilizes for transcripts
n. Add Cum_GPA column and use Albert’s data he utilizes for transcripts
o. Remove Family Size column
p. Remove Family Income Column
q. Remove Employment_Hrs column
r. Remove Run_Type_Date
s. Calculated Major: Change criteria to be most recent degree/certificate where program.id not like ‘%.ND’
t. Remove all columns between Sem_Units_Att_SCC through District_Grade_Points
u. Change Label on “Academic” column to “Student_Standing”
v. Add Run_Date column for validation purposes
2. Seats Table, Student_Seats change name
a. School: convert all old code values of 0 to CC, 1 to CE for data comparison
b. Remove Session column
c. Ticket_No: change label to “Section_No”
d. Remove Subject_ID column
e. Change label of Active_Date to ‘Section_Start_Date’, display short date if possible
f. Change label of Effective_Date to ‘Student_Status_Date’, display as short date if possible
g. Grade_Final: change label to ‘Grade’
h. Units: round to two decimal places such as 3.25
3. Student_Static Table
a. School: convert all old code values of 0 to CC, 1 to CE for data comparison
b. Birthdate: display short date if possible
c. Street: Change label to ‘Address’
d. Remove Matriculant column
e. Remove Cum_Units_Att
f. Remove Cum_Units_Comp
g. Remove Cum_Grade_Points
h. HS_Code: Change coding to look at Institution.ID
i. Social Security: Move column next to ID_Number
j. Grade_Completed: Add to new VATEA table, remove form here
k. Remove Native_Language
l. Remove Declared_Major
m. Remove Cum_Units_Att_SCC
n. Remove Cum_Units_Comp_SCC
o. Remove Cum_Grade_Points_SCC
p. Remove Academic column
q. Add column for Academic_Program, show only POS1
r. Alt_ID: move next to Social Security Column
s. Add column Email_Address from Student_Email
4. Class_Session_Table, Course_Sections change name
a. School: convert all old code values of 0 to CC, 1 to CE for data comparison
b. Ticket_No: change label to Section_No
c. Remove Session Column
d. Remove Subject_ID
e. Subj_No change label to Subject
f. Subtitle_No remove
g. Change Status data to display description instead of code
h. Begin_date: Short date if possible
i. End_Date: Short date if possible
j. Area_1 rename column to Loc
k. Area_2 rename column to building
l. Area_3 rename column to room
m. Remove Budget_No
n. Remove Budget_Desc
5. Catalog Table: Course_Catalog change name
a. School: convert all old code values of 0 to CC, 1 to CE for data comparison
b. Subj_no change label to Subject
c. Remove Subtitle_No
d. Change status as above, remove Funding Acctg Method X
e. Title: use long title instead of short title
f. College: change label to Loc
g. Remove Non_Credit
h. Max, Min & Increment: shorten to 2deimal places
i. Cat_Lecture_Hrs: chsnge codeto LIKE LEC%
j. Cat_Lab_Hrs: change to NOT LIKE LEC%
k. Remove Cat_Arrg_Hrs
l. USOE_Code remove
m. Priority: Limit results to course_levels like SAM%
n. Add new column PTCL: Limit course_levels like PTCL%
o. Remove Credit_NoCredit
p. Apprenticeship: Alter display of code to show APPR or AP321 code instead of grouping them together.
q. Remove Remedial
r. Basic_Skills: Alter display to show real codes of PBS, BS, or NBS
s. CSUC_GE, 2 & 3: Rename columns CSU1, 2, & 3
t. CSUC_GE_A, B C: Rename columns to AA1, 2 & 3
u. IGETC1, 2: Add IGETC 3
v. Remap all user codes to CSU, AA IGETC
w. SAC_GE remap to correct user field
x. Remove VATEA_Funded
6. Student_Degree Table
a. School: convert all old code values of 0 to CC, 1 to CE for data comparison
b. Remove Session
c. Find out where Honors is stored
d. Add column Honors_Desc column with description of Honors code
e. Remove Dept_Honors
f. Add column Degree_Length (Graduation Date – SPRO Start Date)
7. Test Table, Student_Test change name
a. School: convert all old code values of 0 to CC, 1 to CE for data comparison
b. Test_Date convert to short date
c. Remove Test_Score_1 through Test_Score_10 columns
8. Student_Needs Table
a. Needs_Date convert to short date
b. Remove all columns for Needs_Questions 1 – 20
c. Remove all columns for Needs_Services 1 – 34
d. Remove all columns for Needs_Goal
e. Remove Needs_Alt
f. Remove Needs_Asm
g. Remove Non_ESL
h. Remove ESL_Test_Waived
i. Remove ESL_CNSL_REC 1-3
j. Remove ENG_WAIVED
k. Remove RDG_WAIVED
l. Remove Needs_Other
m. Remove Math_Preq
n. Remove Math_Waived
o. Remove Counseling 1-5
p. Remove Study_Skills 1-5
q. Remove Counselor_No
r. Remove Transfer_College
s. Remove Level_Date
9. Benefit Table, Student_Award_Benefit change name
a. School: convert all old code values of 0 to CC, 1 to CE for data comparison
b. Remove Session
c. Remove Benefit_Code
d. Remove Benefit_Units
e. Add column Benefit_Amount
10. Student_DSPS
a. Disability_Start_Date change to short date
b. Diability_End_Date change to short date
11. Waivers, Student_Waivers change name
a. Add_Date change to short date
12. College_Degree_Research, Student_SelfDeclared_Degrees change name
a. Remove End_Date
b. Add Institution_Desc as column
c. Remove Degree_Date
d. Remove Major
e. Remove Minor
f. Remove CCD
g. Remove CCD_Date
h. Remove Honors
i. Remove Acad_Location
j. Institution_ID: point to actual school ID not RSCCD value
13. Credit_by_Exam Table, Student_Placement name change?
a. School: convert all old code values of 0 to CC, 1 to CE for data comparison
b. Remove College
c. Change label SAC_EQUIV_SESSION to Term
d. Change label SAC_EQUIV_SUBJ_NO to Subject
e. Change label SAC_Equiv_Course_No to Course_No
f. Remove SAC_Equiv_Subtitle_No
g. Change label SAC_units to Units
h. Change label SAC_Grade to Grade
i. Look into codes for GE_Area_Code and Major_Area_Code
14. Keep Datatel_Student_EthnicRaces however change name to just Student_EthnicRaces
15. Keep DTSRUNLOG as is
16. Update all existing tables with LookUp descriptions as an added column, then remove all LookUp Tables
17. Keep SEMSTERTOPROCESS as is no change
18. Paul will clean up all staging files, no need to review as they are used for processing only
19. Keep Survey Table as is for historical purposes
20. Transfer_Work rename to Student_Transfer_Work
a. School: Same as above
b. Add column Transfer_School_Desc. To obtain school name
c. Remove SAC_EQUIV_SESSION column
d. SAC_EQUIV_SUBJ_No change label to Subject
e. SAC_EQUIV_COURSE_No change to Course_No
f. Remove SAC_EQUIV_SUBTITLE_NO
g. Remove GE_Area_Code
h. Remove Major_Area_Code
i. Set to update with other tables
21. Table deletions
a. Remove all TEMP tables
b. Remove TEST_ALL_CC
c. Remove TEST_ALL_CE
d. Remove TEST_ORIGINAL
e. Remove TESTBADDATE
f. Remove ACAD_CREDENTIALS_LS_PROD
g. Remove ACAD_CREDENTIALS_PROD
h. Remove ACAD_PROGRAMS_PROD
i. Remove ALL_STUDENTS
j. Remove BENEFITS_ORIGINAL
k. Remove all 3 CAPP tables
l. Remove CITIZEN_CODES (LookUp) table after adding description to STUDENT_STATIC?
m. Remove STUDENT_EMAIL after adding EMAIL_ADDRESS to STUDENT_STATIC
n. Remove CREDIT (LookUp)
o. Remove DATATEL_PERSON_ALT_ID
p. Remove DATATEL_PERSON_DEMO
q. Remove EDUCATIONAL_GOAL_CODES after adding LookUp values to appropriate table
r. Remove ENROLLMENT_STATUS_CODES after adding LookUp values to appropriate table
s. Remove ETHNIC_CODES after adding LookUp values to appropriate table
t. Remove GPA table
u. Remove IMMGR_UPDATE
v. Remove Import_XCASB_Temp
w. Remove Import_XCASB_TEMP_2011FA
x. Remove IPEDS_report
y. Remove MAJORS_PROD after adding LookUp values to appropriate table
z. Remove all 4 MIKETEMP tables
aa. Remove NEEDS
bb. Remove NSC TRHU 2011-2012
cc. Remove PERSON
dd. Remove PLACMENT
ee. Remove PRIOR_COLLEGE
ff. Remove PRIOR_COLLEGE_ORIGINAL
gg. Remove all PU_** Tables
hh. Remove SAC_FEX_FALL_2010_TBL
ii. Remove SAC_SCC_Degree_Temp_Imports
jj. Remove SEAT_ORIGINAL
kk. Remove Semester
ll. Remove SEMESTERTOPROCESSCC
mm. Remove SEMESTERTOPROCESSCE
nn. Remove SSN_PERM_DATATEL_ID_DEMO
oo. Remove all STD_SESS tables
pp. Remove STU_IMMGR_LOC
qq. Remove STU_SCHL_LOC
rr. Remove all Student_888 tables
ss. Remove Student_Degree_BAKold
tt. Remove Student_Degree_YR
uu. Remove Student_Email_Original
vv. Remove Student_Format_eSARS
ww. Remove Student_MaxSnapshot_eSARS
xx. Remove Student_Needs_Original
yy. Remove Student_Session_BKUP
zz. Remove Student_Session_CRT
aaa. Remove Student_Session_Original
bbb. Remove Student_Session_PreEnrStatUpdate
ccc. Remove Student_Static_BKUP
ddd. Remove Student_Static_CIT
eee. Remove Student_Static_Original
fff. Remove Student_Temp_eSARS
ggg. Remove Student_Test
hhh. Remove Survey_Original
iii. Remove T_MIS
jjj. Remove Transcript_Sent
kkk. Remove Transcript_Sent_Original