SISINFO Reporting

1
SISINFO Reporting

• Presented By Rochele Cotter, Director
• Client Advocacy Office, 353-4856, cotterr_at_msu.edu

2
Course Outline

• Systems Platform Schematic What IS MSU DATA
  Where Is It?
• MSU Data History
• Security Considerations Authorization
  Requirements
• Types of Tables
• Data Currency
• Explanation of Tables
• Agenda for Part II
• How to Get Help

3
Lesson 1 System Platform Schematic
4
Where IS MSUDATA?
Outside Networks (via MSUnet)
AIS Mainframe SIS, E03, A17, etc. via Host
Explorer
WWW via Explorer, Netscape etc.
MSU DATA via ODBC
LAN
Your hard drive
5
How Do the Data Get There? (ETL)

• Nightly, data are
• Extracted from the mainframe nightly after
  system processing
• Transformed into logically oriented tables
• Loaded into databases that permit connection
  via end user tools
• SISINFO is one of the many databases in MSUDATA.
  It contains data from SIS.

6
SISINFO One of Many SIS Databases (more later as
well)

• SISINFO Most current
• SIS FROZEN Snapshots frozen on key dates
• SIS FULL Entire SIS database, quarterly

7
You are Linking to Data on MSUDATA

• NOT downloading
• NOT importing
• Connecting to them via the ODBC connective
  software

8
Lesson 2 MSUDATA (SISINFO) History
9
MSUDATA (SISINFO) History

• Pre-1992 (advent of SIS)
• Client Based Computing (CBC) mainframe extracts
• Separate extracts for each student data system
  and for other systems
• Dis-integrated data made reporting difficult!

10
Why Was This So Hard?

• Required mainframe access and knowledge of
  mainframe reporting tools
• Dependence on mainframe process load what else
  was going on? (Queries in a queue)
• Complicated logic if more than one system was
  involved
• Dependence on central offices to for local
  reporting needs

11
1992-95 Student Information System (SIS)

• Integrated set of Student Systems
• Admissions, Academic History, Course Catalog,
  Registration, Billing, Financial Aid, etc.
• Wealth of data in ONE system
• Logical design of reporting extracts
• Client Server technology
• Need for a READ ONLY version of the system

12
Other Influencing Factors Circa 1995

• Development of WWW Browsers
• Interest in Web Based applications
• ODBC (Open Data Base Connectivity) software
  widely available and compliant with popular,
  local, database development packages (e.g.,
  ACCESS)

13
Systems Using MSUDATA/SISINFO

• STUINFO (Read only student data for students and
  staff)
• GRASP (Graduate admissions)
• CLIFMS (Course Load Instructors)
• AAHR (Administrators Assistant HR)
• ATHLINFO (Student Athlete Progress Monitoring)
• Registrars Office Forms

14
MSU Data Expands

• Client base initially started with about 15 prior
  SIS Client Based Computing (CBC) has expanded
  into hundreds

15
Enhancements Over Time

• Content level security to limit access to certain
  types of students (SISINFO)
• Frozen files for snapshots and historical
  comparisons (SIS FROZEN and SISFULL)
• More and faster hardware
• Separation of data tables
• MSU_______ ALL MSU Students
• SIS________ ALL Students (MSU Law)
• DCL________ LAW Students Only

16
Uses for SISINFO

• Example for SISINFO
• Create a local database to monitor unit data
  elements in light of SIS data Monitor State
  certification exam results in light of GPAs
• Example for SISFRZN
• Compare aggregate GPAs in a given major
• Example for SISFULL
• Pull a list of all students (1968 and forward)
  who have graduated from your college

17
Lesson 3 Security Considerations
18
A Different Environment You have control---and
responsibility!

• First and foremost These are Sensitive Data
  and most are confidential!!! (MSD website
• http//lct.msu.edu/security/ RO FERPA website
  http//reg.msu.edu/ROInfo/Notices/PrivacyGuideline
  s.asp)
• Password protect your computer
• Avoid storing data on your hard drive
• Make-Table data stored on a server where only
  YOU have access
• Providing data to others Principle of
  non-disclosure to third parties
