Xporter-Mock-up.png

XVault Integrators Guide

This document provides assistance to technical developers using Groupcall XVault to provide data to their systems via SQL.  Its primary purpose is to provide technical direction and guidance when reading data from the XVault database and to highlight potential pitfalls of certain implementation approaches.  While the operation of XVault and how it obtains data from school fall outside of the scope of this document, a high-level overview is provided to enable the reader to contextualise the technical information provided.

Written documentation will never be able to cover all scenarios and/or implementation specific details, and so Groupcall also provide integration consultancy if required.  For more information, contact your account manager or your assigned implementation consultant.

Table of Contents

XVault Overview

 

XVault data collection


Click image to expand.

XVault Performance

Groupcall XVault is designed to be ready to scale, both in terms of performance achieved by a single instance and in terms of having a design that would enable development of a multi-instance load balanced approach once the requirement arises.

The largest factor in XVault performance is the underlying SQL database, which should be specified appropriately for the workload being considered.  Groupcall provide a set of indexes on that database to optimise XVault internal performance, and you are able to add in your own indexes where you feel they are required.

XVault SQL access is optimised to cause minimal read and write load on the SQL server, this is detailed in the following section.

XVault Operational Behaviour

Data Request

XVault works by requesting data from schools, driving the collection of data.  It is possible for schools to transmit change events to XVault in some configurations. However, since these are saved in the same way as responses to data requests, the discussion of events is outside the scope of this document.

 

Advanced Messaging: How to contact recipients


Click image to expand.

 

Collection Templates

XVault supports definition of multiple Collection Templates and allows a school to be assigned to a single Collection Template at any one time.  A Collection Template defines a set of SIF objects to request, any filtering criteria on those objects and the request frequency for each object.

New schools

When using Groupcall Data Broker Services with XVault, any new school registering with Data Broker Services will be shown by default as a new school in XVault within around 60 minutes.  A default Collection Template is typically applied to such schools that request only SchoolInfo, which is an object providing school summary details such as school name, statutory reporting identifiers and school contact details.  This Collection Template is used to confirm the school is working, but not to risk data exposure, by virtue of only requesting public domain information.  A new school should be switched over to a more suitable Collection Template once confirmation of school consent has been double checked.

Data import and retention

XVault operates a data deletion policy that effects after the completion of response processing, it operates at a SIF Object level per school.  When a response is received for a SIF object for a school, XVault calculates a checksum of the data and compares it to the checksum in the XVault database. If the value matches then the lastReceived column is updated accordingly and no further action is taken. If the value doesn’t match then the record is updated and the lastModified column is also updated.  In the case that a new record is created, the createdDate column is similarly set.  This applies for every row of data in the XVault database.

Once XVault has finished receiving a response for a SIF Object in a zone it then seeks all rows that have not been touched in some way, and considers them for deletion.  This consideration takes into account a soft-configuration value in XVault determining how long to retain objects for after they stop appearing in responses.  For any object with lastReceived before that threshold point the record is deleted.  This auto-deletion mechanism effectively provides a configurable period where records no longer presented by a school are eventually expired (and removed from the XVault database), without causing data deletion for schools that are experiencing e.g. a loss of service and are not returning any data at all.  The threshold value is measured in hours and can be set to immediate deletion, the default of 48 hours, or disabled. This can be done on a per SIF Object basis.

Where an event message is received from a school regarding the deletion of a record from the MIS data source, XVault will apply that change immediately regardless of the retention settings.

SIF Data Objects

Groupcall XVault stores data in a slightly normalised version of the UK SIF data model, hence for any queries about data specification for a specific SIF data object, element, or attribute you should refer to the UK SIF specification.

SIF Data Object Contents

Object

Data Area

Contents (Presence of contents will vary by MIS)

SchoolInfo

School

Information about the school – e.g. name, head teacher, contact information, governance, phase.

LearnerPersonal

Learner

Personal information about a learner, including ULN, UPN, Address, DoB, Phone, Email.

LearnerSchoolEnrolment

Learner/School

Information about the learner in relation to the school, e.g. start date, academic year.

WorkforcePersonal

Workforce

Personal information about an employee in a school, including Address, DoB, Phone, Email and Post held.

SchoolGroupType

School

Different types of Group the school provides – e.g. class (lesson), year, registration (form).

SchoolGroup

School

Specific groups in the school, their type and which WorkforcePersonal are linked.

LearnerGroupEnrolment

School

Membership of LearnerPersonals into SchoolGroup.

ContactPersonal

Parental contacts

Information about a contact, including address, phone, email.

LearnerContact

