SISINFO Reporting

1. “SISINFO” Reporting Presented By: Rochele Cotter, Director Client Advocacy Office, 353-4856, cotterr@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?

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 (Administrator’s Assistant HR) ATHLINFO (Student Athlete Progress Monitoring) Registrar’s 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 GPA’s Example for SISFRZN: Compare aggregate GPA’s 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/PrivacyGuidelines.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., Dean’s 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 level—this 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: Don’t 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 calculation—the 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).

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

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)

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 AOP’s (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 day’s 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; awards—i.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 ID’s) 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 ID’s, 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 ID’s

40. MSU Hierarchy (Simplified) The MSU Board of Trustees are elected officials. They are responsible for the overall management of the institution. The President reports to them. The two primary reports to the President are the Provost, and the Vice President for Operations. While there are some additional units reporting directly to the President, generally, the academic units (e.g., colleges and departments) report to the Provost, and the operational units (e.g., those responsible for the “physical” aspects of the University like Housing and Foods, Physical Plant, etc.) report to the VP for Operations. In SIS you are most likely concerned with the units reporting to the Provost. The MSU Board of Trustees are elected officials. They are responsible for the overall management of the institution. The President reports to them. The two primary reports to the President are the Provost, and the Vice President for Operations. While there are some additional units reporting directly to the President, generally, the academic units (e.g., colleges and departments) report to the Provost, and the operational units (e.g., those responsible for the “physical” aspects of the University like Housing and Foods, Physical Plant, etc.) report to the VP for Operations. In SIS you are most likely concerned with the units reporting to the Provost.

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. In SIS, the College (or “MAU”, noted with a 2 character numeric code) “owns” departments (“Dept” , noted with a three character numeric code). A given department is also able to belong to more than one college.In SIS, the College (or “MAU”, noted with a 2 character numeric code) “owns” departments (“Dept” , noted with a three character numeric code). A given department is also able to belong to more than one college.

42. The College of Business (MAU/college code 08) owns the departments of Information Systems, Finance, Hospitality Business, Management and Marketing.The College of Business (MAU/college code 08) owns the departments of Information Systems, Finance, Hospitality Business, Management and Marketing.

43. A College with “Joint Departments” (because you may run into this when dealing with majors and/or courses) The Department of Physiology is “owned” jointly by multiple colleges: Human Medicine, Natural Science, Osteopathic Medicine.The Department of Physiology is “owned” jointly by multiple colleges: Human Medicine, Natural Science, Osteopathic Medicine.

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. Courses, majors, academic work and many other constructs are organized in SIS by term. The term code contains both the academic period, and the year of the academic period.Courses, majors, academic work and many other constructs are organized in SIS by term. The term code contains both the academic period, and the year of the academic period.

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 won’t 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?

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 Registrar’s 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?

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

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

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.)

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

65. Seniors (class = 4)Enrolled for Summer 06 in College 04 (Arts & Letters)

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

69. PIDPILOT: Pilot Addresses for PIDS Description: The MSUnet ID for matriculated PID’s. Use this in accordance with the MSU policy for sending email to students. Unique Row: PID

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

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

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.

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

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

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

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 Registrar’s 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

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

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

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@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@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