Pros and Cons of SSRS/MS Report Builder

[RootsSchImp]

Hi everyone,

 

I've been asked about SSRS reporting and whether it's worth using to query the tables of an MIS (SIMS or Bromcom) to overcome the limits of their reporting engines. Has anyone done this and was it worth the trouble? Also, for Progresso users how do you rate SSRS in general? Would you say it's easy to train a data manager use it to get all the reports they need?

 

I've had a look and it seems complicated to me but I'd really appreciate any input from the hivemind.

 

Cheers

 

Ed

Edited October 4, 2017 by RootsSchImp
Clarity of thread subject

[pcstru]

Any direct access to the schema of a modern schools MIS will be problematic. Typically, the schemas are many hundreds of tables and finding the tables you need and their relations is not a simple task. Ideally, if using a 3rd party reporting or analysis package, you want a layer of abstraction that removes unnecessary detail and is well documented. Otherwise you are into reverse engineering a complex schema, which even if you have the skills to do it, is time consuming.

[Katy]

Any direct access to the schema of a modern schools MIS will be problematic. Typically, the schemas are many hundreds of tables and finding the tables you need and their relations is not a simple task. Ideally, if using a 3rd party reporting or analysis package, you want a layer of abstraction that removes unnecessary detail and is well documented. Otherwise you are into reverse engineering a complex schema, which even if you have the skills to do it, is time consuming.

 

I can only echo this. Our MIS is iSAMS which itself uses SSRS for all report generation, and even after doing all their courses on "SSRS with iSAMS" it's a very complex task working out which table is what, currently only myself and my line manager ("Assistant Head i/c Digital Strategy", also head of computing) write SSRS reports as it's far too complicated to give the task to anyone else, definitely not secretarial staff. Even my senior tech struggles with SSRS linked to the MIS.

[RootsSchImp]

Interesting thoughts, thanks guys, I thought it seemed like a challenge on a school admin level, although perhaps mitigated with different software - anyone use Cristal Reports/similar?

 

How about for a MAT team? Is anyone part of a trust that can query their MIS directly with SSRS?

[danbuntu]

We are using SSRS combined with SSIS to create a datawarehouse. This is very complex to set up but done correctly you end up with nice neat de-normalised tables to report from. It also becomes reltivly easy to manage once the learning curve starts to flatten.

 

I've also found the Apex SQL search plugin super useful when reverse engineering tables.

[pcstru]

Interesting thoughts, thanks guys, I thought it seemed like a challenge on a school admin level, although perhaps mitigated with different software - anyone use Cristal Reports/similar?

Yes I've used Crystal. It is the same issue - the MIS will be 1000 tables - figuring out where your data is and how it all joins together can be ... err ... fun. I've so far done Bromcom and CMIS from scratch, SIMS and ONE I had sneaky inside info on so a huge head start.

 

The other thing that will also happen is that if you report directly off the tables, upgrades are likely to break your creations (and that will of course become apparent at the most inconvenient times!).

 

How about for a MAT team? Is anyone part of a trust that can query their MIS directly with SSRS?

I write SQL and query directly using VBA and powershell. It is possible - just a matter of someone having the right skills and the time to investigate how the tables hang together. It is not trivial, has lots of got-cha's and is costly (in expensive expert time).

[matt40k]

Wouldn't touch Crystal reports for all the tea in china. What you trying to do @RootsSchImp? SSRS is designed for static reports like letters. If you're after BI type reports - dashboards, summary reports, data exploration, graphs that you can drill into\filter - you'd be better off with PowerBI \ Tableau. I know some third parties have written connectors so you can just get on working with nice simple de-normalised tables rather then doing all the boring leg work.

[RootsSchImp]

I'm advising a client about the suitability of using SSRS for reporting in general, they don't have a very specific idea of what they want, but they do have someone who used SSRS with CMIS and wants it back. My worry is what happens when that guy leaves and how easy will it be to train others to maintain the reports he creates?

[matt40k]

You won't be able to train someone in a few weeks to know SSRS and SQL to the required level. Capita won't support you (write stored procedure to use in your SSRS report) and I doubt you'll find a cost effective solution. You'll be better off going "pfff, SSRS was so 3 years ago, it's all PowerBI now" then buy a SIMS > PowerBI connector and use that \ SIMS reporting engine.

[NotSoFast]

I can only echo this. Our MIS is iSAMS which itself uses SSRS for all report generation, and even after doing all their courses on "SSRS with iSAMS" it's a very complex task working out which table is what, currently only myself and my line manager ("Assistant Head i/c Digital Strategy", also head of computing) write SSRS reports as it's far too complicated to give the task to anyone else, definitely not secretarial staff. Even my senior tech struggles with SSRS linked to the MIS.

Yes, this. I and the data manager are the only people who can write SSRS reports for Progresso, and it has taken many hours of learning and practice. It is powerful though.

[bigdannyb79]

