Data extraction and automation
The Little SIS Sync Agent interface for selecting and mapping source CSV data is a flexible tool. It lets you map and join your data within the tool. This reduces the amount of data transformation you must do upstream.
Basic file requirements
- Filename(s) must remain consistent. The automation should overwrite or replace the file on update.
- The 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.
- Add double quotes around any values where commas may occur in the data, such as Class Title. This format ensures that the comma in the data is not read as a field delimiter. For example, "Governance, Germanic"
- Avoid concatenating fields, such as Class Alias, in your export. The Little SIS Sync Agent interface lets you concatenate fields. Having 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. Once joined with class, teacher, and school information, each enrollment record in the file will contain the following source data fields.
Note: Header names may vary in your SIS and in the CSV export itself.
- Class Alias (sub-fields): Required, leave as separate fields in export. You will combine these fields in the Little SIS Sync Agent to create a unique string per class group. You will learn more about Class Aliases later in this article. The following fields make up the Class Alias:
- 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 Google Workspace Email: Required, must be the primary email address, not an alias.
- Teacher Google Workspace Email: Required, this is the primary teacher of the course. Co-teachers are not supported by Little SIS Sync but can be added at will by teachers in Google Classroom. This email must be the 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 connect it with an unchanging value in your SIS. The alias may be a composite of values, for example, ENG12-3-001-1617. Each class alias must be unique across the whole domain, across time. There is no need to include aliases in your export. Once you import data to the Little SIS Sync Agent, you can create aliases using the Expression Builder.
The data needed for the alias may exist in multiple files or tables. You can export and join the data in Little SIS Sync Agent using a unique database key like Student ID or Course ID. 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. Each entity type, teachers, students, courses, course sections, and student enrollments, has its own table and a unique ID field. The unique ID is used to relate records across tables.
While this is an efficient data structure for a computer, Google Classroom needs data in a single table. You need to join table records by their unique keys using queries. This allows information from multiple tables to export to one file.
Another scenario is when the Google Workspace email address is not readily available in the SIS but needs exported from another system and joined later.
Little SIS Sync Agent can handle almost all these join scenarios via its own join feature. But 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. You will also 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 CSV format
- Includes unique column headers
- Includes newlines at the ends of records.
This is the preferred method to use with Little SIS. It is simple, powerful, and easy to maintain.
- Powerschool (Data Export Manager)
- Skyward (SkyBuild)
- Resources for SFTP server setup
- Resources for OpenSSH server setup
- SFTP file delivery resources by SIS
Pulled export via database client: This is the most technically advanced, 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 depending on the database type. Either manually via command line, via shell script, or via a native SQL workbench app like Navicat. Once the query has been defined, it can be run as a scheduled task.
SIS companies are generally understood to be obligated by FERPA and their district contract to provide customers with a read-only database user account. Using this account, the database is remotely accessed and queried against using an application. In some cases, 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. This method is more difficult for non-expert staff to maintain. We highly recommend you exhaustively explore option #1 before 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 run Little SIS Sync manually, all the time, this method is not preferred.
SIS data APIs: Some SIS providers, like Powerschool, have attempted to create additional revenue 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. Our hosted solution, Managed Sync for Google Workspace, does use Clever as its data source. We do not currently have any OneRoster offerings.