Skip to main content
EMS Software, LLC

Colleague Unidata Views Data Model

EMS Software will code a set of views specific to your organization’s use of Unidata to allow for integration between EMS and Unidata. Outlined are the baseline tables/columns accessed in the views.

You will be provided a Campus Technical Environment Questionnaire which allows for identification of specific fields used by EMS that tend to vary in individual Unidata implementations. Following return of the questionnaire, EMS Views will be coded for your organization and a final review done in EMS. Your Implementation Consultant can assist in more detailed explanations or demonstrations of how the information will be used in EMS to support the Academic Scheduling process, as needed for completion of the questionnaire.  Once reviewed and accepted, further view modifications will be subject to EMS Professional Services time and materials fees.

Note: Optionally and on a custom basis, up to 10 User Defined Fields can be added to the new SQL compliant dictionaries and EMS_vwCourses view for each organization to support desired workflow. Detailed business requirements are needed to provide a quote for customization of the views for this purpose. 

In order to build the EMS views the following are required:

  1. The VSG (Visual Schema Generator), an executable that is shipped with Unidata, must be installed on a computer that can access Unidata. If you cannot locate the .msi installer for this tool, EMS Software can provide a copy.

    1. The purpose of the VSG is to create SQL compliant dictionary files based on the dictionary files that contain data that EMS requires. Once SQL compliant dictionaries are created by the VSG the final EMS views can be created and permissions granted via the VSG.

  2. The 32-bit IBM Unidata ODBC driver must be installed in order for the VSG to connect to Unidata. If you cannot locate this installer, EMS Software can provide a copy.

Modification of SQL compliant dictionaries and views by the customer is unsupported and voids the Annual Service Agreement for the integration with Colleague Unidata.

Fields highlighted in BLUE are required to be unique.

New Unidata Dictionary Legend:
               BLACK – Fields generated from VSG – no AE Editor changes required.
               GREEN – New fields that must be added via the AE Editor.
               RED – Required changes via the AE Editor.

Buildings

EMS View: EMS_vwBuildings

View Permissions: Read Only

Unidata Dictionary: BUILDINGS
New Dictionary: BUILDINGS_EMS
 

@ID............
TYP
LOC..........
CONV
NAME...........
FORMAT
SM
ASSOC.....

BLDG_DESC

D

1

 

Description

30L

S

 

ID

D

0

 

BUILDINGS

10L

S

 

ID0

D

0

 

BUILDINGS_EMS

10L

S

 


Purpose: Used to define buildings list in EMS. Required for buildings that host academic courses; additional buildings can be added manually to EMS if used only for non-academic purposes.

 

EMS Field

BUILDINGS_EMS Field

SISID

ID

BuildingCode

ID

BuildingDescription

BLDG_DESC

Room Types

EMS View: EMS_vwRoomTypes

View Permissions: Read Only

Unidata Dictionary: ROOM.TYPES
New Dictionary: ROOM_TYPES_EMS
 

@ID............
TYP
LOC..........
CONV
NAME...........
FORMAT
SM
ASSOC.....

ID

D

0

 

ROOM.TYPES

10L

S

 

ID0

D

0

 

ROOM_TYPES_EMS

10L

S

 

RMTP_DESCRIPTION

D

1

 

Description

30L

S

 

 

Purpose: Used to define room type list in EMS. Room Types classify each room and allows for searching, sorting, and reporting by classification. Additional room types can be added to EMS manually.
 

EMS Field

ROOM_TYPES_EMS Field

SISID

ID

RoomTypeDescription

RMTP_DESCRIPTION

Rooms

EMS View: EMS_vwRooms

View Permissions: Read Only

Unidata Dictionary: ROOMS
New Dictionary: ROOMS_EMS (Requires modifications via AE Editor)
 

@ID............
TYP
LOC..........
CONV
NAME...........
FORMAT
SM
ASSOC.....

ID

D

0

 

ROOMS

10L

S

 

ID0

D

0

 

ROOMS_EMS

10L

S

 

ROOM_NAME

D

1

 

Description

30L

S

 

ROOM_CAPACITY

D

2

MD0,

Capacity

7R

S

 

ROOM_TYPE

D

4

 

Room Type

20L

S

 

ROOMS_BLDG_ID

I

FIELD(@ID,”*”,1)

 

Building

4L

S

 

ROOM_CAPACITY_1

I

IF ROOM_CAPACITY=’’ THEN ‘30’ ELSE ROOM_CAPACITY

 

 

7R

S

 

ROOM_CODE

I

FIELD(@ID,”*”,2)

 

 

30L

S

 

