InstructureCon 2024: Canvas Data 2 Meetup Follow-Up

[NickChevalierUT]

Introduction

It was great seeing everyone this past week, and thank you to all who attended our Canvas Data 2 Meetup on Thursday, July 11!

As promised, @JenniferLash and I wanted to follow up so we can continue the awesome and engaging discussion we all had on Thursday, as well as invite others who may find this post to join in as well.

I didn't realize it at the time, but the "helpful community member" I mentioned who had the GitHub repository with a ton of CD1 queries was in attendance: @reynlds! Thank you, Mark, for putting all of that together and really being the inspiration for us wanting to create something similar for CD2.

Canvas Data 2 Repository

With that in mind, I'd like to propose we, as a community, utilize this Google Drive folder (let me know of any access issues, but it should be open access) to share the queries we've been able to come up with. I've started by adding the following SQL files:

• ADQ_Last_Submission_By_Term_(EST)
• Assignment_Submission_Download_Links_By_Course
• Course_Enrollments_By_User

My hope is that this repository can grow into something really helpful for the data community!

Keep the Discussion Going!

"What happens in Vegas doesn't stay in Vegas" (don't blame me—quote supplied by @JenniferLash), so let's keep the conversation going! Please use this discussion to share your use cases and ask questions so we can all help each other. "A rising tide lifts all boats," right? (Okay...that one was on me.)

[asergay]

I added my CD2 beginner documentation. 

[NickChevalierUT]

Woah, this is awesome, @asergay, thank you! Very comprehensive, and I especially love the inclusion of the current CD2 tables.

[asergay]

You're welcome. BTW, I'm getting laid off. If you know of any instructional design, LMS admin, or tech writing opportunities please let me know. I would really love to help Canvas fix its interface issues, but that's not a job they post.

[Jeff_F]

@asergay  - thanks for the smile. I am beyond enamored to see the list of questions CD answers. If you wish, send me your resume at fernerj at erau dot edu and I will share it with our Instructional Design leadership. With a team of +80 we do have openings from time to time.

[reynlds]

Feel free to disseminate anything from my Github repos. I've got quite a bit I need to upload there (I'm naughty and don't use source control as I should). These are collections of Python, SQL and Linux shell scripting. Some are specific to my institution or a particular host, but most are generic enough to apply broadly. Enjoy!

https://github.com/reynlds-illinois/enterprise_learning_systems

[sgergely]

Hello, 

I feel I have missed everything because I couldn't attend Instructure Con this year. This is amazing that you had a CD2 Meetup, love that!

I would like to join in to the discussion and give you some updates of the upcoming changes we I'm planning and working on.

Would you join an online Canvas Data 2 Meetup hosted here as an Instructure Live event? I'm thinking of 16th of August 12:00 EST which is 09:00 PST and 18:00 CEST

Do you have any specific topic you would be interested in or like me to cover regarding Canvas Data 2?

[NickChevalierUT]

@asergay - I'm so sorry to hear about your position. We don't have anything in the moment at our institution, but I'll keep an eye out!

@sgergely - this would be great, and I'd be very interested in attending! That time on August 16 would work for me personally.

[NickChevalierUT]

So, one of the queries I added to the Google Drive folder, Assignment_Submission_Download_Links_By_Course, is actually not working for me at the moment. As of the last time I ran it sometime back in May 2024, it worked like a charm. Now, when I run the query in pgAdmin, it takes forever and never actually finishes running, even for a single course. I'm currently at over 15 minutes, and still no results. Did something change in the past couple months, or is my query just poorly written (by ChatGPT 😅)?

This query should give me the student name, SIS user ID, course name, SIS course ID, the assignment title, the attachment title, and then a concatenated full URL to download the file submission in question.

EDIT: my query finally just completed after nearly 20 minutes. I guess it's working, which is good, but it's never taken this long before, and I'll need to run this query for multiple courses at a time.

SELECT
    u.name AS user_name,
    p.sis_user_id AS user_id,
    c.name AS course_name,
    c.sis_source_id AS sis_source_id,
    a.title AS assignment_name,
    att.display_name AS attachment_display_name,
    CASE 
        WHEN s.attachment_ids IS NULL OR s.attachment_ids = '' THEN 'No file was submitted'
        ELSE CONCAT('https://[YOUR-DOMAIN].instructure.com/files/', att.id, '/download?download_frd=1&verifier=', att.uuid)
    END AS download_url
FROM
    public.submissions s
JOIN
    public.users u ON s.user_id = u.id
JOIN
    public.pseudonyms p ON u.id = p.user_id
JOIN
    public.courses c ON s.course_id = c.id
LEFT JOIN
    public.assignments a ON s.assignment_id = a.id
LEFT JOIN
    public.attachments att ON ARRAY[att.id::text] <@ string_to_array(s.attachment_ids, ',')
WHERE
    c.sis_source_id IN ('Course_SIS_ID_Here') -- Add your list of courses here

[saustin]

@asergay First of all, thank you for all you do.   I'm so sorry to hear of your impending layoff.

Please check this site for the University of South Florida's job openings.

https://www.usf.edu/work-at-usf/careers/browse-apply-for-job-openings.aspx

I don't work there, I now work at the University of Tampa with Mr. Chevalier, but I had worked there at one point, and their Instructional Designers are top of the line.  They frequently have job openings for Designers, as well as, related positions.    As you mentioned that you were interested in Interface issues, I think the combination of your experience, combined with your interest in UI, would make you a great candidate for the kinds of positions they have there.

See if there is a current opening for Position Number: 00019900.

Good luck!  We're keeping our fingers crossed for you!

--Sharon

[fcolina]

@asergay thank you so very much for your documentation.  I'm eager to set up an early warning system at our institution and your work looks very promising and most helpful.  I'll add that I'm sorry about the work situation and wish you the best with your future job prospects.

Fernando

[reynlds]

@NickChevalierUT  I got similar results when running this using DBeaver. I did have to put double quotes around the protected word "name" before it would kick off. Weird-o-rama that it didn't spike my CPU/memory usage as so many other queries that take a long time to complete will, and it's not overly complex either. Hmmmm...

[NickChevalierUT]

@reynlds - thanks for giving it a try! Thankfully, based on feedback from the requesting department, I was able to modify the query to narrow down the results to only target assignments with a specific word or term in the title, so that's sped things up a bit.

On a separate note: has anyone tried using CD2 for quiz question item analysis across multiple courses for Classic Quizzes?

At UT, we have several courses in our gen ed program that use the same quizzes and questions across multiple courses and sections. I've been asked whether it's possible to generate a report that is essentially the item analysis report you can run on an individual quiz basis, but they want to be able to aggregate that data across an entire term.

For example, let's say the quiz is called "Chapter 1 Quiz," and it has the same title in every single course, and every version of the quiz uses the same questions (but the questions have different question bank ids/question ids—we don't yet use account-level question banks). Is there a CD2 (or maybe even an API) solution to aggregate some kind of item analysis data for the same quiz questions across multiple courses in a single term? I know through the API I could pull multiple (but individual) item analysis reports, but that doesn't sound like it would satisfy the requester's requirements.

