Using Wonde to feed Power BI

[pete]

We're feeding data from various MIS into PowerBI. While it works, it's inelegant, the process is brittle (scheduled task > command reporter > CSV > hot folder > power automate > Power BI).

 

We already have a lot of that data in Wonde to sync to third-party services, so I'm asking Wonde about pulling data (across all our schools) from Wonde > Power BI to cut out a lot of the faff and unnecessary steps.

 

Has anyone else done this and how did it go?

[jthompson]

Not to feed into PowerBI, but we have various Google Apps Script projects which pull data from the Wonde API to feed into Google AppSheet apps via the AppSheet API. Super reliable. Waaay better than using commandreporter CSVs.

 

Not too familiar with Power Automate so can't think how you'd be querying the Wonde API. If you're going to be doing it in PowerShell scripts, you'll want to handle the Wonde API key(s) securely. Either PowerShell SecretStore or Azure Key Vault would be recommended ways to do that, just so that your API key isn't sitting around in plaintext.

[dhicks]

Has anyone else done this and how did it go?

 

We don't at the moment, but it was something I was thinking about the other day when I was looking at what data we export to Wonde, so do please let us know how the conversation with Wonde goes. I got the impression access /to/ data is how they make their money, charging software companies for access, I'm not sure if they have pricing for schools to be able to read their own data back out.

Edited October 5, 2023 by dhicks

[jthompson]

We don't at the moment, but it was something I was thinking about the other day when I was looking at what data we export to Wonde, so do please let us know how the conversation with Wonde goes. I got the impression access /to/ data is how they make their money, charging software companies for access, I'm not sure if they have pricing for scholls to be able to read their own data back out.

 

You can register with Wonde as an app so that you can query your own school's data. The price is something like £300 one-off with £10 per month to read data.

https://www.applytosupply.digitalmarketplace.service.gov.uk/g-cloud/services/585578085617074

https://assets.applytosupply.digitalmarketplace.service.gov.uk/g-cloud-13/documents/708462/585578085617074-pricing-document-2022-05-17-1956.pdf

[dhicks]

You can register with Wonde as an app so that you can query your own school's data. The price is something like £300 one-off with £10 per month to read data.

 

Ah! Very handy to know, thankyou.

[CTIDTech]

I think paying for access to our own data is pushing it a little! Keen to hear the final outcome.

[jthompson]

I think paying for access to our own data is pushing it a little! Keen to hear the final outcome.

Yeah it's a bit of a weird one isn't it, but the way I see it can just be lumped in with whatever other MIS costs you have, and gives the advantage of not tying your custom application(s) to any particular MIS. You could switch out SIMS for something else and your custom apps likely won't care/notice.

[Marci]