ROOM_TYPE_1

I

IF ROOM_TYPE = ‘’ THEN ‘0’ ELSE ROOM_TYPE

 

 

20L

S

 

 

Purpose: Used to define list of rooms and their capacities for academic purposes in EMS; related to Buildings and Room Types. Required for rooms that host academic courses; additional rooms can be added manually to EMS if used only for non-academic purposes.

 

EMS Field

ROOMS_EMS Field

SISID

ID

SISBuildingID

ROOMS_BLDG_ID

SISRoomTypeID

ROOM_TYPE_1

RoomCode

ROOM_CODE

RoomDescription

ROOM_NAME

Capacity

ROOM_CAPACITY_1

Instructors

EMS View: EMS_vwInstructors

View Permissions: Read Only

Unidata Dictionary: FACULTY
New Dictionary: FACULTY_EMS (Requires modifications via AE Editor)
 

@ID............
TYP
LOC..........
CONV
NAME...........
FORMAT
SM
ASSOC.....

ID

D

0

 

FACULTY

10L

S

 

ID0

D

0

 

FACULTY_EMS

10L

S

 

FAC_FIRST_NAME

I

TRANS(“PERSON”,@ID,”FIRST.NAME”,”X”)

 

Faculty First Name

35L

S

 

FAC_FULL_NAME

I

FAC_LAST_NAME:’, ‘:FAC_FIRST_NAME

 

 

35L

S

 

FAC_LAST_NAME

I

TRANS(“PERSON”,@ID,”LAST.NAME”,”X”)

 

Faculty Last Name

35L

S

 

Purpose: Used to define list of instructors in EMS; related to Courses.

EMS Field

FACULTY_EMS Field

SISID

ID

InstructorDescription

FAC_FULL_NAME

Terms

EMS View: EMS_vwTerms

View Permissions: Read Only

Unidata Dictionary: TERMS
New Dictionary: TERMS_EMS (Requires modifications via AE Editor)

@ID............
TYP
LOC..........
CONV
NAME...........
FORMAT
SM
ASSOC.....

ID

D

0

 

TERMS

10L

S

 

ID0

D

0

 

TERMS_EMS

10L

S

 

TERM_DESC

D

2

 

Description

30L

S

 

TERM_START_DATE

D

5

D2/MDY

Start Date

8R

S

 

TERM_END_DATE

D

6

D2/MDY

End Date

8R

S

 

Purpose: Used to define list of terms in EMS; related to Courses.  In conjunction with Domains, terms separate courses into distinct lists that will go through the academic planning process (synching, optimizing, publishing, reporting) as a unit.

EMS Field

TERMS_EMS Field

SISID

ID

TermCode

ID

TermDescription

TERM_DESC

StartDate

TERM_START_DATE

EndDate

TERM_END_DATE

Subjects (no Campus Codes)

EMS View: EMS_vwSubjects

View Permissions: Read Only

Unidata Dictionary: SUBJECTS
New Dictionary: SUBJECTS_EMS (Requires modifications via AE Editor)
 

@ID............
TYP
LOC..........
CONV
NAME...........
FORMAT
SM
ASSOC.....

ID

D

0

 

SUBJECTS

10L

S

 

ID0

D

0

 

SUBJECTS_EMS

10L

S

 

SUBJ_DESC

D

1

 

Description

30L

S

 

CAMPUS_CODE

I

‘’

 

 

30L

S

 

DIVISION_CODE

I

‘’

 

 

30L

S

 

DEPT_CODE

I

‘’

 

 

30L

S

 

LEVEL_CODE

I

‘’

 

 

30L

S

 

CUSTOMCODE1

I

‘’

 

 

30L

S

 

CUSTOMCODE2

I

‘’

 

 

30L

S

 

Purpose: Used to define list of subjects in EMS. Within EMS, subjects are divided into Domains in order to create distinct lists of courses per term that will go through the academic planning process (synching, optimizing, publishing, reporting) as a unit, managed by the academic central scheduler.

EMS Field

SUBJECTS_EMS Field

SISID

ID

SubjectCode

ID

SubjectDescription

SUBJ_DESC

CampusCode

NULL

DivisionCode

NULL

DepartmentCode

NULL

LevelCode

NULL

CustomCode1

NULL

CustomCode2

NULL

Subjects (with Campus Codes)

EMS View: EMS_vwSubjects

Unidata Dictionary: COURSE.SECTIONS
New Dictionary: COURSE_SECTIONS_EMS (See Courses Below)

Course Types

EMS View: EMS_vwCourseTypes

View Permissions: Read Only