Parental contacts

The relationship between a LearnerPersonal and a ContactPersonal, including parental responsibility and contact priority.

LearnerSpecialNeeds

SEN

Special needs status for a learner, and what their requirements are.

LearnerAttendance

Attendance

Detailed Session attendance marks for a learner.  This can optionally also support Lesson attendance for some MIS products.

LearnerAttendanceSummary

Attendance

Summary attendance statistics for a learner.

PersonPicture

Learner, Workforce

Photo of a learner or a workforce.

LearnerExclusion

Exclusions

Exclusion periods for a learner.

LearnerEntitlement

Free School Meals

Periods of FSM entitlement for a learner, historic entitlements are supported for most MIS products.

LearnerBehaviourIncident

Behaviour/Achievement

Negative or positive behaviour events for a learner

TermInfo

Timetable

Details the term dates in the current year for the school.

Cycle

Timetable

Provides the structure of the current timetable(s), in terms of days, periods, timings, logical to temporal mappings, etc.

TTRoom

Timetable

Defines rooms used by the timetable

TTTeacher

Timetable

Defines teachers used by the timetable, which typically share their identifying ‘RefId’ with WorkforcePersonal objects.

TTSubject

Timetable

Defines timetabled subjects

TeachingGroup

Timetable

Defines timetabled groups of students, typically mapping back to the identifying ‘RefId’ of a SchoolGroup.

Lesson

Timetable

Defines a Lesson occurring during a timetable period, involving a TTSubject and a TeachingGroup.

AssessmentResultGradeSet

Assessment

Represents grades over time for a result.

AssessmentSession

Assessment

Defines an assessment period, such as summer exams.

AssessmentResultComponent

Assessment

Defines an assessment aspect, e.g. Drama practical

AssessmentResultComponentGroup

Assessment

Defines a grouping of assessment aspects.

AssessmentLearnerSet

Assessment

Defines a set of learners related to an aspect.

LearnerAssessmentResult

Assessment

Defines an assessment result, awarded on a date and optionally during a specific assessment session.

SIF Data Objects Entity Relationship Diagram

This diagram is available via the UK SIF Association website.  The UK SIF community develops the SIF standard for data in the UK.


SIF data objects entity relationship diagram


Click image to expand.
SQL Tables per Data Object

SIF RefIds

SIF data objects are identified using RefIds, which are basically GUIDs with a different formatting.

A RefId within a zone can be relied upon to be unique, however you should not rely on RefId to be unique between zones. For that reason, you should safeguard against incorrect joins between SIF objects by matching on both RefId and ZoneId.

The RefId for a SIF object is ‘near-persistent’ within a zone; that is, it shouldn’t change but it is possible for it to (for example by virtue of a data integrity issue or due to re-initialisation of the agent providing data to XVault).  The likelihood of change varies by source MIS as some products keep a suitable value within the MIS and some require the extracting agent (typically Xporter) to generate and assign RefIds against other MIS identifier values.  The latter approach obviously carries a slightly increased risk of that mapping table being lost when e.g. a school reinstalls the agent.

RefIds do not move with any person (or other SIF data) between schools, as a learner’s UPN or ULN might.  Hence a learner present in two schools would appear in XVault twice, once in each school zone and with a different RefId in each case. Click here to view more information on Multi-site or Duplicate Learners.

In the case of LearnerAttendance and LearnerEntitlement, Groupcall Xporter agents assign a RefId that is computed rather than randomly generated.  This is to ensure writeback compatibility for those data objects, and they remain guaranteed unique within a zone.

XVault - Typical Integration Scenarios

This section details how to integrate with XVault for specific scenarios, providing example SQL where possible.

Monitoring Data Freshness in XVault

Data monitoring for XVault showing the full end-to-end status of transmission for every school is available via Groupcall Dashboard, a separate Groupcall product designed to simplify operational management and support of your Groupcall estate.

It is possible to use SQL queries to give you a view of general data shortages in the XVault database. XVault has two tables for this:

  • ZoneDetails defines each school zone connected to XVault and which collection template it is using.
  • SifObject defines and controls the collection of each SIF object for each zone, based on the collection template assigned in ZoneDetails.

The SifObject table is updated each time data is received for a school/object pair and hence you can obtain a list of schools that haven’t, for example, returned data in the last 72 hours by running a query like this:

[sql]SELECT

z.[zoneId],

z.[schoolName],

s.[templateName],

s.[elementName],

s.[LastDataRecieved] FROM

[ZoneDetails] z

INNER JOIN [SIFObject] s on z.[id] = s.[zoneid] WHERE

