cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Community Member

Power BI

Anyone using Power BI with Canvas Data? Looking to collaborate on building templates. 

Tags (2)
17 Replies
Highlighted
Community Member

I have started. How are you building your Data Model?

Highlighted
Surveyor

Currently exploring and modelling data downloaded and onsite using Power BI Looking at broad visualizations as the system is only newly launched (logins by day, enrolments) One useful graph showed semester one assignments by deadline. Our libraries and support staff in particular liked this one for more accurately pre-empting peak time. Hoping to expand this out to identify submission type and location so we can look at load on printers, risk of Turnitin slowness etc.

Nest step is joining the data with some user and time dimension tables. User will allow for more granularity in looking at trends within school/programme etc. Time will allow for heatmapping of activities over the course of a day. We have a very supportive database/systems team, which is really helpful.

Happy to share practice if people are interested.

Highlighted
Community Member

Hello All,

any idea how to generate a customized report to find out what canvas tools have been used in each canvas courses?

Thanks

Highlighted

Hi SC I'd start by querying the Requests table (this is in our database as table_requests) You can join this with courses using a field common to both tables (course_id perhaps)

The look in table_requests at the column web_application_controller this will allow you to filter by tool type. Simply remove tools you're not interested in and you should be able to query tool use in a particular course.

Hope this helps. Please let me know how you get on

Cheers

Phil

Highlighted

Hi Phillip, if you continued this work I'm interested to see what you've done. Tks.

Highlighted
Community Member

Finally have made some progress and have working dashboards! One in particular - "Time to Grade an Assignment in Canvas" has had a significant impact. I've pasted the SQL below for the Canvas Data Redshift server. Depending on your instance of Canvas you may need to modify some fields. Suggest you add a limit to the select statement as a trial. This pulls data for the first three quarters of this year. Below is a screenshot of the dashboard. Now it's your time to share what's worked for you!

Power BI report on time to grade an assignment in Canvas