S'just JSON data so you can query it with Get Data > From Web and go direct to each table's endpoint (eg: https://api.wonde.com/v1.0/schools/{school_id}/achieve for Achievements table)

You'd probably need to make a custom connector function to add the optional Authorization header onto the request. Something along the lines of:

[font=courier new](SchoolID as text, APIToken as text, Tablename as text) => let
   BaseUrl = "https://api.wonde.com/v1.0/schools",
   RelativePath = "/"& schoolID & "/" & Tablename,
   Headers = [
       Accept="application/xml",
       Authorization = "Bearer " & APIToken
   ],
   Options = [
       Headers = Headers,
       RelativePath = RelativePath,
       ManualStatusHandling = {400, 404}
   ],
   response = Web.Contents(BaseUrl, Options),
[/font][font=courier new]    buffered = Binary.Buffer(response),
   from_json = Json.Document(buffered),

   value = from_json[value][/font][font=courier new]
in
   value
in
   [name_of_function]
[/font]

 

You'd then build a static table (or bring in an excel file) containing per row schoolID, APIToken, Tablename, and then you'd invoke the function against that which would add it's reponse per row as a nested table that you can expand.

 

Tablename should be whatever comes after {school_id}/ in the CURL example for the appropriate GET request from Wonde's API Docs at https://docs.wonde.com/docs/api/sync/

 

Above code would need expanding to handle paging etc.

Edited October 6, 2023 by Marci

[bescot42]

I'm a data manager for a MAT using Power BI. I started off using SIMS CommandReporter (and still do to get some data that I can't obtain (easily) from Wonde. It was all fine until I wanted to get data from some of our school who used Integris. Now I use Wonde to get the data instead and I'm very pleased with how it works. We pay £10 per school per month after an initial £250 set-up fee. I use Power Automate to get data from their API and build the final json from the pages that they send us, and then dump it into a SharePoint location ready for PowerBI to use. I'm not particularly technically-minded, so there may be more efficient methods, but it's what I've found works for me! The big advantage for me is that the data is in the same format regardless of MIS, and if/when a new school joins the trust, I will be able to integrate their data in a matter of hours with little effort.

[pete]

The big advantage for me is that the data is in the same format regardless of MIS, and if/when a new school joins the trust, I will be able to integrate their data in a matter of hours with little effort.

 

Yeah, that's the killer feature for me - portability between schools. I want our data people having adventures in data, not faffing about trying to coax it out of MIS in a sane fashion.

[vikpaw]

Thinking of doing similar for non-core MIS, so watching for any other tips.

 

I’ve had the same conversation with Wonde about being a consumer and I think the fee is low enough to justify the usage of and support for the API.

[pete]

So, we met with Wonde the other day and gained access to some test data.

 

Within a couple of hours our data person had a PoC against the test data that mirrors what we currently pull via Command Reporter and (judging from the level of excitement in their email), there's a fair amount of scope for more adventures.

 

You're looking at signing a developer agreement, a £250 setup fee (only needs doing once, regardless of how many schools) and then £10 per month per school for access to the API.

[dhicks]

So, we met with Wonde the other day and gained access to some test data.

 

Ah - you mean they have generic fake-but-usable test data you can get your code working with? That sounds very useful.

[bescot42]

Ah - you mean they have generic fake-but-usable test data you can get your code working with? That sounds very useful.

 

Indeed, and you can test everything works with the test data before committing to paying anything.

[garbage46]

Ignore this. I used my initiative, picked up the phone and actually called Wonde. Edited December 14, 2023 by garbage46

[dhicks]

Do you have contact details for someone at Wonde?

 

I spoke to Wonde yesterday - I initially contacted them via their web-based form:

 

https://www.wonde.com/app/try-for-free/

 

We then organised a quick video conference session. The person I spoke to (an Associate Business Development Manager from their Data Sync Team) knew enough to be able to give me the technical basics and answer some questions, it was a useful session. That got me set up with (free) test data access, which I'm aiming to have a tinker with today.

 

ClassCharts → Wonde → My API solution → Bromcom API → Bromcom

 

One of the useful things I learnt from the call yesterday was that Wonde have a list of capabilities for each MIS they support, so you can check and see what features each MIS supports. You'll be able to see if the data you want to be able to get from ClassCharts can be written back to Bromcom.

Edited December 14, 2023 by dhicks

[dhicks]

You'd probably need to make a custom connector function to add the optional Authorization header onto the request.

 

Wonde are pointing people at this guide:

 

https://powerbiforschools.com/wonde-integration-with-power-bi-using-the-api/

[pete]

@bescot42 @pete

 

Do you have contact details for someone at Wonde?

 

My contact is Sam Dew (first initial last name at company dot com), their Business Development Manager.

[dhicks]

We're feeding data from various MIS into PowerBI.

 

How are you licensing Power BI? If I have this right, Power BI Pro comes as part of the A5 license for education - we have A3 licenses for most of our staff and A5 for SMT and admin. That makes sense, we could have SMT and admin able to run Power BI Desktop to create reports and publish to their online service for other staff to consume. If I understand this correctly, that could also include pupil accounts - pupils would be licensed to be able to view Power BI reports, but not their parents as a proper, separate account. Is there a way to publish Power BI reports to parents?

[pete]

How are you licensing Power BI?

.

 

Our licensing is a bit of a mix (schools have usually just renewed a 3 year EES right before joining the MAT, so consolidation takes time), but as an example:

 

SchoolA has A3 licensing.

 

Anyone who needs access to the PowerBI Reports Dashboard gets an additional PowerBI Pro license (on top of A3)

 

People doing development work (having adventures in data) get a Power BI Premium license instead.

 

Our current goal is to standardise reporting (T&L, but also Estates, Catering, Finance, Governance, etc) across the trust in a portable building-blocks way that makes it more readily accessible to the people who need it. Less faffing, more using data.

 

Is there a way to publish Power BI reports to parents?

 

You can export and publish reports externally (https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-publish-to-web) but I need to properly check it out and (since parents can't authenticate to our tenancy) I'd only do that for aggregate and properly anonymous data (attendance across school for a year). You can do the Report > PDF > Email (or other appropriately secured portal) routine though and we're looking into it for school reports.

[pete]

End-of-term update.

 

We've begun rollout to our Integris-using schools and this is probably the best Xmas present I could have bought our trust data person.

 

You will need to adjust data permissions (default read permissions are (as you'd hope) quite conservative) if you want usable data about students, but that's a case of sitting down and going "Do we need X and if we do, why do we need X?" and making sure you're pulling only what you need now.

[Ted2424]

Any elaborate process of integrating wonde api with powerbi will be helpful. anyone?

[dhicks]

Any elaborate process of integrating wonde api with powerbi will be helpful.

 

Is there something specific you are trying to do? If you need access to Wonde's API you need to contact them, they were very helpful when I spoke to them and test access is free, giving you a bunch of non-private test data to play with before you start using pupil's real data. That should allow you to set up a process for exporting data from Wonde to a format PowerBI can input - just exporting to a set of CSV / Excel files works quite well.

 

From experience exporting from other APIs, some applications are more suited to exporting data by having a webhook called each time some data gets updated in their system, calling your system with the details of the updated data. Doing a batch export via a REST-style API can be slow if you have to do multiple calls - that's where something like iSAMS' batch "API" is quite good, you basically get an export in XML format all in one go which you can then parse locally.

[Ted2424]

Im having issues with the queries to get the data from the API into powerbi. Different errors like "cannot convert value 1999 to type Text" and 'Column1 of the table wasnt found' while trying to get attendance mark. Also im needing data for 3 different schools as the dashboard is for a MAT, do i call it separetely or is a a function/line of codes that dies this. Will really appreciate if anyone has done it from the scratch to guide. Thanks for your time.

 

Is there something specific you are trying to do? If you need access to Wonde's API you need to contact them, they were very helpful when I spoke to them and test access is free, giving you a bunch of non-private test data to play with before you start using pupil's real data. That should allow you to set up a process for exporting data from Wonde to a format PowerBI can input - just exporting to a set of CSV / Excel files works quite well.

 

From experience exporting from other APIs, some applications are more suited to exporting data by having a webhook called each time some data gets updated in their system, calling your system with the details of the updated data. Doing a batch export via a REST-style API can be slow if you have to do multiple calls - that's where something like iSAMS' batch "API" is quite good, you basically get an export in XML format all in one go which you can then parse locally.

[dhicks]

Im having issues with the queries to get the data from the API into powerbi.

 

Queries from where - do you have some code written somewhere in PowerBI itself, or are you using an external-to-PowerBI script / application to get the data and pass it to PowerBI in some way?