Child pages
  • SSP v2.8.0 Data Integration Mapping
Skip to end of metadata
Go to start of metadata

SIS Data Mappings v2.8.0.xlsx

The Excel spreadsheet is a template to map data required for SSP and optionally displayed throughout SSP.  The template can be used to map database columns to SSP fields. 

Download the template

View Usage

SSP uses the view names defined below, prefixed with "v_".  These views can select data from any underlying datasource.  The below examples show corresponding database tables, found in the external database schema, that the SSP views utilize by default.  For those institutions that wish to utilize a linked server setup, simply modify the view selection query to select from real-time data, a data-warehouse, etc.  Note that when using the views linked to sources that are not the local tables performance times for the SSP application and the corresponding API may be negatively affected. 

More notes on data integration strategies here.

All varchar fields listed below are implemented as nvarchar in SQLServer deployments and as character varying in PostgreSQL deployments.

All date fields listed below are implemented as date in both SQLServer and PostreSQL deployments. The application interprets these values in the default JVM timezone, which should typically be configured for institution-local time.

All datetime fields are implemented as datetime in SQLServer deployments and as timestamp without time zone in PostgreSQL. The application interprets these values in UTC by default, but this timezone is configurable in ssp-config.properties.

Mapping Template 

SSP Version 2.8 Data Mapping Spreadsheet
Rows/Tables with orange highlight indicate new items since v2.6
Rows/Tables with orange highlight indicate new items since v2.7
View Used: v_external_person
Default Table: external_person
Description: Holds attributes for any user accessing or referenced by SSP. For example, supports search results, early alerting and student creation. Records in this table are periodically synchronized into the "person" table and several of its supporting tables. Both school_id and username must be unique identifiers for each record. I.e. any given school_id or username can occur at most once.
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
school_idPrimaryvarchar(50)NoStudent's id or other identifier used by the institution to identify students, faculty, or other staff.uu351764
usernameUniquevarchar(50)NoThe username for the person. This would typically be the user's username to login to campus systems. It also uniquely identifies the person. May be the same as schoolId in some cases. Examples include uu351764, john.smith, jdoe, etc.john.doeCaseload Assignment
first_namevarchar(50)NoStudent's first name.John
middle_namevarchar(50)YesStudent's middle name.Michael
last_namevarchar(50)NoStudent's last name.Doe
birth_datedateYesFormat YYYY-MM-DD.1980-07-01Main-Dashboard
primary_email_addressvarchar(100)YesStudent's primary email address. Typically the institution-assigned email address.john.doe80@university.eduMain-Dashboard; Main-Details; Main-Contact: Main-Coach
address_line_1varchar(50)YesStudent's first address line.5566 Campus AvenueMain-Contact
address_line_2varchar(50)YesStudent's second address line.Apt 1AMain-Contact
cityvarchar(50)YesCity component of student's address.College CornerMain-Contact
statevarchar(2)YesState component of student's address. Use two character state abbreviation.AZMain-Contact
zip_codevarchar(10)YesZipcode from Student's address. Will accept either XXXXX or XXXXX-XXXX formats.10001.00Main-Contact
residency_countyvarchar(50)YesThe county of residenceHamilton
non_local_addresschar(1)NoIndicator that the address is not local. Use 'N' for false and 'Y' for true. Default is 'N'.NMain-Contact
home_phonevarchar(25)YesStudent's home phone number.111-555-2222Main-Contact
work_phonevarchar(25)YesStudent or staff's work phone number.111-555-2223Main-Coach; Main-Contact
cell_phonevarchar(25)YesStudent or staff's cellular/mobile phone number.111-555-2223Main-Contact
office_locationvarchar(50)YesUsed to specify the office location of institution staff.Krieger Hall, Office 101A
office_hoursvarchar(50)YesUsed to specify the office hours of institution staff.M, W, F 2-4pm
department_namevarchar(100)YesThe department name if this record is an institutional staff member.Psychology
actual_start_termvarchar(20)YesTerm when student first entered the college. Typically called the matriculation term. This value is equivalent to external_term.code.FallReport Form Criteria
actual_start_yearintYesYear when student first entered the college. Typically called the matriculation year. Valid values are 2000-2100.2013Report Form Criteria
marital_statusvarchar(80)YesEquivalent to the value represented in the ssp.marital_status.external_key database column. This value is used for linking to the reference data item and is not shown in the application except for the administrative screens.MarriedMain-Details
ethnicityvarchar(80)YesThe ethnicity of the student displayed in the student intakeHispanicMain-Details
genderchar(1)YesValid values are "M", "F" or null.MMain-Details
F1_statuschar(1)YesY or N, Indicates if the student has a F1 Visa on fileYMain-Details; MAP-Financial Aid
balance_oweddecimal(9,2)YesStudent's balance currently owed to the college. Format is XXXXX.XX.300.00Main-FinancialAid; MAP-Financial Aid
coach_school_idvarchar(50)YesStudent's coach. This field will be the master data for the student/coach relationship when SSP's configuration is setup to utilize this field rather than the coach select box on SSP's caseload assignment screen.uu351789Main-Coach
photo_urlvarchar(250)YesThe url location of the photo for usershttp://institution.edu/students/1234567,jpgMain-Dashboard; Main-Coach
student_type_codevarchar(10)YesThe student_type is a primary characteristic of the student in SSP. This value must match an existing row in student_type.code.FTICMain-Dashboard
race_codevarchar(10)YesThe race of the student displayed in the student intake. Must match an existing value of race.code.HWMain-Details
campus_codevarchar(10)YesThe home campus for the student. The code value must match a configured reference item in Early Alert campus.DAYMain-Details; EAL Details
View Used: v_external_registration_status_by_term
Default Table: external_registration_status_by_term
Description: Holds registration status for each student by term. Used in SSP reporting capabilities. Used in the Main Tool Details Tab
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
school_idPrimaryvarchar(50)NoStudent's id or other identifier used by the institution to identify students, faculty, or other staff.uu351764
term_codePrimaryvarchar(25)NoSpecifies what term the course was taken. Equivalent to external_term.code value.FA12Main-Dashboard; Main-Details; MAP-Financial Aid
registered_course_countintNoHow many classes the student is registered for this term.4
tuition_paidchar(1)YesValid values are "Y", "N". This fields indicates the payment status of tuition for the termYMain-Details; MAP-Financial Aid
View Used: v_external_term
Default Table: external_term
Description: Holds academic calendar information. Used throughout the application.
Column NameTypeNullableDescriptionExampleSSP Display LocationSIS Column
codePrimaryvarchar(25)NoA unique value that identifies a term. Sinclair uses 12/FA. Other schools may use 2012Fall, Fall2012, etc. There are no restrictions on the format of this column.Fall 2012
namevarchar(50)NoName of academic term. Ex - Fall 2012FA12
start_datedateNoStart date of academic term, Format YYYY-MM-DD. Should reflect the date of term start in the application's default timezone (typically the real-world institution's timezone). In pre-2.0.0 versions this was a datetime field. Existing values will be automatically converted to date-only values during the 2.0.0 installation.2012-08-22
end_datedateNoEnd date of academic term, Format YYYY-MM-DD. Should reflect the date of term end, inclusive, in the application's default timezone (typically the real-world institution's timezone). In pre-2.0.0 versions this was a datetime field. Existing values will be automatically converted to date-only values during the 2.0.0 installation.2012-12-06
report_yearintNoReporting year of academic term. Valid years are 1900-2100.2013
registration_start_datedateYesThe date regular registration opens for the term. Registration dates are used in the determination of the SSP calculated cutoff term for the plan status calculation.2014-03-31
registration_end_datedateYesThe date regular registration ends for the term. Registration dates are used in the determination of the SSP calculated cutoff term for the plan status calculation.2014-04-30
View Used: v_external_catalog_year
Default Table: external_catalog_year
Description: Holds academic catalog year information. Used in MAP to define the plans and templates.
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
codevarchar(50)NoThe code that defines the catalog year in the MAP tables13-14
namevarchar(50)NoThe name to display in MAP as the catalog year2013-2014
View Used: v_external_faculty_course_roster
Default Table: external_faculty_course_roster
Description: Holds course roster details for each course taught by an instructor. Used for SSP Early Alert. Each record in this table represents a selectable enrollment in the SSP Early Alert creation UI. For Early Alerts created directly via API, the application may attempt to locate additional course-related data from this table, but this is optional. Any data sourced from this table during Early Alert creation is copied into the Early Alert record, so this table can be periodically pruned, e.g. as a simple means to limit the selectable rosters in the Early Alert UI. This table need not reflect all course/roster entries as the v_/external_student_transcript_course and vice versa.
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
faculty_school_idvarchar(50)NoThe school_id used to identify a faculty member.uf928711
school_idvarchar(50)NoStudent's school id.uu351764EAL-Course-Roster
first_namevarchar(50)NoStudent's first name.JamesEAL-Course Roster
middle_namevarchar(50)YesStudent's middle name.RyanEAL-Course Roster
last_namevarchar(50)NoStudent's last name.DoeEAL-Course Roster
primary_email_addressvarchar(100)NoStudent's primary email address. Typically the institution-assigned email address.james.ryan80@university.edu
term_codevarchar(25)NoSpecifies what term the course was taken by the student and taught by the faculty member. Equivalent to external_term.code value.FA12EAL Details
formatted_coursevarchar(35)NoThe formatted course. MTH-101, ACCT1000A, etc.MTH101
section_codevarchar(128)YesA value that uniquely identifies the course634586 or FA12-MTH-122-002
section_numbervarchar(10)YesThe section number associated with course registration002
status_codevarchar(2)YesDesignator for the student's enrollment status in the courseEEAL-Course Roster
View Used: v_external_faculty_course
Default Table: external_faculty_course
Description: Holds information related to course details for each course taught by an instructor for a given term. Used along with v_/external_faculty_course_roster to support SSP Early Alert creation. This data is completely separate from v_/external_student_transcript_course.
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
faculty_school_idvarchar(50)NoThe school_id used to identify a faculty member.uf928711
term_codevarchar(25)NoSpecifies what term the course was taught by the faculty member. Equivalent to external_term.code value.FA12EAL Details
formatted_coursevarchar(35)NoThe formatted course. MTH-101, ACCT1000A, etc.MTH101EAL Details; EAL Response
section_codevarchar(128)YesA value that uniquely identifies the course634586 or FA12-MTH-122-002
section_numbervarchar(10)YesThe section number associated with course registration002
titlevarchar(100)NoThe title of the course.College AlgebraEAL Response
View Used: v_external_student_academic_program
Default Table: external_student_academic_program
Description: Defines each student's current academic program of study. Used in the Main Tool Dashboard Tab
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
school_idPrimaryvarchar(50)NoStudent's id or other identifier used by the institution to identify students, faculty, or other staff.uu351764
degree_codePrimaryvarchar(10)NoCode for the degree of study. Examples include AA, AAS, etc.BA
degree_namevarchar(100)NoFormatted degree name.Applied Mathematics
program_codePrimaryvarchar(50)NoCode for the program of study. Examples include ART, BIS, etc.BS101MA
program_namevarchar(100)NoFormatted program name used for display in SSP. Ex - "Art : Associate of Arts", "Business Information Systems - AAS", etc.Bachelor of Science in MathematicsMain-Dashboard; Main-Details
intended_program_at_admitvarchar(100)YesFrom the Admissions application, the intended program of student formatted program name used for display in SSP. Ex - "Art : Associate of Arts", "Business Information Systems - AAS", etc.Bachelor of Science in EnglishMain-Details
View Used: v_external_student_test
Default Table: external_student_test
Description: Student test score data. Used in the Main Tool Placement Tab
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
school_idPrimaryvarchar(50)NoStudent's id or other identifier used by the institution to identify students, faculty, or other staff.uu351764
test_namevarchar(50)NoName of test. Examples include American College Test, etc.American College TestingMain-Placement
test_codePrimaryvarchar(25)NoACT, SAT, ACCUPLACER, etc.ACT
sub_test_codePrimaryvarchar(25)NoThe subtest, or test component. Examples include MATH, ENG, COMP, MATHPLM, etc. As of 2.0.0, will only allow nulls if natural keys on external tables are disabled. Previouslly always allowed nulls.MATH
sub_test_namevarchar(50)YesName of subtest. Examples include Chemistry, Math, etc.MATH
test_datePrimarydateNoDate student completed the test, Format YYYY-MM-DD.2012-01-01Main-Placement
scoredecimal(9,2)NoThe score the student earned on the test.22Main-Placement
statusvarchar(25)YesThis field indicates the status of the test submitted. Valid values are "Pending", "Accepted"PendingMain-Placement
discriminatorPrimaryvarchar(1)NoThe field uniquely identies a test taken two or more times on the same day1
outcomevarchar(255)YesA description of any outcome or recommendation based on the test score. These are generally placement into courses.Take MATH 098 and ENGL 099Main-Placement
View Used: v_external_student_transcript
Default Table: external_student_transcript
Description: Holds current, cumulative student transcript summary information. Used in the Main Tool Dashboard Tab. Note that SSP will never attempt to calculate a GPA.
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
school_idPrimaryvarchar(50)NoStudent's id or other identifier used by the institution to identify students, faculty, or other staff.uu351764
credit_hours_for_gpadecimal(9,2)YesThe total value of credit hours included in the Grade Point Average.3.00
credit_hours_earneddecimal(9,2)YesThe total value of credit hours successfully completed or earned.3.00Main-Details; MAP-Financial Aid
credit_hours_attempteddecimal(9,2)YesThe total value of credit hours attempted whether or not included in the hours for GPA or hours earned.3.00Main-Details; MAP-Financial Aid
credit_hours_not_completeddecimal(9,2)YesThe total value of credit hours attempted but not completed3.00
credit_completion_ratedecimal(9,2)YesCompletion rate of hours attempted86%Main-Dashboard; Main-Details; MAP-Financial Aid
total_quality_pointsdecimal(9,2)YesThe total value of quality points used for the calculation of the Grade Point Average.12.00
grade_point_averagedecimal(9,2)NoThe calculated cumulative GPA value4.00Main-Dashboard; Main-Details; MAP-Financial Aid
academic_standingvarchar(50)YesCurrent academic standing of the studentGood, Probation, Dean's ListMain-Dashboard; Main-Details; MAP-Financial Aid
current_restrictionsvarchar(100)YesList of any restrictions or holds on the student recordDismissed, Fine Hold, ConductMain-Dashboard; Main-Details; MAP-Financial Aid
gpa_trend_indicatorvarchar(25)YesDescribes the trend of the gpa from the previous termUp, Down, New, Same
local_gpadecimal(9,2)YesCustom gpa traditionally used for resident credit only3.00Main- Details
program_gpadecimal(9,2)YesCustom gpa traditionally used for program/major credit only3.00Main- Details
View Used: v_external_student_transcript_course
Default Table: external_student_transcript_course
Description: Contains section- or course-scoped student transcript records. Used in the Main Tool Transcript Tab. Each row represents a collection of course completion metrics (grade, credits earned, etc) for a particular student at at least course granularity (course code + term code). Additional values can be specified to scope a given entry to a particular section, but this may not be possible in all cases, e.g. transcript entries sourced from other institutions. The data in this table is typically a superset of the data in v_/external_faculty_course roster. Because it is used to support the application's MAP tool, it should at least contain records for all currently enrolled students. Also, it often contains transcripts for historical enrollments for which it makes no sense to process Early Alerts. Periodically pruning this table will not break the application, but is not usually advisable as it may result in unexpected volatility in reports and other read-only application views.
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
school_idPrimaryvarchar(50)NoStudent's id or other identifier used by the institution to identify students, faculty, or other staff.uu351764
faculty_school_idvarchar(50)NoThe school_id used to identify a faculty member.uf928711
first_namevarchar(50)NoStudent's first name.John
middle_namevarchar(50)YesStudent's middle name.Michael
last_namevarchar(50)NoStudent's last name.Doe
term_codePrimaryvarchar(25)NoSpecifies what term the course was taken by the student and taught by the faculty member. Equivalent to external_term.code value.FA12Main-Transcript; Main-Schedule; MAP-Transcript
section_codePrimaryvarchar(128)NoA value that uniquely identifies the course634586 or FA12-MTH-122-002
section_numbervarchar(10)YesThe section number associated with course registration002Main-Transcript
formatted_coursePrimaryvarchar(35)NoThe course in a format that would normally be displayed on a transcriptMTH 101Main-Transcript; Main-Schedule; MAP-Transcript
subject_abbreviationvarchar(10)NoThe subject abbreviation is the combined with the number to create the formatted courseMTH
numbervarchar(15)NoThe number is the combined with the subject abbreviation to create the formatted course101
titlevarchar(100)YesThe full course title. Ex - Mathematics 101College AlgebraMain-Transcript; Main-Schedule; MAP-Transcript
descriptionvarchar(2500)YesThe course description as it exists in the course catalog.Principles of Algebra …
gradevarchar(10)NoGrade earned in the course by the student.AMain-Transcript; MAP-Transcript
credit_earneddecimal(9,2)YesCredit hours earned by the student in the course.3.00Main-Transcript; Main-Schedule; MAP-Transcript
credit_typevarchar(25)YesIndicates the type of credit earned by the student. Any value is acceptable to represent the type of credit with one condition. For the total transfer hours to be displayed on the Main Tool Details tab, the credit type for transfer courses must be 'Transfer'Institutional, Transfer, ProficiencyMain-Transcript; MAP-Transcript
auditedchar(1)Yes"Y" if the student audited the course.NMain-Schedule
status_codevarchar(2)YesDesignator for the student's enrollment status in the courseEMain-Schedule
course_codevarchar(50)YesA value that uniquely identifies the course738957MAP Plan Status Report
participationvarchar(15)YesA custom value that can represent course participation or other metrics for course. This field is also tied to a participation Success Indicator which can be customized.GOOD or 100Main-Schedule; and worst value in Participation Success Indicator if enabled
View Used: v_external_student_transcript_term
Default Table: external_student_transcript_term
Description: Holds term by term student transcript summary information. Used in the Main Tool Dashboard Tab. Note that SSP will never attempt to calculate a GPA.
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
school_idPrimaryvarchar(50)NoStudent's id or other identifier used by the institution to identify students, faculty, or other staff.uu351764
term_codePrimaryvarchar(25)NoSpecifies what term the course was taken by the student and taught by the faculty member. Equivalent to external_term.code value.FA12
credit_hours_for_gpadecimal(9,2)YesThe total value of credit hours included in the Grade Point Average.3.00
credit_hours_earneddecimal(9,2)YesThe total value of credit hours successfully completed or earned.3.00
credit_hours_attempteddecimal(9,2)YesThe total value of credit hours attempted whether or not included in the hours for GPA or hours earned.3.00
credit_hours_not_completeddecimal(9,2)YesThe total value of credit hours attempted but not completed3.00
credit_completion_ratedecimal(9,2)YesCompletion rate of hours attempted86%
total_quality_pointsdecimal(9,2)YesThe total value of quality points used for the calculation of the Grade Point Average.12.00
grade_point_averagedecimal(9,2)NoThe calculated GPA value (not cumulative)4.00
View Used: v_external_student_financial_aid
Default Table: external_student_financial_aid
Description: Holds student financial aid information. Used in Main Tool Details Tab
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
school_idPrimaryvarchar(50)NoStudent's id or other identifier used by the institution to identify students, faculty, or other staff.uu351764
financial_aid_gpadecimal(9,2)NoThe gpa calculated for financial aid eligibility2.75Main-FinancialAid; MAP-Financial Aid
gpa_20_b_hrs_neededdecimal(9,2)YesThe number of earned hours at a B level required to achieve an overall 2.0 gpa9.00MAP-Financial Aid
gpa_20_a_hrs_neededdecimal(9,2)YesThe number of earned hours at a A level required to achieve an overall 2.0 gpa4.00MAP-Financial Aid
needed_for_67ptc_completiondecimal(9,2)YesThe number of hours required to reach the 67% completion rate required for financial aid (earned/attempted = completion rate)12.00MAP-Financial Aid
current_year_financial_aid_awardchar(1)YesY or N, Inidicates if the student received financial aid for the current academic yearYMAP-Financial Aid
sap_statuschar(1)YesY or N, indicates if the student has achieved Satisfactory Academic ProgressYMain-Dashboard; Main-FinancialAid; MAP-Financial Aid
FAFSA_datedateYesThe date the FAFSA was accepted2012-12-06Main-FinancialAid; MAP-Financial Aid
financial_aid_remainingdecimal(9,2)YesThe remaining amount of financial aid available to the student1300.00
original_loan_amountdecimal(9,2)YesThe original amount of loans available to the student5800.00Main-FinancialAd; MAP-Financial Aid
remaining_loan_amountdecimal(9,2)YesThe remaining amount of loans available to the student4100.00Main-FinancialAid; MAP-Financial Aid
sap_status_codevarchar(25)YesThe SAP status code as determined by the institution. This value must match a configuration item for SAP status code in SSP administration(any configured item)Main-FinancialAid; MAP-Financial Aid
institutional_loan_amountdecimal(9,2)YesThe dollar amount of institutional loans provided to the student10000.00Main-FinancialAid; MAP-Financial Aid
eligible_federal_aidchar(1)Yesindicator for the student's eligibility to receive federal financial aidYMain-Details; MAP-Financial Aid
financial_aid_file_statusvarchar(10)YesThe overall status of the financial aid file for the student's application. If all files have been submitted, COMPLETE would be the appropriate status. Otherwise, INCOMPLETE or PENDING should be used to describe the status. Acceptable values are COMPLETE, INCOMPLETE and PENDINGINCOMPLETE
terms_leftintegerYesThe number of remaining eligible terms to receive financial aid3.00Main-FinancialAid; MAP-Financial Aid
View Used: v_external_student_financial_aid_award_term
Default Table: external_student_financial_aid_award_term
Description: Holds student financial aid information to describe the term by term acceptance of financial aid. Each term financial aid was offered to a student should be included along with the acceptance status for each term. Used in Main Tool Details Tab
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
school_idPrimaryvarchar(50)NoStudent's id or other identifier used by the institution to identify students, faculty, or other staff.uu351764
acceptedchar(1)NoIndicates the acceptance status of financial aid for the term. Acceptable values are Y or NYMain-FinancialAid; MAP-Financial Aid
term_codePrimaryvarchar(25)NoSpecifies what term financial aid was offered to the student. Equivalent to external_term.code value.FA12Main-FinancialAid; MAP-Financial Aid
View Used: v_external_student_financial_aid_file
Default Table: external_student_financial_aid_file
Description: Holds student financial aid information related to the files required in the students application. Each of the required files should be associated to a row for each student. Each row should have an appropriate status code to describe the status of each file. Used in Main Tool Details Tab and MAP
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
school_idPrimaryvarchar(50)NoStudent's id or other identifier used by the institution to identify students, faculty, or other staff.uu351764
file_statusvarchar(10)NoThe appropriate status for the file code. Acceptable values are COMPLETE, INCOMPLETE and PENDINGPENDINGMain-FinancialAid; MAP-Financial Aid
financial_file_codePrimaryvarchar(25)NoThe file name as determined by the institution. Each requiredResidencyMain-FinancialAid
View Used: v_external_course
Default Table: external_course
Description: Holds course details generally found in a course catalog/bulletin. Used in the MAP Tool
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
codePrimaryvarchar(50)NoA value that uniquely identifies the course1035678
formatted_coursevarchar(35)NoThe course in a format that would normally be displayed on a transcriptMTH 101MAP-Main; MAP-Course Details
subject_abbreviationvarchar(10)NoThe subject abbreviation is the combined with the number to create the formatted courseMTH
numbervarchar(15)NoThe number is the combined with the subject abbreviation to create the formatted course101
titlevarchar(100)NoThe full course title. Ex - Mathematics 101College AlgebraMAP-Main; MAP-Course Details
descriptionvarchar(2500)NoThe course description as it exists in the course catalog.Principles of Algebra …MAP-Course Details
max_credit_hoursdecimal(9,2)NoThe total value of credit hours successfully completed or earned. Expect change to a fractional data type (decimal(9,2)) in a future release.3MAP-Course Details
min_credit_hoursdecimal(9,2)NoThe total value of credit hours successfully completed or earned. Expect change to a fractional data type (decimal(9,2)) in a future release.3MAP-Course Details
is_devchar(1)NoIdentifies the course as a developmental education courseYMAP-Main Page
academic_linkchar(2000)YesA http link to academic information about the coursehttp://www.test.edu/biology.htmlMAP-Course Details
department_codechar(50)YesThe department code associated with the department offering the courseDEP_HSMAP-Course Details
division_codechar(50)YesThe division code associated with the department offering the courseA&SMAP-Course Details
master_syllabus_linkchar(2000)YesA http link to the course syllbashttp://www.test.edu/course.htmlMAP-Course Details
View Used: v_external_course_term
Default Table: external_course_term
Description: Defines the terms in which the designated course is offered. Each course with the associated term to include is listed separately. Used in the MAP Tool
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
course_codePrimaryvarchar(50)NoRefers to the course_code in external_course1035678
term_codePrimaryvarchar(25)NoIdentifies terms in which the course will be offered.Fall 2013MAP- Filter Course by Terms Offered
View Used: v_external_course_program
Default Table: external_course_program
Description: Defines the courses required by degree programs. Each course with the associated program is listed separately. Used in the MAP Tool
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
course_codePrimaryvarchar(50)NoRefers to the course_code in external_course1035678
program_codePrimaryvarchar(50)NoRefers to the program code to identify the degree programBA01MTH53
program_namevarchar(100)NoIdentifies the name of the degree programMathematicsMAP- Filter Courses by Program
View Used: v_external_course_tag
Default Table: external_course_tag
Description: Defines the tag codes assigned to courses. The definition of tag is completed in SSP administration. Each course with the associated tag is listed separately. Used in the MAP Tool
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
course_codePrimaryvarchar(50)NoRefers to the course_code in external_course1035678
tagPrimaryvarchar(100)NoIdentifies the code used to identify the tag in SSP adminTRNSF, DLMAP-Main Page; MAP-Course Details
View Used: v_external_department
Default Table: external_department
Description: Defines the list of departments that can be added to the plan template definition. Used in the MAP Tool
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
codePrimaryvarchar(50)NoA unique code of the departmentACCT
namevarchar(100)NoName of the departmentAccountingMAP- Template Definition
View Used: v_external_division
Default Table: external_division
Description: Defines the list of divisions that can be added to the plan template definition. Used in the MAP Tool
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
codePrimaryvarchar(50)NoA unique code of the divisionARTS-HUM
namevarchar(100)NoName of the divisionArts and HumanitiesMAP- Template Definition
View Used: v_external_program
Default Table: external_program
Description: Defines the list of programs that can be added to the plan template definition. Used in the MAP Tool
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
codePrimaryvarchar(50)NoA unique code for the degree programAA02ENGA
namevarchar(100)NoName of the degree programAA in EnglishMAP-Main Page
View Used: v_external_course_requisite
Default Table: external_course_requisite
Description: Defines the relationship of courses for pre, co and pre/co relationships. The table contains each course in the relationship and code to define relationship. Used in the MAP Tool
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
requiring_course_codePrimaryvarchar(50)NoThe course requiring the relationship. IE: 102 requires 101ENG-102
required_course_codePrimaryvarchar(50)NoThe course required in the relationship. IE: 101 is required to take 102ENG-101
required_formatted_coursevarchar(35)NoThe formatted course value for the required courseENG-101MAP-Course Details
requisite_codePrimaryvarchar(8)NoIdentifies the code used to identify the requisite type in SSP adminPRE, PRE_CO, COMAP-Course Details
View Used: v_external_substitutable_course
Default Table: external_substitutable_course
Description: Defines the relationship of courses that may be used during the MAP plan status calculation. An option in the calculation allows for a planned course to be substituted by another course when the matching logic identifies courses. When matched to the substitution (target course), the planned course will be considered a match. Each row will define a single pair of courses, and multiple rows can be inserted to define additional substitutions for a plan course. Used in the MAP Tool
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
term_codevarchar(25)YesThe term code to identify the term for which the substitution will apply. A null value will apply the substitution to all matching source_formatted_courseFall 2012
program_codevarchar(50)YesThe program code to identify the degree program for which the substitution will apply. A null value will apply the substitution to all matching source_formatted_courseAA
source_formatted_coursevarchar(35)NoThe formatted course value for the planned course that may be substitutedSOC-101
source_course_codevarchar(50)YesThe course_code value for the planned course that may be substituted. If the course code matching criteria is configured, the planned course code must match the formatted course and course code for the substitution to apply.375893
source_course_titlevarchar(100)YesThe course title value for the planned course that may be substituted. If the course title matching criteria is configured, the planned course must match the formatted course and title for the substitution to apply.Intro to Sociology
source_credit_hoursnumeric(9,2)YesThe course hours value for the planned course that may be substituted. If the course hours matching criteria is configured, the planned course must match the formatted course and hours for the substitution to apply.3.00
target_formatted_coursevarchar(35)NoThe formatted course value for the transcript course that may be substitutedPSY-101
target_course_codevarchar(50)YesThe course_code value for the transcript course that may be substituted. If the course code matching criteria is configured, the transcript course code must match the formatted course and course code for the substitution to apply.238950
target_course_titlevarchar(100)YesThe course title value for the transcript course that may be substituted. If the course title matching criteria is configured, the transcript course must match the formatted course and title for the substitution to apply.Intro to Psychology
target_credit_hoursnumeric(9,2)YesThe course hours value for the transcript course that may be substituted. If the course hours matching criteria is configured, the transcipt course must match the formatted course and hours for the substitution to apply.3.00
catalog_year_codevarchar(50)YesThe catalog year code to identify the catalog for which the substitution will apply. A null value will apply the substitution to all matching source_formatted_course13-14
View Used: v_external_person_planning_status
Default Table: external_person_planning_status
Description: Defines the status of the student's active plan. Valid values are On and Off. This indicates whether or not the student is on plan to complete the active plan. The requirements and calculation of plan status is done outside of SSP and simply stored in this table. If the student planning status is Off, a reason can be stored in the table. Used in the MAP Tool
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
school_idPrimaryvarchar(50)NoStudent's id or other identifier used by the institution to identify students, faculty, or other staff.uu351764
statusvarchar(8)NoThe status of the active plan. Valid values are On and OffOn and OffMain-Dashboard; Main-Details; MAP-Main Page
status_reasonvarchar(255)YesThe explanation of why the student is Off planFailed last term
View Used: v_external_person_note
Default Table: external_person_note
Description: Notes from any system can be save in the table for display in SSP. Notes can originate from any system and will be displayed to the user in chronological order. Used in the External Notes Tool
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
codePrimaryvarchar(50)NoUnique identifier for the note34090.00
school_idvarchar(50)NoStudent's id or other identifier used by the institution to identify students, faculty, or other staff.uu351764
note_typevarchar(35)NoThe type of note commonly used to define the source systemSIS, CRMMain-Notes
authorvarchar(80)NoThe creator of the note in the external systemSammy JohnsonMain-Notes
departmentvarchar(80)YesThe department of the author who created the noteAdmissionsMain-Notes
date_note_takendateNoThe date the note was created2013-06-28Main-Notes
notetextNoThe note, comment or remarkStudent came in on Monday and…Main-Notes
View Used: v_external_student_risk_indicator
Default Table: external_student_risk_indicator
Description: v2.6.0 introduced a re-formatted Dashboard that includes multiple indicators for students including externaly sourced indicators. This table will hold risk indicators for students based on commercial or institution assessments. Used in the Main Tool
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
school_idPrimaryvarchar(50)NoStudent's id or other identifier used by the institution to identify students, faculty, or other staff.uu351764
model_codePrimaryvarchar(50)NoA namespace for the risk code, used for grouping in the UIPAR
model_namevarchar(100)NoOn screen label for the modelPAR Framework
indicator_codePrimaryvarchar(50)NoUnique indicator codeparT2T
indicator_namevarchar(100)NoOn screen label for the indicatorTerm to TermMain Tool
indicator_valuevarchar(50)NoThe value provided by the risk assessment (text, date, numeric)8
indicator_value_descriptionvarchar(2500)YesText description of the value providedSatisfactory progress in 4 of the last 5 termsMain Tool
View Used: v_external_career_decision_status
Default Table: external_career_decision_status
Description: The Career Decision Status is a display only value to represent a student's current status for career decision. Used in the Main Tool
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
school_idPrimaryvarchar(50)NoStudent's id or other identifier used by the institution to identify students, faculty, or other staff.uu351764Main Tool
codePrimaryvarchar(2)NoA code to represent and match the Career Decision Status configured optionsCPMain Tool
View Used: v_external_student_transcript_non_course
Default Table: external_student_transcript_non_course
Description: Contains non-course records for students that will act as transcripted courses in the MAP status calculation. Used in the Main Tool Transcript Tab. Each row represents an individual non course completion with metrics (grade, credits earned, etc) for a particular student with course granularity (non course code + term code). Because it is used to support the application's MAP tool, it should at least contain records for all currently enrolled students. Periodically pruning this table will not break the application, but is not usually advisable as it may result in unexpected volatility in reports and other read-only application views.
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
school_idPrimaryvarchar(50)NoStudent's id or other identifier used by the institution to identify students, faculty, or other staff.uu351764
target_formatted_coursePrimaryvarchar(35)NoThe non course in a format that would normally be displayed on a transcriptMTH 101Main-Transcript; Main-Schedule; MAP-Transcript
term_codePrimaryvarchar(25)NoSpecifies what term the course was taken by the student and taught by the faculty member. Equivalent to external_term.code value.FA12Main-Transcript; Main-Schedule; MAP-Transcript
non_course_codePrimaryvarchar(50)YesA value that uniquely identifies the non course738957MAP Plan Status Report
titlevarchar(100)YesThe full non course title. Ex - Mathematics 101College AlgebraMain-Transcript; Main-Schedule; MAP-Transcript
descriptionvarchar(2500)YesThe non course description as it exists in the course catalog.Principles of Algebra …
gradevarchar(10)NoGrade earned/awarded in the non course by the student.AMain-Transcript; MAP-Transcript
credit_earneddecimal(9,2)YesCredit hours earned/awarded by the student in the non course.3.00Main-Transcript; Main-Schedule; MAP-Transcript
credit_typevarchar(25)YesIndicates the type of credit earned by the student. Any value is acceptable to represent the type of credit with one condition.Institutional, Transfer, ProficiencyMain-Details; Main-Transcript; MAP-Transcript
status_codevarchar(50)YesDesignator for the student's enrollment status in the non courseEMain-Schedule
View Used: v_external_student_special_service_group
Default Table: external_student_speical_service_group
Description: Special Service Group (SSG) assignments can be set from external data by populating this table. Two configuration options exist to determine how SSP syncs the operational data. Each row is a combination of school_id and SSG code. Used in the Main Tool
Column NameKeyTypeNullableDescriptionExampleSSP Display LocationSIS Column
school_idPrimaryvarchar(50)NoStudent's id or other identifier used by the institution to identify students, faculty, or other staff.uu351764Main Tool
codePrimaryvarchar(50)NoThe code assigned to the SSG definitionDEI1901Main Tool



  • No labels