The 10 most useful tables in the Illuminate database
Disclaimer: this post is for our advanced users who work with SQL. While knowing SQL is not a requirement to using the Data Viz Starter Pack, it can often allow you to bring in data that is not included in our base data sets.
The scripting library that powers the Data Viz Starter Pack extracts data from various data sources, one of them is the Illuminate student information system. We do this by running SQL queries against the school’s database. Illuminate’s database contains thousands of tables. For someone who is new to querying the database, it can be an overwhelming figuring out which tables are the most useful. That’s where we come in! Here are 10 tables we have found to be invaluable.
This is the main table that stores student demographic information. Many other tables will reference a student_id field without including helpful fields such as student name. Joining this table will give you access to fields such as student name, email, gender, birth date, etc.
Similar to the table above, but for staff. You will often find a user_id field referenced in tables. Joining that field to this table will allow you to pull in staff name, email, and other info such as teacher credentials should you choose to store those in Illuminate.
Illuminate has built out a few materialized views in their database and most can be found in the matviews schema. These are virtual tables that are the result of queries written against underlying tables. ss_current will always give you currently enrolled students, the id of the site their enrollment site, and grade level id.
sscube is similar to ss_current, but is used for course enrollment information. Previous course enrollments will not show in this table. This is the first table we pull from when building out our course enrollments data set.
This table houses the overall grade percentage a student has in a course. This can be a tricky table to use in queries as a student may have multiple rows for a course if they have prior course enrollments and if your school year has more than one marking period or term (most schools do). Check out our SQL query for current student grades to see how we used this table to pull in overall course percentage and grade mark.
Thankfully when it comes to transcript grades, Illuminate has provided a materialized view for that data. This will have 90% of what you need when pulling transcript data. We have found a need to join other tables such as public.courses to pull in the data we cannot find in this table.
This table will give you every day a student is enrolled and the all day attendance flag they received on that day. This table does not handle period attendance. We’ve leveraged this table when looking at average daily attendance and attendance breakdown by student.
Illuminate has a few products in their suite of offerings. One of them is their DnA product for assessments. This schema has all tables related to that module. Illuminate provides over a dozen prebuilt reports that are immediately available after an assessment has been given. We have found those go a long way when looking at data for a single assessment. When looking to review data across multiple assessments, querying tables in this schema can be very helpful.
When you upload data files from state assessments and other assessments given outside of Illuminate, a table is created under this schema. Here you will find data tables for ACT, PSAT, SAT, NWEA, and many more.
If you have built a summary assessment, this is where you will find the table for that assessment. It is important to note that if you create a new summary assessment, you will not have access to the underlying table until the next day. This is due to the table being freshly made and ODBC permissions being refreshed nightly. If you need access to the table immediately, you will want to open a help ticket at firstname.lastname@example.org requesting that Illuminate refresh your ODBC permissions.
Next time we talk Illuminate, we will tell you a bit about the functions they have included in their database. Until then, sign up for our newsletter to learn more or send us a message directly on Facebook!