Table Details for Serco Facility Admin

[mattmarson]

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?

[john]

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.

[mattmarson]

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.

[michael2k6]

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

[mattmarson]

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.

[SkywOrca]

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:

 

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.

[mattmarson]

Cheers - thats a great help!

[john]

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

[kealey]

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

[mattmarson]

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.

[kealey]

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

[mattmarson]

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

[atish]

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

[pauljonze]

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?

[atish]

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.

[JPS]

The values separating the results relate to that criterias map value. This can be seen in the Assessment templates section of the Assessment module.

 

You may want to then refer to the assesscriteria table

 

Hope this helps.

[SkywOrca]

NSTURESULTS is extremely difficult to manipulate. As you've found, the criteria data itself is compiled into a single string using a separator (which is actually the Line Feed character, Char 10 on the ASCII table). But worse still is that once the length of the string exceeds 40 characters it splits the additional content into a new row of data. All of which basically means that it's not an efficient table to extract data from.

 

The only way you can access the data through pure SQL is to write a cursor driven stored function. SQL is fundamentally a declarative language (that is it doesn't operate on flow control, i.e. if statements etc, it works by declaring links between things), so writing cursors is automatically an inefficient use of the underlying system. What this basically means is that this code will be sloooow...

 

However, that said, I did write come SQL that will extract the contents of this table into a slightly more user-friendly (and SQL friendly) collection of CriterId : CriterValue pairs along with associated key fields (SetId, StudentId etc...).

-- Create the Criteria table function:

CREATE FUNCTION udfSTUCRITERIA (@strSetId VARCHAR(10), @strStudentId VARCHAR(10), @intRecordNum INT)
RETURNS @retSTUCRITERIA TABLE (SetId VARCHAR(10) COLLATE database_default, StudentId VARCHAR(10) COLLATE database_default, RecordNum INT, CriterId VARCHAR(10) COLLATE database_default, CriterValue VARCHAR(8000) COLLATE database_default) AS
BEGIN
DECLARE @strCriteriaData VARCHAR(40), @strFullCrit VARCHAR(8000), @strCriterId VARCHAR(10), @strCriterValue VARCHAR(8000), @strCriterBit VARCHAR(1)
DECLARE @intNumRecords INT
SELECT @strFullCrit = '', @strCriterId = '', @strCriterValue = '', @strCriterBit = 'I'
SELECT @intNumRecords = COUNT(*) FROM NSTURESULTS WHERE SetId = @strSetId AND StudentId = @strStudentId AND RecordNum = @intRecordNum
IF @intNumRecords > 1
BEGIN
	DECLARE curNSTURESULTS CURSOR FOR SELECT CriteriaData FROM NSTURESULTS WHERE SetId = @strSetId AND StudentId = @strStudentId AND RecordNum = @intRecordNum ORDER BY LineNum
	OPEN curNSTURESULTS
	FETCH NEXT FROM curNSTURESULTS INTO @strCriteriaData
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @strFullCrit = @strFullCrit + @strCriteriaData
		FETCH NEXT FROM curNSTURESULTS INTO @strCriteriaData
	END
	CLOSE curNSTURESULTS
	DEALLOCATE curNSTURESULTS
END
ELSE
	SELECT @strFullCrit = CriteriaData FROM NSTURESULTS WHERE SetId = @strSetId AND StudentId = @strStudentId AND RecordNum = @intRecordNum
WHILE DATALENGTH(@strFullCrit) > 0
BEGIN
	IF @strCriterBit = 'I'
	BEGIN
		IF LEFT(@strFullCrit, 1) = CHAR(10)
			SELECT @strCriterBit = 'V'
		ELSE
			SELECT @strCriterId = @strCriterId + LEFT(@strFullCrit, 1)
	END
	ELSE
	BEGIN
		IF LEFT(@strFullCrit, 1) = CHAR(10)
		BEGIN
			INSERT INTO @retSTUCRITERIA SELECT @strSetId, @strStudentId, @intRecordNum, @strCriterId, @strCriterValue
			SELECT @strCriterBit = 'I', @strCriterId = '', @strCriterValue = ''
		END
		ELSE
			SELECT @strCriterValue = @strCriterValue + LEFT(@strFullCrit, 1)
	END
	SELECT @strFullCrit = RIGHT(@strFullCrit, DATALENGTH(@strFullCrit) - 1)
END
RETURN
END

-- Create the Criteria View:

CREATE VIEW vNSTURESCRIT AS
SELECT DISTINCT s.SetId, s.StudentId, s.RecordNum, u.CriterId, u.CriterValue FROM NSTURESULTS s CROSS APPLY udfSTUCRITERIA(s.SetId, s.StudentId, s.RecordNum) u

 

This should result in a view (vNSTURESCRIT) that you can incorporate into your SQL statements to analyse the data further. I would strongly recommend that you actually feed the results from the view into a table somewhere on your system if you're going to be pulling lots of data out at a time in order to minimise the computational impact of the cursors.

 

As always, SQL should be used with care, make sure you have backups etc just in case even though this script does nothing to write data back - you never can be too careful IMO. The script also comes with no warranty that it'll work without modification on your system, or that it won't corrupt your data, cause your server to catch fire, or cause the Moon to spin off into outer space, but none of the above should happen.

[pcstru]

A useful technique when trying to find out where data is stored is to snapshot a count of all the records in the database tables, add something to the area you are interested in, snapshot again and then look at which tables have changed. I have a nice little spreadsheet which does this - I'll have a look at making it available somewhere.

 

For results I'd agree with SkywOrca - difficult to manipulate. We deal with them via a stored proc to extract the result attribute we're after (so you need to know the mapvalue of the attribute) and a view which combines multiple line criteria data (where they go over 40 chars) into a single field. SQL for the view is :

 

select case when nsr1.criteriadata is null then '' else nsr1.criteriadata end + 
      case when nsr2.criteriadata is null then '' else nsr2.criteriadata end +
      case when nsr3.criteriadata is null then '' else nsr3.criteriadata end "AllCriteria",
      nsr1.*

 from nsturesults nsr1 left join nsturesults nsr2 on nsr1.studentid = nsr2.studentid and
                                                     nsr1.recordnum = nsr2.recordnum and 
                                                     nsr1.linenum + 1 = nsr2.linenum                                                   
                       left join nsturesults nsr3 on nsr1.studentid = nsr3.studentid and
                                                     nsr1.recordnum = nsr3.recordnum and 
                                                     nsr1.linenum + 2 = nsr3.linenum
where nsr1.linenum = 0

 

The SQL will only deal with criteria split over 3 records which is the most we currently have. The stored proc for extracting the result attribute is :

 

CREATE FUNCTION ResFromCrit( @critdata varchar(200), @MapValue varchar(10) ) returns varchar(20) AS
BEGIN


Declare @StartPos as int			-- Start position in critdata of the mapvalue
Declare @RPart as varchar(200)		-- Part of the string to the right of MapValue
Declare @RPPos as int				-- Position of next space after map attribute
Declare @FReturn as varchar(20)
Declare @DSLen as int
Declare @DSStr as varchar(20)
Declare @LPPos as int
Declare @LPart as varchar(200)

Declare @Result as varchar(50)	-- Final function return value

 
SET @Result = ''

-- Get the position of the beginning of the first instance (if there is one)
SET @StartPos = charindex(@MapValue+char(10),@critdata) 

  -- If the particular mapvalue is not present anywhere, then startpos will be zero 
IF (@StartPos = 0)
	SET @Result = NULL
ELSE BEGIN

  	-- Get the string part which starts after the Mapvalue and the chr(10) delimiter
  	SET @RPart = right( @critdata, len(@critdata)-(@startpos+len(@mapvalue)) )
  	
  	-- Find the next chr(10) (delimits the end of the attribute of the mapvalue)
  	SET @RPPos = charindex(char(10),@RPart)
  	
  	-- Length of the data is one less than the position of the space (if found)
  	SET @DSLen = @RPPos - 1 
  	
  	-- Reality check on string length - sometimes there is no attribute, in which case
  	-- the length is zero
  	IF (@DSLen > 0) BEGIN
 	   	
		-- We need to check that there is a delimiter infront of the MapValue or that
		-- it was at the start of the string. Otherwise it's an embedded number i.e.
		-- looking for Mapvalue = 179 and finding it in the number 34179
		IF (@StartPos = 1)
			SET @Result = 'AT START' 
		ELSE
		
			SET @LPart = left (@critdata, (@StartPos)-1)
			
			IF (right(@LPart,1)<>char(10))
			   SET @Result = NULL
			ELSE BEGIN
				SET @Result = LEFT( @RPart, @RPPos -1 )
			END
		
	END ELSE
		SET @Result = NULL
 	END

 	RETURN @Result
 
END

 

This can then be used in SQL, i.e we want a single record returned for students in year 7 with their results listed as columns :

 

select nstupersonal.studentid,   
      nstupersonal.forename,    
      nstupersonal.surname,      
max( case when PCS_StuCritData.moduleid = 'AR' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "AR",
max( case when PCS_StuCritData.moduleid = 'DA' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "DA",
max( case when PCS_StuCritData.moduleid = 'DR' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "DR",
max( case when PCS_StuCritData.moduleid = 'DT' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "DT",
max( case when PCS_StuCritData.moduleid = 'EH' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "EH",
max( case when PCS_StuCritData.moduleid = 'FR' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "FR",
max( case when PCS_StuCritData.moduleid = 'IT' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "IT",
max( case when PCS_StuCritData.moduleid = 'MA' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "MA",
max( case when PCS_StuCritData.moduleid = 'MU' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "MU",
max( case when PCS_StuCritData.moduleid = 'PE' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "PE",
max( case when PCS_StuCritData.moduleid = 'PSHE' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "PSHE",
max( case when PCS_StuCritData.moduleid = 'SC' then  dbo.resfromcrit( PCS_StuCritData.AllCriteria, '185') else '' end ) "SC"
from nstupersonal left join PCS_StuCritData on nstupersonal.studentid = PCS_StuCritData.studentid, 
     students  where nstupersonal.studentid = students.studentid AND 
      nstupersonal.setid = '2009/2010' AND 
      students.setid = '2009/2010' AND 
      NSTUPERSONAL.DateLeft = '' AND 
      NSTUPERSONAL.LeftSchool = 'N' AND 
      students.courseyear = '7' AND 
      ( PCS_StuCritData.assessid = 'KS3Assess' OR PCS_StuCritData.assessid IS NULL ) AND 
      ( PCS_StuCritData.examid = 'Year7' OR PCS_StuCritData.examid IS NULL ) 
Group by nstupersonal.studentid, nstupersonal.forename, nstupersonal.surname 
Order by nstupersonal.studentid 

 

Where PCS_StuCritData is the view created by the first bit of code.

Edited June 9, 2010 by pcstru

[pcstru]

A useful technique when trying to find out where data is stored is to snapshot a count of all the records in the database tables, add something to the area you are interested in, snapshot again and then look at which tables have changed. I have a nice little spreadsheet which does this - I'll have a look at making it available somewhere.

 

Spreadsheet should be attached, brief instructions are included.

DBAnalysis.xls

[Marci]

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?

 

A bit late, but what you're after is the MIS Block that got removed from Guy's Facility-to-Moodle project. It's this part that gives access to attendance, timetable, behaviour etc etc.

It's still available separately, but appears not to be listed in Moodle Modules area. Gimme a shout if you need a copy.

Edited September 29, 2010 by Marci

[jonawd]

Marci,

 

Yes please!

[Marci]

ygpm fella...

[workteam123]

A bit late, but what you're after is the MIS Block that got removed from Guy's Facility-to-Moodle project. It's this part that gives access to attendance, timetable, behaviour etc etc.

It's still available separately, but appears not to be listed in Moodle Modules area. Gimme a shout if you need a copy.

 

can I have a copy of that as well pls.

[SpuffMonkey]

And me!