Open Source BI Tool for Schools

[duncanb]

Hello there!

 

Having worked in schools as a technician, I'm painfully aware of how much money they spend on IT licensing -- I've had many a business manager complain directly, so I was always keen to promote OS software as an alternative.

 

Having switched jobs, I now work for a data company, and a project they have is an open source BI tool for schools. It uses the Xporter API to extract data from SIMS (and others) and populates the data into a PostgreSQL DB, which they can then visualise using something like Superset.

 

Ideally this could be used in schools teaching A-Level IT or CS, so that students could work on the project as part of their coursework, and also provide some benefit to the school by giving them free access to their data.

 

From my time working as a school technician, I can only see benefits to this, but I was curious if anyone had any ideas, feedback or can poke any holes in this, or even better, would like to contribute to the project?

 

Ben

 

P.S. http://www.schools.bi

Edited December 1, 2021 by duncanb
Including link

[TechMonkey]

Nice idea, but letting pupils have direct access to the school's pupil and staff data, which would bypass any security and restrictions the MIS puts in place, seems like a bit of a flaw.

 

Also, I'm not up on Xporter API but does that work with CLoud systems? I know iSAMS has its own API so direct DB access is very much restricted.

[garbage46]

I use Google Data Studio for free... not quite as feature packed as Power BI but getting there and updates have been rolling out rapidly over the past 18 months.

CommandReporter with some MSSQL and SSIS for ETL.

Google Drive File Stream thingy to sync to "cloud"

App Scritps with scheduled triggers for importing new csv's to Google Sheets

Google Sheets as data sources for Data Studio dashboards.

All locked down and secured through standard Google stuff, just like Google Drive.

https://datastudio.google.com/overview

[mavhc]

Good idea, your website needs some kind of call to action though, can I at least Star a github repo?

 

Something that would be useful for testing would be a set of fake data, and then providing that fake data in formats that each MIS would output

[duncanb]

Nice idea, but letting pupils have direct access to the school's pupil and staff data, which would bypass any security and restrictions the MIS puts in place, seems like a bit of a flaw.

 

Also, I'm not up on Xporter API but does that work with CLoud systems? I know iSAMS has its own API so direct DB access is very much restricted.

 

For sure, it's understandable that schools might not want students to have access to school data, so in that instance they can just use test data from Xporter to assist in development, and the school can still use the software in a more restricted environment -- with the help of IT of course...

 

Groupcall's Xporter works with most MIS systems, cloud and on-prem. The only one we've found that isn't compatible is EBS, which I think is more for colleges/sixth forms, as part of the functionality of the software is processing applications.

[dhicks]

I was curious if anyone had any ideas, feedback or can poke any holes in this, or even better, would like to contribute to the project?

 

I agree with the general idea of a project of some sort to work on data analytics for schools, but your website is rather short on detail. You mention Groupcall Xporter and Apache Superset in your post, is your project basically some kind of application that gets data from Groupcall and puts it into Superset? How is this application implemented - as something a school installs on a web server and points users at?

 

My current plan for data analytics for our school / group is to have a set of (probably Python) scripts that take in data from CSV / XML files (we export data from the iSAMS API) and produce a set of static HTML files that can then be served to appropriate users, with suitible authorisation settings. I'd split users into groups (teachers, senior management, finance, etc) and generate a separate dashboard for each, making sure each dashboard could only be viewed by the appropriate users. If a bunch of data-processing scripts already existed in some form which I could use that would be very handy and save a lot of time.

[GrumbleDook]

Hi Ben

 

It has been interesting to see how the project has developed and I wish you the best with it.

[duncanb]

Good idea, your website needs some kind of call to action though, can I at least Star a github repo?

 

Something that would be useful for testing would be a set of fake data, and then providing that fake data in formats that each MIS would output

 

Noted! Thanks for the feedback. You can follow the project on GitHub here: https://github.com/SchoolsBI -- Xporter provide a nice sandbox for us to test the code in, which we're doing now, and we've coded for a number of endpoints so far.

[duncanb]

I use Google Data Studio for free... not quite as feature packed as Power BI but getting there and updates have been rolling out rapidly over the past 18 months.

CommandReporter with some MSSQL and SSIS for ETL.

Google Drive File Stream thingy to sync to "cloud"

App Scritps with scheduled triggers for importing new csv's to Google Sheets

Google Sheets as data sources for Data Studio dashboards.

All locked down and secured through standard Google stuff, just like Google Drive.

https://datastudio.google.com/overview

 

I like it! I've not used Google Data Studio before but I'll check it out, ditto with Command Reporter.

[duncanb]

I agree with the general idea of a project of some sort to work on data analytics for schools, but your website is rather short on detail. You mention Groupcall Xporter and Apache Superset in your post, is your project basically some kind of application that gets data from Groupcall and puts it into Superset? How is this application implemented - as something a school installs on a web server and points users at?

 

