Extracting data automatically from CMIS

[Kained]

i can see no reason why you cant directly interface with the db to get the data that you want.

 

if you want to look at whats in the db either take a direct look at the sql db or restore a backup to a msaccess db and open it up and have a play around if you want to test take the backup restored to access move it into a iis set up and use that to get the hang of things and run tests on.

 

Im curious of what other people use CMIS DB for anyone want to "own up" to and take credit for?

 

Personally i use it to pull student info, assessment results and events into intranet apps.

[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!

 

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.

 

No i didnt

 

there is however a "Data Dictionary" that is available through SERCO's new support forum but tbh its a load of poop

[maniac]

I'm looking into linking into our CMIS database at the moment to pull student timetable data to import into moodle with any luck. Does anyone else on here do this, or know if someone somewhere's developed a way of doing it. I've checked out the moodle forums, and there is talk of development of a cmis link, but no actual working solution by the looks of things.

 

cheers,

 

Mike.

[limbo]

Im curious of what other people use CMIS DB for anyone want to "own up"

 

I use the CMIS db for a host of different things outside of the CMIS and eportal apps.

 

- All our usernames for the network are generated automatically from the student tables (Link2ICT said their vision is that this will be possible in as little as 12 months - and we have been doing it for abour six years one way or another!)

 

- Our entire learning platform is linked directly to the data including assessment and attendance reports, simple internal email system using timetable and group data to work out who can email to who (including staff to an entire class group) All dynamically making our learning platform zero maintenance.

 

- Assessment spreadsheets for staff linked directly to the data, including being able to update them from home via the web (http without a VPN)

 

- enrolment photograph system to take a picture of every student in the sixth form as they enrol and link it to their student id

 

- All exchange email lists are updated from the data - year group tutors, department lists tc.

 

- Mentoring targets are recorded in a seperate db but linked to CMIS studnet accounts.

 

- All data needed for other apps such as report writing and library is extracted via sql rather than using CMIS report writer.

 

- School website staff lists, tutor lists etc. all dynamically linked to the db so that the maintenance is reduced.

 

- IT helpdesk linked directly to CMIS for staff lists and rooms

 

- Asset loan database linked to CMIS for staff lists

 

Plus a few other little bits and pieces that we have developed over the years.

 

Curently working on an assessment tracking system that pulls out KS3 levels taken every term and comparing them to what would be an expected progression based on KS2 SATS and KS3 expected grades which is giving me a headache but had a breathrough yesterday which has given me hope!

[Kained]

impressive list this is the kinda stuff i want us to be doing, but alas i have been offered another job with less responsibility more money and closer to home so i feel i gotta go.

 

So my development with cmis will stop but still up for helping people if ya need any help so gimme a shout

[mark80]

- Assessment spreadsheets for staff linked directly to the data, including being able to update them from home via the web (http without a VPN)

 

Extracting the assessment data from the CMIS tables is the main issue i had and posted my issue on page 1 of this thread

 

Could you post on here the tables you use and the relational links between them?

 

Mark

[limbo]

Extracting the assessment data from the CMIS tables is the main issue i had and posted my issue on page 1 of this thread

 

Sorry mark80 - I thought you were talking about the events log rather than the assessment data.

 

To understand the data you need to understand some of the terminology CMIS uses (forgive me if you already know this bit - but others may not)

 

Criteria - this is a single collection of data with its rules, for example a KS3 level that is going to be either 2, 3, 4, 5, 6 etc. You only have to define this once and it can be used repeatedley and the same rules always applied to give continuity. Another example could be effort, forecast grade, expected grade etc. This allows life for like grades taken at different times to be based on the same logic and therefor comparable.

 

Assessment - this is a collection of different criteria. For example a simple assesssment could be an effort and KS3 attainment level. This might be collected every term, once a year etc. Collecting the two criteria together into one assessment makes it easier to do the next step which is:-

 

Examination - this is the actual event you are asking staff to enter information against. So the simple assessment example above might be collected every term - so you set up a different examination for each of those terms that consists of that assessment. Maybe Year 7 Autumn Term, Year 7 Spring Term etc. All containing the simple KS3 assessment above.

 

OK - bare with me, I am getting to the important bit now:-

 

All the results are stored in NSTURESULTS which reference the StudentID, the ExamID and the AssessID as well as the actual results which are in the CriteriaData field (not recognisable as a result just yet - you need to read on because it is not that simple!)

 

Also in this table is the ModuleID - this is how you determine which subject the particular results were for (so a student may have several lines in this table with the same assessment and exam information - once for maths, science, technology etc.)

 

Hopefully you will notice that the SetID is also in the table, but remmber that this refers to the dataset the results were recorded in, so do not automatically just strip out this years dataset, or you will only get this years results.

 

The next table is the EXAMINATIONS table, useful if the examid is not enough to identify the exam you are after - sometimes itis only the name of the assessment (which you will find in this table) that gives this away, rather than the examid found in the examinations table. This table also tells you the relevant AssessID for this exam, but you should already know that from the nsturesults table. So this table may prove to be unecessary for you.

 

The ASSESSMENTS table will give you the nme of the assessment relating to the assessid field in the results table - again only necessary if you cannot identify what you need from the assessId field.

 

Next is the ASSESSCRITERIA table will give you the information you need to be able to extract the data from the results table - this is the bit that is not so simple.

 

If you look up the relevant assessid in this table you will see there are multiple lines for many of the assessments - the difference being the criteriaID (and related Critlabel). What you are really interested in from this table is the mapvalue field - a numeric field.

 

Take this numeric field and look back at the criteriadata field in your NSTURESULTS table (and it will need to be one with the right assessid for this mapvalue) - you will hopefully see the mapvalue appear in the criteriadata field followed perhaps by a control character and then another value. This second value is the result for that critiera. So an assessment will generate a string in the criteriadata field that contains several of these map values followed by the result.

 

All you have to do is search that string for the mapvalue and then the next piece of data after that is the result you are looking for.

 

Hope that all makes sense - I am going to a lie down now!

 

 

And again more details, if needed, about the individual criteria can be found in the CRITERIA table.

[mark80]

Very comprehensible peice of info there and much appreciated

[limbo]

no probs - let me know how you get on

[mark80]

Compiled a query in Access that has given me the MapValue and the CriteriaData so visually i can see what each part of the CriteriaData string is but i want to be able to turn this:

 

[] represents the character in the CriteriaData field that splits the criteria

 

CritLabel	MapValue         CriteriaData
Comment         22             20[]4a[]23[]BE[]127[]C[]126[]EF[]22[]61588
Concern         126           20[]4a[]23[]BE[]127[]C[]126[]EF[]22[]61588
Effort          127           20[]4a[]23[]BE[]127[]C[]126[]EF[]22[]61588
More Able       140           20[]4a[]23[]BE[]127[]C[]126[]EF[]22[]61588
Summer NCL       20            20[]4a[]23[]BE[]127[]C[]126[]EF[]22[]61588
Working          23            20[]4a[]23[]BE[]127[]C[]126[]EF[]22[]61588

 

Into either a table in Access with Summer NCL, Comment, More Able etc as field headings and the content filled with actual vales (not codes) from the criterdata field

 

Hope it makes sense :?:

[limbo]

There are two ways to do this depending on how much data you are wanting to look at at any one time.

 

You can have the data converted and displayed live using a crosstab query - but depending on how much data you want to look at at any one time this may be slooooow.

 

Alternatively you can write a number of queries that build the data into a table which you can then call on - this will be quicker, but will need updating every now and then.

 

A couple of the spreadsheets I produce have 200 columns or so - for this I use a combination of tables and live queries. Things like SAT results hardly every change so that would probably be a good candidate for a permenant table wherease attendance changes twice a day so that is better live.

 

Anyway - copy and paste this into the SQL window of the Access query designer and it should give you what you want (just hope it does not screw the page up!)

 

TRANSFORM First(Left(Mid([criteriadata],InStr(1,[criteriadata],[mapvalue])+1+Len([mapvalue]+1),100),InStr(1,Mid([criteriadata],InStr(1,[criteriadata],[mapvalue])+1+Len([mapvalue]+1),100),Chr$(10)))) AS [Value]
SELECT NSTURESULTS.StudentId, NSTURESULTS.ExamId
FROM ASSESSCRITERIA INNER JOIN NSTURESULTS ON ASSESSCRITERIA.AssessId = NSTURESULTS.AssessId
WHERE (((NSTURESULTS.ExamId)="<>"))
GROUP BY NSTURESULTS.StudentId, NSTURESULTS.ExamId
PIVOT [ModuleID] & " - " & [CritLabel];

 

Then go back to the design view and you should see the place to put your desired examid.

 

It will do this for every student that has ever had that examid - if you want to filter it to a certain year group then include the STUDENTS table and filter for the relevant setid and courseyear.

[mark80]

Wow thats a great way of parsing the string into its components not 100% what i want yet but a great help in getting me statred

 

i owe you a drink

[limbo]

Let me know if I can help get you to 100%!!

[unclesamo]

Has anyone done anything with pulling students who miss more than a set number of days in the academic year?

We have a PA register (Persistent Absentee) which is currently done in Excel and exported from CMIS by hand each week. The spreadsheet then has graphs on other worksheets based on the data in the first worksheet. Basically wanting to pull out those students who are persistently absent from school.

 

I know with office 2007 you can run an SQL import, but that requires getting the sql right first! I have the code to pull out the student details I need, but I can't work out how many 'sessions' the students should be in school for and then how many they actually are there for.

 

Spreadsheet columns are:

StudName, YearGroup, Total Number of Sessions, Number of sessions missed, Gender, SEN, FSM, Ethnicity, CLA, YC

(Sessions - I.e: AM attendance slot would = 1 session. In one day if a student is present for both AM & PM tutor registration then they would be 2 sessions)

 

Has anyone got any ideas?

Cheers.

[BarryWAaMC]

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

 

Dan,

 

If you don't mind playing with PHP, then Moodle VLE has two add-on modules which connect to CMIS, both of them are for different areas:

Facility to Moodle - Connects students for authentication to Moodle and imports teaching groups.

MIS - For looking up student ePortal information.

 

I'm sure the coding of these two modules would help with finding the solution that you would like.

 

Hope thats of help.

 

Barry.

Winton Arts and Media College