Data Extraction and Automation:
Overview: The Little SIS Sync Agent 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 without special characters.
- 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 Sync Agent 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 Sync 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 the Little SIS Sync Agent 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 - optional, used for display purposes in Little SIS Sync.
- School Name or ID - required, used for alias and display purposes in Little SIS Sync.
- 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 Sync, 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 the Little SIS Sync Agent 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 Sync Agent 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 the Little SIS Sync Agent 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 Sync.
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 may 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, G Suite email addresses may not be directly available in the SIS, but need to be exported from another system and joined later. Little SIS Sync Agent 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 where possible.
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 Sync, 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 an 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 Sync. 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 Sync 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 Sync 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 and OneRoster by IMS Global 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 Sync does not currently support Clever, however, our hosted solution, Managed Sync for G Suite, does use Clever as its data source. We do not currently have any OneRoster offerings.