SIMS Learning about SQL and Writing basic Scripts

[LosOjos]

To be honest, I've never seen a Data Manager job that requires SQL experience; all the MIS I know of strongly discourage you directly accessing the DB. Also, taking a copy of the DB home is a huge breach of DPA! Really wouldn't recommend that.

 

Using a combination of CommandReporter, XML and $LanguageOfChoice you can do a hell of a lot with your data though, plenty to get stuck in to. Admittedly you can't write back to the DB this way and the data is never truly live, but as a DM myself it's never been a huge problem.

 

All that said, SIMS DB would be a horrible one to try to learn SQL on; it's structure is (probably purposely) obscure, you'd spend more time working out where your data is than doing anything with it, then after each update it's likely to change!

 

If you needed any more convincing; Capita will bleed your school's budget dry to fix anything you break while tinkering with SQL; you'll almost certainly be fired and once word spreads you'll be unlikely to get another DM job soon.

 

TL;DR - I strongly advise you leave the SIMS SQL DB alone

[chrisateen]

To be honest, I've never seen a Data Manager job that requires SQL experience; all the MIS I know of strongly discourage you directly accessing the DB. Also, taking a copy of the DB home is a huge breach of DPA! Really wouldn't recommend that.

 

Using a combination of CommandReporter, XML and $LanguageOfChoice you can do a hell of a lot with your data though, plenty to get stuck in to. Admittedly you can't write back to the DB this way and the data is never truly live, but as a DM myself it's never been a huge problem.

 

All that said, SIMS DB would be a horrible one to try to learn SQL on; it's structure is (probably purposely) obscure, you'd spend more time working out where your data is than doing anything with it, then after each update it's likely to change!

 

If you needed any more convincing; Capita will bleed your school's budget dry to fix anything you break while tinkering with SQL; you'll almost certainly be fired and once word spreads you'll be unlikely to get another DM job soon.

 

TL;DR - I strongly advise you leave the SIMS SQL DB alone

 

I would never ever write to the SIMS Database even if I had tons of programming experience due to the high chances of bugs and errors. All I really want to do is run scripts that enables me to get data out of SIMS (which getting data out of SIMS is mainly what we do in our jobs anyway)

 

Thanks for the help everyone

[LosOjos]

I would never ever write to the SIMS Database even if I had tons of programming experience due to the high chances of bugs and errors. All I really want to do is run scripts that enables me to get data out of SIMS (which getting data out of SIMS is mainly what we do in our jobs anyway)

 

