[ARCHIVED] Power BI

[wknelson]

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

[a1222679]

I've been doing just that.  You can see a couple of examples here.

[wknelson]

Examples look interesting. Have you tried pulling anything from AWS Canvas Data?

[a1222679]

No I haven't.  All our Canvas data is surfaced in a data lake

[a1222252]

Hi,

To expand on Daniel's response, we download daily from the Canvas Data Portal using the canvasDataCli tool. This is then uploaded into an Oracle database and presented as views in the data lake that Daniel refers to.

Regards,

Stuart.

[ryan_schulte]

It seems like a lot of institutions are using the bulk downloads.  We are querying Redshift via ODBC into excel files and putting them in sharepoint.  Our Power BI reports refresh from those sharepoint files.  The sharepoint files have auto refresh through a program called Power Update or a PowerShell script.  Reworking this via SQL Server is on the agenda but not crucial as we are not dealing with huge amounts of data.

In the future I would like to develop an application which connects Power BI to redshift via the API interface.  All the data analysis / visuals would be created through Python scripts and the managing program would be in C++.  This is still VERY far away though. 

[a1222252]

Hi Ryan,

Our requests data is currently about 200 GB and growing at about 3 GB per day during the semester. What is your experience of performance when querying requests data via Redshift?

When downloading using the canvasDataCli sync process, only files which don't already exist in the local data store are downloaded, so it's not really a bulk download.

Regards,

Stuart.

[ryan_schulte]

Stuart,

There are only two reports I have created which rely on the Request table.  For both reports I am looking at / analyzing real specific things so the queries I am running are not pulling in more than 500k rows.  I also do not bring the tables into excel, rather, I load them to the data model through PowerQuery.  Usually, there is a good bit of filtering I can do within PowerQuery before that as well.  For Power BI the data query process is very similar to PowerQuery.  It does a good job of compressing the data so you can get a good amount in before it starts to lag.

The only other thing that helps is having a computer with an i7, SSD, graphics card and a good amount of ram helps.  If I query just an open month's worth of data then it will take a ton of time to complete.  However, if you know what you want from the table it is not too bad.  

[drangel]

HI FRIEND 

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

[a1222679]

I don't have sufficient privileges to open that link anymore either.  It was an old post and I've done a lot more since.  Basically, we're saving the canvas flat files in an Oracle database.  I query that using Alteryx to produce csv files which I upload to PBUI for visualisation.  I can't offer much in the way of advice regarding database management but I've been querying and visualising Canvas data for four years so may be able to help in that regard.

[ryan_schulte]

I have started to transition some of our reports to Power BI, although most have been unrelated to academics thus far.  

If Canvas BI developers want to collaborate and / or share count me in though.    

[conim4180]

personally i finde all this to be complicarted

[twheeler]

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

[p_b_rothwell]

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.

[caroline_thimot]

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

[scgoh1]

Hello All,

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

Thanks

[p_b_rothwell]

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

[wknelson]

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!

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'

[drangel]

Buenas tardes,

Alguien tiene ejemplos con power BI