s.[allowedSubscribe] = 1

AND s.[lastDataRecieved] < DATEADD(HOUR, -72, GETDATE())

ORDER BY

s.[lastDataRecieved][/sql]

Extracting a list of currently enrolled students

This is one of the most common things partners need to do, however you must take account of three caveats:

  • XVault may contain subsidiary or guest learners that are not ‘primary’ records for a learner.
  • XVault may contain preadmission or leaver records that are not current
  • The SIF standard stores the current academic year of learners in LearnerSchoolEnrolment, not in LearnerPersonal – because the latter is of the learner not of the learner in a school.

You can use a query such as the below to obtain the current primary records for all learners.

[sql]SELECT

lp.[RefId],

lp.[UPN],

lp.[ULN],

lp.[Name_GivenName],

lp.[Name_FamilyName],

lp.[Name_PreferredGivenName],

lp.[Name_PreferredFamilyName],

lp.[Gender],

lp.[BirthDate],

lp.[Email_Address],

lp.[Email_Type],

lse.[MembershipType],

lse.[NCYearGroupActual],

lp.[ZoneId] FROM

[LearnerPersonal] lp

INNER JOIN [LearnerSchoolEnrolment] lse on lp.RefId = lse.LearnerPersonalRefId and lp.ZoneId = lse.ZoneId

WHERE

lse.[MembershipType] IN ('C', 'M')

AND (lse.[ExitDate] IS NULL or lse.[ExitDate] > GETDATE())

AND (lse.[EntryDate] IS NULL or lse.[EntryDate] <= GETDATE())[/sql]

Extracting a list of Staff

The following SQL query will obtain staff and their current posts, note that it is possible for staff to have more than one concurrent post and more than one ‘Other email’, hence this specific query will cause duplicate rows – but it’s purpose is to show the relation between the tables.

[sql]SELECT

wfp.[RefId],

wfp.[Name_Title],

wfp.[Name_GivenName],

wfp.[Name_FamilyName],

wfp.[Name_PreferredGivenName],

wfp.[Name_PreferredFamilyName],

wfp.[Gender],

wfp.[Email_Address] as [MainEmail],

wfp.[Email_Type] as [MainEmailType],

ca.Post,

oe.Email_Address as [OtherEmail],

oe.Email_Type as [OtherEmailType],

wfp.ZoneId,

wfp.LastReceivedDate

FROM

[WorkforcePersonal] wfp

LEFT OUTER JOIN [CurrentAssignment] ca on wfp.RefId = ca.OwnerRefId and ca.OwnerType = 'WorkforcePersonal' and wfp.ZoneId = ca.zoneId

LEFT OUTER JOIN [OtherEmail] oe on wfp.RefId = oe.OwnerRefId and oe.OwnerType = 'WorkforcePersonal' and wfp.ZoneId = oe.zoneId[/sql]

Extracting a list of School Groups and/or Teaching Groups

School Groups in SIF are essentially a grouping of students and staff with a specific name and type, the type has two properties

– a category (Generic, Registration or Teaching), and a title (User, Year Group, House, etc)

[sql]SELECT

sgt.GroupType,

sgt.Title,

sg.GroupName,

sg.[Description],

sg.WorkforcePersonalRefId as [MainWorkforcePersonalRefId],

t.WorkforcePersonalRefId as [OtherWorkforcePersonalRefId],

t.[Role] as [OtherWorkforcePersonalRole] FROM

SchoolGroupType sgt

INNER JOIN SchoolGroup sg on sg.SchoolGroupTypeRefId = sgt.RefId and sg.ZoneId = sgt.ZoneId

LEFT OUTER JOIN Teacher t on sg.RefId = t.OwnerRefId and t.OwnerType = 'SchoolGroup' and sg.ZoneId = t.zoneId[/sql]

 

And for a list of LearnerPersonals in a given group:

[sql]SELECT

LearnerPersonalRefId

FROM

LearnerGroupEnrolment

WHERE

SchoolGroupRefId = '...' and ZoneId = '...'

AND (EntryDate IS NULL or EntryDate <= GETDATE()) AND (ExitDate IS NULL or ExitDate > GETDATE())[/sql]

Extracting Timetable Information

Timetable in SIF is relatively straightforward, and is defined in the Cycle object.

