Student Contact Details

 With so many schools using ePortal for Parental Access to their child’s data it seems a good time to mention something about Student Contacts.

If systems are working as they should then there should be one unique entry in the database for each student contact.  Where there are student siblings, the same student contact should be used for each sibling.  In reality however, this does not allways happen.  There is a ‘drift’ across years where checks are not always carried out effectivly and this results in multiple entries for the same contact.  Generally this is not a real problem until you give contacts access to a childs data through ePortal.  If things are wrong then the contact will recieve multiple logons for each child instead of one logon that covers all related siblings.

In addition to this, the quality of Contact data is not always very good.

We need to identify duplicate ‘Student Contacts’ so that they can be removed and replaced by linked siblings.

Enter the following SQL into Options|Advanced|SQL

SELECT DISTINCT NSCONTACTS.HomeAddress + ‘ ‘ + NSCONTACTS.Surname + ‘ ‘ + NSCONTACTS.Forename AS myKey, NSCONTACTS.ContactId, NSTURELCONTACTS.ConPriority, NSTURELCONTACTS.StudentId FROM NSCONTACTS INNER JOIN NSTURELCONTACTS ON NSCONTACTS.SetId = NSTURELCONTACTS.SetId AND NSCONTACTS.ContactId = NSTURELCONTACTS.ContactId WHERE (NSCONTACTS.SetId = ’2009/2010′) AND (NSTURELCONTACTS.ConPriority = 1) OR (NSCONTACTS.SetId = ’2009/2010′) AND (NSTURELCONTACTS.ConPriority = 2) ORDER BY MyKey,CONTACTID 

This will build a list with three columns.  The first column will contain the combined Home Address, Surname and Forename into a single entry called myKey, the second column will hold the Contact Id the third will hold the contact priority and the forth will hold the associated StudentId.  Export the list and open it in Excel.  In Excel 2007 there’s a clever ‘Conditional Formatting’ feature that automatically hi-lights the duplicate rows.  If you select the entire contents of Column A and Column B, then apply the conditional formating the duplicate rows will be shaded.  If the Student Contact has the same ContactId for different students the entire row will be shaded.  If however the Contact has a different ContactId, then only Column A will be shaded.

  

 Now I know that it’s not perfect, but it does go some way towards helping to identify and remove duplicate Student Contacts between Siblings.

Comments and other suggestions welcome.

 

Extracting assessment data from NSTURESULTS – Part 2

Part one of this series explained (loosely) how Assessment data is storred within the CMIS database. 
This part includes a SQL Procedure which combines multiple rows of CriteriaData into a single CSV.

