Building the Illuminate SIS connector
The Data Viz Starter Pack extracts data from your key data systems to seed a series of data sets that you can use to create reports in Google Data Studio. We support a number of edTech applications and student information systems, one of them being Illuminate SIS. This article is a “behind the scenes” look at how we engineered this integration so you can understand how the Starter Pack does what it does. The Starter Pack is a no-code environment for the data analyst with a Python scripting library on the backend that handles the refreshing of the data sets. This post is for our advanced users who plan to grow their work to include custom SQL and to expand the scripting library itself. Here we go…
Illuminate has a few ways to pull data out so that you can work with it in other systems. One method is to use their API. This is a REST based API that handles requests and responses via JSON encoded strings. On their API documentation site, Illuminate provides a few code samples written in PHP and Python to give you a solid start to your script. The first step to accessing the API is to email firstname.lastname@example.org. They will provide you with the the user keys, tokens, and secrets needed to implement the authentication piece of the code. You will use OAuth to authenticate with the API and then retrieve data by hitting the various endpoints. The API is fairly basic, only providing a slice of the data that is in Illuminate. It feels like the API has been built out as a response to vendor needs from companies such as Clever and Canvas vs built out to provide comprehensive data access to schools and districts. The API has a few POST methods which allow you to push data back into Illuminate. There is one for assessment scores and another for summary assessments. However, the best way to push data to Illuminate remains their core data import tool. Data specs located here.
Direct Database Access
In addition to the API, Illuminate also allows for direct database access via ODBC connections. Zachary Rankin, from Illuminate, has published a very helpful guide that covers a ton of best practices that are useful when working with the Illuminate database. The database is a PostgreSQL database containing thousands of tables. If the API has the data you are looking for, it is recommended you use the API over querying your database directly. The reason being if you retrieve data from the enrollment endpoint and it is incorrect, you open an Illuminate help ticket for them to troubleshoot and fix it. However, with database access, you need to write the custom SQL that pulls that data and the Illuminate help desk cannot assist with writing SQL. Querying the database directly is far more complex, but it also gives you access to all data available in your Illuminate instance. If you want to go this route, emailing email@example.com should be your first step in setting this up. You will want to tell them you need ODBC credentials and will want to give them your IP address so they may whitelist IP. Connections are only allowed from IP addresses that have been whitelisted by Illuminate. If you are a Starter Pack customer, you already have your ODBC credentials or they can be provided to you by our support desk.
With the Starter Pack we have opted to query the database directly. Prior to starting this project I worked in an organization that used Illuminate as their student information system. This gave me 5 years of experience querying the database. While querying the database directly is a lot more complex, it gives you access to everything. At this stage of the work, we don’t know the final versions of all of our data sets. We wanted to build an integration where we know we can always pull in anything that a customer has stored in their Illuminate instance.
That’s it! Illuminate may not have the most robust API out there, but it is fantastic that they allow their customers to query their database directly. Most of the data analysts who join the Starter Pack will never need to interact with the Illuminate database directly, but we have built our scripting library in a way that allows an advanced user to modify the SQL queries to pull in additional data. In a future blog post we will cover the 10 most useful tables in the Illuminate database. That will be a great starter for someone who is learning how to query their Illuminate database. Until then!