A Cycle has:

  • A RefId and a name
  • A number of days in the Cycle – e.g. 5, or 10 for a two-week timetable.
  • A list of standard periods in the cycle
  • A list of TTDays in the Cycle, each TTDay has:
  • An arbitrary TTDayID, e.g. Mo2 for the second Monday. This is set by the school.
  • A short and long name for the day
  • A Day Index, indicating the order of the TTDay in the Cycle
  • A list of periods in the TTDay, each period has:
    • A PeriodIndex, indicating the order of the period in the TTDay
    • A long name and short name, e.g. Period 2 or Registration.
    • A start time and end time
    • A type (T = Teaching, N = Non-teaching)
    • Groupcall Xporter supplements this data where possible to provide a list of dates that a TTDay is effective on, enabling logical-to-temporal mapping.

Periods Ordered Across A Cycle

To obtain a list of periods ordered across a cycle you can use the following query:

[sql]SELECT

c.RefId,

c.CycleDays,

c.CycleName,

ttd.DayIndex,

ttd.ShortName,

ttd.LongName,

pd.PeriodIndex,

CONVERT(VARCHAR(8), StartTime, 108) as StartTime,

CONVERT(VARCHAR(8), EndTime, 108) as EndTime,

pd.ShortName,

pd.LongName,

pd.Type

FROM

Period pd

INNER JOIN TTDay ttd on pd.OwnerRefId = ttd.OwnerRefId AND ttd.OwnerType = 'Cycle' and pd.zoneId = ttd.zoneId and pd.TTDayId = ttd.TTDayId

INNER JOIN Cycle c on c.RefId = ttd.OwnerRefId and c.ZoneId = ttd.zoneId

WHERE

  1. OwnerType = 'TTDay'[/sql]


Important Note: Cycle also contains a generic period list, where pd.OwnerType is ‘Cycle’ but since schools can have differing sets of periods on different days the implementer is generally directed to the per-TTDay period list.

 

Determine Which TTday Is Effective On A Given Date

To determine which TTDay is effective on a given date, you can query via the TTDayDate table:

[sql]SELECT

c.RefId,

c.CycleDays,

c.CycleName,

ttd.DayIndex,

ttd.ShortName,

ttd.LongName,

pd.PeriodIndex,

CONVERT(VARCHAR(8), StartTime, 108) as StartTime,

CONVERT(VARCHAR(8), EndTime, 108) as EndTime,

pd.ShortName,

pd.LongName,

pd.Type

FROM

Period pd

INNER JOIN TTDay ttd on pd.OwnerRefId = ttd.OwnerRefId AND ttd.OwnerType = 'Cycle' and pd.zoneId = ttd.zoneId and pd.TTDayId = ttd.TTDayId

INNER JOIN Cycle c on c.RefId = ttd.OwnerRefId and c.ZoneId = ttd.zoneId

INNER JOIN TTDayDate ttdd on c.RefId = ttdd.OwnerRefId and ttdd.OwnerType = 'Cycle' and ttdd.zoneId = c.ZoneId and ttdd.TTDayId = ttd.TTDayId

WHERE

pd.OwnerType = 'TTDay'

AND ttdd.DayDate = '2011-09-05'[/sql]

 

Which Lessons to Present in Given Periods for A Given Member of Staff

The next likely requirement is to determine for a member of staff which lessons to present in those periods, this can be achieved by identifying the Lesson objects that apply for the given TTDayId and PeriodIndex. The main teacher can be identified via the GroupTeacher table, where the source MIS supports this concept, but all associated teachers can be identified via the TeachingGroup object.

 

For the main teacher, where the source MIS supports this concept:

[sql]SELECT

l.TTDayId,

l.PeriodIndex,

ttr.Code as [TTRoomCode],

l.Type as [LessonType],

l.Length as [PeriodsCovered],

t.TTTeacherRefId as [WorkforcePersonalRefId],

tg.GroupDefinition_Shortname,

tg.GroupDefinition_Longname,

l.ZoneId

FROM

Lesson l

LEFT OUTER JOIN Teacher t on t.OwnerRefId = l.RefId and t.OwnerType = 'Lesson' and t.zoneId = l.ZoneId

INNER JOIN TeachingGroup tg on tg.RefId = l.TeachingGroupRefId and tg.ZoneId = l.zoneId

LEFT OUTER JOIN TTRoom ttr on ttr.RefId = l.TTRoomRefId[/sql] For all teachers associated with a teaching group:

[sql]SELECT

l.TTDayId,

l.PeriodIndex,

ttr.Code as [TTRoomCode],

l.Type as [LessonType],

l.Length as [PeriodsCovered],

tts.Code as [SubjectCode],

tts.Description as [SubjectDescription],

t.WorkforcePersonalRefId as [WorkforcePersonalRefId],

t.Role as [WorkforcePersonalRole],

tg.GroupDefinition_Shortname,

tg.GroupDefinition_Longname,

