Ability to see where Student and Instructors are spending time within a Course

[bneporadny]

First and foremost we are extremely excited about having Canvas provide us all of this wonderful data that we can pull and use as our institution deems necessary.

We were hoping for 2 things with Hosted Data Service. One the ability to more easily pull a grades by assignment report, which we are not able to and two be able to see the amount of time that our student and instructors are spending in class and big bonus would be to be able to break that out by the time they spent on each particular page within the course.

Canvas currently gives us the ability to see "Total Activity" time on the People/Course Roster page, but there isn't an easy way to get to that data and then be able to pump it up against other student and instructor data to be able to do some really in depth reporting.

I guess my question here is, is Instructure planning on adding additional tables or columns to tables that might fulfill the need/want of your customers to be able to see where students and teachers are spending the most time within a course?

[katerina_newton]

Thank you for bringing it up, Brian! We were looking for the exact same things actually. Our curriculum development team wanted to know how long the assignments take/which one look like a problem and how long and where the students and instructors are spending their time in a course. I haven't analyzed every single table yet but from the brief scan of it, I don't see how we can get that information so if Instructure can help us see how we can accomplish that, that would be greatly appreciated!

[crawfom]

We are also looking for more data from the Hosted Data Service.  We would like to see that last time a teacher updated their gradebook.  I can see a timestamp for a submission, but what if the Assignment has a "No submission" selection and a teacher grades something by hand and enters the score into their gradebook.

We now have a district policy that a teacher must update their gradebook at a minimum of every 2 weeks.

[msmith]

Grades are next on our list to add to the data warehouse.   This has be the #2 most requested dataset we are currently missing (right behind data around external tool installation).  Timeframe will be hopefully late May, early June.

Where is time spent? - This is a question that is also asked frequently.   The "raw material" for determining where users spend their time in Canvas is the requests table.   This table shows every interaction users have with the application.   There may be some data massaging involved depending on the questions you are asking the data, but this table represents the sum of what we have relating to this topic.  We may engineer some common rollups or enrichments on this data down the road to make questions like these easier, but right now our priorities are focused on getting the all of the data modeled. 

Hope this helps, if I'm off base with the requests table let's keep the conversation going.

[Kerlene_King]

Hi Matt

Do you have an ETA on when the grades will be available and will you be including all grades for every assignment or just final grades?

[bneporadny · Accepted solution]

Kerlene,

I am already able to pull the grades by assignments. Below is the query I am using to pull the individual grades by specific term or terms. Hope this helps.