SELECT
"user_dim"."Id" AS "id",
"user_dim"."Canvas_id" AS "canvas_id",
"user_dim"."Root_account_id" AS "root_account_id",
"user_dim"."Name" AS "name",
"user_dim"."Time_zone" AS "time_zone",
"user_dim"."Created_at" AS "created_at",
"user_dim"."Visibility" AS "visibility",
"user_dim"."School_name" AS "school_name",
"user_dim"."School_position" AS "school_position",
"user_dim"."Gender" AS "gender",
"user_dim"."Locale" AS "locale",
"user_dim"."Public" AS "public",
"user_dim"."Birthdate" AS "birthdate",
"user_dim"."Country_code" AS "country_code",
"user_dim"."Workflow_state" AS "workflow_state",
"user_dim"."Sortable_name" AS "sortable_name",
"enrollment_dim"."Id" AS "id (enrollment_dim)",
"enrollment_dim"."Canvas_id" AS "canvas_id (enrollment_dim)",
"enrollment_dim"."Root_account_id" AS "root_account_id (enrollment_dim)",
"enrollment_dim"."Course_section_id" AS "course_section_id",
"enrollment_dim"."Role_id" AS "role_id",
"enrollment_dim"."Type" AS "type",
"enrollment_dim"."Workflow_state" AS "workflow_state (enrollment_dim)",
"enrollment_dim"."Created_at" AS "created_at (enrollment_dim)",
"enrollment_dim"."Updated_at" AS "updated_at (enrollment_dim)",
"enrollment_dim"."Start_at" AS "start_at",
"enrollment_dim"."End_at" AS "end_at",
"enrollment_dim"."Completed_at" AS "completed_at",
"enrollment_dim"."Self_enrolled" AS "self_enrolled",
"enrollment_dim"."Sis_source_id" AS "sis_source_id",
"enrollment_dim"."Course_id" AS "course_id",
"enrollment_dim"."User_id" AS "user_id",
"course_dim"."Id" AS "id (course_dim)",
"course_dim"."Canvas_id" AS "canvas_id (course_dim)",
"course_dim"."Root_account_id" AS "root_account_id (course_dim)",
"course_dim"."Account_id" AS "account_id",
"course_dim"."Enrollment_term_id" AS "enrollment_term_id",
"course_dim"."Name" AS "name (course_dim)",
"course_dim"."Code" AS "code",
"course_dim"."Type" AS "type (course_dim)",
"course_dim"."Created_at" AS "created_at (course_dim)",
"course_dim"."Start_at" AS "start_at (course_dim)",
"course_dim"."Conclude_at" AS "conclude_at",
"course_dim"."Publicly_visible" AS "publicly_visible",
"course_dim"."Sis_source_id" AS "sis_source_id (course_dim)",
"course_dim"."Workflow_state" AS "workflow_state (course_dim)",
"course_dim"."Wiki_id" AS "wiki_id",
"assignment_dim"."Id" AS "id (assignment_dim)",
"assignment_dim"."Canvas_id" AS "canvas_id (assignment_dim)",
"assignment_dim"."Course_id" AS "course_id (assignment_dim)",
"assignment_dim"."Title" AS "title",
"assignment_dim"."Description" AS "description",
"assignment_dim"."Due_at" AS "due_at",
"assignment_dim"."Unlock_at" AS "unlock_at",
"assignment_dim"."Lock_at" AS "lock_at",
"assignment_dim"."Points_possible" AS "points_possible",
"assignment_dim"."Grading_type" AS "grading_type",
"assignment_dim"."Submission_types" AS "submission_types",
"assignment_dim"."Workflow_state" AS "workflow_state (assignment_dim)",
"assignment_dim"."Created_at" AS "created_at (assignment_dim)",
"assignment_dim"."Updated_at" AS "updated_at (assignment_dim)",
"assignment_dim"."Peer_review_count" AS "peer_review_count",
"assignment_dim"."Peer_reviews_due_at" AS "peer_reviews_due_at",
"assignment_dim"."Peer_reviews_assigned" AS "peer_reviews_assigned",
"assignment_dim"."Peer_reviews" AS "peer_reviews",
"assignment_dim"."Automatic_peer_reviews" AS "automatic_peer_reviews",
"assignment_dim"."All_day" AS "all_day",
"assignment_dim"."All_day_date" AS "all_day_date",
"assignment_dim"."Could_be_locked" AS "could_be_locked",
"assignment_dim"."Grade_group_students_individually" AS "grade_group_students_individually",
"assignment_dim"."Anonymous_peer_reviews" AS "anonymous_peer_reviews",
"assignment_dim"."Muted" AS "muted",
"assignment_dim"."Assignment_group_id" AS "assignment_group_id",
"submission_dim"."Id" AS "id (submission_dim)",
"submission_dim"."Canvas_id" AS "canvas_id (submission_dim)",
"submission_dim"."Body" AS "body",
"submission_dim"."Url" AS "url",
"submission_dim"."Grade" AS "grade",
"submission_dim"."Submitted_at" AS "submitted_at",
"submission_dim"."Submission_type" AS "submission_type",
"submission_dim"."Workflow_state" AS "workflow_state (submission_dim)",
"submission_dim"."Created_at" AS "created_at (submission_dim)",
"submission_dim"."Updated_at" AS "updated_at (submission_dim)",
"submission_dim"."Processed" AS "processed",
"submission_dim"."Process_attempts" AS "process_attempts",
"submission_dim"."Grade_matches_current_submission" AS "grade_matches_current_submission",
"submission_dim"."Published_grade" AS "published_grade",
"submission_dim"."Graded_at" AS "graded_at",
"submission_dim"."Has_rubric_assessment" AS "has_rubric_assessment",
"submission_dim"."Attempt" AS "attempt",
"submission_dim"."Has_admin_comment" AS "has_admin_comment",
"submission_dim"."Assignment_id" AS "assignment_id",
"submission_dim"."Excused" AS "excused",
"submission_dim"."Graded_anonymously" AS "graded_anonymously",
"submission_dim"."Grader_id" AS "grader_id",
"submission_dim"."Group_id" AS "group_id",
"submission_dim"."Quiz_submission_id" AS "quiz_submission_id",
"submission_dim"."User_id" AS "user_id (submission_dim)",
"submission_dim"."Grade_state" AS "grade_state",
"enrollment_term_dim"."Id" AS "id (enrollment_term_dim)",
"enrollment_term_dim"."Canvas_id" AS "canvas_id (enrollment_term_dim)",
"enrollment_term_dim"."Root_account_id" AS "root_account_id (enrollment_term_dim)",
"enrollment_term_dim"."Name" AS "name (enrollment_term_dim)",
"enrollment_term_dim"."Date_start" AS "date_start",
"enrollment_term_dim"."Date_end" AS "date_end",
"enrollment_term_dim"."Sis_source_id" AS "sis_source_id (enrollment_term_dim)",
-- Data from account_dim
"account_dim"."root_account_id" AS "root_account_id (account_dim)",
"account_dim"."root_account" AS "root_account (account_dim)",
"account_dim"."parent_account_id" AS "parent_account_id (account_dim)",
"account_dim"."parent_account" AS "parent_account (account_dim)",
"account_dim"."id" AS "Id (account_dim)",
"account_dim"."canvas_id" AS "Canvas_Id (account_dim)",
"account_dim"."name" AS "Name (account_dim)",
"account_dim"."subaccount1_id" AS "subaccount1_id (account_dim)",
"account_dim"."subaccount1" AS "subaccount1 (account_dim)",
"account_dim"."subaccount2_id" AS "subaccount2_id (account_dim)",
"account_dim"."subaccount2" AS "subaccount2 (account_dim)",
"account_dim"."grandparent_account_id" AS "grandparent_account_id (account_dim)",
"account_dim"."grandparent_account" AS "grandparent_account (account_dim)"

