Data Extraction and Automation:
Overview: The Little SIS interface for selecting and mapping source CSV data was designed to provide a very flexible approach, allowing you to map, and even join your data within the tool, thereby reducing the amount of data transformation you must do upstream.
Basic file requirements:
- File name(s) must remain consistent (e.g. automation should overwrite or replace the file on update)
- First row must contain unique headers
- Data must be comma separated
- A newline separator must exist at the end of each record
- To ensure no issues with comma field delimiting, double quotes should be used around any values where commas may occur in the data, such as Class Title.
- Fields such as Class Alias should NOT be concatenated in your export. The Little SIS interface includes the ability to build concatenated fields, and having the individual terms available for rule-building will prove beneficial.
The primary data export file required by Little SIS must contain one record per student per class - e.g. student enrollment records. Ideally, once joined with class, teacher, and school information, each enrollment record in the file will contain the following source data fields (header names may vary in your SiS and in the CSV export itself)
- Class Alias (sub-fields) - required, leave as separate fields in export... these will be combined within Little SIS to create a unique string per class group)
- School ID
- Course ID
- Section ID or Section Number
- Year / Semester ID
- Class Name or Title - required, used to populate Classroom interface
- Section Number - optional, used to populate Classroom interface
- Period - optional but nice to have, used to populate Classroom interface
- Room - optional but nice to have, used to populate Classroom interface
- Class Description - optional, used to populate Classroom interface
- Student ID - required, used for display purposes in Little SIS
- School Name or ID - required, used for display purposes in Little SIS
- Student G Suite Email - required, must be primary email address (not an alias)
- Teacher G Suite Email - required, this is the primary teacher of the course. Co-teachers are not supported by Little SIS*, but they can be added at will by teachers in Google Classroom. Must be primary email address (not an alias).
About Class Aliases
A class alias is a unique string used by Little SIS to “nickname” the course in Google Classroom and to connect it with an unchanging value in your SIS, which may be a composite of values (e.g. “ENG12-3-001-1617”). Each class alias must be unique across the whole domain, across time. There is no need to compose this in your export, as concatenated values can be constructed in the Little SIS expression builder.
In reality, it is quite possible (and it may be necessary) to arrive at this data structure by exporting multiple files or tables and joining them within Little SIS using a unique database key like Student ID or Course ID. Remember to always include database IDs if you intend to perform joins downstream in Little SIS.
Student Information Systems typically have a normalized relational data structure behind the scenes, which means that entities like teachers, students, courses, course sections, and student enrollments each typically has its own table, and each entity has its own unique ID field (student ID, teacher ID, course ID, etc.) that is used to relate records across tables. Unfortunately, while this represents an efficient data structure for a computer, these tables very likely do not contain all the information that Google Classroom needs in a single table. Instead, table records will need to be JOINED by their unique keys via queries against the database, allowing information from multiple tables to be exported in the same file. In other cases, GAFE email addresses may not be directly available in the SIS, but need to be exported from another system and joined later. Little SIS can handle almost all of these join scenarios via its own join feature, though it is generally better to join all fields into a single export file.
Student information systems vary widely, but automated data export methods can be grouped into several categories:
- Pushed export via SIS platform - this method involves the scheduled delivery (push) of a templated export file from the SIS to a destination network drive or SFTP folder. If your SIS is in a hosted server environment, you may need to ask your SIS provider to enable these file delivery features for you, and you will typically need to have an SFTP server in your local environment to receive the files. If using this method with Little SIS, be sure the export is in CSV format and includes unique column headers and newlines at the ends of records. Because of its simplicity, power, and ease of maintenance, this is the preferred method to use with Little SIS.
- SFTP file delivery resources by SIS
- Pulled export via database client - the most technically advanced, but a potentially powerful and flexible way to access SIS data, this method represents a “pull” of data via SQL query against the actual database that backs the SIS. SQL queries can be run via an ODBC or JDBC driver (different connectors, depending on the database type) manually via command line, via shell script (which can be scheduled), or via a native SQL workbench app like Navicat, that may include an interface with a drag and drop query builder and task scheduling engine. SIS companies are generally understood to be obligated by FERPA and their district contract to provide customers with a read-only database user account, which can then be used to remotely access and query against tables using a application. In some cases, due to business interests, it may be necessary to strongly insist on gaining this access from your SIS provider. If you have the skills and requisite access, this may be an appropriate method to use with Little SIS. Because this method is more difficult for non-expert staff to maintain, we highly recommend you exhaustively explore option #1 prior to considering this approach.
- Manual exports - some SIS providers will give “lower level” consumers of data a graphical interface for manually producing and downloading CSV reports. In most cases, these “reporting” features don’t include the ability to schedule an automated export. Unless you plan to only run Little SIS manually, this method is not preferred.
- SIS data APIs - Some SIS providers, like Powerschool, have attempted to create additional business by selling other vendors access to secure data endpoints. The idea is to have learning software vendors become paying partners. Little SIS does not currently support any SIS data APIs. If you believe this is the only way to extract data from your SIS, please contact us so we can help explore.
- Roster Integration APIs as a Service - Companies like Clever have made a business model out of roster integration by offering data integration services “free” to as many schools as possible by investing in a massive effort to establish market share, and then charging vendors per-district and per-site fees to gain access to the APIs. These fees are then typically passed on to the district in the form of higher software prices. Little SIS does not currently support Clever. If you believe this is the preferred way to extract data from your SIS, please let us know so we can explore further. OneRoster is the new IMS global roster API spec, which is open source alternative to Clever that we are also quite open to exploring.