API data in SQL Server Data Warhouse

[psjohnson]

Hi all - 

I may just not have entered the proper search parameters in Google to get this, so, I'm going to post it here.  If this has been answered already, please direct me to the proper place.

We are brand new to Canvas - coming over from Blackboard (thank goodness!!).  We are also a Microsoft shop and will be putting the Canvas API data into a SQL data warehouse.  I just started with using the canvasDataCLI node.js tool and have our tables downloading and unpacking - super easy!!  The next thing I need to do is put them into SQL Server.  I was going to use an SSIS solution to get the unpacked data to the database, but will need to build up my tables and relationships.  I can certainly do that by hand, but may take quite some time. 

Does anyone have a set of scripts that can create the blank SQL Server schema needed for the API data to load into?

[paul_alexander1]

Hi Paul,

We've also just started with canvasDataCLI and are using LogParser to create a skeleton database structure using the datatypes discovered by Logparser's (LP) interpretation of the unpacked text files which works quite well once there is sample data.

I haven't yet found a simpler method of extracting the files to SQL Server but probably should have explored SSIS further.

The idea is to allow LP to create all the tables by iterating over the files.txt which is quick and then create LP templates for each extract defining the correct datatypes guided by the Canvas Data warehouse data dictionary.

[raj_veeravalli]

Hi Paul,

We are a small school that has just started looking at Canvas Data. I am doing imports using the Canvancements script (linked below) by  @James  into a MySQL database and played with SQL queries which seems to work.

I have also experimented with R and Excel (Power Pivot) and found it be a lot of work (and found Excel lacking in places) before settling on MySQL warehouse.

We are primarily a Microsoft shop but also use Linux at places. I too haven't found a simple method to manage Canvas data and am open to suggestions and ideas.

Canvancement script

https://github.com/jamesjonesmath/canvancement/tree/master/canvas-data/mysql.

Thanks,

Raj

[robotcars]

Since I released Managing Canvas Data with Embulk after this thread was created, I wanted to share and suggest it.

 @raj_veeravalli 

I also like the code  @James ‌ shared for Canvas Data import, it is the current importer for our CD LTI, but 6 years in, with the size of our data, I now need a multi-threaded importer with filters. If you still choose to use it, but maybe prefer SQL Server (being a MS shop), I have a modified version of his import.sh which uses BCP.

[raj_veeravalli]

carroll-ccsd thank you for suggesting and sharing this. I am very keen on multi-threaded imports and scaling as our data grows. I will take a look.

Thanks,

Raj