FROM "user_dim"
-- Adding account_dim for school/department info
-- joining account_dim.ID field to course_dim.account_id to get correct account record
-- Reminder Note:
-- account_dim has four ID fields, ID, root_account_id, grandparent_account_id, and parent_account_id
-- documentation says the course_dim.account_id is the parent account for this course, but it is
-- actually the account_dim.Id field, as opposed to the account_dim.parent_account_id.
INNER JOIN "enrollment_dim" ON (("user_dim"."Id" = "enrollment_dim"."User_id")
and "enrollment_dim"."Workflow_state" not in('deleted','rejected')
and "enrollment_dim"."Type" = 'TeacherEnrollment')
INNER JOIN "course_dim" ON (("enrollment_dim"."Course_id" = "course_dim"."Id")
and "course_dim"."Workflow_state" <> 'deleted')

INNER JOIN "assignment_dim" ON (("course_dim"."Id" = "assignment_dim"."Course_id")
and "assignment_dim"."Workflow_state" <> 'deleted')

INNER JOIN "submission_dim" ON (("assignment_dim"."Id" = "submission_dim"."Assignment_id")
and "submission_dim"."Workflow_state" NOT IN('unsubmitted', 'deleted'))
-- and ("user_dim"."Id" = "submission_dim"."User_id"))

INNER JOIN "enrollment_term_dim" ON ("course_dim"."Enrollment_term_id" = "enrollment_term_dim"."Id")

-- adding account_dim table to try to see school info
INNER JOIN "account_dim" ON ("account_dim"."id" = "course_dim"."account_Id")
and ("account_dim"."Workflow_state" <> 'deleted')

where "course_dim"."Workflow_state" <> 'deleted'

AND "enrollment_term_dim"."Name" = 'SU2018' OR "enrollment_term_dim"."Name" = 'FA2018' OR "enrollment_term_dim"."Name" = 'WI2019'

Highlighted

HI FRIEND 

no he podido ver esos ejemplos porque aparece roto el enlace!

Highlighted
Surveyor II

Buenas tardes,

Alguien tiene ejemplos con power BI