cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
rubyn
Community Participant

Will there be a data model diagram provided for Canvas Data?

Didn't see anything similar to an e/r diagram - or anything that depicts the relationships between the tables in the documentation portal.  Did I miss it? 

21 Replies
James
Community Champion

Everything I know about dimensional modeling has been learned since the advent of Canvas Data, so I won't claim to be an expert, but what I've read is that Entity Relation type diagrams aren't the right way to approach Dimensional Modeling. An ER diagram may contain multiple DM diagrams.

You have a Fact (fact) table that joins to Dimension (dim) tables using foreign keys specified in the fact table. Those foreign keys are specified in the schema on the documentation portal. Those docs say that "Canvas Data also mostly adheres to a Star schema convention, which means that most of the relations should only be one join away." That means that you should, for the most part, not have to worry about the ER model where you join A to B and then B to C so that A you can use A and C in the same report. Instead, The fact table (A) contains a key to both B and C directly so you join A to C and A to C.

That doesn't mean that a figure wouldn't be nice, but it isn't as beneficial (necessary) as with a ER diagram. The main difference is that instead of having one diagram with lines all over the place, you would have a different diagram (page?) with a few lines for each fact table (currently 22 of them). Those dimension tables that would be linked are in the fact tables. I actually thought about writing a program that would generate those diagrams automatically from the tables but I have too many projects that I start and never finish already sitting in the queue.

Someone else may have already produced such a program and Canvas may have them internally, but the answer to your question is that it isn't there on the documentation portal unless it's buried on an unpublished link.

rubyn
Community Participant

Yes, I was looking for Instructure to provide a data model diagram in their documentation portal that shows the relationships between the tables.  What I've seen from other vendors is a view from the Dim and Fact tables, and the tables they relate to; so not a single e/r diagram, sorry for the confusion. 

rob_callicotte
Community Contributor

Has anyone found the table schema for these tables? That would be handy in the creation of the tables on this side.

 @rob_callicotte ​,

When you ask about the table schema, are you talking about something different than those in the documentation at  Canvas Data Portal ?

Although I haven't had a chance to look at it so I can't speak to it's use in this case, there is a Canvas Data CLI tool that is available that will help with fetching the data. It's in Javascript and uses the Nodejs library. If you'd rather use PHP or PERL, I've written some code that will allow access to the API through a class or module. That would give you access to the schema from one of those languages. I have written (but not published) code that will write a .sql file that can be used to create a MySQL database to house the information.

rob_callicotte
Community Contributor

James, I wrote a C# program that took the table data from each table on that same page and converted it to SQL CREATE scripts, but there is no listing anywhere of field sizes for VARCHAR, etc., which hampers appropriate setup using SSIS for creation and maintenance of tables. Until we get an official word from Instructure on exact sizes and keys, etc., we're just guessing.

 @rob_callicotte ​,

Download the schema through the API rather than trying to get it off the webpage. The schema through the API comes in JSON format and it has the information you're looking for in there.

When the type is varchar, there is an extra field called length. For example, here is the entry for the course_dim.name field.

{

"description":"The friendly name of the course.",

"type":"varchar",

"length":"256",

"name":"name"

}

The Schema returned through the API also lists the dim tables you need to use with the fact tables in a field called dimension.

The authentication portion of the API seems to give the most trouble to people. I wrote a document that explains it giving PHP and PERL examples. If you don't want to mess with it, links are provided at the bottom that have complete API modules that you can just use in your PHP or PERL code and not worry about the implementation. Canvas Data API Authentication

All of that said, all of the varchar fields in schema 1.3.0 are 256 bytes long with the exception of requests.id (50) and external_tool_activation_dim.url (4096).

rob_callicotte
Community Contributor

Thanks James. Are you saying then that using this (http://canvas.park.edu/api/schema/latest​&<access_code>) we can get the schema? That simple? If so, great. I've been using the API, but noticed that some of the available fields in the Canvas Data are not in the API. But, you are saying that doesn't keep us from knowing the schema for the Canvas Data, because it is the same as what we find in the API?

No, I am not saying anything close to that.

Canvas Data API is different from the regular Canvas API and has a different host and authentication scheme. When I mention API in this thread, I'm talking about the Canvas Data API, not the Canvas API.

They are two different beasts with some overlap as to information available, but otherwise, completely different systems with different URLs and different hosts. Canvas API interacts directly with the Canvas system and is live and real-time. The Canvas Data API provides read-only access to archived database tables that about a day old.

rob_callicotte
Community Contributor

Yes, I see...and I'm just now reading through your fixing up the sloshed information from Canvas about the Canvas Data API. This changes everything. Thanks James! You have been incredibly helpful.