Unidata Dictionary: INSTR.METHODS
New Dictionary: INSTR_METHODS_EMS
 

@ID............
TYP
LOC..........
CONV
NAME...........
FORMAT
SM
ASSOC.....

ID

D

0

 

INSTR.METHODS

10L

S

 

ID0

D

0

 

INSTR_METHODS_EMS

10L

S

 

INM_DESC

D

1

 

Description

30L

S

 

Purpose: Used to define the course type list in EMS; related to coursedates. Course Types are used to classify coursedates for purposes of searching, sorting, optimizing, and reporting.

EMS Field

INSTR_METHODS_EMS Field

SISID

ID

Description

INM_DESC

Courses

EMS View: EMS_vwCourses

View Permissions: Read Only

Unidata Dictionary: COURSE.SECTIONS
New Dictionary: COURSE_SECTIONS_EMS

@ID............
TYP
LOC..........
CONV
NAME...........

FORMAT

SM

ASSOC.....

ID

D

0

 

COURSE.SECTIONS

21L

S

 

ID0

D

0

 

COURSE_SECTIONS_EMS

21L

S

 

SEC_TERM

D

15

 

Term

35L

S

 

SEC_SUBJECT

D

6

 

Subject

40L

S

 

SEC_COURSE_NO

D

17

 

Course Number

7L

S

 

SEC_NO

D

18

 

Section

5L

S

 

SEC_SHORT_TITLE

D

1

 

Short Title

30L

S

 

SEC_FACULTY

D

40

 

Faculty

10L

S

 

SEC_CAPACITY

D

5

MD0,

Capacity

5R

S

 

SEC_MIN_CRED

D

8

MD25

Min Cred

8R

S

 

SEC_XLIST

D

32

 

XList

10L

S

 

CAMPUS_CODE

I

‘’

 

(This record required when NOT using Campus Codes. Otherwise do not include this record.)

40L

S

 

DIVISION_CODE

I

‘’

 

 

40L

S

 

DEPT_CODE

I

‘’

 

 

40L

S

 

LEVEL_CODE

I

‘’

 

 

40L

S

 

CUSTOMCODE1

I

‘’

 

 

40L

S

 

CUSTOMCODE2

I

‘’

 

 

40L

S

 

SEC_ACTIVE_STUDENT_COUNT

I

IF SEC_ACTIVE_STUDENTS AND SEC_ACTIVE_STUDENTS NE "0" THEN (((COUNT(SEC_ACT

IVE_STUDENTS,@VM)) + (1))) ELSE (0) 

MD0,

Active Student Count

6R

S

 

CSXL_PRIMARY_SECTION

I

TRANS(COURSE.SEC.XLISTS,SEC_XLIST,’CSXL.PRIMARY.SECTION’,’X’);IF @=ID THEN ‘0’ ELSE IF @=’’ THEN ‘0’ ELSE @

 

Primary Section

21L

S

 

SEC_FIRST_FACULTY

I

TRANS(“COURSE.SEC.FACULTY”, SEC_FACULTY, “CSF.FACULTY”,”X1”);IF @=’’ THEN ‘0’ ELSE @

 

Faculty

10L

S

 

SEC_CURRENT_STATUS

I

FIELD(SEC_STATUS,@VM,1)

 

Current Status

5L

S

 

SEC_STATUS

D

88

 

Status

30L

S

 

SEC_SYNONYM

D

61

 

Synonym

11L

S

 

SEC_ACTIVE_STUDENTS

D

66

 

 

6R

S

 

SEC_SUBJ_DESC

I

TRANS(“SUBJECTS”, SEC_SUBJECT, “SUBJ.DESC”, “X1”)

 

Subject Description (This record required when using Campus Codes)

30L

S

 

SEC_LOCATION

D

6

 

Location

(This record required when using Campus Codes)

40L

S

 

Purpose: Used to define the list of courses per term; related to subjects, instructors, terms.

EMS Field

COURSE_SECTIONS_EMS Field

SISID

ID

SISTermID

SEC_TERM

SISSubjectID

SEC_SUBJECT

CampusCode

NULL (or SEC_LOCATION if using Campus Codes)

DepartmentCode

NULL

LevelCode

NULL

CustomCode1

NULL

CustomCode2

NULL

CRN

SEC_SYNONYM

Course

SEC_COURSE

Section

SEC_NO

CourseTitle

SEC_SHORT_TITLE

SISInstructorID

SEC_FIRST_FACULTY

EstimatedEnrollment

SEC_CAPACITY

CrosslistParentID

CSXL_PRIMARY_SECTION

ActualEnrollment