All the more reason to use CommandReporter - it's read only, Capita approved and will allow you to pull whatever data you want from SIMS (so long as you have the permissions). It'll allow you to do more than SIMS own reporting tools will as you can pull whatever data you like to XML and tie it all together from a student ID (get yourself added to the "Third Party Reporting" permissions group to open up the usually hidden ID's).

 

Have a read through these threads to get an idea of how CommandReporter works:

http://www.edugeek.net/forums/mis-systems/66215-sims-net-reporting.html

http://www.edugeek.net/forums/mis-systems/83623-executing-commandreporter-scripts.html

http://www.edugeek.net/forums/mis-systems/119999-command-reporter-filtering-reports.html

http://www.edugeek.net/forums/mis-systems/86701-clr-params-string.html

[pcstru]

To be honest, I've never seen a Data Manager job that requires SQL experience; all the MIS I know of strongly discourage you directly accessing the DB.

 

I would certainly be asking for some SQL experience in a Data Manager and we make use of SQL to query the CMIS database. I think being cut off from native access to the data would be like losing a hand. I can certainly understand supplier reluctance to have users writing to the database and I can even understand how poor SQL can cause issues (transactional consistency can lead to performance issues) just reading the database. However, the talk is always then of supplier provided API's or reporting engines and I'm the curious as to why SQL is not seen as an appropriate API? It is so fundamental as an API that the suppliers themselves use it when developing the applications.

[chrisateen]

I would certainly be asking for some SQL experience in a Data Manager and we make use of SQL to query the CMIS database. I think being cut off from native access to the data would be like losing a hand. I can certainly understand supplier reluctance to have users writing to the database and I can even understand how poor SQL can cause issues (transactional consistency can lead to performance issues) just reading the database. However, the talk is always then of supplier provided API's or reporting engines and I'm the curious as to why SQL is not seen as an appropriate API? It is so fundamental as an API that the suppliers themselves use it when developing the applications.

 

They are asking for it when looking at Data Manager/MIS Roles in London and they tend to pay the top wage as well and if i want to get the best data manager jobs in London its something I have to learn. Plus SQL is a basic requirement for a Data Manger in the corporate world. I agree with Capita being wary of people writing to the database but I don't see the problem in people being able to have read only access as one cannot edit the database.

[chrisateen]

This forum and the photo program has given me an idea to design a program for teachers to view exam timetable eventually I want to use it to search and produce Student Timetable using exam entries data from SIMS. I know it sounds quite simple and I could do one up for the whole school on word/SIMS but i want something interactive and also a chance of developing something. I am hoping that by doing this my job can develop further and something I can put on my CV in the future.

 

I intially wanted to develop it using vba in excel but thought that over time I want to be able to develop it further and further into something more complex and not sure vba in excel is the best way to approach this.

 

Any ideas of what programming language I should use. Ideally a language that is not difficult to learn as a beginner with online/youtube tutorials and something in which I can develop the program further as I am more able to program.

 

Any ideas will be appreciated

[LosOjos]

I would certainly be asking for some SQL experience in a Data Manager and we make use of SQL to query the CMIS database.

 

The role of School DM is such a new one that it varies wildly from school to school. I personally develop in house systems and spend most of my time between that and reporting on student progress/estimates. At the other end of the scale, I know DMs who don't do much more than data input! To be honest, if a job in a school was asking for DQL knowledge, they'd have to be offering a decent wage increase compared to what I'm on - could earn a lot more in the private sector.

 

I think being cut off from native access to the data would be like losing a hand. I can certainly understand supplier reluctance to have users writing to the database and I can even understand how poor SQL can cause issues (transactional consistency can lead to performance issues) just reading the database. However, the talk is always then of supplier provided API's or reporting engines and I'm the curious as to why SQL is not seen as an appropriate API? It is so fundamental as an API that the suppliers themselves use it when developing the applications.

 

I completely agree with you, but in the specific case of SIMS you have to ask yourself if it's worth the risk of creating a huge financial burden for the school in the event that you accidentally drop a table or alter the structure in such a way the SIMS front end stops working and Capita have to repair it for you (why you couldn't just restore from backup is another matter of course...). I'd argue that it almost certainly isn't worth the risk for most SIMS DMs.

 

If anyone chooses to access the SQL DB directly, so be it! Just so long as they know the risk.

 

This forum and the photo program has given me an idea to design a program for teachers to view exam timetable eventually I want to use it to search and produce Student Timetable using exam entries data from SIMS. I know it sounds quite simple and I could do one up for the whole school on word/SIMS but i want something interactive and also a chance of developing something. I am hoping that by doing this my job can develop further and something I can put on my CV in the future.

 

I intially wanted to develop it using vba in excel but thought that over time I want to be able to develop it further and further into something more complex and not sure vba in excel is the best way to approach this.

 

Any ideas of what programming language I should use. Ideally a language that is not difficult to learn as a beginner with online/youtube tutorials and something in which I can develop the program further as I am more able to program.

 

Sounds like an ambitious project - should provide plenty of opportunity for you to develop your programming skills. There's no right or wrong language but in terms of simplicity, use one of the .NET languages. I personally prefer C# and it seems to be growing in popularity in the industry so why not give it a go? The great thing about programming is that once you know the foundations, learning a new language is just semantics (at least, learning it enough to patch something together is! Although it might not be the most optimized code in the world )

Edited October 15, 2013 by LosOjos

[pcstru]

I completely agree with you, but in the specific case of SIMS you have to ask yourself if it's worth the risk of creating a huge financial burden for the school in the event that you accidentally drop a table or alter the structure in such a way the SIMS front end stops working and Capita have to repair it for you (why you couldn't just restore from backup is another matter of course...). I'd argue that it almost certainly isn't worth the risk for most SIMS DMs.

I think the big risk is inserts and updates where you don't quite manage to maintain the integrity the application layer expects. I'd hope the SIMS support teams would spot a dropped table PDQ and I thought they had automated integrity checking at the level of the schema structure. But unpicking a bad update could take a while - which would be a bummer at consultancy rates. Sometimes the way suppliers tell it though, you would think THEY had never made a mistake or pushed poorly performing code out the door!

[LosOjos]

Sometimes the way suppliers tell it though, you would think THEY had never made a mistake or pushed poorly performing code out the door!

 

Yeah, it's not like SIMS has ever gone wrong!

[chrisateen]

