Move from Scholarpack to Bromcom

[Leia]

we've got a primary that wants to move from Scholarpack to Bromcom, nothing wrong with their Scholarpack MIS but their Head is a previous Bromcom user and very keen to move. Has anyone on here had much success in getting out Attendance data for the migration via the excel spreadsheets that Bromcom sends the school to complete? Thanks in advance, May the force be with you today.

[Ditto]

we've got a primary that wants to move from Scholarpack to Bromcom, nothing wrong with their Scholarpack MIS but their Head is a previous Bromcom user and very keen to move. Has anyone on here had much success in getting out Attendance data for the migration via the excel spreadsheets that Bromcom sends the school to complete? Thanks in advance, May the force be with you today.

 

Bit of a side topic, but we had a new head that want to move from our cloud based system to SIMS - it's was an utterly selfish and illogical approach just because they didn't want to spend a little time learning something new. He wanted to ditch gmail for outlook too, and tried to do it himself by using some replication tool and caused havoc resulting in endless numbers of staff to complain about non-replies - he never saw them!!! We were a new cloud based school and the policy for our MIS and Google Suite was long established. Fortunately the exec-head, aligned with the governors, put the head straight!

 

On your topic of the transfer of data, I would expect Bromcom manage this for you as part of the well-tested migration piece.

[paulkerton]

Bit of a side topic, but we had a new head that want to move from our cloud based system to SIMS - it's was an utterly selfish and illogical approach just because they didn't want to spend a little time learning something new. He wanted to ditch gmail for outlook too

The days of headteachers making executive decisions for schools or MATs is long gone. What a shame he didn't realise that.

[asteer]

we've got a primary that wants to move from Scholarpack to Bromcom, nothing wrong with their Scholarpack MIS but their Head is a previous Bromcom user and very keen to move. Has anyone on here had much success in getting out Attendance data for the migration via the excel spreadsheets that Bromcom sends the school to complete? Thanks in advance, May the force be with you today.

 

While Im not looking at a transfer of data from Scholarpack to another MIS, I am looking at getting attendance out of our 7 Scholarpack schools for some central MAT analysis, and I can say that the inbuilt reporting in the system is a dogs dinner. Ive only looked at the reports so far and not the data extracts (CTF, B2B ect) but I cannot find an easy way to get a flat file of a students attendance. The best I can do is using the Official register report to extract a file that only allows a 30 day time period and im currently looking at transforming this into a format that will be useable for further analysis.

 

It might be something the school needs to go to scholarpack about to see if they can do it straight from the DB.

 

This is one of the reasons I hate cloud based systems, while from a "core IT" persepctive, it gives gains on security, backup, access ect. From a data point of view ive never come across a good system. They make access to the data hard to get to, and give you reporting solutions that are subpar compared to what else is out there.

 

I know cloud is the way forward, but give me an on-prem DB server anyday

[bobsmith]

I know cloud is the way forward, but give me an on-prem DB server anyday

 

Once upon a time I was with you on this topic - but a database you can get striaght into isn't quite the advantage you may think it is.

 

1. you gotta be pretty sharp at SQL reporting to get anything

2. very easy to break stuff if you don't know exactly what you're doing

3. have you seen the schema for SIMS???

4. You know SIMS (and everyone else) are REALLY unhappy if you mess around with the database (even read-only in SIMS Case)

 

Now with what I've seen from bromcom and arbor - you can use modern reporting tools like powerbi to query via API and it looks pretty good so far.

 

I'm getting our first test database (with our real data) on the 18th of March and I will be testing extensively to see what I can pull out into PowerBI.

[supportman]

Just export to CSV and archive? Why does it need to be in the new MIS?

[asteer]

Once upon a time I was with you on this topic - but a database you can get striaght into isn't quite the advantage you may think it is.

1. you gotta be pretty sharp at SQL reporting to get anything

2. very easy to break stuff if you don't know exactly what you're doing

3. have you seen the schema for SIMS???

4. You know SIMS (and everyone else) are REALLY unhappy if you mess around with the database (even read-only in SIMS Case)

Now with what I've seen from bromcom and arbor - you can use modern reporting tools like powerbi to query via API and it looks pretty good so far.

I'm getting our first test database (with our real data) on the 18th of March and I will be testing extensively to see what I can pull out into PowerBI.

 

It’s probably blowing my own trumpet, but I’ve been an SQL report developer for 15 years (using raw SQL as well as proprietary reporting solutions), so I personally feel confident I can get the data I need (I'm a data person, not an IT person (I’ve got the quals, just went down the database route, not the cable bashing/hardware route) - do I need to hand my edugeek card in for not being an "IT" techie

 

I’ve seen some really bad scheme in my time (UNIT 4 QLS at HE was a nightmare), but as I spend every day looking at the data I can normally understand the scheme after a while.

 

I feel that statutory education (Primary and Secondary) software providers really don't like you looking at the DB, but having worked at FE and HE I can tell you the opposite is true there, most suppliers loved us doing this, and sometimes even supported us/expected us to do it.

 

I can see why the statutory sector wouldn't want it done, with schools traditionally only having the "tech support" onsite for that school, no-one would want the office admin who has no idea of data integrity and security to be accessing the DB directly. But with the move to MAT’s and more centralisation I think this view is very old fashioned, if a central team has the ability to do this kind of work, it should be empowered to do it.

 

With cloud based systems that are not properly designed, if I cannot even get a flat file CSV/API integration into common tools (i.e. not tools that run on a cloud based subscription model), I don’t think it’s really worth it. You have to look at a system as a whole, it could have all the bells and whistles at the front end, but if I cannot analyse the data whats the point.

 

If an organisation, such as a MAT, functions very much like a mini local authority/Further Education College/University (who would all have access/tools and people to do this), it should act like one. Also if the DB is on prem, I will do what I want with it, as long as I take regular backups and add views and tables to a linked server and not to the production DB I see no problem with it.

[psydii]

The cost of HE/Charity equivalents of Schools' MIS are 10-100 times those schools are used to paying. This why school MIS are inflexible, and if you aren't paying 100's of thousands per year of the software, you *likely* aren't paying for a competent SQL DBA either. And the worst thing that could happen to a database is for somebody who does not realise their limitations goes in with an update query.

 

That said, an onsite DB is your data on your disks. It is a lot easier for a 3rd party to migrate everything out of that, vs some limited API provided by the out going supplier. To which I think is what the OP is alluding.

 

Why would you want to bring across legacy data? Analytics. You need historical data to draw trend lines. Sometimes you want a report that you hadn't thought of from last years data. THe quality of data is dramatically better everywhere I look compared to ten years ago. Back then just abandoning data was fine because it was argued to be garbage. It is harder to make that case today.

 

*I had a competent DBA/Developer on my team once. Proved multiple times that bugs in the application code were causing the errors from the database. Without them, SLT would have accepted the vendors assertion that it was our server hardware/ windows installations that were at fault.

[Ditto]

@asteer, on topic (sort of) I used Scholarpack so briefly and so minimally, I can't give hands-on advice. Yet, your goal does seem very reasonable and should be straight-forward. A quick google of Scholarpack reporting sent me to https://support.scholarpack.com/hc/en-gb/articles/204438771-Attendance-Reports#reporting-reports-attendance-0-0

 

Is there anything in there that helps (and apologies if you have already researched that route). However, I would hope a 5 minute support call with Scholarpack should have you up and running - are they able to help?

 

On the slightly more off topic conversation around data access and the pros/cons of cloud versus local server based solution, I disagree with a few comments made by others and agree with some. Like you, I am more of a data person than true IT techie, but I think we are both welcome to Edugeek!

 

The bit I agree with is it all comes down to the design and data models and less whether the implementation is cloud or local server. I had quite a few years working on Progresso. I had access to the usual reports, but in addition I had access to SSRS to and would have been able to pull a report together in quick time if needed - and that system, whilst having some horrific data models, was fully cloud based. The non-school MIS I am using now is fully cloud based, but reporting is weak. I'm currently working with the provider to have read access to the raw data and developing my skills in PowerBI and possibly looking into Tableau. Even as read only, I need to be careful I don't swamp their resources with poorly written queries leading to poor execution plans. There is a danger I could impact other users performance (and I am aware there are solutions to that, indeed progresso implemented sharding), but the current MIS provider I am using is very small. So I guess in conclusion, the data conundrum for me is all about the provider, the data models and willingness to play ball, and not whether it's cloud or not.

[Mr.Ben]

The days of headteachers making executive decisions for schools or MATs is long gone. What a shame he didn't realise that.

Absolutely this. They need to be consulted (as do all users of any system before change).

 

The clue I always feel is in the job title Head Teacher. I've met a few great primary heads who have a handle on finance, it and HR but they really are a tiny minority.

 

Head teachers should not be dealing with operational issues in MATs. The time saved should free them up to improve T&L.

[Leia]

Thank you everyone for your concise replies. I was looking for anyone with experience from the move from ScholarPack to Bromcom really. The Bromcom excel extract they require seems impossible to obtain from ScholarPack via their basic reporting system (none of their basic reports cover it - though, and we cant extract via SQL (im pretty sharp at that myself), we cant export to .CSV either. Given the excel extracts that Bromcom expect us to export the data into i don't think they've much experience if any with a ScholarPack migration?

[asteer]

@asteer, on topic (sort of) I used Scholarpack so briefly and so minimally, I can't give hands-on advice. Yet, your goal does seem very reasonable and should be straight-forward. A quick google of Scholarpack reporting sent me to https://support.scholarpack.com/hc/en-gb/articles/204438771-Attendance-Reports#reporting-reports-attendance-0-0

Is there anything in there that helps (and apologies if you have already researched that route). However, I would hope a 5 minute support call with Scholarpack should have you up and running - are they able to help?

Cheers for that, I've been through the Scholarpack attendance reports and while they work fine internally for a school, I cannot find anything that easily gives me a flat file format for any period over 30 days that will allow me to extract the information for further analysis. Scholarpack do offer a MAT version of the system, but it will have a cost so I want to see what I can engineer first (I'm a big fan of the KISS principle (Keep it Simple, Stupid))

 

Back to the origional post @Leia, what is the format of the Bromcom spreadsheets that you need to use. Ive been sucessfull* in taking a report from Scholarpack and then converting it into a format that is semi-reasonable for further data translation

 

The extract comes out of Scholarpack like the below.

 

[TABLE=class: grid, width: 500]

[TR]

[TD]First Name[/TD]

[TD]Surname[/TD]

[TD]Year[/TD]

[TD]01/02/2022[/TD]

[TD]02/02/2022[/TD]

[TD]03/02/2022[/TD]

[/TR]

[TR]

[TD]Test[/TD]

[TD]Student[/TD]

[TD]3[/TD]

[TD]/\[/TD]

[TD]/\[/TD]

[TD]/\[/TD]

[/TR]

[TR]

[TD]Bob[/TD]

[TD]Smith[/TD]

[TD]3[/TD]

[TD]/\[/TD]

[TD]L\[/TD]

[TD]M\[/TD]

[/TR]

[/TABLE]

 

I then use an ArrayFormula with Flatten and Split functions in Google Sheets to format the above data as the below

[TABLE=class: grid, width: 500]

[TR]

[TD]Name[/TD]

[TD]Date[/TD]

[TD]Period[/TD]

[TD]Mark[/TD]

[/TR]

[TR]

[TD]Test Student[/TD]

[TD]01/02/2022[/TD]

[TD]AM[/TD]

[TD]/[/TD]

[/TR]

[TR]

[TD]Test Student[/TD]

[TD]01/02/2022[/TD]

[TD]PM[/TD]

[TD]\[/TD]

[/TR]

[TR]

[TD]Test Student[/TD]

[TD]02/02/2022[/TD]

[TD]AM[/TD]

[TD]/[/TD]

[/TR]

[TR]

[TD]Test Student[/TD]

[TD]02/02/2022[/TD]

[TD]PM[/TD]

[TD]\[/TD]

[/TR]

[TR]

[TD]Test Student[/TD]

[TD]03/02/2022[/TD]

[TD]AM[/TD]

[TD]/[/TD]

[/TR]

[TR]

[TD]Test Student[/TD]

[TD]03/02/2022[/TD]

[TD]PM[/TD]

[TD]\[/TD]

[/TR]

[TR]

[TD]Bob Smith[/TD]

[TD]01/02/2022[/TD]

[TD]AM[/TD]

[TD]/[/TD]

[/TR]

[TR]

[TD]Bob Smith[/TD]

[TD]01/02/2022[/TD]

[TD]PM[/TD]

[TD]\[/TD]

[/TR]

[TR]

[TD]Bob Smith[/TD]

[TD]02/02/2022[/TD]

[TD]AM[/TD]

[TD]L[/TD]

[/TR]

[TR]

[TD]Bob Smith[/TD]

[TD]02/02/2022[/TD]

[TD]PM[/TD]

[TD]\[/TD]

[/TR]

[TR]

[TD]Bob Smith[/TD]

[TD]03/02/2022[/TD]

[TD]AM[/TD]

[TD]M[/TD]

[/TR]

[TR]

[TD]Bob Smith[/TD]

[TD]03/02/2022[/TD]

[TD]PM[/TD]

[TD]\[/TD]

[/TR]

[/TABLE]

 

 

A couple of caveats on this:

1. I can only find a report that runs for a 30 day period, so if you want a couple of years worth of data, you have to re-run this report for multiple 30 day periods in the bigger timescale
   
2. You need to have a another extract of student information to allow you to create the concatenated student name (the actual data im using will use either the UPN or the primary id within Scholarpack to do the matching)
   
3. The code has been bashed together by me from a couple of sources, with additional support from some "random" person on Stackoverflow so may have some unintended consequences.
   
4. Ive desinged this in Google Sheets as I have plans further down the line to use the data, in could be done in Excel, but the code would need reverse enginerring
   

 

If you need any help with this, let me know

 

*I got about 99% of the way there and then someone of Stackoverflow helped me the rest of the way - https://stackoverflow.com/q/71323530/9606612

Edited March 3, 2022 by asteer