All my research up until this point tells me this is an impossible ask, but I figure if anyone knows of an answer, they would be here. 🙂

[NickChevalierUT]

As a follow-up to my previous comment on the Item Analysis across multiple courses—the Quiz Statistics API might be the key: Quiz Statistics - Canvas LMS REST API Documentation (instructure.com)

Since the API and CD2 tend to have similarities, I wonder if CD2 will eventually have Quiz Statistics available as a table? If so, that might give me exactly what I'm looking for. I could pull the data for each question and add together the student counts for a comprehensive look at an entire semester.

[sgergely]

Hi, just a follow-up to my last question, I'm going to host an online meetup this Friday here: https://community.canvaslms.com/t5/Instructure-Live/Canvas-Data-2-Online-Meetup/ec-p/609912#M753

[NickChevalierUT]

Awesome, @sgergely, thank you for sharing! I'll definitely be there.

[saustin]

@sgergely Thank you so much!  Looking forward to it!  And thank you @NickChevalierUT for alerting us to the meetup!

[LindaWilin1]

@asergay 

There's a 'Full Time Learning Management Technology Coordinator' position open at LCCC in Elyria, Ohio.  Must be able to work on campus.  Postings are here =   https://www.lorainccc.edu/human-resources/employment-opportunities-at-lccc/

   Not sure how much longer they'll be taking applications.   

@sgergely

Thank you for setting up the Meetup Friday