The role of School DM is such a new one that it varies wildly from school to school. I personally develop in house systems and spend most of my time between that and reporting on student progress/estimates. At the other end of the scale, I know DMs who don't do much more than data input! To be honest, if a job in a school was asking for DQL knowledge, they'd have to be offering a decent wage increase compared to what I'm on - could earn a lot more in the private sector.

 

 

 

I completely agree with you, but in the specific case of SIMS you have to ask yourself if it's worth the risk of creating a huge financial burden for the school in the event that you accidentally drop a table or alter the structure in such a way the SIMS front end stops working and Capita have to repair it for you (why you couldn't just restore from backup is another matter of course...). I'd argue that it almost certainly isn't worth the risk for most SIMS DMs.

 

If anyone chooses to access the SQL DB directly, so be it! Just so long as they know the risk.

 

 

 

Sounds like an ambitious project - should provide plenty of opportunity for you to develop your programming skills. There's no right or wrong language but in terms of simplicity, use one of the .NET languages. I personally prefer C# and it seems to be growing in popularity in the industry so why not give it a go? The great thing about programming is that once you know the foundations, learning a new language is just semantics (at least, learning it enough to patch something together is! Although it might not be the most optimized code in the world )

 

C# it is then. Thanks for the advice you've been so helpful.

 

In terms of Data Managers having SQL skills I'm not really surprised especially when you come to a forum like this and find out many of us data managers are developing all sorts of inhouse systems. When a data manager has such a skill and decide to leave schools then think they should ask for that skill from their replacement.

 

However the jobs I have seen them is for are paying £40k in London which is not bad at all in this economy

[LosOjos]

However the jobs I have seen them is for are paying £40k in London which is not bad at all in this economy

 

Yeah for that money I'd definitely want more than a data input-er DM jobs around here are all sub £25k - if they insisted on SQL knowledge for that, I think they're be lucky to find anyone! I develop systems for a similar reason you plan to - career development. I'm currently studying towards a degree in Software Design with the OU so I figure if I have a few solid, working systems developed when I finish it should make finding a job that bit easier.

 

Plus I find coding much more interesting than the other aspects of my job so I'll find any excuse to do a bit!

[CAM]

Thanks for the CommandReporter advice LosOjos. Had a play with it just now and it worked (seems easier exporting to CSV then XML though!).

 

Quick note for Powershell users, wrap your parameter strings in single quote and CommandReporter.exe in double quote. Also launch it with the following syntax:

 

& ".\CommandReporter.exe"

[dhicks]

I'm starting to doubt whether I should continue as a data manager but the fact I had a 2.2 degree, will have to do a Masters (which costs £9k) plus I will have to take a salary cut to break into the industry is making me question if i should change careers.

 

A "bootcamp" style course might be more suitible for you - 12 weeks of tuition, and you come out at the end with a bunch of coding experience, a couple of completed projects to use in a portfolio and an introduction to some interesting companies at a startup fair:

 

Makers Academy

 

That particular company runs courses about the same cost as a Masters - if you don't have £7,000 or whatever to spend I'd stick with the think-of-a-useful-program-and-figure-out-how-to-write-it option.

[LosOjos]

Thanks for the CommandReporter advice LosOjos. Had a play with it just now and it worked (seems easier exporting to CSV then XML though!).

 

No problem, it's a very handy tool to have at your disposal.

 

As for CSV vs. XML, it all depends on what you want to do with it. If you're simply pulling data out then yeah, CSV is much easier to read! However, most of the work I do with CR is to pull data in to another system - that's where XML is extremely useful, as you can pull specific bits of data straight from it using XPath queries and quickly parse it in to whatever format you need for your program.

[chrisateen]

No problem, it's a very handy tool to have at your disposal.

 

As for CSV vs. XML, it all depends on what you want to do with it. If you're simply pulling data out then yeah, CSV is much easier to read! However, most of the work I do with CR is to pull data in to another system - that's where XML is extremely useful, as you can pull specific bits of data straight from it using XPath queries and quickly parse it in to whatever format you need for your program.

 

Thanks to your advice I sat down yesterday and learnt with videos from youanoinow to extract data from a database onto the programme that I am using. This will be so useful as all I need to do is extract data from SIMS import it into my system and do what I need to do with it I'm gonna try to learn how to do it with all sorts of files including csv, xlm, sql e.t.c

 

