Live MIS data in Google Spreadsheets

[CyberNerd]

Hi All.

 

I've managed to get live (ish) SIMS data imported into Google spreadsheets (and hence appengine and any other service).

 

What I've done so far is:

Create a report in SIMS.

Have commandreporter export that data to a csv on the SIMS server, scheduled every 120 minutes.

Install the Google Secure Data Connector.

Have the Secure Data connector pick up the SIMS report

 

used the function =importData("https://192.168.1.123/SIMSExport.csv")

 

and hey presto it populates a spreadsheet with several hundred student details. I can then share the data with teachers.

 

I know there is massive potential to have live spreadsheets as well as a development platform - I just need some ideas as to what to do with the data.

It would be trivial to pull out anything in SIMS, so an email app would be good but I've never coded in appengine before, so spreadsheets would probably be a starting point.

Edited January 23, 2012 by CyberNerd

[penfold_99]

I had a chat with a google developer at BETT about Google Secured Data Connector to see if it was possible to link SDC to a protected source of information.

The issue with SDC is that the source of the information has to be unsecured (ie internal web server etc) you can't pass a username and password to if the source requires authenication.

 

The best use of the data would be as a data source for a mail merge, this would allow admin staff to create the document in google docs instead of SIMS>net reporting.

[jdoyle]

has this been run by whoever is responsible for data protection?

[edutech4schools]

Not something I have looked into yet. Nice one CyberNerd.

[CyberNerd]

I had a chat with a google developer at BETT about Google Secured Data Connector to see if it was possible to link SDC to a protected source of information.

The issue with SDC is that the source of the information has to be unsecured (ie internal web server etc) you can't pass a username and password to if the source requires authenication.

 

I'm not sure which engineer you spoke to, but it certainly doesn't have to be in an unsecured webserver - for example the SDC could get the data from any established secure method, then publish it to itself. The way I did it was to make sure the only machine that can access the SIMS server is the Secure data connector (SDC) in the DMZ. This is over an HTTPS connection. The firewall won't allow connection from elsewhere and the Apache installation on the SIMS server won't allow a connection from anything other than the SDC. The only internet machine that can talk to the SDC in the DMZ is the Google server.

 

 

has this been run by whoever is responsible for data protection?

 

Yes. It's all on a safeharbor site - and we informed the ICO about it.

The risky think is teachers sharing the data with people outside the organisation, wich I estimate is about the same as tehm emailing it unsecured.

[CAM]

If you give that data to a teacher, they will E-Mail it accross the Internet unencrypted or leave it on the train on a memory stick/laptop in a flash. Exporting data from your MIS needs to be kept to a minimum because:

 

A) Security risks from data loss, spreadsheets make this much easier as staff do not always understand how sensitive it is!

B) As soon as the data leaves your MIS it is out of date.

C) Any changes you make to the data cannot always be imported back in (SIMS for example won't allow writeback).

 

If you are storing data in an external source anyway, why are you bothering with a MIS? The data held in the MIS is the data you should be going to. If exporting the data to a VLE, only the data needed should be exported and not full pupil records as an example.

 

It's a very good idea from a technical standpoint (hell I'd play with that idea using test data) but the use and security is a tad questionable unless you only want snapshots, not as a live system.

[CyberNerd]

If you give that data to a teacher, they will E-Mail it accross the Internet unencrypted or leave it on the train on a memory stick/laptop in a flash. Exporting data from your MIS needs to be kept to a minimum ...snip

 

We already allow teachers to run reports and export Excel (which they can access at home). The difference here is that the spreadsheet is updated automatically, and isn't out of date.

 

I

If you are storing data in an external source anyway, why are you bothering with a MIS? The data held in the MIS is the data you should be going to. If exporting the data to a VLE, only the data needed should be exported and not full pupil records as an example.

 

 

 

Good question. I'm not entirely sure why I've done it. That was part of the question. I can get all the data into appengine so I can now write an application to do pretty much anything I want with the data. As Penfold_99 said, mail merges would be a starting point. Presumably there would be a way of giving parents secure access to reports using appengine. I've not thought about how to do that though. Any more ideas keep em coming

 

 

It's a very good idea from a technical standpoint (hell I'd play with that idea using test data) but the use and security is a tad questionable unless you only want snapshots, not as a live system.

 

It's only 'live' in that it's exported directly from SIMS. It doesn't write back into the SIMS database. it would be cool if it does, but it doesn't.

[CyberNerd]

I wonder if I could export TimeTable Data out of SIMS and import it directly into the student/teachers Google Calendar....

might have a look at this later.

[edutech4schools]

If you give that data to a teacher, they will E-Mail it accross the Internet unencrypted or leave it on the train on a memory stick/laptop in a flash. Exporting data from your MIS needs to be kept to a minimum because:

 

The data is going into Google which is secure and will also have lots of other sensitive data. Why would they need to put it on a stick or anywhere else if they can access the data from anywhere anyway, that's the point of going cloud based is it not.