• Secondary systems
• Student databases that give universal access
  (access to all students regardless of major)
  require special (i.e., Deans office) clearance

19
Authorization Requirements

• Approval required from Assistant Dean of College,
  Unit Director
• Forms signed by Unit Security Administrator,
  routed to Administrative Information Services
  (AIS)
• Understand the database application (e.g., MS
  ACCESS) at more than an introductory levelthis
  is key!

20
Lesson 4 Types of Tables
21
Types of Tables

• Two types
• Data Tables
• Support Tables

22
General Data Organization Principles

• SISINFO is a relational database composed of
  tables constructed with these principles
• Dont duplicate information when not required
  (e.g., NAME only in 2 tables)
• Use coded values whenever possible (for
  consistency, efficiency)
• Preserve historical information
• Generally raw (non-derived) and disaggregated
  data (means you may have to calculate fields)
• Identify cross functional relationships of data
  (a course is something a student enrolls in AND
  is the basis for tuition calculationthe
  student/course relationship is maintained once
  but has multiple purposes)
• Concepts of unique row of data, index, key

23
Types of Tables Data Tables

• Contain the extracted data about students,
  entities, and courses. NOTE entities are
  third party groups affiliated with MSU to which
  we associate students. The most prevalent
  entities are the post secondary and secondary
  institutions from which our students come to MSU.
• Many/most values are coded
• Occurrences based on the current terms being
  extracted (more later on this).

24
Data Table (from ___PLVT Person Level Term
Query)
25
Types of Tables Support Tables

• Used to help refine query criteria
• To decode the many code values that make up the
  data tables

26
Support Table Example
dbo_AWRDSTAT
27
Often You Need Both for Understandable Reports

• Neither raw data nor support tables are useful on
  their own (unless you are a power user)

___PLVT Query (Person Level Term Data)
REGSTAT (Registration Status Code Support)
28
Lesson 5 Data Currency
29
How current is CURRENT?

• CURRENT (daily SISINFO Files)
• Students who have enrolled for last 2 complete
  academic yrs (and as much of the current/future
  as is available)
• SO, what is available now (July 06)?
• 2006-07 (students who have enrolled for Fall and
  Spring 06-07)
• 2005-06 Academic Year and Summer 06
• 2004-05 Academic Year and Summer 05
• Parameter Academic Year 04-05
• In August parameter year will be increased by an
  increment of one year (so 04-05 data will not
  be available)

30
Frozen? When?

• Frozen Data (snapshots)
• End of early enrollment
• Post AOPs (for Fall)
• Day before first day
• Quarter of term (official counts, official degree
  data from previous term)
• Middle of term
• End of term
• SISFULL (all student academic history back to
  1968, updated each quarter)

31
When to Use Which?

• To capture daily changes in data, most accurate
  picture TODAY CURRENT
• To document comparisons in data from like periods
  (quarter of term one year to same quarter of term
  in others) FROZEN

32
When Using Current Tables

• How to determine if tables have been updated on a
  given day?
• Go to AA TABLES table in SISINFO
• Select the table you want (identified from the
  query you are running)
• Go to the Last_Updated field
• Check the date and time (updates of the previous
  days data are usually completed by noon of the
  following day)

33
Lesson 6 Explanation of Tables
34
Hundreds of Tables Documentation/Help?

• Complete documentation available
• http//ntweb11.ais.msu.edu/J9700/dbdoc/
• Print this presentation (train.msu.edu) and use
  this documentation to follow along with me as I
  review key tables

35
SISINFO Tables Subjects and Categories

• Major table subjects and categories
• Persons (students, prospects, applicants,
  affiliates) their admission applications (and
  credentials) majors awardsi.e., degrees
  courses grades past institutions demographic
  data
• Courses sections, schedules, buildings, grading
  options, equipment in classrooms
• Entities (post secondary and secondary
  institutions from which students come to MSU)
  names addresses type (high school, college or
  university, proprietary)

36
Important Person Tables

• MSUPAWD (awards degrees)
• MSUPCRS (MSU courses grades)
• MSUPLVT (enrollment registration by level by
  term)