Another question how to do roll out your system to staff members once you have created your system. The reason I am asking is because at our school our IT is outsourced to different companies the main people being RM. The guy from RM who works on our school site has mentioned possible issues with security when running an exe file. I was also hoping to have Visual Express installed on our server assuming that as the school teaches GCSE & A Level computing they will have something installed and maybe even pushing it I can get Visual Studio c# if the students learn another language. However apparntly installing it is a security issue due to people being able to code an execute thier own programme which I understand but then wonder that if they teach computing how on earth do they teach it then??????

[StevieTSS]

It's good to know i'm not the only one who has dreamt of seeing SIMs elusive SQL code. I get frustrated when SIMs doesn't act like I expect, and because we used mainly SQL in my old industry job it is almost a reflex reaction to see what is actually happening behind the scenes.

 

SQL management studio sounds like a good place to start for better interrogation... how do I get it? Is it just the case that our in house IT technicians are guarding it fiercely, and I just need to bribe them? and can I run it on my laptop?!

[pcstru]

SQL management studio sounds like a good place to start for better interrogation... how do I get it? Is it just the case that our in house IT technicians are guarding it fiercely, and I just need to bribe them? and can I run it on my laptop?!

 

You can run it on any machine but you will need appropriate permissions to connect to the server and the database - that SHOULD be fiercely guarded! ISTR SIMS users are controlled by the application (but my info may be out of date), so just because you can access SIMS doesn't mean you can access the database.

[LosOjos]

Another question how to do roll out your system to staff members once you have created your system.

 

You really need to speak to your IT team, tell them what you're doing and go from there. Every school is different, I suspect if your support is outsourced you may well need support from higher up to give RM the go ahead to let you start pushing EXE's out to users.

 

As for Installing Visual Studio on the network - why? You only need it on the machine you're developing on, not the whole network. Visual Studio Express is free and should be ample to start developing: Visual Studio Express 2012 Products | Microsoft Visual Studio

 

As for how I personally roll out programs - anything that needs rolling out school wide (such as assessment tracking) is developed in ASP.NET/C# which all staff have access to via the Intranet. Most stuff I develop I'm the sole user of so they exist solely on my HDD. There are a few systems that are needed by various teams that I install locally on their machines - I have the advantage of having an IT team who trust me to do that!

[matt40k]

Going slightly off topic - @dhicks (and co) You might want to look at https://www.edx.org - they offer free courses. Harvard has a VERY good course CS50, basically they were finding students were coming to them without a basic understanding of programming - CS50 is a intro course to programming, it uses virtual Linux machines, so you don't have to worry about licensing, students hacking the system or just configuring it with the right version of this and that so it works.

https://www.edx.org/course/harvard-university/cs50x/introduction-computer-science/1022

 

Few guys here have completed it and I was surprised how good it was.

[CAM]

Thanks Matt! Passing that on to the HOD for IT.

[chrisateen]

You really need to speak to your IT team, tell them what you're doing and go from there. Every school is different, I suspect if your support is outsourced you may well need support from higher up to give RM the go ahead to let you start pushing EXE's out to users.

 

As for Installing Visual Studio on the network - why? You only need it on the machine you're developing on, not the whole network. Visual Studio Express is free and should be ample to start developing: Visual Studio Express 2012 Products | Microsoft Visual Studio

 

As for how I personally roll out programs - anything that needs rolling out school wide (such as assessment tracking) is developed in ASP.NET/C# which all staff have access to via the Intranet. Most stuff I develop I'm the sole user of so they exist solely on my HDD. There are a few systems that are needed by various teams that I install locally on their machines - I have the advantage of having an IT team who trust me to do that!

 

I don't need Visual Studio on the network (in fact the RM consultant that currently works at our school said he could install it on my machine only). What I was trying to get at was that the school offers a computing course both at GCSE and A Level so the kids need something to practice their programming skills in lessons and in school at that may mean installing programming software on school computers.

 

Sounds like I have to do a web based application using ASP.NEt/C# or worst to the worst use VBA in excel

 

Going slightly off topic - @dhicks (and co) You might want to look at https://www.edx.org - they offer free courses. Harvard has a VERY good course CS50, basically they were finding students were coming to them without a basic understanding of programming - CS50 is a intro course to programming, it uses virtual Linux machines, so you don't have to worry about licensing, students hacking the system or just configuring it with the right version of this and that so it works.

https://www.edx.org/course/harvard-university/cs50x/introduction-computer-science/1022

 

Few guys here have completed it and I was surprised how good it was.

 

Thank you soo much for that. I have signed up for the course starting in January. Hopefully I will have the time to complete it