Get Started as a...

April 11, 2016

Analyze LiveSchool Behavior Data with Illuminate

Learn how to upload behavior data to Illuminate to access advanced reporting features.

In this guest post by Peter Setter, learn to combine your academic data with LiveSchool to create enhanced "360 degree" student reports.

Setting up Illuminate to Store LiveShool Data

One of Illuminate’s best features is access to their well-documented SQL back-end. Summary assessments allow you to create your own tables with your own schema. It’s this feature that I use to upload and store LiveSchool data in Illuminate. The steps below outline how to get started.

  1. After you log into Illuminate, click on Assessments then Create a New Summary Assessment
  1. Give the summary assessment a specific name. Since we’re a multi-site network, I use the name of the school and specify the month. Since the points log is exportable by month, I created a separate summary assessment for each month. Having a separate summary assessment for each month makes the SQL more cumbersome; however, it is easier to upload data on a regular basis.
  2. Click on Columns to set-up the fields you want to upload.
  • Name your columns the same as the column names in the points log. When you upload the points log, Illuminate will automatically match the columns, so you don’t need to by hand.
  • You can choose to add all the columns or a subset. When in doubt, add all of them, because you may have questions you want to answer later.
  • Remember to specify the correct column type. This makes your data easier to work with when you pull it later.

Uploading Points Logs

  1. After you download the points log, go to the summary assessment.
  2. If you are uploading a points log in a summary assessment where you have data, remember to remove all student responses. The alternative to removing all student responses is selecting DISTINCT record IDs in your query.
  3. Click Save and you’re ready to upload your first file.
  4. Use Duplicate Repository under Advanced to quickly make all the summary asseesments you’ll need for the year.
  1. Click on Responses and Import Student Responses from a File.
  1. The points log is a comma separated values file. Click on Add Student Data. If you choose Update Student Data you can only upload one record per student. Using Add allows unlimited records. Considering footnote 2, you could upload data without clearing it out first, but that will result in duplicating records each time you upload. Choose your file then click Upload File.
  1. The last step is to specify the Student ID column; you always need to do this manually. If the names of your summary assessment match that of the points log, they will be matched for you. Click Import and you’re done.

Getting Data Out of Illuminate

  1. If you don’t currently have Illuminate database access, you’ll need to contact Illuminate support to set this up. This is beyond the scope of this document, but the steps are:
  • Provide Illuminate with your IP address. Database access is location specific. If you want to pull data off-site, you’ll need to either provide this additional IP address or set-up a VPN.
  • Illuminate will send you your login information.
  • Download software to build and run your queries. Don't forget the drivers. I use the open-source pgAdminIII and R through the RODBC package.
  1. Ask Illuminate to update your repositories. Summary assessments have table names in the form of dna_repositories.respository_#. You can find the repository ID in the URL of the summary assessment. For example:dna/?action=index&repository_id=91&page=Repository_IndexController
  2. Now you’re ready to start pulling your data for analysis or reporting, such as Tableau, progress reports, or report cards.

Example Query

In the query below, I roll-up LiveSchool data which will be used in an elementary school report card.

WITH ls AS (    SELECT *    FROM (    SELECT *    FROM dna_repositories.repository_97    UNION    SELECT *    FROM dna_repositories.repository_99    UNION        SELECT *    FROM dna_repositories.repository_123    UNION        SELECT *    FROM dna_repositories.repository_124    UNION        SELECT *    FROM dna_repositories.repository_125    UNION        SELECT *    FROM dna_repositories.repository_126    UNION        SELECT *    FROM dna_repositories.repository_127    UNION        SELECT *    FROM dna_repositories.repository_128    UNION        SELECT *    FROM dna_repositories.repository_129        UNION        SELECT *    FROM dna_repositories.repository_130    UNION        SELECT *    FROM dna_repositories.repository_131    ) AS repo_all    )

I combine all the LiveSchool data in a WITH clause. Since I’m including all the repositories, I can use this code in all of my queries for this school. (As opposed to only using the queries for a particular month.)

SELECT local_student_id, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Homework' AND field_points > 0 THEN field_points ELSE 0 END) AS complete_homework, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Homework' AND field_points < 0 THEN field_points ELSE 0 END) AS incomplete_homework, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be a Leader' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_leader, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be a Leader' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_leader, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Work Hard' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_workhard, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Work Hard' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_workhard, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be Kind' AND field_points > 0 THEN field_points ELSE 0 END) AS glow_bekind, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Be Kind' AND field_points < 0 THEN field_points ELSE 0 END) AS grow_bekind, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Community' THEN field_points ELSE 0 END) AS community, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Justice' THEN field_points ELSE 0 END) AS justice, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Love' THEN field_points ELSE 0 END) AS love, 

SUM(CASE WHEN split_part(field_behavior, ' (', 1) = 'Grow' THEN field_points ELSE 0 END) AS grow 

FROM ls INNER JOIN public.students s ON s.student_id = ls.student_id 

WHERE field_official_date BETWEEN '8/17/2015' AND '11/13/2015' 

GROUP BY local_student_id

Note that Illuminate affixes field_ to all your column names.

At our elementary school, each of the behaviors have a point value in its name, like Homework (5). This set-up allows teachers to enter values for the entire week rather than day-by-day. split_part divides the string. I use the first part to match the behavior name. If you’re data isn’t set-up this way, you can simply use: field_behavior == my_behavior.

local_student_id is the stuD from your district or state. student_id is the internal Illuminate ID.

Example Reports

Using this approach, we are able to create "360 degree" reports for our students and families. Below are the front and back of a sample report. You can see that we combine a variety of data sources to provide the fullest picture possible about a student's progress:

The front of our "360 degree" report card
The back of the report lists specific behavior recorded by teachers

Happy reporting!

From the Blog