My current plan for data analytics for our school / group is to have a set of (probably Python) scripts that take in data from CSV / XML files (we export data from the iSAMS API) and produce a set of static HTML files that can then be served to appropriate users, with suitible authorisation settings. I'd split users into groups (teachers, senior management, finance, etc) and generate a separate dashboard for each, making sure each dashboard could only be viewed by the appropriate users. If a bunch of data-processing scripts already existed in some form which I could use that would be very handy and save a lot of time.

 

 

Hi David, the way it works is we use Groupcall's Xporter to take data from an MIS system, and then basically populate the data into a PostgreSQL database, which can then be queried and visualised using something like Superset (or if the school has a prioprietary BI tool they can use that). The code could run locally on a machine as long as the user has relevant permissions, however it may be best running on a server, where the authorised user can log on and query data, it's really up to the school how it can be implemented.

 

With what you're trying to do, you could run the code from a server and host the database in the same place. You can then set the permissions at the database level for each user/group, and have them connect to the server via their local machine using Superset, Power BI, whichever tool they use, and they can then access the data authorised to that user based on the permissions set at the DB level. You could also script the import to run on the server at a set time, say once a day, so the users always get fresh data.

[duncanb]

Hi Ben

 

It has been interesting to see how the project has developed and I wish you the best with it.

 

Hi Tony, thanks very much!

[dmj]

Noted! Thanks for the feedback. You can follow the project on GitHub here: https://github.com/SchoolsBI -- Xporter provide a nice sandbox for us to test the code in, which we're doing now, and we've coded for a number of endpoints so far.

"SchoolsBI doesn't have any public repositories yet."

 

You might have trouble getting PR's from the community

[dhicks]

we use Groupcall's Xporter to take data from an MIS system, and then basically populate the data into a PostgreSQL database, which can then be queried and visualised using something like Superset (or if the school has a prioprietary BI tool they can use that).

 

Okay, so your project is producing a bit of software that sits between Xporter and PostgreSQL. What language / framework / technology stack is this component written in?

 

Are you also aiming to provide pre-made templates or similar for Superset? Sorry, I'm not familier with Superset, but I would guess it lets you import templates of some kind for the reports / dashboards you want to produce, is that right?

 

With what you're trying to do, you could run the code from a server and host the database in the same place. You can then set the permissions at the database level for each user/group, and have them connect to the server via their local machine using Superset, Power BI, whichever tool they use, and they can then access the data authorised to that user based on the permissions set at the DB level. You could also script the import to run on the server at a set time, say once a day, so the users always get fresh data.

 

My plan would be to have an, as much as possible, self-contained application that is easily deployable on a school's own infrastucture, with users authenticating and viewing dashboards via a web browser. I currently have an application that can be deployed on a server to provide a very basic web-based interface to trigger batch jobs (see https://github.com/dhicks6345789/web-console - currently on version 0.1, not for a production environment, although I am using it myself for a couple of things and it mostly works) and am developing a software-as-service application to handle OAuth / email logins to work with that application.

 

If you are working on something that could be triggered as a batch job (once a day or whatever) that that might work very nicely with what I'm doing. I can be available to talk via email or video conference at some point if you want to have a more detailed conversation, although if this is an open project then replies to this public forum might be equally appropriate.

[dylanm]

Noted! Thanks for the feedback. You can follow the project on GitHub here: https://github.com/SchoolsBI -- Xporter provide a nice sandbox for us to test the code in, which we're doing now, and we've coded for a number of endpoints so far.

 

Wonde are pretty good - They can provide you with a sandbox like environment / fake data.

 

If I was building or using the product, I would prefer to use Wonde (both as a developer and school tech) as it allows the school to control what data is being sent to your application (eg. filter students where parents haven't consented to their kids data being used to other systems) and plus they have a unified API for multiple MIS's so you don't have to write a different code base for each MIS.

[dhicks]

plus they have a unified API for multiple MIS's so you don't have to write a different code base for each MIS.

 

I think Xporter provides something very similar, although I'm not familiar with it myself. Other unified-interface-for-multiple-MIS systems exist (meta-MIS?), in particular Clever in the US. I'm guessing this project would, ideally, support getting data via several mechanisms and putting them all into one standard data store (a meta-meta-MIS?).

[duncanb]

Wonde are pretty good - They can provide you with a sandbox like environment / fake data.

 

If I was building or using the product, I would prefer to use Wonde (both as a developer and school tech) as it allows the school to control what data is being sent to your application (eg. filter students where parents haven't consented to their kids data being used to other systems) and plus they have a unified API for multiple MIS's so you don't have to write a different code base for each MIS.

 

Yeah I've used Wonde in some schools and it seems useful for a lot of applications. We've chosen Groupcall's Xporter to develop initially as that seems to be generally used most (at least in this area), but when the repository is made public there's nothing stopping someone developing a version to work with the Wonde API as well.

[duncanb]

Hi all,

 

Just a short note to say that the schools.bi project is now open on GitHub!

 

Link to repo: https://github.com/mgregson-vldb/schools_BI

 

Let me know what you all think, we'd love to hear some feedback.

 

Ben

[JStocksy]

Whatever happened to this? Did it not take off? The website has disappeared.