Grade Books Grade Cumulative GPAs API - ETL

Giovanni Franco

Hi there,

I am new in this platform, my second week :) and I wonder if someone can point me to the right direction. I am looking to have the output for gradebook grade cum GPAs for all periods, 6 to 12 graders with all possible columns associated to a student. To keep this a history as the data is moving forward.
My goal is to do an ETL with the API with Azure SQL. Does anyone has a good suggestion? Or Perhaps a better architecture to create a pipelines.
Also, what would you recommend to begin with.

Thank you.

Lindsey Bryant

Hi @Giovanni Franco . You won't find an API for this unfortunately, We have the same requirement and we pull down all the grades a student has and then calculate everything in our Data Warehouse. We've taken an approach of building an ODS table for almost every single API that is out there where we have a configuration built, using the Get List API to pull data down in Advance List when an specific API isn't available and now have also resorted to building PowerAutomate Jobs to run Sky list to download exports and then use our ETL tool to populate ODS tables from there. With all the data in our ODS, we then start to calculate things like term gpa, cumulative gpa, etc. I'm not saying this is the best approach but it is the one we took due to the timing of us needing the data, the speed the APIs are built and the gaps of getting data out of BBEM. As more APIs are available, we will slowly switch them to remove our dependencies on the advance list or sky list. In full context thought, we're in year three of this design.

Feel free to reach out if you want to connect and we can talk more if it's helpful.

Giovanni Franco

This is terrific, this is what I am thinking too. Based on what you are telling me sometimes the best approach is to create a report (list) and access that info via API. Is that correct? This is my second week on this position and trying to figure it out how to do it. Which tool are you using for ETL? I am planning to use data factory, but not sure which data I can pull to accomplish specific task. EG cumulative gpa. I am thinking to call that report(list) from the api and do the same approach as yours. Is there any scheduler at this time that I can rely on it for the updating the lists reports or should I go with Power Automate? Never used that tool before, but seems interesting. I want to see If I can schedule a call with you next week. My email is gfranco@belenjesuit.org. Thank you.

Lindsey Bryant

Hi Giovanni. I'll follow up with you via email but respond here as well.

We use Talend (Qlik recently purchased it) as our ETL tool. Within the ETL, we have it scheduled to run daily though some of our other integrations runs the APIs hourly. You'll want to use this API: https://developer.sky.blackbaud.com/api#api=school&operation=V1ListsAdvancedByList_idGet

In general, make sure the account that is running ANY API in blackbaud has the correct roles to that section (i.e. Academic Manager, Content Manager, Grades Manager, etc). Being a Platform Management isn't enough for some odd reason.

We created all of our advanced list under a specific service account to eliminate the possibility of an end user messing with it. Once it's saved and you see a list of all of your advanced list, you'll just want the ID to pass into the API itself. Here is an example:

PowerAutomate is fine but we are running into issues with it as well. We're still hopeful that BBEM will roll out APIs a bit more aggressively this year as some we've been waiting for over 2 years and we're just trying to keep the PowerAutomate jobs working.

Let's connect some more and we can dive into questions you may have.

Giovanni Franco

Hi Lindsey, I want to review this list if you do not mind. Can I clone it? If so, how do I do it?

Thank you.

Lindsey Bryant

Hi Gio,

BBEM doesn't have an option to clone it or share things like this. Each user has to built it manually. But here are some screentshots:

Giovanni Franco

Awesome. Thx.