“Measurement is our opiate of choice in the business world precisely because it temporarily allays fear all the way up the ladder. Look boss, there’s the number, right there on the chart — I hit the mark, so don’t blame me!” – Liz Ryan in a Forbes article.

Blackboard Data is a key product that closes the loop on monitoring and improvement.  Have you ever heard the saying: “you cannot manage what you do not measure” ?  Whether it is true in all settings or not, due diligence requires that we measure activity.  There are more important things than what we can possibly measure in a database, such as student engagement or instructor effectiveness, and we also pay close attention to those things.

In this series of articles on exploration of Blackboard Data we will consider various scenarios and SQL techniques necessary to extract the data.  Finally, we’ll consider how to apply data visualization to the raw data in order to effectively communicate the data.

Scenario #1.

In the Spring 2019 semester, which users spent the most time in the LMS and how many interactions did they have?

This is what the Blackboard Data Snowflake environment looks like.  On the left you’ll see database tables and fields, on the right you type in select SQL statements.  You can save each worksheet and you can export the data to a CSV.

a1

This is the SQL code for this scenario:

select  lp.last_name, lp.first_name,
lsa.person_id,
round(sum(duration_sum)/60/60,0) as duration_hours,
sum(interaction_cnt) as clicks

from cdm_lms.session_activity lsa
inner join cdm_lms.person lp

on lp.id = lsa.person_id
where

first_accessed_time between
'1/12/2019' and '5/1/2019' -- Spring 2019
group by

lp.last_name,
lp.first_name,
lsa.person_id

order by duration_hours desc

Most of the SQL statements you can simply copy and paste.  You do not have to be a database administrator to run reports in the Snowflake system.  Database administrators take care of storage, memory, and backup of database systems, working with SQL is actually a relatively small part of their job.  Business or data analysts tend to use SQL language, but Blackboard Data simplifies access to data, so the SQL code is not complex.

The SQL above can gather data either by course_id pattern, such as 2019.spring.%, or by date/time of the events.  This example will collect data fro Spring 2019 term: first_accessed_time between ‘1/12/2019’ and ‘5/1/2019’

You can play with the SQL by trying things out and getting it wrong.  You’re not breaking anythings.  Editing the dates or other elements of the stamens will help you to get comfortable with the process.

Scenario #2.

“As a History department head, I need to know which of my instructors did not access their LMS courses in Fall 2018, so that I can prompt faculty engagement + adoption in subsequent terms, in order to improve the student experience”
Since in the initial installation of Blackboard Data you may not have the SIS data adapter, we will use the course_id to extract term data:

select lp.first_name,
lp.last_name,

lp.email,
ifnull(count(distinct lpc.course_id),0) as courses_assigned_count,
ifnull(count(distinct lca.course_id),0) as courses_accessed_count,
ifnull(sum(lca.minutes),0) as course_minutes,
ifnull(sum(lca.accesses),0) as course_accesses,
ifnull(sum(lca.interactions),0) as course_interactions,

case when courses_accessed_count = 0 then 0 else course_minutes / courses_accessed_count end as minutes_per_course,
case when courses_accessed_count = 0 then 0 else course_accesses /  courses_accessed_count end as accesses_per_course,
case when courses_accessed_count = 0 then 0 else course_interactions / courses_accessed_count end as interactions_per_course

from cdm_lms.person_course lpc

inner join cdm_lms.person lp
on lp.id = lpc.person_id
and lpc.course_role = 'I'

inner join cdm_lms.course lc
on lc.id = lpc.course_id

left join (
-- creating a summary of activity per person and course

select
person_id,
course_id,
sum(duration_sum)/60 as minutes,
count(distinct id) as accesses,
sum(interaction_cnt) as interactions

from cdm_lms.course_activity
group by person_id, course_id

) as lca
on lca.person_id = lpc.person_id

and lca.course_id = lpc.course_id

where
-- Department includes "hist" as course code

lc.course_number like '2019.spring.hist%'

group by

lp.first_name,
lp.last_name,
lp.email

order by course_accesses, course_minutes

Next, scenario #3: How do we get user_id or batch_uid since they are not listed as columns in the person or course tables? Blackboard Data: Exploration ( scenario 3 ) user_id and batch_uid from STAGE

After reading this article, would you like to see if you can learn more about SQL?

Try this interactive lesson on Code Academy site:

https://www.codecademy.com/learn/learn-sql

Or Khan Academy tutorial:

https://www.khanacademy.org/computing/computer-programming/sql