You won't be able to train someone in a few weeks to know SSRS and SQL to the required level. Capita won't support you (write stored procedure to use in your SSRS report) and I doubt you'll find a cost effective solution. You'll be better off going "pfff, SSRS was so 3 years ago, it's all PowerBI now" then buy a SIMS > PowerBI connector and use that \ SIMS reporting engine.

 

If my MIS wasnt fulfilling reporting requirements and was expensive in comparison to alternatives I'd be questioning if it was fit for the purpose rather investing further funds in other solutions to do the job for the MIS. At least ISAMs and Bromcom (for example) get that some customers have different needs so give you a reporting engine that is relatively accessible but then make available more advanced methods without you having to shell out for the prvillige. I'm amazed at how limited SIMS reporting for a product so widely used as an MIS. It should be the critical element but people seem to prioritise a familiarity with putting info in without considering the limited abilty to get info out IMO.

[matt40k]

I hate my self for saying this, but "out-of-the-box" reporting meets the requirements of a school, there is a fair amount of build in reports, then the reporting engine, then Discover. Failing that, if you want to use non-Capita tools and actually progress you're career - PowerBI is free and a connector is like £5 per school. Now excuse me whilst I go scrub myself for saying "works out-of-the-box" AND "Capita" in the same sentence.

[bigdannyb79]

Hmm depends if you want data or Information out of it I’d argue much is the former. And discover is actually quite limited when you try to use it extensively. I went from a system with just SSRS and thought the market leader would offer me more out of the box and find export to excel is the default action when analysis is needed. I see a lot of ‘happy’ sims schools that do all analysis out of the MIS - that ain’t selling it for me ...

[RootsSchImp]

Yeah, my issue with SIMS re Analysis has always been their working assumption that everyone's an enthusiastic SIMS user, so you can expect HoDs to look at a marksheet here, a group analysis over here, a photo report from somewhere else.

 

In my experience it's far more common for a muggins to have to work out everything that needs working out and deliver it to HoDs which is so labour intensive.

[fewd01]

You could simplify your reporting by setting up another database set aside from your MIS data (maybe on another server even) then run am sal task to extract the data you need. That's the techie part but you'll be left with just the data you need in more of a database structure that's relevant to your project. Also no changes to the MIS system's database to keep that happy. The powerbi connector route sounds interesting too though, haven't played with that yet.

[bigdannyb79]

So when you need data out of your MIS you look to extract it into a database for reporting on - errr isnt that the point of your MIS ?!!! Power BI or similar should be available and the MIS providers should make it easy for you to build reports that provide genuine BI and MI. Or if not provide a low cost solution you then have augment with bolt on products to provide this requirement. My point at present is you have a premium price wihtout the premium functionality.

[pcstru]

So when you need data out of your MIS you look to extract it into a database for reporting on - errr isnt that the point of your MIS ?!!!

There are a lot of potential benefits to decoupling reporting from transactions. You can then build intelligence in that makes access to information easier - an example would be partitioning & sharding data in MAT so that users in individual schools only see data for their context (potentially then reports etc could be shared without anyone needing to dip in and alter filters).

Power BI or similar should be available and the MIS providers should make it easy for you to build reports that provide genuine BI and MI. Or if not provide a low cost solution you then have augment with bolt on products to provide this requirement. My point at present is you have a premium price wihtout the premium functionality.

However good the MIS, it's application in a particular environment will be particular to that environment. Some schools make really good use of their MIS, some ... less so. The software provider will never be able to cater individually to all of those needs (they must cater on mass to meet ALL of the needs), so there will inevitably be bolt on products you can buy that can add value. The existence of those bolt on products doesn't mean the MIS providers are giving poor value to their customers. You would surely need to have a good handle on their cash flow to evidence that?

[Geoff]

Yeah this is basic ETL. Take data from your systems, shove into new database, create a cube, create reports.

[bigdannyb79]

I'll agree to disagree on a lot of things but the value question is a laughable one to debate and and a lot bigger fish than me, albiet a now dead one, called BECTA, agreed in their extensive report on the subject.

 

And yeah when the next reporting query pops up on here I'll just tell them to create and cube and bobs your uncle . . .

 

I stand by my original point that if your adding functionality to a core product in numerous areas I think you should be looking if that core offering is best fit and best value for your organisaiton rather than endless filling gaps with other products.

 

A monopoly was created by LA's making MIS choice automiatic and I think we would all be a lot better off when that monopoly no longer exisits and that wont happen until more people start to ask 'is there an alternative' . . .

[matt40k]

So when you need data out of your MIS you look to extract it into a database for reporting on - errr isnt that the point of your MIS ?!!!

You're missing the point. MI is operational - its focus is normalization - storing data once, optimized for writes. BI is denormalization, it doesn't matter data is stored multiple times, its optimized for reads. The BI produce should be product agnostic - your business (school) isn't just 1 system. MI is day-to-day, BI is more long term. It physically can't be one product \ system - their purposes are totally polar.