• PIDPILOT (MSUnet IDs)
• SISPADR_Local or SISPADR_Perm (addresses)
• SISPMJR (majors)
• SISPRSN (demographic)

37
Important Course (and Section) Tables

• SISCRSV (courses, course college and department)
• SISCRSD (course descriptions)
• SISCRSI (section instructors)
• SISCRSM (course section meeting times, building
  and room location)
• SISCRSS (course section detail

38
Important Entity Tables

• SISENTY (entity IDs, categories-H,2,4,O)
• SISEADR (entity addresses)

39
Lesson 6 A Brief Diversion

• OR, how colleges and departments are organized at
  MSU term codes and term sequence IDs

40
MSU Hierarchy (Simplified)
41
Organization Structure (OS Module in SIS)

• Colleges
• Departments
• Colleges and Departments are key attributes of
  majors and courses.
• Sometimes, a department may belong to more than
  one college.

42
(No Transcript)
43
A College with Joint Departments (because you
may run into this when dealing with majors and/or
courses)
44
What is the Convention for Identifying TERMS?

• First two characters identify the academic
  period. FALL SEMESTER FS SPRING SEMESTER
  SS SUMMER SEMESTER US.
• OR, if prior to fall 1992, FALL QUARTER FQ
  WINTER QUARTER WQ SPRING QUARTER SQ SUMMER
  QUARTER UQ.
• Last two characters identify the year of the
  academic period FS06 refers to Fall Semester
  2006 US06 refers to Summer Semester 2006 WQ91
  refers to Winter Quarter 1991.

45
In SISINFO the TERM SEQUENCE ID (term_seq_id)
Is Also Important

• The TERM code is alpha/numeric
• The Term Sequence ID is its NUMERIC equivalent
• When selecting records from multiple terms, if
  you want a correct sort, the term code wont
  work
• FS99 FS00
• SS00 SORTS SS00
• US00 US00
• FS00 SS01
• SS01 FS99

46
How Does the Term_Seq_ID Work to Create the
Correct Chronology?
First character academic year century
delimiter (01900s 12000s) Second Third
character academic year delimiter (1999-00 or
2000-01 in this example) Fourth character term
(2summer 4fall 6 spring)
47
Lesson 6 End of Diversion

• Back to Tables

48
Format for Reviewing Specific Tables

• 1) Description of table, what data and records
  are contained in it
• 2) Attributes (data elements), emphasizing
  important ones, and keys
• 3) Example of what you might do with this table
  (using a query)
• 4) Important support table(s) that will make your
  results more useable
• 5) Those listed at beginning of this lesson will
  be covered

49
MSUPAWD Person Award Records for MSU (non law)
Students

• Description One record exists for each award
  belonging to a student (MSU only). Each record
  includes the student's level, intended award
  term, major, and department. The student must
  have been registered in the past two years, and
  have an award.
• Remember (from SIS BASICS) An award is the end
  result of a set of courses that combine to form a
  major, minor, specialization, certificate
  program, degree program, etc.
• Unique row PID, Award_Seq_ID, Student_Level_Code,
  Intended_Award_Term

50
MSUPAWD and Degree Certification

• Registrars Office final listings only
  completely accurate AFTER QUARTER OF TERM for the
  SUCCEEDING TERM!
• Example Spring degrees are only completely
  accurate after quarter of term for summer (June
  12-15, usually).

51
MSUPAWD Data Elements
52
Who Graduated from ___ College this Spring?
53
MSUPAWD, Students Graduating in college of
Natural Science, spring 06
54
Support Tables to Help

• Majormnt Attributes of Majors (in this case the
  COLLEGE code was used in the query). But, the
  title of the major would also help
• Awardstat Award Status Codes (and conferral
  flags Y). Doing this changes the outcome of
  the data! We also get recommended teaching
  degrees.
• Acadlevl Description

55
Spring 06 Conferred Degrees (sample data decoded)
56
MSUPCRS Person Course Records for MSU (non law)
Students

• Description A complete course history exists for
  each student (MSU only) registered in the past
  two years. A course is identified by term,
  academic level, subject, course and section
  codes.
• Unique Row PID, Term_Seq_ID, Student_Level_Code,
  Subj_Code, Crse_Code, Sctn_Code

