+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 24
MIS Systems Thread, Table Details for Serco Facility Admin in Technical; I need help from anyone who knows a little (or lot) about the database structure of CMIS and where data ...
  1. #1

    Join Date
    Apr 2010
    Location
    matt.marson@stuart-bathurst.org.uk
    Posts
    8
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Table Details for Serco Facility Admin

    I need help from anyone who knows a little (or lot) about the database structure of CMIS and where data in stored eg. in which tables... would anyone be able to assist with this?

  2. #2

    john's Avatar
    Join Date
    Sep 2005
    Location
    London
    Posts
    10,447
    Thank Post
    1,537
    Thanked 1,069 Times in 934 Posts
    Rep Power
    305
    What are you looking to do with it? There are a good number of Serco users on here including myself and various Serco staff so it would be good to know what you are wanting to find as there are a huuuuge number of tables.

  3. #3

    Join Date
    Apr 2010
    Location
    matt.marson@stuart-bathurst.org.uk
    Posts
    8
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    hi john

    We are using Moodle as our Learning Platform and have enlisted the support of the University of London Computer Centre to develop an ILP and ePortfolio (Mahara). We are currently trying to get data out of Facility CMIS SQL database to display in Moodle for parents, students and staff.

    The data we would need access would be a range of things:

    - Student details
    - Lessons or Courses (set lists for automatic creation of courses)
    - Student Behaviour Events
    - Student Attendance (AM PM roll call)
    - Student Timetable
    - Staff Timetable
    - Assessment data (from assessment tracker - eg. 6 data tracks throughout the year and a target grade)
    - Results (SATs, Teacher Assessments, GCSEs)

    I'm sure there is more, but that is all I can think of at present...

    ULCC have managed to find some of the data and it is working in terms of functionality and appearing the in ILP... with so many tables, many without data its very time consuming.

    I have approached Serco for support with regards the database schema but they say they only support API.

  4. #4

    Join Date
    Jun 2007
    Location
    Wakefield, West Yorkshire
    Posts
    636
    Thank Post
    101
    Thanked 135 Times in 105 Posts
    Rep Power
    68
    Matt,

    You're right about the API. We have a partnership programme which entitles people to the data structure used by the dataserver to query the database. This is the only way we will support any 3rd party softwares connecting to a facility database.
    The table structure and the way the data is stored is very complex so i would recommend you look into the partnership programme as an alternative.

    Mic@Serco

  5. #5

    Join Date
    Apr 2010
    Location
    matt.marson@stuart-bathurst.org.uk
    Posts
    8
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Hi Mic

    I'm not a particularly technical person in all honesty, the organisation we are working with have integrated Moodle with SIMS without hassle. In theory the data in the database is the property of the school and therefore access to it is not prohibited. I am beginning to think that the 'complex' or messy structure of the tables is by no way incidental but another measure to claw more money from schools. We have already signed up for a customised Moodle support package and a further £3,500 to become a Serco 'partner' seems extortionate! In theory, we pay you £3,500 and then you tell us where the data is stored in the database? This leaves us in somewhat of a dilemma - with the only 'easy' solution being to revert back to SIMS where we have LA support and the integration has already proved successful - this would be a shame as so much work has gone in (on our part and money spend on consultancy) to making Facility CMIS work beyond the odds... very dissatisfied with Serco's support with this project.

  6. #6
    SkywOrca's Avatar
    Join Date
    Sep 2008
    Posts
    90
    Thank Post
    7
    Thanked 18 Times in 17 Posts
    Rep Power
    48
    As I said before, the structure of the database is a bit obtuse, but with a bit of digging you should be able to find what you need. I posted some pointers to some of the data you need in another thread:

    Quote Originally Posted by SkywOrca View Post
    As John pointed out, NSTURESULTS holds assessments data, but you might also need to make reference to ASSESSCRITERIA to see if it's a comment type, and if it is you'll need to cross refer to STURESCOMM to get the actual comment data out rather than the numeric CommentId.

    Timetable data is kind of spread out across several tables, it really depends on what you need to get at the SQL of this data for. The main bulk of it is in TIMETABLE, but you'll find that much of this is encoded as object Ids (like SlotId, WeekId etc.), and you'll need to find the related tables to bring out the human-readable version of this info.

    Attendance is split across two tables, ATTDETAIL and NSTUATTDATA. The data in each should be identical, it's horses for courses as to which you prefer, personally I find the data format in ATTDETAIL to be easier to work with.

    Personal data is stored mainly in STUDENTS, NSTUPERSONAL and UKSTUSTATS. Still depends on exactly what you're after, however.
    Behaviour will depend on whether you've bought the Behaviour Management module or not, if not then the data is stored in APPAPPEVENT for the main record of an event and APPAPPFIELDS for the individual field data. Note, these tables are conglomerate tables that store data for Students, Staff and Applicants, so you'll want to filter to RecType 3971 if you're only interested in student data. If you're on BM then you'll have to do some digging, I haven't poked about in that much yet.

    Lesson and courses (by this I'm assuming you mean teaching groups?) will be stored in STUGROUPS for the links and TEACHINGGROUPS for the human-readable group info (group names etc.).

    For assessment tracker and exams results you'll be looking mostly in the NSTURESULTS table again, SATs are elsewhere in STUSATRESULTS with the SAT test details in SATTESTS.

  7. #7

    Join Date
    Apr 2010
    Location
    matt.marson@stuart-bathurst.org.uk
    Posts
    8
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Thanks

    Cheers - thats a great help!

  8. #8

    john's Avatar
    Join Date
    Sep 2005
    Location
    London
    Posts
    10,447
    Thank Post
    1,537
    Thanked 1,069 Times in 934 Posts
    Rep Power
    305
    Also worth talking to the guys at Twynham School (they are on here) as they have got good integration of Serco and the VLP, in there case its SharePoint but they have some amazing data coming out of Serco into that and I'm sure they will have custom developed a load of them so they could also be a good set of people to talk to

  9. #9
    kealey's Avatar
    Join Date
    Nov 2008
    Location
    Derbyshire
    Posts
    9
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Hi
    There is an extension on Moodles website that integrates Moodle with CMIS very well. It was written by Guy Thomas and does everything we need it to do.
    GHope this helps
    Allan

  10. #10

    Join Date
    Apr 2010
    Location
    matt.marson@stuart-bathurst.org.uk
    Posts
    8
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Thanks Kealey - I have looked at this but the functionality at the moment only allows for groups of students to be bought over from CMIS and course creation I believe - I don't think it brings over attendance information, behaviour events, grades, courses etc.

  11. #11
    kealey's Avatar
    Join Date
    Nov 2008
    Location
    Derbyshire
    Posts
    9
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by mattmarson View Post
    Thanks Kealey - I have looked at this but the functionality at the moment only allows for groups of students to be bought over from CMIS and course creation I believe - I don't think it brings over attendance information, behaviour events, grades, courses etc.
    The version I run gives me an attendance calendar and timetable as well as all the assessments that the pupil is signed up for broken down into Autum, Spring and Summer ie the assessment templates in CMIS. It does not give behaviour events but it seems to do pretty much everything else

  12. #12

    Join Date
    Apr 2010
    Location
    matt.marson@stuart-bathurst.org.uk
    Posts
    8
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    do you have the link?

    do you have the link for this module?

    is it the one from the ossett school?

    would you be able to screenshot some of your screens please?

    matt.marson @ stuart-bathurst.org.uk

  13. #13

    Join Date
    May 2010
    Location
    Newcastle
    Posts
    2
    Thank Post
    4
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    NSTURESULTS table in Facility CMIS

    Quote Originally Posted by SkywOrca View Post
    As I said before, the structure of the database is a bit obtuse, but with a bit of digging you should be able to find what you need. I posted some pointers to some of the data you need in another thread:



    Behaviour will depend on whether you've bought the Behaviour Management module or not, if not then the data is stored in APPAPPEVENT for the main record of an event and APPAPPFIELDS for the individual field data. Note, these tables are conglomerate tables that store data for Students, Staff and Applicants, so you'll want to filter to RecType 3971 if you're only interested in student data. If you're on BM then you'll have to do some digging, I haven't poked about in that much yet.

    Lesson and courses (by this I'm assuming you mean teaching groups?) will be stored in STUGROUPS for the links and TEACHINGGROUPS for the human-readable group info (group names etc.).

    For assessment tracker and exams results you'll be looking mostly in the NSTURESULTS table again, SATs are elsewhere in STUSATRESULTS with the SAT test details in SATTESTS.
    Could someoen please explain the logic/method of reading data stored in the field "CriteraData" in table "NSTURESULTS"? I think it has some special charachter to seperate each criteria and its points but how could we write a script to extract the information to make some sense out of it?

    many thanks

  14. #14
    pauljonze's Avatar
    Join Date
    May 2008
    Location
    Staffs
    Posts
    86
    Thank Post
    17
    Thanked 22 Times in 15 Posts
    Rep Power
    23
    If you look closer you'll see there is a pattern to the separators... I think it starts at 15 and goes to 22 but can't remember. I have a SQL query for it somewhere but can't get on the server now. If you want to look at it as a starting point for your won stuff I can send it next week?

  15. #15

    Join Date
    May 2010
    Location
    Newcastle
    Posts
    2
    Thank Post
    4
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by pauljonze View Post
    If you look closer you'll see there is a pattern to the separators... I think it starts at 15 and goes to 22 but can't remember. I have a SQL query for it somewhere but can't get on the server now. If you want to look at it as a starting point for your won stuff I can send it next week?
    Hi Paul,

    I would really appriciate if you could when you get a chance.

    Thank You.



SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. [ACS] Serco's Facility
    By ANiceEnglishman in forum MIS Systems
    Replies: 49
    Last Post: 3rd April 2011, 02:42 PM
  2. serco facility
    By garrysaddington in forum MIS Systems
    Replies: 8
    Last Post: 8th June 2009, 11:22 AM
  3. Serco Facility Experiences!?
    By Nick_Young in forum MIS Systems
    Replies: 18
    Last Post: 6th March 2009, 04:10 PM
  4. Fronter and Serco Facility
    By cheredenine in forum Virtual Learning Platforms
    Replies: 3
    Last Post: 10th November 2008, 09:24 AM
  5. Serco CMIS Facility
    By bursar in forum MIS Systems
    Replies: 11
    Last Post: 22nd May 2007, 05:36 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •