Extracting data automatically from CMIS

[danIT]

Is there a way of extracting data from CMIS Facility automatically at set periods, for instance run a report and extract its contents to a csv file.

 

A way to reference the databse and pull out data perhaps for a learning platform?

 

Dan

[bossman]

I am speaking to a third party software vendor at the moment who in turn is working alongside another software vendor and we have been asked if we would like to trial this software for them.

The short of it is they have produced a intermediatary piece of software that sits between Sims or Cims plus any other SQL database and periodically queries the database for whatever information you require.

I am waiting for them to get back to me at the mo will update.

[danIT]

Sounds right up my street, exactly what im looking for.

 

Would be intrested in hearing about any results or findings, and perhaps also in PM the name of the company lol!

[danIT]

a simple vbscript or some kind of script which will connect to a sql database.

 

connect to facility database and look up the table structure?

 

anyone know anything about creating sql connections? etc...

[Guest]

If you run CMIS on SQL server (which you probably do) you should have some software with SQL Server called Enterprise Manager and Query Analyser.

 

with Query Analyser you can write SQL statements that access CMIS data and export it to a csv file.

 

You then add these statements to a procedure.

 

In enterprise manager you create a scheduled job to run this procedure every night.

It isn't very hard in theory, but you need a good understanding of SQL and of the structure of the CMIS data tables.

 

An other way is to create a "view" of the data (like a query in Access) and then link an excel spreadsheet to the view via ODBC, excel can be set to pull the data from the SQL data when it is opened (or when you click the refresh the data link). A VB script can open the excel file, refresh the data, save it and close it again - this would also need scheduling.

[Kained]

I have always found cmis way of doing things very clunky so decided that as cmis worked on a database i would write a web application to deal with the information i wanted.

 

I have written a web application to manage student events we call them referals, it allows me and staff at our school to log on and view the top "most wanted" for each year and look up which teachers they get the most referals from which lessons / times of the day and which days they struggle to behave on in hope to find patterns.

 

Anyway long and short of it dependant on what you want to do, i know a little about the structure of the facility database so if u want some help let me know.

[GeorgeFaux]

I've done it using a consultant to publish attendance data into excel documents in order to have a good overview of sessional attendance, so it is definately possible.

[limbo]

I do this on a regular basis - in fact our whole MLE / VLE / VLP or whatever it should be called is based on being able to do this.

 

If you have CMIS installed on your machine then you arlready have the ODBC connection you need to be able to access the data through something like Excel or Access.

 

Access is by far the easiest option if you are familiar with it. Assuming you are just link to the tables (or export them if you are nervous about accessing the data live) as you would any other data source.

 

That is the easy bit - the next bit is working out what data you want and where it sits in the hundreds of tables (many of them empty) in the database. Then build the queries that extract the data you want in the Access query builder.

 

I am nervous of saying too much becase the LEA have already promised to send me to bill of any school whose data gets screwed up by accessing data directly having following my advice - hence me being a little vague.

 

But I can at least confirm it is exceptionally possible to get at the data without any scripting or SQL knowledge at all. Taking it to the next step and automating it could require a little knowledge depending on what exactly you want to exctract and how.

[Kained]

I access data live via sql but as i only read from the tables it shouldnt affect the data itself, lol @ you lea threatening you with the bill

 

*hides all projects linking to cmis*

[limbo]

Even more distrubing is that the guy who said this too me now works for Capita.

 

It all came from us publishing an app that would unlock tables when they got locked. They were giving out advice for staff to go to the SQL server and manually edit the data on the system which allowed for all sorts of things to go wrong.

 

Our app was actually safer because it targeted and changed only what was needed rather than letting users loose in the raw data.

 

And we were not even charging for it!

[slilley]

I have always found cmis way of doing things very clunky so decided that as cmis worked on a database i would write a web application to deal with the information i wanted.

 

I have written a web application to manage student events we call them referals, it allows me and staff at our school to log on and view the top "most wanted" for each year and look up which teachers they get the most referals from which lessons / times of the day and which days they struggle to behave on in hope to find patterns.

 

Anyway long and short of it dependant on what you want to do, i know a little about the structure of the facility database so if u want some help let me know.

 

That's exactly what I want to do, but I can't find which table in the database that the referral information is stored in! I looked in one called STUEVENTS but it appears to be empty. Can you please tell me where to look?

thanks,

Simon

[mark80]

Hi Simon

 

i have found the tables that are used for the "Events" part of CMIS

 

APPAPPFIELDS

APPAPPEVENT

APPEVENTDEFN

 

I am trying to put together this in MS Access through an ODBC link to the live data.

 

I have stumbled across how some of the tables are linked but to be honest its a real pigs ear!!

 

Anyone know the significance of the "AppEventId"??

 

Mark

[limbo]

Just had a very quick look at this - and we have hardly used the event log so I do not have a lot of data to go by.

 

It seems that the AppEventID is the unique occurance for that student - if you find a nice high number in this field and then filter any student that has that number then they appear to have a lot of entries with different AppEventID's - my data has a lot of gaps in these numbers which may or may not be the norm.

 

So the key to the data seems to be matching the AppEventID (defined in the APPEventDefN table) with the EventID between the APPAPPEvent table (where this pair of entires should be unique in any one record) and the APPAPPFields table where the pair of numbers can be found together frequently, each time with a different field ID and Value Data. This FieldID will probably match up with the defined fields in the APPEVFields table.

 

Remember to match the students in both tables and the SetID.

 

Does any of that make sense? I think I started to loose it on about the second line.

[limbo]

I have just thrown together a query on this that should pull everything out - not sure about posting the SQL on here in detail - worried about possible security implications and what it might do to the layout of the page!

 

PM me if you want a copy of the SQL statement.

 

One thing I found to watch for - the fields seem to be very limited on the number of characters, so some of the longer inputs have been spread accross two records - the linenum value in the APPAPPFIELDS table is the order these inputs need to appear in (all other values will be the same - apart from the input obviously).

[mark80]

@ Limbo

 

cheers for taking time to look into this for me

 

the attached picture shows the query output that i have created in MS Access. This particular student has 12 events that can be viewd in CMIS but i cannot see a reason as to the jump from 8 to 431??

 

If you could PM that SQL that would be great 8)

 

Mark

[limbo]

I do not think there is a reason for the jump - my data has the same gaps.

 

There is no other data that defines the individual instance so that has to be the field.

[mark80]

Cheers m8

 

Looks like we are singing from the same sheet.

 

As i am looking to importing a spreadsheet that keeps track of students visiting our referral room (yes i know we can do that in ePortal directly but dont ask!! )

 

If i am going to import this spreadsheet into the system i need to find what determines the value of the AppEventId :?:

 

Mark

[limbo]

Not brave enough to pull data back in to CMIS for just this reason - many of the tables have no logic to them at all - have you seen the way it records lesson by lesson attendance!!

[mark80]

cheers limbo

 

i have asked our Serco support to provide me with the database diagram that i can import to the SQL Server so i can see what and how tables are linked

 

It boggles my mind that this isnt readily available as surely you would need such a thing to create the bloomin database!

[budgester]

Well here is some fun code that I have been hacking around with in php.

 

It reads assesment data from the KS3, KS4 Assesment framework.

 

It should be relatively safe to use, as at the moment it only does database reads, no updates or drops etc.

 

This is not release quality, but if enough people are interested it could be built into a community project.

 

License is GPL 2

 

edit the config.php file with the connection details for your database.

 

Regards

 

Budgester

chace-mis.tar.gz

[slilley]

Hi Simon

 

i have found the tables that are used for the "Events" part of CMIS

 

APPAPPFIELDS

APPAPPEVENT

APPEVENTDEFN

 

I am trying to put together this in MS Access through an ODBC link to the live data.

 

I have stumbled across how some of the tables are linked but to be honest its a real pigs ear!!

 

Anyone know the significance of the "AppEventId"??

 

Mark

 

Thanks Mark, I see what you mean about the pigs ear!!

 

Simon

[budgester]

If you mean APPEVENTID in the APPAPPSFIELDS table.

 

Each occuring event is given an individual ID

 

If an event has more than one field assigned to it then each field is stored in a seperate row in the APPAPPFIELDS table, and the event is identified by the APPEVENTID.

 

Or so I believe.........

[Kained]

mark80

It boggles my mind that this isnt readily available as surely you would need such a thing to create the bloomin database!

 

CMIS was written by an external company and changed hands many times the reason for the blank tables are for some schools in different areas that require them and for bits of the app you may not use, but most of all they have no idea what all the tables do and no one their end will remove any

 

-*-*

 

I have my project in asp and has been runining for well about 4 months now with only a few problems all been solved , if anyone is also going to be using asp and has any probs send me a pm and i will see if i can help with the problem

[mark80]

If you mean APPEVENTID in the APPAPPSFIELDS table.

 

Each occuring event is given an individual ID

 

If an event has more than one field assigned to it then each field is stored in a seperate row in the APPAPPFIELDS table, and the event is identified by the APPEVENTID.

 

Or so I believe.........

 

That makes sense, but what doesnt is the inconsistancy of how the number is allocated see my post on page 1

[StArbiter]

cheers limbo

 

i have asked our Serco support to provide me with the database diagram that i can import to the SQL Server so i can see what and how tables are linked

 

It boggles my mind that this isnt readily available as surely you would need such a thing to create the bloomin database!

 

Did you ever get a copy of the database diagram or any help with interfacing with CMIS ? Do we have to use SIF or are we "allowed" direct conenction to the tables ? I am getting mixed messages.