[edutech4schools]

@CAM what does your sig mean?

K l E++++ m5 C B-18 O L S0 T A H b1 D0

[penfold_99]

I'm not sure which engineer you spoke to, but it certainly doesn't have to be in an unsecured webserver - for example the SDC could get the data from any established secure method, then publish it to itself. The way I did it was to make sure the only machine that can access the SIMS server is the Secure data connector (SDC) in the DMZ. This is over an HTTPS connection. The firewall won't allow connection from elsewhere and the Apache installation on the SIMS server won't allow a connection from anything other than the SDC. The only internet machine that can talk to the SDC in the DMZ is the Google server.

 

When I said unsecured I really meant unauthenticated.

SDC doesn't allow you to pass a username and password to the server that the file you wish to access resides on.

 

I was discussing with the developer how we could embed SDC but replace how I'd connects to the data source

As we would like to connect SDC to our SIF agents data source. This would be constantly upto date and with app engine integration allow right back into sims.

[CyberNerd]

When I said unsecured I really meant unauthenticated.

SDC doesn't allow you to pass a username and password to the server that the file you wish to access resides on.

 

I was discussing with the developer how we could embed SDC but replace how I'd connects to the data source

As we would like to connect SDC to our SIF agents data source. This would be constantly upto date and with app engine integration allow right back into sims.

 

oh. I understand. There are methods to put the data to SDC which are authenticated. An ssh/rsync push would do it, perhaps wget from the SIMS server - either way mean you then need to have SDC pick up the data just from the SDC server itself.

Write back into SIMS would be really good - a much better method than commandreporter.

I

[CAM]

@CAM what does your sig mean?

 

It's a riddle. Bob knows what it is.

[edutech4schools]

It's a riddle. Bob knows what it is.

 

Hmmmm another riddle.

 

any clues?

[vikpaw]

If you just dumped live (ish) data into a spreadsheet, whack on some formulae and analyse it! The biggest problem is analysing assessment data and extracting via a report is quite possible, so giving access to staff and then analysing it makes a lot of sense. You could even with other data such as photos and email addresses, send it to someone. there are other ways to do it, and not much point doing it in Google, but as a proof of concept, for something that's free, it's pretty cool.

[matt40k]

I hope for your sake the schools governers has sign this off - personally as a parent I would want to be informed that my child's data is being sent off to non-EU companies\data centers - I appreate this is Google and they take security very seriously - Security & Privacy ? Google Apps for Education but still.

[CyberNerd]

I hope for your sake the schools governers has sign this off - personally as a parent I would want to be informed that my child's data is being sent off to non-EU companies\data centers - I appreate this is Google and they take security very seriously - Security & Privacy ? Google Apps for Education but still.

 

Yes and we informed the ICO - parents can see out ICO listing. Many multinational companies are using Google for this sort of thing. As I said earlier in the thread I think we are more at risk with people sharing or loosing spreadsheets exported from SIMS. At least I can audit permissions and see who has access with Google.

[matt40k]

Yes and we informed the ICO - parents can see out ICO listing. Many multinational companies are using Google for this sort of thing. As I said earlier in the thread I think we are more at risk with people sharing or loosing spreadsheets exported from SIMS. At least I can audit permissions and see who has access with Google.

 

So long as you've agreed a solution and you've limited the risk, you'll be fine. So if you're exporting spreadsheet to USB pens, the usb pens are encrypted and the teachers are trained not to save other then the encrypted usb pen. Similar thing with Google Spreadsheet, don't save anywhere else etc.

[edintheclouds]

Hey, good work, CyberNerd! I stumbled across your post when looking for a way to interface with SIMS in order to extract the data needed to create Groups in GApps. Does your approach do that, and do you know if that's the best way to tackle this requirement? I have next-to-no programming/scripting knowledge, but could lay my hands on some if need be.

[Steven_Cleaver]

Hope this isn't off thread, took a slightly different approach created an App for this so created the Marksheets in SIM's with data being analysed as much as possible then export these to Excel onto the Network and tidied these up then these are linked to the App with a button for each departments data.So data doen't leave Network Staff use remote access if they wish to use access the data outside of school hours. So data stays in school.

[CyberNerd]

Hey, good work, CyberNerd! I stumbled across your post when looking for a way to interface with SIMS in order to extract the data needed to create Groups in GApps. Does your approach do that, and do you know if that's the best way to tackle this requirement? I have next-to-no programming/scripting knowledge, but could lay my hands on some if need be.

 

We used the Google Active Directory sync to create teachers mailing groups.

I've not tried creating classgroups in google apps (we have classgroups in moodle synced to SIMS and tend to use moodle for that type of thing) but maybe you could do it with google-apps-manager and a scheduled script?

GettingStarted - google-apps-manager - Downloading, Installing and Using GAM - Dito Google Apps Manager - Google Project Hosting