Community

rubyn · ‎01-11-2017

I had this question a while back when CanvasData first became available and here is that thread: How to tell if a course is published? Not sure if there is better/updated info now - I'd have to defer to Canvas!

rubyn · ‎12-03-2015

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.

rubyn · ‎12-03-2015

Looking into it further: Character Types - Amazon Redshift It looks like VARCHAR can hold more than 256 characters. Is this something that Instructure might change in the future so that entire comments fields will be stored in the database?

rubyn · ‎12-02-2015

Looks like the "best" date from within the Redshift instance would be max(timestamp) from requests - which is a day earlier than the date on the files. We plan to use that as the data extract date for communication to usrs.

rubyn · ‎12-02-2015

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?

rubyn · ‎12-02-2015

web_applicaiton_action isn't spelled correctly. hopefully this will save some of you some time banging your head against a wall trying to figure out why your sql isn't working...!

rubyn · ‎11-25-2015

You mean the date from the files? Is there anything within the Redshift DW?

rubyn · ‎11-24-2015

Hi, I'm looking for a "data extract date" or "data as of date" so that we know when the data was pulled into Redshift. I didn't see a status date or table anywhere in the documentation, but maybe I missed it? We could retrieve the max(timestamp) from one of the fact tables, but is that the best/only way?

rubyn · ‎11-24-2015

We were previously using /api/v1/courses/:course_id/analytics/student_summaries to get page_views per student per class. How do we get the same page_views value using the Canvas Data requests table? Doing straight count of id and excluding urls that start with "api/v1" doesn't get it quite the same...

rubyn · ‎10-07-2015

Please let us know if we are joining incorrectly, or is this a bug? I expect these to be able to join to one other. E.g. if I want to get a list of all quiz submissions that have been done, I should be able to see both when the user had finished the quiz (dim) and their score (fact). But this returns an empty set: SELECT * from quiz_submission_fact qsf inner join quiz_submission_dim qsd on qsd.submission_id=qsf.quiz_submission_id; Thinking I got the ID wrong, I also tried this, which is also an empty set: SELECT * from quiz_submission_dim qsd inner join quiz_submission_fact qsf on qsd.id=qsf.quiz_submission_id;

rubyn · ‎08-25-2015

We were told that is a Redshift limitation - that we would need to go to the files to get the full text over 256 characters. I'm not sure if that has changed at all...

rubyn · ‎08-18-2015

I have the same question. How can we filter out the "noise" and be able to tell which resources are being used when...

rubyn · ‎06-18-2015

Hi, I was comparing submissions data and noticed a difference between the "graded_at" dates that were returned via Canvas Data in Redshift and the Submissions API. I suspect it is timezone-related since it matches some of the time but not all the time, but wanted to confirm what we are seeing and understand why. I am looking at submissions_dim.graded_at and comparing to what gets returned via API /api/v1/courses/:course_id/students/submissions for graded_at value. e.g. for canvas course 618, assignment 4972, student 262554, Redshift returns 2/23/15 for graded_at while API returns 2/22/15. For canvas course 618, assignment 4889, student 262554, the dates match. Please advise, thanks.

rubyn · ‎06-15-2015

I am also interested in this answer - I am looking at page_views count. The results from Redshift doesn't match the Analytics API for the page_views value, nor what I see on the Course Analytics page in Canvas. Should I count a different thing, or add more filters to get the expected results? Redshift query: select u.canvas_id as "user id", c.canvas_id as "course id", count(r.id) from requests r inner join user_dim u on r.user_id = u.id inner join course_dim c on r.course_id = c.id inner join enrollment_fact ef on r.user_id = ef.user_id and r.course_id = ef.course_id inner join enrollment_dim ed on ef.enrollment_id = ed.id where c.canvas_id in ('565') and ed.type = 'StudentEnrollment' and ed.workflow_state <> 'deleted' group by u.canvas_id, c.canvas_id order by u.canvas_id compared to Analytics API: https://umich.instructure.com//api/v1/courses/565/analytics/student_summaries Thanks.

rubyn · ‎06-03-2015

Is there always a 1:1 relationship between a course and enrollment term (I believe a course can only exist in one term at a time, but wanted to confirm)? Also, we see enrollment_term_id in the quiz_submission_fact and submission_fact, but not in the requests table. For requests, looks like we need to go through course_dim to get enrollment_term. Is that by design? Please advise, thanks!

rubyn · ‎06-01-2015

Thank you, I added it as a comment in that thread. Please keep me posted on its progress. On Fri, May 29, 2015 at 1:43 AM, jordan@instructure.com <

rubyn · ‎06-01-2015

It would be good to also have analytics for the student at the course-level, not just the assignment. E.g. Student A is in the 95%ile for the class, Student B is in 25%ile, etc. Would enable us to see which students are in trouble, etc. Also make this information available via API, DW, etc. Ditto for student's percentile for the assignment.

