[ARCHIVED] Connect canvas data to power bi

[petern]

I'm trying to pull Canvas data into Power BI and I'm struggling at the first hurdle.  How do you get Power BI to read the data from Canvas?  

I've read through the guides and I'm still lost.

[robotcars]

@petern

What step are you on?

Have you downloaded the files yet, either using the Canvas Data Portal, or the API?

I haven't used PowerBI, but assume it's much like Tableau, where it can read flat files or connect to a database. For quick looks, the flat files can be downloaded and viewed; but for more robust setups, the API can be used to script a job that downloads new files every day and loads them into a database.

If you have the files, they are gzip compressed... double clicking them should unpack them, or maybe use WinZip. The CLI has an unpack feature as well.

[petern]

Hi @robotcars 

From PowerBI, I can go to Get Data > Web and input something like

https://<canvas>.instructure.com/api/v1/accounts?access_token=<token>

But that's just loading the JSON information from the normal Canvas API and only doing one query. 

I can download the files one by one and load them into PowerBI.  

I assume I can connect PowerBI to the Canvas Data API, but I'm finding the documentation difficult to understand.  I'm trying to set things up with the Canvas Data API and can't figure out the first step.  Is the Canvas Data API the same as the Canvas API?

[robotcars]

Hi @petern 

The Canvas Data API is separate from the Canvas API.

Generally, the best way to handle this (and avoid tediously downloading each via the CD Portal) is to use the Canvas Data CLI. https://github.com/instructure/canvas-data-cli

Obtain the key and secret for your user.

https://community.canvaslms.com/t5/Admin-Guide/How-do-I-obtain-an-API-access-token-in-the-Canvas-Dat...

Using the CLI you can run a couple of commands to fetch or sync the files to a local desktop or server. There are also baked in commands to unpack those files. I assume PowerBI has the ability to read CSV/TSV files directly from disk, and if that's sufficient for you there's no need to load them into a database.

If loading into a database is necessary there are couple of community contributions for getting started.

I recently started using this one...

https://community.canvaslms.com/t5/Developers-Group/Build-a-Canvas-Data-Warehouse-on-AWS-in-30-minut...

But started here...

https://community.canvaslms.com/t5/Developers-Group/Managing-Canvas-Data-with-Embulk/ba-p/272746

[de_millington]

Hi @robotcars, just been reading your thread replies with interest. Been with Canvas for 6 years and only now trying to get things working, now that I have acquired some skills in Power Query and PowerBI. My question to you is this. Perhaps I have overlooked the possible Data download options from the Data Portal. While I could see an option to download data relating to Grades, I couldn't see any options to download data on feedback and annotations in Speedgrader. The setup through the UI is that every assignment has to be downloaded manually. It would be great if these could be downloaded in bulk. Are you aware if this is available? Thanks in advance.

[petern]

Thanks @robotcars !  I'll try the Canvas Data CLI later this week.

[robotcars]

Hi @de_millington 

That might be part of submission comments, but not entirely sure all annotations and comments are included.

https://portal.inshosteddata.com/docs#submission_comment_dim

[petern]

Hi @de_millington .  I did a little bit of work on this a few months back and noticed that the submission comments and annotations are separate.  The submission comments can be pulled as @robotcars has suggested.  However, I couldn't find any way to grab annotations.

[de_millington]

Thanks for the quick response @petern. Interesting to know!

[de_millington]

Hi @petern further to the facility to extract submissions comments that you were able to confirm, did you manage to test if that utility extends to file attachments placed by teachers or students?

[de_millington]

Hi Community Data Interested Colleagues. I have managed to upload an unzipped folder downloaded from our Canvas Data portal (one submissions_comments_dim files and one submission_comments_fact) to Power Query. Just one file alone adds nearly 600,000 rows to the Query.

Some of the data is obvious; the comments for example, and another column has scores, while  another that has "/N"- not sure what that is?  Can someone offer any further guidance on what the 18 digit numbers mean? Some of these have a negative sign before them. I assume these are some form of ID designed to merge data from the dim files with the facts, but it's not making any sense at the moment!!

The dim sheet looks like this...