Click here to skip to the material on installing or updating the ODBC drivers.)
The databases introduced in these training sessions are SISInfo and SISFrzn. The SISInfo database is a reporting database populated with Admissions, Enrollment and Academic History data from the Student Information System (SIS) database. The SISFrzn database also contains Academic History data from the SIS database, but the tables in this database represents data as it looked at various points in the semester. SISFrzn tables are loaded at the end of early enrollment, after the first day of class, at quarter of semester, mid semester, and end of the semester, and for fall semester only, at the end of the Academic Orientation Program (AOP).
Both Microsoft SQL Server databases are housed on a database server called MSUDATA that is administered by Administrative Information Services (AIS). AIS performs all database loading and updating, backups, software maintenance, etc. MSUDATA also houses several other databases that are available to various departments across campus on an as-needed basis. SISInfo and SISFrzn though, are the databases that provide the data that is used most heavily at MSU.
Security
Access to reporting databases is limited to those persons authorized by their academic deans or unit directors. All requests for access are processed through the Client Advocacy Office (CAO) after training is completed. Training in the meaning and use of SIS data is necessary due to its complexity and the many interrelationships between the tables within the databases: SISInfo contains 187 tables and the SISFrzn database contains 129 tables. Once the CAO has approved your request for access to the MSUDATA databases, AIS will assign you a LoginID and password.
Please note that depending on the functions you need to perform, you may be restricted to a subset of the tables that are available within any particular database.
Writing Queries
Data is read from the database through the use of queries written in Structured Query Language, or SQL. Most people use Microsoft Access to build these queries since Access provides a graphical, easy-to-understand way to generate SQL. Some people use Access to create the initial query and then edit the SQL directly to fine-tune it. Training in Microsoft Access is available through the Libraries, Computing and Technology Training Program. Though Microsoft Access will do a lot of the work for you in generating queries, you may find that for really complex queries, you’ll need to know more about how to write or modify SQL. The HELP feature in Microsoft Access can provide valuable information about writing your queries and local bookstores carry books on SQL. There are many resources on the Internet that may be of interest also. Using the word “SQL” as a search argument within the AltaVista search engine returned over one million responses. One such tutorial can be found here>. Other sites may be of use also.
Database Documentation
In addition to the table descriptions for SISInfo that you receive during training, there are two other ways to find this information as you’re building queries. Both methods will show you the same type of information that you find in the printed materials, but you may find it simpler to look up a table definition online as you’re working. The first method is to go to the database documentation web site. At the login screen, click on the “Guest” button. You will then be able to display a list of tables for the database you’re interested in. Then, for each table, you can display all of the column names and definitions that make up that table.
The second method is to directly query the data found in the AATABLES and AACOLUMNS tables on the database you’re writing queries against. As you may guess, AATABLES contains information about each table in the database and AACOLUMNS contains information about individual columns. These tables are used to populate the web pages described above and are available for all reporting databases on the MSUDATA server. Since you’ll be using SQL to display data from these tables, you may customize your selection criteria so that you see only the subset of data from each that you need to write your query. For instance, the query SELECT TABLE_NAME, COLUMN_NAME, SHORT_DESC FROM AACOLUMNS WHERE TABLE_NAME = 'SISPMJR' would yield this result:
| TABLE_NAME | COLUMN_NAME | SHORT_DESC |
|---|---|---|
| SISPMJR | Pid | Person Identification Number |
| SISPMJR | Term_Seq_Id | Term Sequence Code |
| SISPMJR | Student_Level_Code | Student Level |
| SISPMJR | Major_Code | Major Code |
| SISPMJR | Term_Code | Term |
| SISPMJR | Primary_Major_FlaG | Primary Major Flag |
| SISPMJR | Permit_Rgstn_Flag Major | Permits Registration Flag |
| SISPMJR | Primary_Lvl_Flag | Primary Level Flag |
| SISPMJR | Aplcn_Ref_Num | Application Reference Number |
| SISPMJR | Time_Stamp |
Notice that the last column in this table is called Time_Stamp. This column will tell you the last time that the table was updated. Most reporting databases are updated daily and will contain the information found on the mainframe at the end of the previous evening’s processing. During certain busy times, such as during the first few days of a new semester when there is a great deal of activity such as student course drops and adds, the updates may occur after 8:00am. If a report you need to create depends on having the most up-to-date data, you can look at the time_stamp to see if the database has been updated with yesterday’s adds, changes and deletes.
Connecting to the MSUDATA Server
Before connecting to any of the databases for the first time, you must load special programs that allow your computer to communicate with the server. These programs are generally referred to as ODBC drivers. ODBC is an acronym for Open Data Base Connectivity. Once the drivers are installed, you may use any ODBC-compliant software to access the database: Microsoft Access is one example of ODBC-compliant software. Please see the attached pages for instructions on getting your computer ready to write queries.
Where to Go for Help
If you have trouble downloading ODBC drivers or defining the data sources you need or if you’ve forgotten the LoginID and/or password assigned to you, please call the AIS Help and Support Center at 353-4420, ext. 311. The Help Desk will put you in touch with someone on the AIS Data Administration team who can assist you.
If you need help using Microsoft Access to write queries or to create forms or reports, please call the AIS Help and Support Center at the number above. They will have someone contact you who can answer your questions. This will likely be someone from the MSU Computer Lab and there may be a charge for a consultation, depending on the time spent helping you.
Finally, if you have questions about the meaning or proper usage of the data available in a particular reporting database, please contact Rochele Cotter in the Client Advocacy Office at 353-4856.
Instructions for installing ODBC drivers and creating Data Sources
ODBC drivers, referred to as MDAC (Microsoft Data Access Components), are available at the Microsoft Software download website. After downloading from the Microsoft site, be sure to "open" the file that downloaded. This will start the actual installation of the components you'll need. Please note that if your PC's operating system is Windows 95, you will first need to download DCOM for Windows 95 as instructed on the Microsoft site above.
Once your download is complete, you will need to define an ODBC Data Source for each of the databases you will be accessing. Follow the instructions below for creating a Data Source.
(Note: this icon may be labeled “32-Bit ODBC Data Sources”.)
Add or modify data sources under the System DSN tab.
a. Click on the System DSN tab
b. Click the Add button.
This option allows multiple people using the same workstation to use the same data sources.
Choose SQL Server as the driver, then click Finish.
Click in the Name box and type in the name of the data source (in this case, SISInfo).
Click in the Server box and type msudata.ais.msu.edu.
Fill in this window’s information as noted using your own LoginID and password. For this class, you will use the Login ID and password provided by your instructor. Before clicking on the Next button, click the Client Configuration button (VERY IMPORTANT!).
To return to this screen, open the Control Panel; click on the ODBC icon, click on the SYSTEM DSN tab, and select SISINFO.
If you have comments or suggestions about this webpage Email us. (Please do not change the subject line)