The following SQL builds a Function which is storred within the main CMIS database Programmability|Functions|Scalar-values Functions.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GETCRITERIADATA2]
(
  @AssessId VARCHAR(100),
   @StudentId VARCHAR(100),
   @ModuleId VARCHAR(100)
)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @CRITDATA VARCHAR(1000)
SELECT @CRITDATA = COALESCE(@CRITDATA + ”, ”) + CriteriaData
FROM NSTURESULTS 
WHERE  (AssessId = @AssessId) AND (StudentId = @StudentId) AND (ModuleId = @ModuleId)
ORDER BY linenum
RETURN left(Replace(@CRITDATA , char(10),’,'),LEN(@CRITDATA)-1)
END

An example of the Function being used is below, together with some example data.

SELECT [CMIS].[dbo].[GETCRITERIADATA2] (‘TDKS3Ass’,’4001′,’EN’)

’5,6,112,33.000000,113,5,114,39.000000,115,6,116,5,117,5,167,33.000000′

It is not elaborate and there’s no error checking, but it works and forms the basis of more advanced procedures.  

 The function works by using the COALESCE function to combine the multiple rows of data associated with a single assessment into a single string.   After this, the char(10) characters are replaced with ‘,’(commas)

When used the function expects 3 variables to be passed to it.

@AssessId – the AssessmentId that you want to get data on.
@StudentId – the StudentId
@ModuleId – the ModuleId (Subject) that you want the data retrieved from

The next blog will include a further function which processes the CSV data returned from GETCRITERIADATA2 into a two column table with the Criteria Label and the Criteria Data

Extracting assessment data from NSTURESULTS – Part 1

Who ever it was that originally wrote the CMIS Facility Admin / ePortal database must have only completed half (no maybe a quarter) of the Basic SQL database course and they also had probably never heard of Codd’s Law.  There are so many issues with the way that the CMIS database is construced with repeating data in many different places, it is amazing that it works as well as it does.  And on the whole – it does work well. 

It is obvious to me that much of the data processing done within Facility Admin is done progromatically at the application level with very little actually being done on the SQL Server.  I suspect that the situation will remain this way for a considerable amount of time to come.

This blog covers the process of extracting Assessment data from the CMIS database.  It involves querying the following tables:-

• NSTURESULTS
• CRITERIA
• ASSESSCRITERIA
• CRITERVALUES
• STURESCOMM

 Any assessment data entered through ePortal is eventually storred in NSTURESULTS (table description)

[SetId] [varchar](10)
[StudentId] [varchar](10)
[RecordNum] [int] NULL,
[ExamId] [varchar](10)
[GroupId] [int] NULL,
[AssessId] [varchar](10)
[DeptId] [varchar](10)
[ModuleId] [varchar](10)
[LineNum] [int] NULL,
[CriteriaData] [varchar](40)
[CommentId] [int] NULL,
[StudyLevel] [varchar](10)
[ModuleUnit] [varchar](10)
[RefItemId] [varchar](10)
[CourseId] [varchar](10)
[CrsYear] [int] NULL,
[InstId] [int] NULL,
[SemId] [int] NULL,
[QualUnitId] [int] NULL,
[ACKey] [varchar](43)
[ACUser] [varchar](40)
[DateChanged] [varchar](12)
[SemRank] [int] NULL

The important columns here are the first 10.

SetId, StudentId, RecordNum, ExamId, GroupId, AssessId, DeptId, ModuleId, LineNum, CriteriaData

Any Assessment data entered through ePortal adds a minimum of 1 (one) row to this table with entries in the above 10 columns. 
The SetId, StudentId, ExamId, GroupId, AssessId, DeptId and ModuleId are straight forward entries and need little explanation.  The CriteriaData and LineNum entries need some explanation.

Assessment data is storred in the CriteriaData column in two values separated by a char(10) - carriage return .  The first value is the MapValue  of the Criteria  as taken from the Assesscriteria table (table description)

[AssessId] [varchar](10)
[CriterId] [varchar](10)
[OrderNum] [int] NULL,
[MapValue] [int] NULL,
[CritLabel] [varchar](10)
[ModuleUnit] [varchar](10)
[CritArray] [char](1)
[FixedSize] [int] NULL,
[ItemNum] [int] NULL,
[CriterType] [varchar](1)
[Expr] [varchar](50)
[LowerLimit] [int] NULL,
[UpperLimit] [int] NULL,
[AllComponents] [char](1)
[CritStatus] [int] NULL,
[AssElemId] [int] NULL,
[ForGPA] [int] NULL,
[CriteriaType] [varchar](10)
[NeedAllForCalc] [char](1)
[AssignMax] [char](1)
[CWeighting] [float] NULL,
[LogChanges] [char](1)
[LogStartDate] [varchar](8)
[CritComp] [varchar](10)
[LinkLabel] [varchar](10)

Using this value the CritLabel can be identified.  The second value of the pair relates to the actual data being storred.  This can be one of several types and will result in the actual data being entered into the NSTURESULTS-CriteriaData column or as a key to another table where the data is storred as is the case with Comments which are storred in the STURESCOMM table (table description)

[SetId] [varchar](10)
[StudentId] [varchar](10)
[CommentId] [int] NULL,
[LineNum] [int] NULL,
[CommData] [varchar](80)
  

How you identify how the data is storred is by looking at the CriterType value in the Criteria table (table description)

[CriterId] [varchar](10)
[ItemNum] [int] NULL,
[Name] [varchar](30)
[CriterType] [varchar](1)
[LowerLimit] [int] NULL,
[UpperLimit] [int] NULL,
[CriterLabel] [varchar](10)
[Expr] [varchar](50)
[CritStatus] [int] NULL,
[AssElemId] [int] NULL,
[ForGPA] [int] NULL,
[ModuleUnit] [varchar](10)
[CriteriaType] [varchar](10)
[NeedAllForCalc] [char](1)
[AssignMax] [char](1)
[CWeighting] [float] NULL,
[LogChanges] [char](1)
[LogStartDate] [varchar](8)
[CritComp] [varchar](10)
[DeptId] [varchar](10)
[LinkLabel] [varchar](10)
[Disabled] [char](1)

If the CriterType is a ’5′ then the value storred as the second value of the data pair in the NSTURESULTS table will refer to the CommentId in the STURESCOM table, which if the Comment length is greater than 80 characters will also require merging all rows with the same CommentId, ordered by LineNum.

If necessary once the the CriteriaId has been identified, the CriteriaValues table (description below) can also be queried to return the CriterCode or CriterValue values depending upon requirement.

 [CriterId] [varchar](10)
[ValueNum] [int] NULL,
[ItemNum] [int] NULL,
[CriterCode] [varchar](10)
[CriterValue] [varchar](100)
[AssessId] [varchar](10)
[CritLabel] [varchar](10)
[GradePoints] [float] NULL,
[GradePtsPre04] [float] NULL

This is part one of a series of Blogs which explains how to work with this data.  Part two will go on to explain how to use SQL to extract information and also how to merge multiple rown into a single value, which is necessary reconstitute the CriteriaData into a single stream of data.

I

 

Becta recomendations : Information Handling

Here we go!  The topical and Mammoth subject of Information Handling (most commonly referred to at the moment as  ‘two factor authentication‘.

Becta have produced a series of documents containing guidance and good practice to help schools protect personal and sensitive data.  link 

It is important to note that nothing in these guidelines is mandatory or enforceable.  However and it is a big however, these guidelines are based upon the findings taken from the Cabinet Office Data Handling Procedures in Government: Final Report, which relates to the Data Protection Act 1998 (DPA 1998), which is mandatory and enforceable.  A school would be foolish to continue practices which are known to breach the DPA 1998 and  leave themselves wide open to prosecution for any ‘mishaps’ that occur with School Data.

It goes without saying that there have been several high profile incidents recently where data has been lost or stolen.  Imagine the impact that the loss of data relating to your staff or students would have were it lost!  It is simply not worth taking the risk of not doing anything.

The Good Practice in information handling: Secure Remote Access document from Becta spends the first part (pages 4 to 10) referring to technologies that are of no real use us at present.  The technologies mentioned are too immature and too undefined to be implemented. 

 Constantly mentioned however is the need to carry out a ‘Risk – Assessment’ any available data. 

These key points should help in this are:

• What data is available?
• What levels of access are available to this data (Full / Partial /Low / Group / Individual / Admin user / Normal user etc.)
• How is the data accessed? (by web page -sharepoint webparts, Flash or Silverlight objects /ePortal, Facility Admin application, direct SQL Database access / Access database)
• Who has access to the data?(specific users or open access)
• Where is the data accessed from?(home / office /school site / web cafe’ / library etc.)
• How does it get there? (Secure socket layers SSL, HTTPS:// v’s http://, transported as email attachment, USB memory stick or removable hard drive, copied onto laptop etc.)
• Where is it stored when not in use? 

I opened this blog entry with the statement that this topic was a ‘Mammoth subject’, and after having read through the guidance document I have to conclude that it is! 

So to reduce the scale of the issue I will try to relate (as I see them) the implications that arise with data access by Facility Admin and ePortal.

A school relies upon its Data in order to perform and improve.  Until recently, controlled access to this data has been a secondary consideration. 

 Consider these points and see if any apply:-

• It is common practice for users to be given full admin rights on a Serco Facility database.  Whilst access is only available from a single site then this is not too much of an issue but as soon as access is available from multiple sites then there is a real security issue.
• Movement of Data.  Timetables are often created over a long length of time and off site.  This brings with it the possibility of whole school databases over multiple datasets being transported between school and home.  Unless precautions are taken, this data is unencrypted and easy to access.
• If data backups are taken off site, whilst they are in transit and consist of a template or data ‘.bdb’ file that is password protected during the backup process and possibly zipped / compressed with another password then they are reasonably secure.  But when restored into either a MSSQL or Access Database to work with, this data is easily accessible and extremely vulnerable.  Loss or theft of a laptop/ pc would result in a breach of the DPA1998!
• ePortal Access.  The vulnerability of data through ePortal is relative to the category of user that is accessing the data.  For instance, an Admin or Lecturer/Teacher user has access to a wealth of data.  Misuse of this access would result in a breach of the DPA1998.  Access gained through a Student or Student Contact level user would not constitute a serious breach as the data presented is relative only to the particular user that is used to access the data via ePortal.

Rather than offer a solution to the issues, the following pointers may prove useful:

On Site Access:

• Members of staff that have access to Facility Admin must access the data on site and access should be controlled by Active Directory Network Logon Credentials and Facility Admin Logon details.  The use of Single Sign On (SSO) from Serco is preferred as the internal logon details do not need to be known by the user.  User rights attributed to these users within Facility Admin must be appropriate to their levels of use. Individual’s must have their own logon details (not group logon accounts.)  Where there is no write access required, the account should be categorised as ‘read only’
• Student Access – Where student access is granted, SSO should be used.  Student access levels only permit them to see their own details.
• Any views of data through SharePoint Web parts must be controlled and appropriate.

Off Site Access:

• ePortal access must be secured with the use of SSL certificates (HTTPS)
• If users only have access to one set of personal data (or family members data in the case of Parental Access via ePortal) then NO Two Factor Authentication is required.
• If users have access to multiple sets of user data, then access should be secured by some form of Two Factor Authentication (HTTPS and Active Directory Account details do NOT constitute two factor authentication)
• Presentation of data through webparts in Sharepoint follows the same quidelines.  Access to single/family data – no TFA required.  Access to multiple data items –TFA required.

Transport of data

• On NO account should complete dataset backups be send via email without first being password protected and encrypted using an authenticated encryption method.  At the very least they should be password protected and encrypted by winzip or pgp
• On NO account should complete datasets be installed onto a laptop/home PC unless it is first encrypted at the Hard Disk Level, using a technology which renders the laptop useless when not authenticated i.e. BitLocker.

These pointers will help you to create a data environment that is safe and secure.  There are of course many other implication and solutions available.  In the new year (Sept onwards) we will be looking at our own Data Protection issues to see what needs to beupdated / changed.  As this is done, I will post updates.

As always – comments and feedback welcome.

Martin Byford-Rew
IT Manager
Thomas Deacon Academy
Peterborough.

Restarting ePortal automatically

OK!  So the story goes that you should restart ePortal every night to ensure that it runs well.  Is this true?

The truth is that there is no real proof that rebooting ePortal every night will make it run any better, but hey what harm can come of it.

There are some compelling reasons why you would want to restart the two ePortal services (Facility Data Server and Facility ePortal.)  The most compelling reason is that both processes appear to grow in size over time when viewed in Windows Task Manager.  The bigger that they get, the more memory (real or virtual) is consumed and the more likelyhood that memory will be deprived from other services and therefore cause an issue or stoppage.   That alone is good enough a reason for me to restart ours!

Here’s how it’s done:-

First, log onto the server that homes Facility Dataserver and Facility ePortal.

Create a text file in notepad and call it “restartPortal.bat” and save it in c:\Windows {this is a convenient place as the command will appear in the default PATH, meaning that it can be run from the Command Line at any time.

Enter the following into the newly created text file:

@echo off
net stop “facility eportal”
net stop “facility data server”
ping 10.120.129.0 -n 1 -w 7500
net start “facility data server”
ping 10.120.129.0 -n 1 -w 7500
net start “facility eportal”

Now, navigate through the Start Menu to
Start|Programs|Accessories|System Tools|Scheduled Tasks

In the window that appears, double click ‘Add Scheduled Task” (a MS Wizard should appear)
Press Next
Use the Browse Button to navigate to C:\Windows and choose the restartePortal.bat file
Choose ‘Daily’ and press Next
Enter a time for the task (01:00)
Ensure that the Perform this task option is set to ‘Every Day’
Ensure that the Start Date is set to the current data
Enter user credentials for the user who will run the command (we use the System Administrator)
Press ‘Next’
Press ‘Finish’

That’s about all there is to it!  Now every night your Facility Data Server and Facility ePortal will be restard.

Comment Welcome!

Adding custom fields into ePortal

Adding custom fields to Student Personal Details

ePortal comes pre-configured with a subset of details for the Student Personal Details (Minimised and Maximised views) However there are occasions when you need to add additional details.  We used this process to add details of Gifted & Talented and EaL (English as an additional language)

** IMPORTANT : Currently the settings in StudentDetails.xml are not merged or updated when an upgrade to ePortal is done (Serco Knowlegebase item ref:   KI Ref: 18647 refers).  To ensure that your customisation is present in an upgraded ePortal, record them and enter them into the studentdetails.xml file of a newly upgraded ePortal **

This process is relatively straight forward.

First, locate the installation folder of your current running version of ePortal.

Ours was:-c:\Facility\ePortal\webapps\eportal

 

In this folder you will find another folder called ‘Conf’ and this is where the settings adopted in ePortal are taken from.

** I recommend that you back up this folder in its entirety before changing any settings.**

The files in the Config folder are simple text files and can be edited in any text editor, but I have found that Notepad++is a very effective editor.  One of its useful features is the ability to search for a phrase and highlight all occurrences of it within documents.  This is useful in this case as there are several section in the config files that are repeated, but apply to different categories of users.  Without Notepad++ it would be easy to edit a section and then wonder why the changes are not seen in ePortal.

Working example.  We are going to add an entry for EaL into the Student Personal Information section of ePortal.  To do this, you need to know where in the (many) Facility database tables the EaL data is stored.  In this case, the EaL is stored in the NSTUPERSONAL table in column ‘cat12′ (see image below for more useful fields)

Once you have identified the fields that you want to show, it is time to edit the config files.  Open ‘studentdetails.xml’ which can be found in the ‘config’ folder.

Search for the following entry:

<!– Student Screen Data types Defined –>

This area of the config files is where any data items display are defined.  It is not where they are actually displayed, they are simply defined here so that they can be called later.  We need to add a reference to the new field ‘EaL’.  This is probably best achieved by copying and editing an existing entry.

The finished entry should look similar to the following:

<field identifier=”EAL” title=”EAL” expression=”personal.cat12″/>

That’s part one complete.  Now we must enter the corresponding entries into the relevant sections dependant upon who should be able to view them.  The default sections are (Admin users / Student Users / Student Contact Users / Lecturer Users)  This is where the ‘find all’ feature in Notepad++ is useful.  Search (Ctrl + F) for the phrase ‘Default view’

Now, by clicking on each entry from the four found items, you can jump to each section. 

I want Staff to see the EaL details for their students but NOT Students or Parent Contacts.  Therefore, I need to enter the Lecturer section.  Within this section (and most of the others) there are two sub sections, one for the Minimised view and one for the maximised bitlet views.  Decide where you want the data visible and enter the following into the relevant section(s)

<data-item identifier=”EAL” alternatelabel=”EAL” />

Once you have entered the sections that you need, Stop and Restart Facility ePortal and Facility Data Server. 

Upon restart your new fields should be visible.

If they are not, check that the ‘identifier’ term is identical in all sections and that you have entered it into the correct sections. (Note: if you are an Admin user, you will not see changes in the Lecturer section – a common mistake)

This is the first post of (hopefully) many.

If its been useful, leave a comment.  Likewise, if you have any suggested topics request them and I’ll add a section (if I know)

Welcome

OK.  So lots of Schools are using Facility Admin and ePortal as their school Management Information Systems (MIS) but lets face it – it’s a bit of a beast to use effectivly.

In this Blog, I hope to empart some of the knowlege that I have accumulated over the past 5-6 years to make life easier for fellow users.

I don’t profess to be an expert in evey facet of ePortal or Facility Admin, but I do have a significant amount of tips that I am happy to share.

We are also looking to incorporate some aspects of ePortal into SharePoint (WSS3) as Webparts to make access to student data easier.  Not that it’s difficult at the moment as all students, staff and parents have their own logons but the general concensus of opinion is that even with Facility Single Sign On (SSO) it would be beneficial to have access to data withoug navigation between sites.

Anyway – enough from me for now!

On with the show