“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.
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.
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.
“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:
Or Khan Academy tutorial: