[ARCHIVED] Submission_type in Submission_dim null value

[tylerweldon]

I am working on a report that counts the number of submissions in a Canvas course. When I run a count on the number of submission IDs (from submission_dim) in a course its larger than the number displayed in course statistics. I expanded on this more in Tableau and found that I have a large number of submissions with a null value in the submission_type column. When I remove that column from my count, I have the same number as in course statistics. Does anyone know why some submissions would get a null value in the submission_type? I checked several of these and they should be showing online_upload

[Jeff_F]

 @tylerweldon ‌ - this may be a complex one.    Are you my chance using Tableau?   I am using such a workbook and I am filtering to see only the graded submissions as a data source filter.  I also exclude submission_types. not_graded and none as part of a workbook filter.

Have you tried to count using the names of the students who submitted?  I do this now to derive the # Students as this workbook does not currently link in to our SIS.  I've attached the SQL used for that.  See below for the join.

---------

I am glad to have a session with you to review, if interested.  :smileycheck:

[James]

Submissions with a submission_type of null can mean different things. One of which is that there is no submission.

I know that sounds illogical at first. To understand this better, it helps to know that the grades in the gradebook are tied to the submissions. The Submissions API is what Canvas queries when it populates the gradebook. In Canvas, a submission entry is created ahead of time for every assignment / student pairing. 30 students with 40 assignments is 1200 submission entries before any student starts turning anything in.

There are several things going on to tell if there is an actual submission.  @Jeff_F  is great on Tableau, but he's right about it not being a Tableau issue as much as needing to filter the data. Depending on your needs, I may not base it off of graded since I think that would exclude those assignments that students have turned in but haven't been graded yet. You may want to use workflow_state=unsubmitted as an exclude.

Another thing to check is that you're getting all of the information from Canvas Data. This may just be me, but I have some inconsistencies in my data. Even things that you think wouldn't be possible sometimes show up. For example, I have one assignment where the submission_type is null but I have a submitted_at date. Just 1 -- 1 out of 4,423,328 submission entries. That makes me think that maybe my database setup is wrong and I missed a submission type (I'm using enumerated types based on the descriptions in the Canvas Data Portal documentation). carroll-ccsd‌ has already pointed out that I'm missing some by doing this, I just haven't made time to fix it yet. I've focused on optimization rather than flexibility and that causes some of the things to get lost. I don't use Canvas Data enough to worry about it, but bring in the critical information from another method.

[Jeff_F]

Ah, yes - a key point I left out is the Tableau info I posted here is for review of the grading after the term has concluded.  If one's goal is for reviewing during the term then the filters would need to be adjusted.

[stimme]

As far as I know, there is just one value you need to add to the enumerated list in schema.json: basic_lti_launch. I found it in the flat files after seeing something was missing. Now I have the column type defined as:

enum('basic_lti_launch','discussion_topic','external_tool','media_recording','online_file_upload','online_quiz','online_text_entry','online_upload','online_url') 

[robotcars]

basic_lti_launch is one.

The other day I found on_paper in assignment_dim.submission_types

While I don't have a submission_dim.submission_type row with on_paper, it would seem that some data sets might.

I feel like unless we monitor the source code for the potential values no one institution will have a full definition of the dataset.