rubyn · ‎05-28-2015

It would be good to have analytics for the student at the course-level, not just the assignment. E.g. Student A is in the 95%ile for the class, Student B is in 25%ile, etc. Would enable us to see which students are in trouble, etc. Also make this information available via API, DW, etc. 🔎 This idea has been archived. While this idea isn't open for comments, it is an important part of Instructure’s idea conversations and development process. Contributions like this are valuable as Instructure prioritizes work on new or existing features.

rubyn · ‎05-19-2015

Are all courses included in the hosted data or just published? Would like to see the published courses - currently we use /api/v1/accounts/:account_id/course where published=true. Don't see a similar filed in course_dim...

rubyn · ‎05-12-2015

Still erroring out. This is from our developer, any help is appreciated. I finally got it to connect from designer and was able to get the tables loaded into my mapping as sources using the ODBC driver. Then to run the workflow to get data into a target table, I changed the kiwi's ODBC.ini file and made an entry there. I am logging in as the same user/pw to get the sources that were provided to me. At the top: PGRESQL_SRC_Redshift_Test=DataDirect 7.0 PostgreSQL Wire Protocol In the body: [PGRESQL_SRC_Redshift_Test] Driver=/home/kiwi02/app/etl/Informatica/PowerCenter9.5.1/ODBC7.0/lib/DWpsql26.so Description=DataDirect 7.0 PostgreSQL Wire Protocol Database=canvas HostName=52.5.214.139 LogonID= Password= PortNumber=5439 When I run my workflow, it errors out with: Error connecting to database [ [unixODBC][Informatica][ODBC PostgreSQL Wire Protocol driver]Can't connect to server on '52.5.214.139'[unixODBC][Informatica][ODBC PostgreSQL Wire Protocol driver]Connection refused. Verify Host Name and Port Number. Database driver error... Function Name : Connect Database driver error... Function Name : Connect Database Error: Failed to connect to database using user [canvas] and connection string [PGRESQL_SRC_Redshift_Test].].

Post Details	Date Published	Views	Kudos
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?	12-02-2015	8572	11
Warning to all: Spelling error in requests table (web_applicaiton_action) web_applicaiton_action isn't spelled correctly. hopefully this will save some of you some time banging your head against a wall trying to figure out why your sql isn't working...!	12-02-2015	453	1
Is there a "data as of" date in Canvas Data? Hi, I'm looking for a "data extract date" or "data as of date" so that we know when the data was pulled into Redshift. I didn't see a status date or table anywhere in the documentation, but may...	11-24-2015	1759	3
Unable to join quiz_submission_fact to quiz_submission_dim? Please let us know if we are joining incorrectly, or is this a bug? I expect these to be able to join to one other. E.g. if I want to get a list of all quiz submissions that have been done, ...	10-07-2015	618	1
Discrepancy between Canvas Data "graded_at" and Submissions API "graded at" values? Hi, I was comparing submissions data and noticed a difference between the "graded_at" dates that were returned via Canvas Data in Redshift and the Submissions API. I suspect it is timezone-re...	06-18-2015	650	1
Question on enrollment_term_id Is there always a 1:1 relationship between a course and enrollment term (I believe a course can only exist in one term at a time, but wanted to confirm)? Also, we see enrollment_term_id in the ...	06-03-2015	838	2
Provide student analytics at course level, not just assignment. It would be good to have analytics for the student at the course-level, not just the assignment. E.g. Student A is in the 95%ile for the class, Student B is in 25%ile, etc. Would enable u...	05-28-2015	1483	25
How to tell if a course is published? Are all courses included in the hosted data or just published? Would like to see the published courses - currently we use /api/v1/accounts/:account_id/course where published=true. Don't s...	05-19-2015	1969	4

Posts	21
Solutions	0
Kudos given	3
Kudos received	66
Member Since	‎04-20-2015

Community

Have a Canvas Question?

Have a Mastery Question?

Have an Elevate Question?

Have an Impact Question?

Re: Is there any way to discern when a course was ...

Re: Will there be a data model diagram provided fo...

Re: Truncated fields

Re: Is there a "data as of" date in Canvas Data?

Will there be a data model diagram provided for Ca...

Warning to all: Spelling error in requests table ...

Re: Is there a "data as of" date in Canvas Data?

Is there a "data as of" date in Canvas Data?

Re: Canvas Data vs data in API?

Unable to join quiz_submission_fact to quiz_submis...

Re: Truncated fields

Re: Understanding the requests table: routes, and ...

Discrepancy between Canvas Data "graded_at" and Su...

Re: Redshift vs. Canvas Analytics

Question on enrollment_term_id

Re: Provide student analytics at course level, not...

Re: Course Analytics - Reporting

Provide student analytics at course level, not jus...

How to tell if a course is published?

Re: Is anyone using Informatica PowerCenter to con...