l.ZoneId

FROM

Lesson l

INNER JOIN TeachingGroup tg on tg.RefId = l.TeachingGroupRefId and tg.ZoneId = l.zoneId

LEFT OUTER JOIN Teacher t on t.OwnerRefId = tg.RefId and t.OwnerType = 'SchoolGroup' and t.zoneId = tg.ZoneId

LEFT OUTER JOIN TTSubject tts on tg.GroupDefinition_TTSubjectRefId = tts.RefId and tg.zoneid = tts.zoneid

LEFT OUTER JOIN TTRoom ttr on ttr.RefId = l.TTRoomRefId[/sql]



Important Note: For Groupcall agents, TTTeacherRefId is common with WorkforcePersonalRefId to reduce the number of joins required and the same also applies of TeachingGroup and SchoolGroup RefIds. Hence in both examples above we use TTTeacherRefId as a WorkforcePersonalRefId and in the latter example we also use TeachingGroupRefId to join to SchoolGroup.RefId.

 

Identify the Timetable for A Specific Student

The query above can be adjusted slightly to join LearnerGroupEnrolment and identify the timetable for a specific student:

 

[sql]SELECT

l.TTDayId,

l.PeriodIndex,

ttr.Code as [TTRoomCode],

l.Type as [LessonType],

l.Length as [PeriodsCovered],

tts.Code as [SubjectCode],

tts.Description as [SubjectDescription],

lge.LearnerPersonalRefId,

tg.GroupDefinition_Shortname,

tg.GroupDefinition_Longname,

l.ZoneId

FROM

Lesson l

INNER JOIN TeachingGroup tg on tg.RefId = l.TeachingGroupRefId and tg.ZoneId = l.zoneId

INNER JOIN LearnerGroupEnrolment lge on l.TeachingGroupRefId = lge.SchoolGroupRefId and l.ZoneId = lge.ZoneId

LEFT OUTER JOIN TTSubject tts on tg.GroupDefinition_TTSubjectRefId = tts.RefId and tg.zoneid = tts.zoneid

LEFT OUTER JOIN TTRoom ttr on ttr.RefId = l.TTRoomRefId[/sql]

Extracting Attendance

 

XVault supports three different types of Attendance:

  • Summary: simple statistics for authorised and unauthorised session absence
  • Session: marks for AM and PM attendance
  • Lesson: marks for learners in specific lesson periods.

The former two are subject to statutory definition in England and Wales. The latter is defined by each school as required, although usually based on the statutory codes with additions.

Summary Attendance Information

To obtain summary attendance information is a simple query in a single table:

[sql]SELECT

LearnerPersonalRefId,

SchoolYear,

SessionsPossible,

SessionsAttendedTotal,

AuthorisedAbsences,

UnauthorisedAbsences,

ZoneId

FROM

LearnerAttendanceSummary[/sql]

 

Session Attendance Mark String

Groupcall agents also provide a Marks column providing the Session attendance mark string to date for further analysis or presentation.

 

For attendance you should be very considerate to the volume of data you might be querying against. For a school of 1,500 students teaching for e.g. 195 (52 – 13 * 5) days in a year this will cause 585,000 (1500 * 195 * 2) session attendance records to be stored for that year. If you spread this across a local authority of e.g. 80,000 students this could result in just over 3.1m session attendance records per year. As such, you should carefully consider how you index, join, and filter against such a large table – noting that the same table also stores lesson attendance marks which can number up to 8 or 10 marks per day, a scale factor of at least four against our examples above.

 

[sql]SELECT

AttendanceDate,

Session,

LearnerPersonalRefId,

AttendanceCode

FROM

LearnerAttendance

WHERE

AttendanceDomain = 'Session'

AND AttendanceDate > '...'

AND AttendanceDate < '...'[/sql]

 


Important Note: The SIF attendance code types, and particularly SIF-specific code ‘K’, which mean that there is no mark stored in the MIS for this session.

 

Lesson Attendance

For Lesson attendance it is necessary to relate back to a specific SchoolGroup or TeachingGroup by timepoint as most MIS products don’t store Lesson attendance marks against a lesson, but instead against a point in time. Therefore, we have to join across the timetable. First by determining the TTDay that the AttendanceDate refers to, then determining the PeriodIndex on that TTDay that StartTime refers to, then determining which Lessons are occurring at that time. We can now join across LearnerGroupEnrolment to establish which of those lessons the learner was in.

 

[sql]SELECT

la.LearnerPersonalRefId,

la.AttendanceDate,

la.StartTime,

la.AttendanceCode,