SELECT DISTINCT

                                    user_dim.name

                                    ,pseudonym_dim.sis_user_id

                                    ,pseudonym_dim.unique_name as email

                                    ,course_dim.sis_source_id as CourseId

                                    ,enrollment_term_dim.name AS Term

                                    ,enrollment_term_dim.sis_source_id

                                    ,assignment_dim.title

                                    ,assignment_dim.submission_types                               

                                    ,submission_fact.score

                                    ,assignment_dim.points_possible

                                FROM user_dim                               

                                INNER JOIN pseudonym_dim on pseudonym_dim.user_id = user_dim.id

                                INNER JOIN requests on requests.user_id = user_dim.id

                                INNER JOIN enrollment_fact on enrollment_fact.user_id = user_dim.id

                                INNER JOIN course_dim on course_dim.id = enrollment_fact.course_id

                                INNER JOIN enrollment_term_dim on enrollment_term_dim. id = enrollment_fact.enrollment_term_id

                                INNER JOIN assignment_dim on assignment_dim.course_id = course_dim.id

                                LEFT OUTER JOIN submission_fact on submission_fact.assignment_id = assignment_dim.id AND submission_fact.user_id = user_dim.id

                                WHERE enrollment_term_dim.sis_source_id in (input your campuses SIS Term Id's here)

                                ORDER BY course_dim.sis_source_id,user_dim.name,assignment_dim.title

[Kerlene_King]

Thanks Brian, that was very helpful. Our folks were looking for this exact report. I did notice that pseudonym_dim.unique_name stores user's email address at your institution but for my campus that field holds the user's login id. Can the user's email address be found anywhere else?

[bneporadny]

Kerlene,

I am not seeing any other way to pull the email from within the hosted data service unless it is used as the pseudonym_dim.unique_name.  I check the user_dim table and I am not seeing an email field within there.

Maybe we can get Canvas to put that field within that table.

What we are doing is running the query against the hosted service data base and pulling the data back into our own database here so that we can then serve up standardized report and ad hoc reports when needed.

[triunk]

Hi Brian and everyone that can offer their perspective.

I just found this query. 

Two quick questions: 

(a) Is this query still valid today (since the post is a bit old)?

(b) Since request table as it saved in our sql server has data only one date date (the data the canvas data store was updated) can I exclude request table from this query without compromising data quality and scope of this request?

Thank you

Christos

[bneporadny]

Hi Christos,

The query is still valid today and yes you can remove the request table from being included in query without it compromising the data. 

[triunk]

Thank you Brian!

[katerina_newton]

Hi Matt,

I pulled the "requests" table just now and it's blank. The headers are there but the data is not. Can you check and see where the data is, please? Thanks!

[msmith]

Katerina,

There should be data in there now.  Can you check.   It is only for the past few days, but should be enough to get started with.    We will be doing a historical refresh sometime later in the month.

[katerina_newton]

Hi Matt, yes the data was there but Excel times out every time trying to execute the query. Here is what our programming instructor wrote to me (he is working on Hosted Data project for our company as well).

I ended up installing SQL Workbench/J (also discussed in the training).  SQL Workbench worked fine and configuration was quite easy, all the tables and data came in quickly and mapped successfully. 

Well - that's where the good news ends.  I am encountering two road blocks that are keeping me from moving forward.  I have attached a document of the two main errors I'm encountering.  The first one is the requests table - no matter what software I use, I continue to encounter a syntax error on the table that I'm guessing is a data transformation error, and it rests with Canvas - the table is crashing at the quiz_id field.

The second problem is the data sets themselves.  At first, I was running everything on my desktop system (32-bit, 8Gb RAM), but I could not load all the data from any table (not enough virtual memory), despite re-configuring my Virtual memory, taking every last K of memory available and maxing out the Windows swap file, I still could not load the entire data set from any table (I was able to retrieve almost 300,000 rows in the assignment table).  So, I installed my software on a 64-bit 8Gb RAM system, and was able to double the row load (600,000 rows), but it still wasn't enough to load the entire table.  I'm going to run some row count commands and see if I can find the ceiling. As you can surmise that without being able to load the entire table we will not be able to run reports off partial data. 

My next test (idea), is to configure one of my systems as a SQL server and run the ODBC from a connection to that server.  Unfortunately, that is going to take some time to configure, and I'm leery even that would be able to handle the row count.

Thoughts/ideas?

[msmith]

Katerina,

Can you send me the entire SQL query that is having issues?

As for the second issue (big tables)... yes, many of the tables have large amounts of data in them that will not fit into memory on a single machine.   Many people have success by issuing SQL queries to aggregate, filter, group or project the data into what is needed for a specific report they are trying to build as opposed to download all the data for local manipulation.   If you would like to manipulate the data locally you will need to break it up into chunks.  You maybe break it up by date, or by course, or by some other large grouping attribute. 

[katerina_newton]

Hi Matt,

I have asked our programming instructor to provide a query example that is producing an error but haven't heard back yet. However, he just sent me this yesterday afternoon:

"The problem is - even in SQL workbench, I can map the connection, but all of the table fields are coming up as - Expr1000, Expr1001, etc.

I can't see field names in the tables"

I was just wondering if anyone else is having the same issues with SQL workbench and if so, do you have any tips on how to fix it?

Thank you!