albiet a now dead one, called BECTA, agreed in their extensive report on the subject.

OMG, next you'll be talking about interactive whiteboards!

I stand by my original point that if your adding functionality to a core product in numerous areas I think you should be looking if that core offering is best fit and best value for your organisaiton rather than endless filling gaps with other products.

100%. But this is just common sense!

A monopoly was created by LA's making MIS choice automiatic and I think we would all be a lot better off when that monopoly no longer exisits

This annoys me so much, this stupid point of view - LA are public sector, the same as schools. They only have the schools best interest at heart. They are fully transparent and follow strict rules. If you don't like the recommend MIS system, fine, don't use it. Use something else. They pick the system based on their schools input. As painful as it is, they picked SIMS for one reason. It was the best of a the (bad) bunch. The fact schools are switches now only proves MIS providers have upped their game

[bigdannyb79]

Sims license in many la’s was top sliced from funding so to change schools had to effectively pay twice that isn’t a choice ! And for a long time many did not follow proper procurement process. And you say it’s common sense but many aren’t even aware they have a choice - that lack of awareness of the market comes from the long existence of a monopoly.

 

So I’ll keep saying similar comments when people aren’t get the information they need from their MIS - if it ain’t doing the job maybe you should see if another product that natively and freely provides multiple ways and means to access your data is a better fit - oh and it’ll prob cost you less too...

[matt40k]

Sims license in many la’s was top sliced from funding

Again, LA could only do that if the schools agreed - the idea was buying in bulk = discounts, its what MAT's are doing nowadays.

 

IMHO it's better to actually suggest these solutions then to bash folks trying to help (LAs etc) and whine about how broken the system is. At the end of the day, we're talking about a few thousand pound, yes it's alot of money, but at best we can reduce it down to few hundred and I'm not 100% sold on the real-world benefits to the students, especially when you look at the other common sense changes that need to happen - I've worked in schools that have boilers from the dark ages, they cost a small fortune to run - both in terms of maintenance and gas\electric\oil. Replacing them would save a thousand and would have a guarantee positive impact (swimming pool not being closed or school closed in winter and more money for staff). It's a question of working down the list and each school will be at different points and the MIS will be at different proprieties, personally, primary schools who use SIMS, change MIS should be pretty high on the list, it just doesn't make sense when you look at the alternatives like ScholarPack. I think Capita agrees as they've released SIMS8 for primaries. Will it be the answer? I don't know.

 

100% agree, if you're spending hundreds on your "core" MIS, then thousands on extras. That's room for improvement. If you're having to recruit loads of extra admin staff to "workaround" your MIS. That's room for improvement. But not picking your ideal MIS because its a monopoly - that's foolish. Not changing your MIS (or anything for that matter) because "it's how we've always done it" that is dangerous!

 

(going off topic - that reminds me of a story of why you need to understand the process and the purpose and not just what buttons to push)

 

Old Staff - "Put this (default value) into that field"

 

New Staff - "Why?"

 

Old - "We've always done that."

 

New - "Well why don't we not do that and save ourselves some hassle"

 

Old - "OK, uh, won't let me save if it doesn't have something"

 

New - "Shall we ask support how to change it so we can save it without it"

 

Support - "You need that for census, if you don't enter a valid value your Census will be off and it will affect your funding"

[bigdannyb79]

Individual schools were not aware and did not agree - it was taken out of the la pot before funding was distributed out to schools.

 

I’m not saying it happens now but it did and it contributed to the market position. A market dominated by one supplier is never a good scenario for the customer, in this case schools, and that’s my issue.

 

I’ll leave it there as we’re getting off topic and I’d add ssrs has a place but I find for a lot of applications in schools something like power BI is more useful with its more accessible and responsive interactivity. But be sure to read up on the licensing of power BI as it isn’t free to share anymore and these changes could kill it as a prospect for some.

[matt40k]

I think we should be clear

 

Individual schools were not aware and did not agree - it was taken out of the la pot before funding was distributed out to schools.

It's possible it the message got lost in the noise \ other things took priority at the time. Not everyone as to agree, but the bulk of them need to agree. We're not suggesting anything wasn't done correctly and is illegal. Just to want to be clear before people start slinging solicitors around.

 

 

 

Final point. Power BI might be free to get going, but it has a cost. You're time isn't free and you'll need to get the OK before you start - you need to check your data policy covers its usage for starters and you definitely want to check your covered before you publish - don't want parents kicking up a fuss when they find out you've send their data to Ireland

[bigdannyb79]

Notwithstanding the clear recommendation in the 2005 report, the 2010 Market Study reported that:

“It is clear that many LAs do not fully understand their legal obligation to conduct EU compliant

procurements for the provision of MIS, or how frequently such an exercise should be undertaken.

We estimate that, depending on the outcome of legal advice being sought by Becta, between 60%

and 80% of local authorities could be in breach of EU procurement requirements with regards to

how they acquire MIS”.

 

- - - Updated - - -

 

Its in a public document not my words.