ttd.LongName,

p.EndTime,

tg.GroupDefinition_Shortname,

tg.GroupDefinition_Longname,

tts.Description as [SubjectDescription] FROM

LearnerAttendance la

INNER JOIN TTDayDate ttdd on la.AttendanceDate = ttdd.DayDate and la.ZoneId = ttdd.zoneId

INNER JOIN TTDay ttd on ttd.TTDayId = ttdd.TTDayId and ttd.zoneId = ttdd.zoneId

INNER JOIN Period p on ttd.TTDayId = p.TTDayId and CONVERT(VARCHAR(8), p.StartTime, 108) = CONVERT(VARCHAR(8), la.StartTime, 108) and p.zoneId = ttd.zoneId

INNER JOIN Lesson l on l.TTDayId = ttdd.TTDayId and l.PeriodIndex = p.PeriodIndex and l.ZoneId = p.zoneId

INNER JOIN LearnerGroupEnrolment lge on lge.SchoolGroupRefId = l.TeachingGroupRefId and lge.LearnerPersonalRefId = la.LearnerPersonalRefId and lge.ZoneId = l.ZoneId

LEFT OUTER JOIN TeachingGroup tg on tg.RefId = l.TeachingGroupRefId and tg.ZoneId = l.ZoneId

LEFT OUTER JOIN TTSubject tts on tts.RefId = tg.GroupDefinition_TTSubjectRefId and tts.ZoneId = tg.ZoneId

WHERE

la.AttendanceDomain = 'Lesson'

AND (lge.EntryDate IS NULL or lge.EntryDate <= GETDATE()) AND (lge.ExitDate IS NULL or lge.ExitDate > GETDATE())

AND la.LearnerPersonalRefId = '...'

AND la.AttendanceDate = '...'[/sql]

Extracting Assessment Results

Assessment in SIF is quite normalised, and XVault supports the following SIF objects:

  • AssessmentResultComponent: the equivalent of an Aspect
  • AssessmentResultComponentGroup: the equivalent of a Mark Sheet
  • AssessmentSession: periods of assessment e.g. Summer assessment
  • LearnerAssessmentResult: specific assessment results for a learner

Caveats:

  • Assessment is big, schools can create millions of records. Consequently, Groupcall Agents provide only Key Stage results back by default.
  • Currently the SIF data model doesn’t make provision for forecast grades, although if a business case arose this could likely be accommodated.

An example query that provides results for a learner, attributing to a date, an aspect, a subject and potentially an assessment session is shown below:

[sql]SELECT

lar.LearnerPersonalRefId,

lar.Result,

lar.AchievementDate,

arc.Name,

arcs.[Subject],

sessname.SIF_Value as SessionName,

sessenddate.SIF_Value as SessionEndDate,

lar.ZoneId

FROM

LearnerAssessmentResult lar

inner join AssessmentResultComponent arc on lar.AssessmentComponentRefId = arc.RefId and arc.ZoneId = lar.ZoneId

inner join [Subject] arcs on arc.RefId = arcs.OwnerRefId and arcs.OwnerType = 'AssessmentResultComponent' and arcs.zoneId = arc.ZoneId

inner join AssessmentSessionRefId sesslink on sesslink.OwnerRefId = lar.RefId and sesslink.OwnerType = 'LearnerAssessmentResult' and sesslink.zoneId = lar.ZoneId

left outer join SIF_ExtendedElement sessname on sessname.OwnerRefId = sesslink.AssessmentSessionRefId and sessname.OwnerType = 'AssessmentSession' and sessname.SIF_Name = 'Name' and sessname.zoneId = lar.ZoneId

left outer join SIF_ExtendedElement sessenddate on sessenddate.OwnerRefId = sesslink.AssessmentSessionRefId and sessenddate.OwnerType = 'AssessmentSession' and sessenddate.SIF_Name = 'EndDate' and sessenddate.zoneId = lar.ZoneId

ORDER BY

lar.LearnerPersonalRefId,

arcs.[Subject],

lar.AchievementDate

[/sql]

 

Duplicate or Multi-Site Learners

One of the most common challenges when identifying learners in an aggregated data store is picking out duplicate learners, where those duplicates may or may not be intended. It is possible for a learner to legitimately be in two or more schools for their learning. However, it is also possible for a learner to accidentally be in two schools. For example, if a learner has changed school but their old school is offline to XVault this will prevent the deletion of their old record.

It is possible in both cases to identify such learners once a duplicate record has been identified.  Let’s assume that we have two learners, A and B, that appear to be duplicates – that is they share common demographic details within LearnerPersonal and are presenting in different zones:

[sql]SELECT

[LearnerPersonalRefId],

[EntryDate],

[ExitDate],

[MembershipType],

[LastReceivedDate],

[ZoneId] FROM

[LearnerSchoolEnrolment] WHERE

[LearnerPersonalRefId] IN ([your list of duplicate RefIds])

AND ([ExitDate] IS NULL or [ExitDate] > GETDATE())

AND ([EntryDate] IS NULL or [EntryDate] <= GETDATE())[/sql]

1) Check the current LearnerSchoolEnrolment table for both learners. Specifically note the MembershipType column.  The values for this are in the UK SIF specification, but suffice to say that the following values are most common:

    • C – this is the learner’s current enrolment.
    • M – this is the learner’s current main enrolment, they may also exist in another school.
    • S – this is a subsidiary enrolment for a learner, they will have a Main enrolment in another school and attend this school for e.g. a specific subject or course.
    • G – the learner is a guest in this school and has their enrolment elsewhere.
      • If learner A has a MembershipType of G and learner B has a MembershipType of M then you should hold precedence for learner B over learner A.

 

2) Check the EntryDate and ExitDate columns for both learners. By default, school agents do not provide learners who are leavers or future enrolments. However, this functionality is possible and could, for example, result in a record for learner A being their current enrolment and learner B being their future enrolment (pre-admission) at their next school.

3) Check the lastReceived for each learner. If A is significantly staler than B then most likely there is an auto-deletion issue in XVault or the source school has yet to upload a record set that doesn’t contain A.

4) Allow 72 hours to pass and confirm if the issue still exists, as XVault may not yet have had sufficient data to meet its auto-delete criteria. Typically, a learner movement means the learner stops appearing in the current set from their old school and starts appearing in the current set from their new school. However due to the nature of school data entry, this can also happen the other way around and therefore can take a few days to be automatically corrected.

5) If both learners are current, and there is no evidence to suggest which takes precedence, then you should speak to the schools providing the records. The schools will be able to clarify and it may be that one school has accidentally recorded their statutory data incorrectly.

When a learner moves school within England and Wales, the schools typically exchange a student definition file (CTF) that migrates the learner’s records from one school to the next. This migration includes Key Stage assessment, parental contacts, attendance summary history, free school meal entitlements, their school history, and their UPN or ULN where applicable.

Use of SQL Triggers

Groupcall XVault will continue to operate if the SQL database has triggers created within it; you should exercise caution in not causing excessive or recursive locks when invoking triggers.  The safest approach with triggers is to cause a change to a non-XVault table in the same database.  You can create extra tables in the XVault database if you require, however we strongly recommend prefixing them with something unique to your organisation to guard against future table name conflicts.

One of the most common requirements for integrators is to identify rows that have recently been deleted from the XVault database, for example the removal of LearnerPersonal records.  This could, for example, be used to populate a table of LearnerPersonal RefIds that have recently been deleted or recently modified.
 

Examples

For example, if a table was created and indexed as below:

[sql]CREATE TABLE XVaultUpdateHistory

(

TableName         VARCHAR(40)    not null,

RefId     VARCHAR(64)    not null,

CreatedDate      DATETIME null,

[ModifiedDate] DATETIME null,

Dirty      INT not null         DEFAULT 0,

[Action] VARCHAR(3)

CONSTRAINT [XVaultUpdateHistory_PK] PRIMARY KEY CLUSTERED

(

[TableName] ASC,

[RefId] ASC

)

 

);

 

CREATE INDEX XVUpdateHistory_Dirty ON XVaultUpdateHistory(Dirty);

CREATE INDEX XVUpdateHistory_ModifiedDate ON XVaultUpdateHistory(ModifiedDate);

CREATE INDEX XVUpdateHistory_Action ON XVaultUpdateHistory([Action]);[/sql]

 

Then a trigger could be created on LearnerPersonal to pick up delete events:

[sql]CREATE TRIGGER after_delete_trigger ON LearnerPersonal FOR DELETE AS

SET NOCOUNT ON;

IF EXISTS(SELECT * FROM DELETED)

BEGIN

DECLARE @TableName VARCHAR(40);

DECLARE @Action            VARCHAR(3);

DECLARE @RefId              VARCHAR(64);

DECLARE @ModifiedDate            DATETIME;

SET @TableName = 'LearnerPersonal';

SET @Action = 'DEL';

SELECT @RefId = [RefId],@ModifiedDate = [ModifiedDate] FROM DELETED;

 

UPDATE XVaultUpdateHistory

SET

Action = @Action,

ModifiedDate = @ModifiedDate,