SEC_ACTIVE_STUDENT_COUNT

CreditHours

SEC_MIN_CRED

Subjects with Campus Codes (distinct values)

EMS Field

COURSE_SECTIONS_EMS Field

SISID

SEC_SUBJECT

SubjectCode

SEC_SUBJECT

SubjectDescription

SEC_LOCATION

CampusCode

NULL

DivisionCode

NULL

DepartmentCode

NULL

LevelCode

NULL

CustomCode1

NULL

CustomCode2

NULL

Course Dates
EMS View
: EMS_vwCourseDates

View Permissions: Read Only

Unidata Dictionary: COUSRE.SEC.MEETING
New Dictionary: COURSE_SEC_MEETING_EMS
New Dictionary Permissions: Update if allowing EMS to update room locations in Unidata

@ID............
TYP
LOC..........
CONV
NAME...........

FORMAT

SM

ASSOC.....

ID

D

0

 

COURSE.SEC.MEETING

10L

S

 

ID0

D

0

 

COURSE_SEC_MEETING_EMS

10L

S

 

CSM_SEC_TERM

I

TRANS(“COURSE_SECTIONS_EMS”,CSM_COURSE_SECTION,”SEC_TERM”,”X”)

 

Term

7L

S

 

CSM_COURSE_SECTION

D

1

 

Section

21L

S

 

CSM_START_DATE

D

2

D2/MDY

Start Date

8R

S

 

CSM_END_DATE

D

3

D2/MDY

End Date

8R

S

 

CSM_START_TIME

D

8

MT

Start Time

8R

S

 

CSM_END_TIME

D

9

MT

End Time

8R

S

 

CSM_BLDG*

D

4

 

Building

35L

S

 

CSM_ROOM*

D

5

 

Room

35L

S

 

CSM_SUNDAY

D

20

 

(this must be blank)

1L

S

 

CSM_MONDAY

D

14

 

(this must be blank)

1L

S

 

CSM_TUESDAY

D

15

 

(this must be blank)

1L

S

 

CSM_WEDNESDAY

D

16

 

(this must be blank)

1L

S

 

CSM_THURSDAY

D

17

 

(this must be blank)

1L

S

 

CSM_FRIDAY

D

18

 

(this must be blank)

1L

S

 

CSM_SATURDAY

D

19

 

(this must be blank)

1L

S

 

CSM_INSTR_METHOD

D

6

 

Instr Method

35L

S

 

ROOM_ID

I

IF CSM_ROOM=’’ THEN ‘’ ELSE CSM_BLDG:’*’:CSM_ROOM

 

 

35L

S

 

SUNDAY

I

IF CSM_SUNDAY=’Y’ THEN ‘1’ ELSE ‘0’

 

Sunday (this is required)

1L

S

 

MONDAY

I

IF CSM_MONDAY=’Y’ THEN ‘1’ ELSE ‘0’

 

Monday (this is required)

1L

S

 

TUESDAY

I

IF CSM_TUESDAY=’Y’ THEN ‘1’ ELSE ‘0’

 

Tuesday (this is required)

1L

S

 

WEDNESDAY

I

IF CSM_WEDNESDAY=’Y’ THEN ‘1’ ELSE ‘0’

 

Wednesday (this is required)

1L

S

 

THURSDAY

I

IF CSM_THURSDAY=’Y’ THEN ‘1’ ELSE ‘0’

 

Thursday (this is required)

1L

S

 

FRIDAY

I

IF CSM_FRIDAY=’Y’ THEN ‘1’ ELSE ‘0’

 

Friday (this is required)

1L

S

 

SATURDAY

I

IF CSM_SATURDAY=’Y’ THEN ‘1’ ELSE ‘0’

 

Saturday (this is required)

1L

S

 

*field used for updating room assignments

Purpose: Used to define the list of meeting patterns per course; related to courses, coursetypes, buildings, rooms.

EMS Field

COURSE_SEC_MEETING_EMS Field

SISID

ID

SISTermID

CSM_SEC_TERM

SISCourseID

CSM_COURSE_SECTION

StartDate

CSM_START_DATE

EndDate

CSM_END_DATE

StartTime

CSM_START_TIME

EndTime

CSM_END_TIME

SISBuildingID

CSM_BLDG

SISRoomID

ROOM_ID

RoomID

CSM_ROOM

Sunday

SUNDAY

Monday

MONDY

Tuesday

TUESDAY

Wednesday

WEDNESDAY

Thursday

THURSDAY

Friday

FRIDAY

Saturday

SATURDAY

SISCourseTypeID

CSM_INSTR_METHOD