-
Notifications
You must be signed in to change notification settings - Fork 8
FAQ
Great question. Stated simply, CanvasDataStore holds data and CanvasDataLevel1 holds views to the data. Here's more detail...
- CDS contains stored procedures and a few reference tables that are used to run the whole download process
- These stored procedures start by downloading the latest Canvas Data schema, and the tables in CDS that hold the data are dynamically generated from this schema information. This is super-important--whenever Instructure modifies the Canvas Data product the CDS processes automatically update the resulting tables.
- Essentially, CDS is meant to be a turnkey solution. Install it. Set up a SQL Job Agent to run it every night. Enjoy a fresh download every morning.
- Each CDS table includes a unique ID field added in SQL Server with the name table_UID. We add this as a primary key field to make sure any merge operations don't fail.
In the spirit of being a turnkey application, we took anything that has to be modified/updated by people (us) and put those things in separate databases. We think (hopefully) that CDS won't need to be updated often (except when there's a mod to Canvas Data that produces a data-type mismatch, or when some data convention (like how they represent null characters) gets changed (it has happened)).
- CDL1 contains views that you use to query the Canvas Data over in CDS
- There is one view for each table in Canvas Data.
- Most raw tables in Canvas Data are actually comprised of two tables that end in _dim and _fact. This the star schema model for database organization. (Read about it in Wikipedia.)
- All the views in CDL1 combine the _dim and _fact tables into one view for convenience:
- Views are based on the _dim table and linked on id=table_id
- When there are duplicate fields in both tables, the field is pulled from the _dim table. (We haven't evaluated yet whether the data really got stored in the _fact table. Please let us know if you see problems with that.)
- All the CDL1 views are built by hand, which means they have to be update by hand if new fields or tables are added to Canvas Data. (That's the main reason we pulled these views out of CDS--we think CDL1 will need to get updated more often.)
Yes. We're working on a set of views in CDL2 that are more readable than what you see in CDL1. They include usernames, course IDs and names, etc. These views should be something that a real human being can look at to get information.
CDL2 will also include views that are more analytic.
The Requests table (CanvasDataStore.dbo.requests, CanvasDataLevel1.dbo.vRequests) shows you webpage views for every user. It comes in two versions. The regular API we use to download Canvas Data files pulls down a requests table view that only covers one day. There is another API which pulls down the full request history.
After extensive testing we determined that SQL Server can't reasonably support the full requests table. Our current full download included over 2.2 billion records. We were able to load that data into a SQL Server table, but it took almost a day to build some indexes on it, and some queries on it would run for 8 hours or more. We are studying creating a separate project to host this data in a no-SQL database application like Hadoop or MongoDB, but it's going to take a while.
We started this project in SQL Server because that's the tool we used internally for our database needs (we didn't have MySQL experience.) We're currently working on a MySQL version of the application, but we don't expect to have it available until Fall 2017.
We welcome contributions to any part of the project, but we're most hopeful that institutions will contribute analytic views/stored procedures/functions to CDL2.