SIMS <--> SQL DB relationship schema

[vikpaw]

Greed - 8 months, what you been up to? did you take a sabbatical?

[GREED]

Vik - I left the school and am working for a Training & Consultancy company, but I missed this place!

Plus that and we are in contact with Capita about partnering with a few things... kinda watch this space.

Hows you geezer?

[vikpaw]

I knew you left, just didn't realise you weren't still active on EG.
Let us know what you might be doing with Capita, always useful to know what's going on. Especially if you are finding ways to use the BO!
I'll pm you to catch up.

[Martin_W]

I was wondering about the reporting possibilities in SIMS, since you all have experience in performing SQL queries on the database. Is it highly unusual that you need to perform custom queries using SQL or is that regular practice? I mean it usually is a lot of work to get the experience in SQL and -maybe even harder- the data model that is used in the database. So I would expect that it is kind of a last resort if you use SQL to get to the information you need.

Does SIMS lack the proper reporting features you think, or is it not that bad?

[localzuk]

I was wondering about the reporting possibilities in SIMS, since you all have experience in performing SQL queries on the database. Is it highly unusual that you need to perform custom queries using SQL or is that regular practice? I mean it usually is a lot of work to get the experience in SQL and -maybe even harder- the data model that is used in the database. So I would expect that it is kind of a last resort if you use SQL to get to the information you need.

Does SIMS lack the proper reporting features you think, or is it not that bad?

Generally, people don't pull data from SIMS via SQL. The only people who do are usually those needing the data for use in third party software, or custom written software.

Most of the time, the information can be pulled out via reports.

[vikpaw]

Is it actually the case that Capita do not disallow you from directly accessing the database, merely that they wont support you if you do?
It seems very odd, i know that it's our data and we provide the software, but i'd have thought that it would be part of the licence / terms of use that we aren't allowed to access the underlying data. I suppose they can't do that.

Answering my own question it seems it's fine to access a copy, and so presumably doing a simple read even on the live data would be okay:

Query, SIMS and access to DB

[DBMan]

SELECT is only a step away from DROP, thus you shouldn't publicly post information about where stuff is. You also are bypassing the auditing and security. A standard SIMS user account isn't able to login directly with SQL, so you would have to create a new SQL account. Personally, if the standard SIMS tools aren't good enough, you've got the business objects. Which keeps everyone happy.

Personally, I've never struggled with the distinction between SELECT and DROP ;-)

Standard SIMS tools and business objects clearly don't keep everyone happy, as witnessed by the many threads about access to SIMS data.

[DBMan]

I was wondering about the reporting possibilities in SIMS, since you all have experience in performing SQL queries on the database. Is it highly unusual that you need to perform custom queries using SQL or is that regular practice? I mean it usually is a lot of work to get the experience in SQL and -maybe even harder- the data model that is used in the database. So I would expect that it is kind of a last resort if you use SQL to get to the information you need.

Does SIMS lack the proper reporting features you think, or is it not that bad?

SQL exists as a data access 'language' and should be the first port of call when running queries/reports against a database. That's it's job. It's not hard and is widely understood by millions...there are only 4 basic commands...SELECT is the main one.

The data model (schema) is the only issue. Once that is understood the rest should be easy. Vendors often don't want you to understand it either because it's crap and they're embarassed by it or there is a claim of intellectual property surrounding it's design.

[matt40k]

Personally, I've never struggled with the distinction between SELECT and DROP ;-)

99% of SIMS users aren't verus in SQL, they want to click a few options and bang out a few fancy reports without fear of dropping data. The SIMS reports engine is excellent at doing so and so are the build in reports, Discover is going to make (assessment) reporting extremely quick and easy. Don't slate a good system.

Standard SIMS tools and business objects clearly don't keep everyone happy, as witnessed by the many threads about access to SIMS data.

Let me first say, the problem with EduGeek is anyone can register. As far as you know I'm a female in china who works in a hotel on the reception desk. I have multiple accounts using my multi email addresses and multi ISPs (I won't get into public proxies etc).

Now this is a problem because we get a wide range of users, let me split this into groups as far as I see it who are interested in this sort of information

1/ User who is SQL guru (jinnantonnix) who likes to play direct with the database who has a SIMS license.

2/ User who thinks they are a (SQL) guru, but aren't, see this public topic do it, then later get a problem which they think they can fix, try and end up messing the system completely up and then reliese there backups don't work and have to go cry to Capita with cap in hand (thankfully, I've not heard of Capita charging - suprising eh?)

3/- User who doesn't have a SIMS license, has no interest in paying capita for a license, or working with a school and has downloaded SIMS via a torrent or such.

I have no issue with 1, other then remember if it's public, just think about 2. No 2 I have no real issue with, so long as you do it once and you learn from your mistake. As for number 3, well the less said the better.

As far as I am aware the business objects cover most things (I did ask about some finance stuff, but WIP), and avalible subject to a NDA, which I believe Penfold got - I never heard him getting charged for it, as the school he works for has a SIMS license already. I could be wrong.