57
MSUPCRS Data Elements
58
How Many Students Received an I Grade in
Courses in College 32 (Nat Sci) for SS06 (sample
data2 step query)?
59
I Grades, College of Natural Science, SS06
(sample data)
60
Support Tables to Help with MSUPCRS

• ACADLVL For the definition of the student level
  code
• SUBJECTS For the definition of the subjects
• SISCRSV Course Versions, not really a support
  table, but required to get the COLLEGE of the
  course. (Also need end_term_sequence_ ID,
  because of multiple versions.)

61
SS06 I Grades in Nat Sci, Decoded (sample data)
62
MSUPLVT Person Level Term Records for MSU (non
law) students

• Description A complete level/term history exists
  for each student (MSU only) for each level in
  which the student enrolled in the past two years.
  Each record represents one level/term combination
  and contains credit summary information, as well
  as, academic status at the beginning and ending
  of that term.
• One of the most important tables, it tells you
  who is/was here at a level, in a term also
  level credit totals for the term and cumulative
• IMPORTANT CONCEPTS FROM SIS BASICS Multiple
  Levels, Multiple Majors, Primary Level, Primary
  Major

63
Other important items

• Unique Row PID, Term-Seq_ID, Student_Level_Code

64
MSUPLVT Data Elements
65
Seniors (class 4)Enrolled for Summer 06 in
College 04 (Arts Letters)
66
Seniors in Arts and Letters Summer 06
67
Support Tables to Help with MSUPLVT

• REGSTAT Registration Status (to understand the
  system registration status codes used in the
  query)
• STDNTCLS Student Class Codes (to select the
  class code that denotes senior)
• MAJORMNT To select students with primary majors
  in Arts and Letters

68
Seniors in Arts and Letters US06 Decoded (Sample
Data)
69
PIDPILOT Pilot Addresses for PIDS

• Description The MSUnet ID for matriculated
  PIDs. Use this in accordance with the MSU policy
  for sending email to students.
• Unique Row PID

70
(No Transcript)
71
SISPADR_LOCAL and PERM

• Description This is a view of SISPADR that
  finds the BEST, OPEN local and permanent address
  for each student.
• Unique Row PID, Adr_Type_Code, End_Date

72
SISPADR_LOCAL AND PERMData Elements
73
Find Local Addresses for Seniors in Arts
Letters who are here this summer
74
Seniors in Arts and Letters Local Addresses Note
Permanent Mailing Types in the Local Query
75
Find Permanent Addresses for seniors in Arts
Letters who are here this summer
76
Seniors in Arts and Letters PERM Addresses
(sample data)
77
SISPMJR Person Major

• Description One record for every major active in
  every term at every level. Used to find ALL
  active majors for a student for a term at all
  levels. (Not just primary)
• Unique Row PID, Term_Seq_ID, Student_Level_Code,
  Major_Code

78
SISPMJR, data elements
79
How many TOTAL Marketing Majors did we have
registered for SS06?
80
SISPMJR used to find ALL majors (not just primary)

• MSUPLVT will find the primary major (for each
  level in which the student has enrolled for a
  term)
• Remember Students may have second majors, or
  second degree majors the honors college major
  concentrations minors as well.

81
A query of the MAJORMNT support table to find the
undergraduate primary majors in Marketing
yields 2 primary major codes, 1237 and 1328
82
But, there are other ways to achieve these two
undergraduate majors, as second majors, and
second degrees. For Marketing and Supply Chain
Management, these major codes are 1238, 1239 and
1329, 1330.
83
The difference between PLVT and PMJR

• For SS06 there were 1,495 students with marketing
  primary majors (using MSUPLVT)
• Using SISPMJR, for SS06 there were a total of
  1,529 students with marketing majors
• The difference is due to second majors and second
  degree majors showing up in SISPMJR

84
The SISPMJR query used to find all Marketing
Majors for SS06
85
SISPRSN (A) Person Information

• Description One record exists for every student
  associated with MSU in the past two years. Each
  record contains personal information on a
  particular student including name, residency,
  birth date and citizenship. The A view does
  NOT contain SSN.
