Narrowing Submission Data Based on Term

[pray4]

Good Morning,

I'm an LMS Admin trying to assist my BI Systems Director with our transition to Canvas Data 2.0 and Managed Teaching & Learning. We pull submission data to share with our campuses and programs to track student progress. However, he's having an issue limiting the submission data as he did in the past and is pulling all submission data from inception, to the tune of roughly 36,000,000 rows. 

My recommendation was to limit based on our active terms. Can someone share a best practice, or at least point me in the right direction for where to look for that data to narrow?

Thank you

[kyle_cole]

Hey!

The submissions table brings in the course_id which can join to the courses table. In the courses table you can join the enrollment_term_id to the enrollment terms table. Once you have all of that data together you can filter down to what you want. 

I would recommend book marking the entity relationship diagram (erd). It makes piecing the different tables together really easy. https://data-access-platform-api.s3.eu-central-1.amazonaws.com/erd/index.html

-Kyle

[ColinMurtaugh]

@kyle_cole  - to be clear, though, they would need to load all of the submissions data from CD2 first; it sounds to me like they are trying to reduce the amount of data they need to download and ingest from CD2. 

@pray4  - there's not really a way to filter the data from CD2 before you download it. (You can run incremental queries to get changes since a point in time, but those incremental updates need to be applied to a full snapshot.)

However, I just loaded our submissions table (using the python dap tool) which coincidentally is also just over 36M records. The process took just under an hour, but it only needs to be run once. After you've fetched and ingested the initial snapshot, you can then fetch (much smaller) incremental updates on a regular basis. 

Hope this helps! 

--Colin