Dirty = 1

WHERE

TableName=@TableName

AND RefId=@refid;

 

IF (@@rowcount=0)

BEGIN

-- no history table object was updated so it must not be

-- in the table yet, go create it instead

INSERT INTO XVaultUpdateHistory (TableName, RefId, CreatedDate, ModifiedDate, Dirty, Action)

values(@TableName, @RefId, CURRENT_TIMESTAMP, @ModifiedDate, 1, @Action);

END;

END;[/sql]

 

Similarly, code could be created to populate the same table on Update events too:

[sql]CREATE TRIGGER after_update_trigger ON LearnerPersonal FOR UPDATE AS

SET NOCOUNT ON;

IF (UPDATE(ModifiedDate))

BEGIN

DECLARE @TableName VARCHAR(40);

DECLARE @Action            VARCHAR(3);

DECLARE @RefId              VARCHAR(64);

DECLARE @ModifiedDate            DATETIME;

SET @TableName = 'LearnerPersonal';

SET @Action = 'UPD';

SELECT @RefId = [RefId],@ModifiedDate = [ModifiedDate] FROM INSERTED;

 

UPDATE XVaultUpdateHistory

SET

Action = @Action,

ModifiedDate = @ModifiedDate,

Dirty = 1

WHERE

TableName=@TableName

AND RefId=@refid;

 

IF (@@rowcount=0)

BEGIN

-- no history table object was updated so it must not be                -- in the table yet, go create it instead

INSERT INTO XVaultUpdateHistory (TableName, RefId, CreatedDate, ModifiedDate, Dirty, Action)

values(@TableName, @RefId, CURRENT_TIMESTAMP, @ModifiedDate, 1, @Action);

END;

END;[/sql]

 


Important Note: The use of triggers is outside of the scope of Groupcall support boundary for XVault; as such the above examples are provided as-is with no warranty.

 

Use of SQL Indexes

Groupcall XVault will continue to operate if the SQL database has indexes created within it. You should exercise caution in your indexing policy to ensure that you do not create excessive or duplicate indexes.

The following SQL allows you to determine SQL indexes already present in the Groupcall XVault database, and Groupcall can provide a default index selection to further optimise the performance of most XVault installations.

 

[sql]SELECT

schema_name(schema_id) as SchemaName, OBJECT_NAME(si.object_id) as TableName, si.name as IndexName,

(CASE is_primary_key WHEN 1 THEN 'PK' ELSE '' END) as PK,

(CASE is_unique WHEN 1 THEN '1' ELSE '0' END)+' '+

(CASE si.type WHEN 1 THEN 'C' WHEN 3 THEN 'X' ELSE 'B' END)+' '+ -- B=basic, C=Clustered, X=XML

(CASE INDEXKEY_PROPERTY(si.object_id,index_id,1,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+

(CASE INDEXKEY_PROPERTY(si.object_id,index_id,2,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+

(CASE INDEXKEY_PROPERTY(si.object_id,index_id,3,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+

(CASE INDEXKEY_PROPERTY(si.object_id,index_id,4,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+

(CASE INDEXKEY_PROPERTY(si.object_id,index_id,5,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+

(CASE INDEXKEY_PROPERTY(si.object_id,index_id,6,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+

'' as 'Type',

INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,1) as Key1,

INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,2) as Key2,

INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,3) as Key3,

INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,4) as Key4,

INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,5) as Key5,

INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,6) as Key6

FROM sys.indexes as si

LEFT JOIN sys.objects as so on so.object_id=si.object_id

WHERE index_id>0 -- omit the default heap

and OBJECTPROPERTY(si.object_id,'IsMsShipped')=0 -- omit system tables

and not (schema_name(schema_id)='dbo' and OBJECT_NAME(si.object_id)='sysdiagrams') -- omit sysdiagrams

-- uncomment to exclude xv_* indexes

-- and si.name not like 'xv_%'

ORDER BY SchemaName,TableName,IndexName[/sql]

 

In terms of the recommended assignment of indexes within XVault this will depend fully on how you query into it, clearly the focus of any additional indexes you add will be determined by which columns you filter or join upon during your daily use.

Next Steps...

If you need any further assistance or get in to any difficulty, then please contact Groupcall Support. If the issue affects Groupcall Partner products you should refer to the support arrangements for that specific Groupcall Partner.

For issues regarding integration with XVault, Groupcall provide consultancy services to assist you in integrating with XVault quickly and efficiently based on your specific needs.  If you’d like to engage these services then please contact your Implementation Consultant or Account Manager.



Need further help?