• Unique Row PID

86
SISPRSN Data Elements
87
All students enrolled SS06 from Japan (Alumni
Office wants to contact them prior to trip)
88
Students enrolled SS06 from Japan (sample data)
NOTE Restriction Flags
89
Useful Support Tables

• Country Codes
• Address Types

90
SISCRSV Course Versions

• Description All course versions defined to SIS
  are included in this table. This means that one
  record exists for each combination of subject,
  course, and ending term sequence ID. The course
  title, reenrollment credit information, status,
  and related college/department codes are included
  .
• Unique Row Subj_Code, Crse_Code, End_Term_Seq_ID

91
SISCRSV data elements
92
Find the Currently Active Courses in Marketing
93
Active Courses in Marketing Dept (sample data)
94
Helpful Support Tables

• Subjects
• Acadlvl

95
SISCRSD Course Descriptions

• Description Contains all course descriptions as
  displayed in the Description of Courses. This
  data does not come from SIS but the Registrars
  Office Courses system. This table holds
  information such as the course description,
  restrictions, recommended background and
  prerrequisites.
• Unique Row Subj_Code, Crse_Code,
  Start_Term_Seq_ID, End_Term_Seq_ID

96
SISCRSD data elements
Query1
97
Getting the Course Description from SISCRSD
98
Query1
99
Use 3 Tables to Gather Information about Course
Sections

• Description SISCRSS (course section detail) All
  course sections are included in this table. This
  means that for every subject, course, section
  term sequence ID and section code combination,
  there exists one record containing information
  related to that specific course. Meeting
  location, status code, enrollment and capacity
  quantities are included
• Description SISCRSM (course section meeting
  times, building and room location) One record
  exists for every subject, course, term, section,
  day code, beginning time, building code and room
  number combination. Information specific to the
  meeting time for the sections of a course offered
  during a given term, including accessibility is
  included.
• Description SISCRSI (section instructors)
  SISCRSI is a companion table to SISCRSS and
  contains the Instructor Name, ZPID, and MSUnetid
  for any instructor who is connected with a
  section in the CLIFMS system. This data
  therefore does NOT come from the SIS mainframe
  system.

100
SISCRSS data elements
101
SISCRSM data elements
102
SISCRSI data elements
Note TA Confidential
103
A fairly complex query using all 3 tables
104
From these tables we have determined the schedule
for marketing courses for SS06 when the courses
were offered, where, and by whom they were taught.
105
Helpful Support Tables BLDGMNT
106
SISENTY and SISEADR

• Description SISENTY One record exists for every
  high school or post secondary school attended by
  any student associated with MSU. Entities are
  known by their ENTITY ID, and each has a category
  code of H (if high school), 2 (if community
  college or 2 yr technical school), 4 (if 4 year
  or more institution, or professional school).
• Description SISEADR One record exists for every
  entity address type maintained for each entity.
  Generally these are permanent mailing addresses.

107
SISENTY Data Elements
108
SISEADR Data Elements
109
Use SISENTY and SISEADR to find all Community
Colleges in Michigan
110
SISENTY and SISEADR sample data for community
colleges in Michigan
111
End of Table Explanation
112
Need More?

• SISINFO Part II is a hands-on class that covers
  the actual use of SISINFO data
• Sign up (with supervisor permission) at
  http//train.msu.edu

113
Agenda Part II Using SISINFO Data

• Grace Rodriguez, Administrative Information
  Services, 353-4420, rodrig16_at_msu.edu
• Creating links to SISINFO and SISFRZN tables
• Using a Make-Table Query
• Creating Sample Queries
• Creating Labels Using Label Wizard
• Using a Start-up macro to update make-table query
• Using queries to summarize data
• Using your access data with MS WORD

114
HELP????

• Understanding data, or appropriate authorization
  Client Advocacy Office, 353-4856
  (cotterr_at_msu.edu)
• Connecting to SISINFO using ODBC AIS Help and
  Support, 353-4420 ext. 311
• Using SISINFO with MS Office Grace Rodriguez,
  AIS, 353-4420

115
Thank you for watching!

• LCTTP Training Team
• http//train.msu.edu