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?
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.
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.
When you ask about the table schema, are you talking about something different than those in the documentation at Canvas Data Portal ?
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.
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.",
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).
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.