Extracting Alps Data from ProSolution

Standard

Probably too late for anybody else wanting to get this done for this years A-Levels, but I thought I’d write up how we’ve got a data extract for the Alps Data working inside ProSolution.

In designing the system for achieving this we wanted to make it as automated as possible for calculating data, but still have the flexibility of completing manual adjustments.  Therefore, we decided to use user-defined fields for the student detail object, and enrolment object.  This way the automated system will update data in scope when the current value is null meaning that adjustments can be made and won’t be overwritten.

Step 1

The details about the Alps Software is available here: https://www.alps-va.co.uk/, it’s a service that compares students results at Level 3 (A-levels) with their GCSE results against other providers.  It helps understand how well students are achieving based on their prior performance at GCSE and against the national picture.

The first thing is to download and install their software from here https://my.alps-va.co.uk/Go/DownloadADCS. With this comes some useful files that we will need later. But for now just run the software and complete the registration bit, and eventually, you will end up with spreadsheet link screen that shows the data fields that are needed. Btw, It’s a great data validation tool.

Step 2

There are two pieces of information that we need to produce that are not standard from ProSolution, the students GCSE Score and the Alps Subject.

The first one to get working is the student GCSE Score.  For this,we will use the Qualification on Entry data held in ProSolution and a custom table with a mapping of Grades to scores.

Create a table ProSolutionReports to hold the mapping by copying and pasting this code.

USE [ProSolutionReports]
GO
/****** Object:  Table [dbo].[QonEScores]    Script Date: 16/08/16 20:37:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[QonEScores](
	[QoEScoresID] [bigint] NULL,
	[LA_TYPE_CODE] [varchar](50) NULL,
	[Qual] [varchar](50) NULL,
	[Grade] [varchar](50) NULL,
	[QCA_Points] [bigint] NULL,
	[Rebased_QCA] [bigint] NULL,
	[QualCount] [bigint] NULL,
	[ALIS_Points] [bigint] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (127, N'1327', N'Key Skills Level 1', N'P', 1, 1, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (128, N'1327', N'Key Skills Level 2', N'F', 0, 0, 0, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (129, N'1327', N'Key Skills Level 2', N'P', 1, 1, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (130, N'1327', N'Key Skills Level 3', N'F', 0, 0, 0, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (131, N'1327', N'Key Skills Level 3', N'P', 1, 1, 0, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (132, N'2999', N'Short GCSE', N'A', 26, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (133, N'2999', N'Short GCSE', N'A*', 29, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (134, N'2999', N'Short GCSE', N'B', 23, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (135, N'2999', N'Short GCSE', N'C', 20, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (136, N'2999', N'Short GCSE', N'D', 17, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (137, N'2999', N'Short GCSE', N'E', 14, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (138, N'2999', N'Short GCSE', N'F', 11, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (139, N'2999', N'Short GCSE', N'G', 8, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (140, N'2999', N'Short GCSE', N'N', 0, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (181, N'0029', N'BTEC First Diploma', N'ME', 196, 0, 4, 24)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (182, N'0029', N'BTEC First Diploma', N'PA', 160, 0, 4, 20)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (183, N'0029', N'BTEC First Diploma', N'D', 220, 0, 4, 30)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (184, N'0029', N'BTEC First Diploma', N'DS', 220, 0, 4, 30)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (184, N'0003', N'GCSE', N'AB', 98, 0, 0, 11)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (185, N'1422', N'GCSE', N'AB', 98, 0, 2, 13)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (186, N'1422', N'GCSE', N'AC', 92, 0, 2, 12)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (187, N'1422', N'GCSE', N'AD', 86, 0, 2, 11)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (188, N'1422', N'GCSE', N'AE', 80, 0, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (189, N'1422', N'GCSE', N'AF', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (190, N'1422', N'GCSE', N'AG', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (191, N'1422', N'GCSE', N'BA', 98, 0, 2, 13)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (192, N'1422', N'GCSE', N'BC', 86, 0, 2, 11)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (193, N'1422', N'GCSE', N'BD', 80, 0, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (194, N'1422', N'GCSE', N'BE', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (195, N'1422', N'GCSE', N'BF', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (196, N'1422', N'GCSE', N'BG', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (197, N'1422', N'GCSE', N'CA', 92, 0, 2, 12)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (198, N'1422', N'GCSE', N'CB', 86, 0, 2, 11)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (199, N'1422', N'GCSE', N'CD', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (200, N'1422', N'GCSE', N'CE', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (201, N'1422', N'GCSE', N'CF', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (202, N'1422', N'GCSE', N'CG', 56, 0, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (203, N'1422', N'GCSE', N'DA', 86, 0, 2, 11)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (204, N'1422', N'GCSE', N'DB', 80, 0, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (205, N'1422', N'GCSE', N'DC', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (206, N'1422', N'GCSE', N'DE', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (207, N'1422', N'GCSE', N'DF', 56, 0, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (208, N'1422', N'GCSE', N'DG', 50, 0, 2, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (209, N'1422', N'GCSE', N'EA', 80, 0, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (210, N'1422', N'GCSE', N'EB', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (211, N'1422', N'GCSE', N'EC', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (212, N'1422', N'GCSE', N'ED', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (213, N'1422', N'GCSE', N'EF', 50, 0, 2, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (214, N'1422', N'GCSE', N'EG', 44, 0, 2, 4)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (215, N'1422', N'GCSE', N'FA', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (216, N'1422', N'GCSE', N'FB', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (217, N'1422', N'GCSE', N'FC', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (218, N'1422', N'GCSE', N'FD', 56, 0, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (219, N'1422', N'GCSE', N'FE', 50, 0, 2, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (220, N'1422', N'GCSE', N'GA', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (221, N'1422', N'GCSE', N'GB', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (222, N'1422', N'GCSE', N'GC', 56, 0, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (223, N'1422', N'GCSE', N'GD', 50, 0, 2, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (224, N'1422', N'GCSE', N'GE', 44, 0, 2, 4)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (225, N'1422', N'GCSE', N'GF', 38, 0, 2, 3)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (226, N'0003', N'GCSE', N'AB', 98, 0, 2, 13)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (227, N'0003', N'GCSE', N'AC', 92, 0, 2, 12)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (228, N'0003', N'GCSE', N'AD', 86, 0, 2, 11)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (229, N'0003', N'GCSE', N'AE', 80, 0, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (230, N'0003', N'GCSE', N'AF', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (231, N'0003', N'GCSE', N'AG', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (232, N'0003', N'GCSE', N'BA', 98, 0, 2, 13)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (233, N'0003', N'GCSE', N'BC', 86, 0, 2, 11)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (234, N'0003', N'GCSE', N'BD', 80, 0, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (235, N'0003', N'GCSE', N'BE', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (236, N'0003', N'GCSE', N'BF', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (237, N'0003', N'GCSE', N'BG', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (238, N'0003', N'GCSE', N'CA', 92, 0, 2, 12)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (239, N'0003', N'GCSE', N'CB', 86, 0, 2, 11)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (240, N'0003', N'GCSE', N'CD', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (241, N'0003', N'GCSE', N'CE', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (242, N'0003', N'GCSE', N'CF', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (243, N'0003', N'GCSE', N'CG', 56, 0, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (244, N'0003', N'GCSE', N'DA', 86, 0, 2, 11)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (245, N'0003', N'GCSE', N'DB', 80, 0, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (246, N'0003', N'GCSE', N'DC', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (247, N'0003', N'GCSE', N'DE', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (248, N'0003', N'GCSE', N'DF', 56, 0, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (249, N'0003', N'GCSE', N'DG', 50, 0, 2, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (250, N'0003', N'GCSE', N'EA', 80, 0, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (251, N'0003', N'GCSE', N'EB', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (252, N'0003', N'GCSE', N'EC', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (253, N'0003', N'GCSE', N'ED', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (254, N'0003', N'GCSE', N'EF', 50, 0, 2, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (255, N'0003', N'GCSE', N'EG', 44, 0, 2, 4)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (256, N'0003', N'GCSE', N'FA', 74, 0, 2, 9)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (257, N'0003', N'GCSE', N'FB', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (258, N'0003', N'GCSE', N'FC', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (259, N'0003', N'GCSE', N'FD', 56, 0, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (260, N'0003', N'GCSE', N'FE', 50, 0, 2, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (261, N'0003', N'GCSE', N'GA', 68, 0, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (262, N'0003', N'GCSE', N'GB', 62, 0, 2, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (263, N'0003', N'GCSE', N'GC', 56, 0, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (264, N'0003', N'GCSE', N'GD', 50, 0, 2, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (265, N'0003', N'GCSE', N'GE', 44, 0, 2, 4)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (266, N'0003', N'GCSE', N'GF', 38, 0, 2, 3)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (1, N'2999', N'Short GCSE', N'U', 0, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (2, N'0003', N'GCSE', N'U', 0, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (3, N'0003', N'GCSE', N'A*A*', 116, 104, 2, 16)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (4, N'0003', N'GCSE', N'AA', 104, 92, 2, 14)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (5, N'0003', N'GCSE', N'BB', 92, 80, 2, 12)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (6, N'0003', N'GCSE', N'CC', 80, 68, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (7, N'0003', N'GCSE', N'DD', 68, 56, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (8, N'0003', N'GCSE', N'EE', 56, 44, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (9, N'0003', N'GCSE', N'FF', 44, 32, 2, 4)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (10, N'0003', N'GCSE', N'GG', 32, 16, 2, 2)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (27, N'0029', N'BTEC First Diploma', N'M', 196, 0, 4, 24)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (28, N'0029', N'BTEC First Diploma', N'P', 160, 0, 4, 20)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (163, N'1422', N'GCSE', N'U', 0, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (164, N'1422', N'GCSE', N'A*A*', 116, 104, 2, 16)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (165, N'1422', N'GCSE', N'AA', 104, 92, 2, 14)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (166, N'1422', N'GCSE', N'BB', 92, 80, 2, 12)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (167, N'1422', N'GCSE', N'CC', 80, 68, 2, 10)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (168, N'1422', N'GCSE', N'DD', 68, 56, 2, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (169, N'1422', N'GCSE', N'EE', 56, 44, 2, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (170, N'1422', N'GCSE', N'FF', 44, 32, 2, 4)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (171, N'1422', N'GCSE', N'GG', 32, 16, 2, 2)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (172, N'1422', N'GCSE', N'A', 52, 46, 1, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (173, N'1422', N'GCSE', N'A*', 58, 52, 1, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (174, N'1422', N'GCSE', N'B', 46, 40, 1, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (175, N'1422', N'GCSE', N'C', 40, 34, 1, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (176, N'1422', N'GCSE', N'D', 34, 28, 1, 4)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (177, N'1422', N'GCSE', N'E', 28, 22, 1, 3)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (178, N'1422', N'GCSE', N'F', 22, 16, 1, 2)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (179, N'1422', N'GCSE', N'G', 16, 8, 1, 1)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (180, N'1422', N'GCSE', N'N', 0, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (72, N'0036', N'FE NVQ Level 1', N'F', 0, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (73, N'0036', N'FE NVQ Level 1', N'P', 1, 1, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (74, N'0036', N'FE NVQ Level 2', N'F', 0, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (75, N'0036', N'FE NVQ Level 2', N'P', 1, 1, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (88, N'0003', N'GCSE', N'A', 52, 46, 1, 7)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (89, N'0003', N'GCSE', N'A*', 58, 52, 1, 8)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (90, N'0003', N'GCSE', N'B', 46, 40, 1, 6)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (91, N'0003', N'GCSE', N'C', 40, 34, 1, 5)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (92, N'0003', N'GCSE', N'D', 34, 28, 1, 4)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (93, N'0003', N'GCSE', N'E', 28, 22, 1, 3)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (94, N'0003', N'GCSE', N'F', 22, 16, 1, 2)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (95, N'0003', N'GCSE', N'G', 16, 8, 1, 1)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (96, N'0003', N'GCSE', N'N', 0, 0, 1, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (97, N'0035', N'GNVQ Foundation', N'D', 136, 96, 4, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (98, N'0035', N'GNVQ Foundation', N'M', 112, 72, 4, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (99, N'0035', N'GNVQ Foundation', N'P', 76, 36, 4, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (100, N'0035', N'GNVQ Intermediate', N'D', 220, 84, 4, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (101, N'0035', N'GNVQ Intermediate', N'M', 196, 48, 4, 0)
GO
INSERT [dbo].[QonEScores] ([QoEScoresID], [LA_TYPE_CODE], [Qual], [Grade], [QCA_Points], [Rebased_QCA], [QualCount], [ALIS_Points]) VALUES (102, N'0035', N'GNVQ Intermediate', N'PA', 160, 24, 4, 0)
GO

Step 3

Create a view that will calculate the GCSE Score based on the learner’s entry qualifications.


CREATE view [dbo].[Calculated_GCSE_Score]
as

SELECT DISTINCT [AcademicYearID], RefNo, StudentDetailID, Surname, FirstForename, [Age on 31 Aug], Sex,
CAST(SUM(Score) AS Decimal(19, 2)) AS Score, SUM(QualCount) AS QualCount,
CAST(CASE WHEN (SUM(QualCount) < 5) THEN SUM(Score) / SUM(QualCount) * (SUM(QualCount) / 5.0) ELSE CAST(SUM(Score) AS decimal(19, 2)) / CAST(SUM(QualCount) AS decimal(19, 2)) END AS Decimal(19, 2)) AS AvgQCAScore, CAST(SUM(ALIS) AS DECIMAL(19,2)) AS ALIS_Score, SUM(ALISCount) AS ALISCount, CASE WHEN SUM(ALIS) >0 THEN (CAST(CASE WHEN (SUM(ALISCount) < 5) THEN SUM(ALIS) / SUM(ALISCount) * (SUM(ALISCount) / 5.0)
ELSE CAST(SUM(ALIS) AS decimal(19, 2)) / CAST(SUM(ALISCount) AS decimal(19, 2)) END AS Decimal(19, 2))) ELSE 0 END AS ALISAvgScore
FROM
(

SELECT DISTINCT
S.AcademicYearID AS [AcademicYearID], S.RefNo AS RefNo, S.Surname, S.FirstForename, S.DateOfBirth AS [Date of Birth],
ProSolution.dbo.GetAgeOn31Aug(S.DateOfBirth, S.AcademicYearID) AS [Age on 31 Aug], S.Sex, S.StudentDetailID, S.StudentID,
LA.LEARNING_AIM_REF AS [Learning Aim Ref No], LA.LEARNING_AIM_TYPE_CODE, LA.LEARNING_AIM_TITLE AS [Learning Aim Title], QOE.Grade,
QS.QCA_Points AS Score, QS.QualCount, ISNULL(QS.ALIS_Points, 0) AS ALIS, CASE WHEN QS.ALIS_Points IS NOT NULL
THEN QS.QualCount ELSE 0 END AS ALISCount
FROM ProSolution.dbo.StudentDetail AS S
INNER JOIN ProSolution.dbo.QualsOnEntry AS QOE WITH (NOLOCK) ON QOE.StudentID = S.StudentID
INNER JOIN ProSolution.dbo.Learning_Aim AS LA WITH (NOLOCK) ON LA.LEARNING_AIM_REF = QOE.QualCode COLLATE database_default
INNER JOIN ProSolutionReports.dbo.QonEScores AS QS ON QOE.Grade = QS.Grade COLLATE database_default AND LA.LEARNING_AIM_TYPE_CODE = QS.LA_TYPE_CODE COLLATE database_default
) AS VrQonEScores

GROUP BY [AcademicYearID], RefNo, Surname, FirstForename, [Age on 31 Aug], Sex, StudentDetailID
GO

Step 4

Identify a student detail user-defined field for storing the Students GCSE Score.  In our system we used userdefined4 and create a procedure to update the field when it is null.


CREATE PROCEDURE [dbo].[SetGCSEScores]
-- Add the parameters for the stored procedure here
@AcademicYearId varchar(5)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

update sd
set sd.userdefined4 = calc.ALISAvgScore
FROM [ProSolutionReports].[dbo].[Calculated_GCSE_Score] calc
inner join ProSolution.[dbo].[StudentDetail] sd on calc.studentdetailid = sd.StudentDetailID
where calc.[AcademicYearID] = @AcademicYearId and [ALISAvgScore]>0 and sd.userdefined4 is null
END

Step 5

We wanted to be able to manage which learners were in the Alps extract from inside ProSolution. This meant being able to include and exclude learners and also include and exclude enrolments. We did this by using a user-defined field in Student Detail and another one in Enrolment. They were both created as List of Values UDFs with an options for Yes and No. The default is null. We will create a process that updates learners and enrolments to be included by setting the UDF to Yes where it is null. If we later decide that the learner or enrolment should not be included we set it to No.

UDF

We use Userdefined8 for the enrolment and Userdefined5 for the student detail.

Step 6

Now we create a procedure to update these UDFs. The student detail udf is update with the following:

create procedure [dbo].[SetStudentsEligibleForAlps]
as
update sd
set sd.userdefined5 = 1

  FROM [ProSolution].[dbo].[vStudentDetail] sd

  where sd.StudentDetailID in (
  select e.studentdetailid from
  [ProSolution].[dbo].[Enrolment] e
  inner join [ProSolution].[dbo].[Offering] o on e.offeringId = o.offeringid
  inner join [ProSolution].[dbo].[Learning_Aim] la on o.QualID = la.LEARNING_AIM_REF
  where e.FundingID=25 and la.NVQLevelID_v2 = '3'
  )
  and sd.userdefined5 is null

This scope is fairly brought, jsut all learners with EFA Funded enrolments at Level 3. We can manually exclude anything we don’t want.

For the enrolment flagging we use this stored procedure:

create procedure [dbo].[SetEnrolmentsEligibleForAlps]
as
update e
set e.userdefined8 = 1
    from
  [ProSolution].[dbo].[Enrolment] e
  inner join [ProSolution].[dbo].[Offering] o on e.offeringId = o.offeringid
  inner join [ProSolution].[dbo].[Learning_Aim] la on o.QualID = la.LEARNING_AIM_REF
  where e.FundingID=25 and la.NVQLevelID_v2 = '3' and e.userdefined8 is null
  and e.ExpectedEndDate<'20'+right(o.AcademicYearID,2)+'-08-01'

I’m sure there is a better way of doing the bit to detect if the enrolment is expected to end this year, but what’s there is working for now!

Step 7

Now we need to setup a mapping for the Offerings in ProSolution to an Alps Subject. Inside the folders that were installed by the Alps Software is Resources folder, C:\Program Files (x86)\Alps\Data collection software 2016\Files\Resources. The file called QAN to Alps mapping is the important one for this step. This contains a worksheet called QAN Only which contains a map of learning aims to Alps Subjects.

Import this into ProSolutionReports database using the SQL Management Studio Import Data function. I brought the data into a table called QAN_Alps_Mapping.

Step 8

THe second file that is included in the resources file is Alps Complete Subject List. We will import this into a table in ProSolutionReports, but we need to define the table first to create a unique identifier for each row.

CREATE TABLE [dbo].[alps_subject](
	[SubjectID] [int] IDENTITY(1,1) NOT NULL,
	[Subject Type] [varchar](max) NULL,
	[Subject Name] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Now import the spreadsheet to this table using the SQL Management Studio Data Import feature.

Step 9

Next, we need to identify a user-defined field on the offering table that we can use to store the Alps Subject. We use UserDefined8. To make this work properly we specified that the field would use a list of values. Then we populate that list using a bit of SQL, there is fair to much to type in!

INSERT INTO [ProSolution].[dbo].[_CCC_UserDefinedFieldLookupValues] (
      [DataSourceID]
      ,[DataSourceColumnName]
      ,[Value]
      ,[Description]
      ,[IsEnabled]
  )

select '46D38261-B36F-4FF1-8DA2-1300F512C3C4','UserDefined8',SubjectID, [Subject Name],1 from ProSolutionReports..alps_subject where
subjectid not in (
select value from ProSolution..[_CCC_UserDefinedFieldLookupValues]
where [DataSourceID] ='46D38261-B36F-4FF1-8DA2-1300F512C3C4'
)

You will need to get the correct GUID of the offering datasource, I’m not sure if it is the same across all installations. Use this bit of SQL to find yours

SELECT [DataSourceID] FROM [ProSolution].[dbo].[_CCC_DataSource] WHERE DataSourceName='Offering'

and if you used a different UDF make sure you update that too.

Step 10

Let’s now create a way to populate that Offering UDF with the subject code based on the QAN lookup table we create a moment ago. We use a stored procedure so we can run it regularly, ie as new offerings get added.

create procedure [dbo].[update_offering_alps_subjects]
as
update o
set
 o.userdefined8 = a.[SubjectID]
 from

ProSolution..Offering o
inner join [QAN_alps_mapping] m on o.QualID = cast(cast( m.[QAN] as int) as varchar)
inner join [alps_subject] a on a.[Subject Name] = m.[Alps Subject]

Step 11

Nearly there with getting the data in place. Lets schedule a job to run the stored procedures on a daily basis.

USE [msdb]
GO

/****** Object:  Job [Alps_data]    Script Date: 16/08/16 21:50:17 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 16/08/16 21:50:18 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Alps_data',
		@enabled=1,
		@notify_level_eventlog=0,
		@notify_level_email=0,
		@notify_level_netsend=0,
		@notify_level_page=0,
		@delete_level=0,
		@description=N'No description available.',
		@category_name=N'[Uncategorized (Local)]',
		@owner_login_name=N'CompassSystemUser', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Set GCSE Scores for 15/16]    Script Date: 16/08/16 21:50:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Set GCSE Scores for 15/16',
		@step_id=1,
		@cmdexec_success_code=0,
		@on_success_action=4,
		@on_success_step_id=2,
		@on_fail_action=2,
		@on_fail_step_id=0,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'EXEC	 [dbo].[SetGCSEScores]
		@AcademicYearId = N''15/16''
',
		@database_name=N'ProSolutionReports',
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Update offerings with Alps Subject]    Script Date: 16/08/16 21:50:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Update offerings with Alps Subject',
		@step_id=2,
		@cmdexec_success_code=0,
		@on_success_action=4,
		@on_success_step_id=3,
		@on_fail_action=2,
		@on_fail_step_id=0,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'EXEC	 [dbo].[update_offering_alps_subjects]',
		@database_name=N'ProSolutionReports',
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Set Students Eligiable for Alps]    Script Date: 16/08/16 21:50:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Set Students Eligiable for Alps',
		@step_id=3,
		@cmdexec_success_code=0,
		@on_success_action=3,
		@on_success_step_id=0,
		@on_fail_action=2,
		@on_fail_step_id=0,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'EXECUTE  [dbo].[SetStudentsEligibleForAlps] ',
		@database_name=N'ProSolutionReports',
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Set Enrolments Eligible for Alps]    Script Date: 16/08/16 21:50:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Set Enrolments Eligible for Alps',
		@step_id=4,
		@cmdexec_success_code=0,
		@on_success_action=1,
		@on_success_step_id=0,
		@on_fail_action=2,
		@on_fail_step_id=0,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'EXECUTE  [dbo].[SetEnrolmentsEligibleForAlps] ',
		@database_name=N'ProSolutionReports',
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily @ 0600',
		@enabled=1,
		@freq_type=4,
		@freq_interval=1,
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relative_interval=0,
		@freq_recurrence_factor=0,
		@active_start_date=20160815,
		@active_end_date=99991231,
		@active_start_time=60000,
		@active_end_time=235959,
		@schedule_uid=N'0d4e85cf-4eb4-442a-ba8e-fb36deb19208'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Step 12

We’re nearly there, all the data is in place (assuming you’ve run the job that was just created), we just need a view to bring the data together and then import it as a datasource into ProSolution. Here’s the view we need:

CREATE view [dbo].[Alps_Export]
as

select sd.[AcademicYearId]
       ,[RefNo]
      ,[Surname]
      ,[FirstForename]
	  ,ProSolution.dbo.GetAgeOn31Aug([DateOfBirth],sd.[AcademicYearID]) - 4 as [Study Year]
	  ,'20'+right(sd.[AcademicYearId],2) as Year
	  ,sd.Sex
      ,[DateOfBirth]
	  , sd.EthnicGroup
	  , null as QCA_Score
      ,sd.[UserDefined4] as GCSE_Score
	  , sub.[Subject Type]
	  , sub.[Subject Name]
      , e.Grade
  FROM [ProSolution].[dbo].[vStudentDetail] sd
  inner join [ProSolution].[dbo].[Enrolment] e on e.StudentDetailID = sd.StudentDetailID
  inner join [ProSolution].[dbo].[Offering] o on e.offeringId = o.offeringid
  inner join [ProSolution].[dbo].[Learning_Aim] la on o.QualID = la.LEARNING_AIM_REF
  left join [ProSolutionReports].[dbo].[alps_subject] sub on sub.subjectID = o.UserDefined8
  where e.userDefined8 = '1' and ProSolution.dbo.GetAgeOn31Aug([DateOfBirth],sd.[AcademicYearID]) - 4 <15
  and sd.userdefined5 = '1'

There are two ProSolution Definition files to import, firstly the datasource:
https://raw.githubusercontent.com/miketrebilcock/AlpsExport/master/Alps_Export.cccds.xml

then the report:
https://raw.githubusercontent.com/miketrebilcock/AlpsExport/master/Alps%20Export.cccrpt.xml

Finished!

You should now be able to manage which learners appear in the report and export the data into a spreadsheet for importing into the Alps Tool. You’ll probably find a number of offerings will need to have the Alps Subject set manually, you can see which ones are blank from the report.

Hope you managed to follow these instructions and found them useful! All the code here is available on a github repo here:

https://github.com/miketrebilcock/AlpsExport

Must forget, a massive thank you to Asmin at Middlesborough College for sharing how they achieve this and giving me the starting point.

 

*usually disclaimers apply, follow at your own risk, make sure you know what you are doing, perhaps try it on training first, etc, etc.

Cornwall’s First Tecademy

IMG_1918
Standard

Last November (2015) I was lucky enough to attend Picademy  in Birmingham, it was brilliant. It’s really designed to help teachers build their confidence in using the Raspberry Pi, showing off how to use GPIO, minecraft and PiCameras. I wasn’t sure if my background in software development would mean I didn’t get much from it, but I was wrong.  I learnt so much, having the time to try things out, with experts on hand to guide me when I got stuck, not to mention the buzz and excitement from interacting with others and talking about the possibilities that the Pi presents.

So by the time I left Picademy I had set myself a challenge, How do I bring this to Cornwall?  I tried lots of things, talking to Google (They provide funding and a location – Google Garage), but they only go to places where there is sufficient urbanisation (or something), nevermind that Cornwall is the experiencing the second highest growth across the UK in the Digital Sector, we don’t have enough people😦, talking with Raspberry Pi about trying to fund it ourselves, but it wasn’t feasible, with the problems we have trying to get teachers together the money wouldn’t be justified.  I was coming to a dead-end.

Then it happened.  Steve Amor popped up and said “lets just run our own”. And so it came to pass…..tecademy was born.  We really wanted give attendees an experience that was similar to Piacademy.  The Software Cornwall marketing engine went into overdrive and we started the campaign to get a group of teachers together, we sent out emails, letters with hand-written envelopes, posters, tweets, facebook posts and mentioned it to anybody that would listen!

When the big weekend happened on 21st and 22nd May we had 8 eager secondary school teachers turn up ready for our Cornish slant on Picademy.  We would loved to have more attend and had nearly as many mentors as attendees, but we were determined to run the first one and get the word out there that this was a course worth doing.

IMG_1812

We took the attendees through a two day programme covering:

  • Scrum Tennis (Works as a great tech based ice-breaker)
  • GPIO and LEDs (Traffic Lights)
  • Computing resources and the Barefoot programme
  • Minecraft
  • Sonic Pi
  • Controlling Motors (GPIO Zero and H-Bridges)
  • PiCameras

And that was just day 1! Check out the time lapse (great work Ben Whorwood)

Day2 was just as action packed

  • Robocode
  • Microbit with Python
  • Project
  • Demos and Certificates

Attendees of Picademy will see how similar an experience we tried to create, and I think we just about did it!

The final projects were outstanding, elements from across the weekend were brought together to make speed detectors, motion sensors linked to cameras, microbit driven push notifications to email and (my favourite!) a class room noise sensor with visible warnings.

IMG_2189 IMG_2160

And our first set of Cornish Certified Tech Educators had their awards conferred up them!

IMG_2253

The success of the weekend was confirmed and sense of achievement enhanced with some great feedback.

I think it was run very well and everything was well planned, timed and resourced. It was the best training I have ever attended – you put my teacher training to shame! Very well done.

 

Really good – interesting to be the learner again. Intense, exhausting but definitely fun.

Thank to everybody that helped make it happen,

  • Steve for the idea and drive,
  • Bluefruit Software for providing a venue,
  • Belinda and Lyssa at Software Cornwall for all the behind the scenes marketing, admin and support,
  • PiTop for the loan of PiTops with Pi3s
  • all of the mentors for volunteering and supporting the teachers
  • and finally the teachers themselves for giving up a weekend and getting so involved.

When is the next one Steve?

Eden of Things – Day 5

team
Standard

Job Done

Having had a worrying night hoping that the sensor we’d left in the Biome wasn’t going to catch fire due to water or ants, I arrived at the Eden Project with a sense of relief after seeing that all the Biomes were in tacked.

The final day was fully of frantic activity as each team worked their hardest ready for the critical customer demo, could we convince our audience that the prototype was good and we were worth investing in to complete the project?

The sensor team were the first with a problem, having added a digital thermometer the previous evening it became apparent that it wasn’t working just before the sensor station was placed in the Biome.  As I drove home I realised that the circuit board was missing a pull-up resistor, in our rush to get the board ready to go back out it had been forgotten.  So the first task was to recover the sensor station from the Biome and also fix the sensor station we didn’t deploy. The sensor station that had been in the Biome came back soaked in water and with ants in the moisture sensor, and it was dead. A worried team opened it up and removed the battery, it had discharged, we hadn’t got around to the power calculations yet.  A new battery and it was back to life. Resistors

Resistors added, it was back to the rain forest to get some live data for the UI team to work with. Then the real problems began.  Michael, from Eden, took a sensor station to the staff platform at the very top of the Biome – no small feat, a 150 step climb around the outside, climbing in through an access hatch and then climbing the steps.  As he made his way back down, Emo from the sensor team had a worried look, “I’m not seeing anything”.  Same with the sensor we had in our hands.  This was not looking good.  We tried to reset the stations and whilst it looked like they were working, the server wasn’t being passed any data.

20160219_104207

Back in our base room, we started to work out what had happened, all that had changed was a new resistor, but first we reloaded code on to the processor, modified the code, changed the batteries, before eventually cutting the resistor out. Suddenly, the device sprung back into live, I’m still not sure what caused the problem, but there was something wrong with the resistor.  Both stations modified and we had run out of time to put them back into the Biome.  We needed on to display, the team also needed to start to prepare for the demo.

The core server team had a similar busy morning.  They still needed to get the data from the sensor team saving and then being passed to the ui-team.  Up until now the ui team had been being provided with dummy data. The team worked right upto and through the customer demo, eventually getting the data passing through just as the ui demo showed off the data visualisation.

The Big Demo

Eventually, the teams stopped and gathered round our group of VIP customers.  Each team presented what they had been doing throughout the week and demonstrated what they had developed.  All the teams did a great job.  Nobody enjoys the presentations, but having the opportunity to practice presenting to non-peers is really important.  Once the attendees are in work, the more confident they can appear to customers and colleagues the better, confidence only comes with experience and practice, so when we have an opportunity we have to make the most of it.  All of the team members tackled the presentation in the same way that they approached all the challenges of the week, with energy and enthusiasm, well-done everyone, you really were brilliant.

A BIG thank you to our VIPs, the Software Cornwall Committee, Paul (Bluefruit), Belinda and Olly (IBM), plus Bran from the Eden Education Team, Michael – Edens’ Horticultural Technologist, Cornwall Colleges IT team leader – Richard and finally, Steve, maker of Bert and Ernie and also the really annoying Change Driver system!

team

We finished with a retrospective covering the week using Allan Kellys Retrospective Dialogue Sheet.  This provided a great format for enabling the teams to reflect on the whole week and talk through the highs, the lows, the lessons they had learnt and how they could apply them in the future.

There is one team that have been truely amazing this week and deserve a special mention – the mentors. Ben, Paul, Andy, Byran and Pete, without you none of this would have been possible. Thank you for all hours of work that you have put in, your patience, your advice and your enthusiasm.

My Reflection

What started as an idea from a discussion with Edens Horticultural Technologist, Michael, the week had come into being after 6 months of planning and preparation. At the beginning of the week I had real concerns that none of it might work, but actually, as with life, it wasn’t really about the outcome, it was about the journey.

We tried to take the teams through a journey of discovery in understanding what it’s like to work as a software developer. I hoped to demonstrate:

  • Software development is hugely varied, from working soldering components, reading data sheets and trying to interact with physical devices to managing and processing data or designing attractive attention grabbing interfaces.
  • Software development is collaborative.  You have to work as a team; not just single team, but a team within teams.  Not only do you have to work in a team you have to be promiscuous and be prepared to move between and form new teams to solve the problem at hand.
  • Nobody is an expert.  The field of software development is so vast, you can’t expect to know everything. A career in software development is about constantly learning. This is a skill, that like any other skill, needs to be practised. One minute you are the expert and have deep knowledge, the next your are having to learn from a “junior”.  This requires an approach to live and work that isn’t predicated on hierarchy, superiority or a knowledge is power perspective, but instead, a shared learning environment where collaboration and shared learning dominate.
  • Software development is exciting. Enough said!

I think we achieved all of those and more. The retrospective sheets had the following brilliant learning points:

  • Split into 2 teams. The sensor team identified that sharing the workload into pairs worked for them.
  • System testing. The sensor team identified that they did this too late, they needed more intra-team communication and more testing with other teams.
  • WebServer.  The sensor team were pushed towards making the sensor webservers, this was different but a convenient way of performing diagnostics (well done me!)
  • Planning with other teams.  The core team identified they needed more communication intra-team.
  • Setup day. The core team felt a day of setup would have been beneficial.
  • Students to have course brief beforehand.
  • Use EMACS.  The core team were shown EMACS, how to customise and use it, to the point that they used it for their presentation!  Some of them intend to carry using it – well done Ben!
  • Learn Javascript.  The UI team identified that having a better knowledge of javascript and JQuery would be useful for a future career.
  • More Resources. The UI team were inspired to work at home and think additional resources would have helped them get further, but they also would have like to be in a position to be less reliant on the mentors.

What an amazing amount of learning, which really only just scratches the surface of what was discovered this week.  Did I mention the classroom?  The classroom was amazing, just a room, but a room with space, with art and stuff, it was bright and interesting and gave us a really creative space.  All classrooms should be like this – thank you Eden.

The project and two main aims, create a work-like experience for students, inspiring them to keep learning, and helping the Eden Project with (my interpretation of) their mission, to educate people about the importance of our planet, it’s ecosystems and the fragility of life. I think we managed both – well done everyone.

What’s next……

Well we have our monthly tech jams –

  • our monthly tech jams – here
  • Summer Huddle – here

And the links

I promised the links to the server.  Only the core team created a production server, you can see the output from the sensors here (the sensors aren’t live anymore): http://178.62.121.17/api/sensors

The site will be live for another week or so.

The source code that the teams created can be found on GitHub: https://github.com/TheEdenOfThings

And our EdenOfThings website is here:

http://codeclub.cornwall.ac.uk/edenofthings/

 

 

 

Eden of Things – Day 4

20160218_123248
Standard

Smokin’

Two of our mentors were away today leaving me with the Senors team instead of Byran – what could possibly go wrong – with Andy and Pete working with the UI team.

So most of my notes today are from the sensor team.  The keen started eagerly, getting a Kanban board produced for the days efforts was tricky as Byran had left them with a plan, they had had a night to think about it and were desperate to get on and get some sensors in operation. They weren’t the only team, everybody was fired up, wanting to get on and make more progress as soon as possible.

The UI team now had data in the right shape from the server team and the server team had a data model which allowed them to start working on a data schema for persisting the data.

The sensors team had a busy day ahead of making and coding.  So far they had two sensors, light and temperature via a PCF8591 module but hadn’t managed to send data to the server team yet due to api end points not being ready. It didn’t take long and the had confirmed that data was being sent.

Meanwhile, the other half of the sensor team continued work on making up a sensor station with a prototyping board. After lots of checking, double checking, bending a few header pins due to clearance and a spot of resoldering after it became clear that two male connectors won’t connect.

20160218_110328

Finally, the team had a sensor station with no need for any wires, powered by a small LIPO battery, the first version only had two sensors, but was ready to deploy!  A jubilant team was joined by Emo from the server team to ensure that when placed the sensor station was sending data back to the server. We had hoped to get to the top platform in the tropical biome, but with so many visitors, the staff were being kept busy and we could get up there, so had to settle for leaving the station on the highest point of the footpath.

20160218_124442

A spot of lunch and it was all hands on deck trying to add more sensors and making additional stations.  By mid-afternoon the team had attached a further 3 sensors and smoked our first components!

20160218_141010

Chris’s carefully hand work had one small flaw, once it had been spotted and the voltage regulator and power cables replaced we were close to having completed a second station.  The afternoon disappear with further board modifications to accept additional sensors and a spot of coding refactoring to make the code look beautiful – well acceptable when Byran looks tomorrow to see what I’ve been letting the team get away with.

We ended the date – late – by attempting to deploy to sensor stations into the tropical biome, as we walked across we realised that the code on one of the stations wasn’t working, in fact it wasn’t there, the ESP was acting as an Access Point, it’s default behavior rather that the teams code.  So by the end of the day, we were able to deploy one station over night.

The server team had a similarly busy day, providing diagnostic tools for the sensor team, working on an admin UI and getting very close to persisting the data.

The UI team have improved the look of their first screen and began work on a second screen to indicate where the sensors were deployed and consider how to show trend data.

It’s been another amazing day, can’t believe that we have to draw it to a close tomorrow.

Eden of Things – Day 3

20160217_142817
Standard

Hot Stuff

It’s really starting to take shape today, code has been deployed to production servers, site surveys completed and sensor boards soldered.

Networking started to present an issue at the end of yesterday and this morning.  Whilst we could connect to the free wifi using the ESP8266 in the biomes and similarly connect the server in the project room, but there was no guarantee that the devices would be able to see each other, plus we didn’t have a static IP for the servers, we could see a whole morning being lost. Rather than waste time trying it investigating it, fault finding, confirming the problem and trying to find and work with the network team, we choose to get a hosted server on the internet.  This solution would mean it didn’t matter which network a device was on, as long as it could get to the internet it could report data.  10 mins later and we had two servers costing a whole $10 from DigitalOcean.  The added bonus was the teams got to practice building a server again and got to experience using a cloud hosted server, something they hadn’t done before. We’re not quite ready to share our great work yet, we’ll share the IP addresses of the servers later this week.

Meanwhile, the UI team finished converting static html to be dynamically generated in Javascript based upon data being ajax called in from the Core server.  More protocols to learn about, the default action of modern browsers is not to permit cross domain/server ajax calls, so a quick lesson on Cross Origin Resource Sharing(CORS) and a tweak of an .htaccess file and the team were pulling data from the server.  We debated whether we should be calling the data from the UI server as a proxy which loads it from the data server, this would overcome the CORS issue.  We decided for speed (not having to write more code on the UI server) we would go for the CORS route, it also meant that longer term, our API server could be used by others directly in the browser.

20160217_154708

By the end of the day we had live data being updated on the webpage and setup a second TV outside of our base room with a Rapberry PI displaying the teams work to visitors – the teams work is on display to the paying public!

The team started with a quick retrospective, a short lesson on Kanban and they created a plan for the day. Once the server team had setup a new server in the cloud, they were able to get back to coding up the APIs, UI and Data logging.

20160217_121631

Just in the nick of time at the end of the data the data logging endpoint was created ready for the sensors team to send data across the network to them.

Having provide that the team could read the input from a light sensor, the sensors team had a fun day of site surveys, coding and building. The first challenge was making the ESP8266 mobile by adding a voltage regulator and connecting it up to a LIPO battery.  A trip around the biomes and a hot and sweaty team came back confirming we could get connection! With Risk Assessments logged, Hot Works Forms submitted and smoke sensors deactivated, the soldering started. The team designed a generic board that could accept different combinations of sensors.

20160217_142857

All was going well until it came to sloting in the ESP8266 and the sensors, only then did it become apparent the the ESP8266 format meant it covered the pins for the sensors – doh!  We’ll work around for the first sensor, but version 2 of the board will be slightly modified!

20160217_154634

It’s been another great day, watching the teams interact with eachother, setup ad-hoc meetings and work out solutions to interaction problems. Feedback from the teams has been great, with one member even staying up all night learning watching arduino videos!

We’re hoping for sensors deployed into the Biome by tomorrow lunch……. (although we’re losing our key embedded software specialist and the team have got me and Pete to help get them live, eek!)

 

Eden of Things – Day 2

20160216_115659
Standard

Making good progress

First task of the day was swapping out the Raspberry Pis from the sensors team and replacing them with x86 based machines. The compiler for the ESP8266 is x86 based and there is no Arm-based version that we could find, we could have rebuilt it, but it was more effort than pulling apart my office and bringing in my home computer and a spare that was being built as a Pi-Net server.

So we didn’t quite fit everything we wanted to into day 1.  It was really important that the teams had a chance to play with the technology and try a bit of coding, that meant we dropped on session on understanding source control using Git.  What better way to start a Tuesday than with a deep dive into source control, commits, merges, branches and detached heads. That is once we got started, we were slightly late today, as day 2 on the job and some of the teams rocked up late.

Once everybody was completely lost with git terminology, me included, the teams got into their respective tasks under the guidance of their mentors, Byran on Sensors, Ben on Core Systems and Paul on User Interface.  The teams had a steep learning curve, with most members having never experienced C, arduino, ESP8266, php, javascript, jquery, REST, etc, etc.  The mentors have been amazing, patiently leading and training the teams in everything from configuring a linux webserver, to building breadboard circuits and initialising SMART TVs. One problem I’ve experienced with teams is our culture doesn’t encourage enough celebration or recognition of achievement.  The teams here were no different.

One problem I’ve experienced with teams is our culture doesn’t encourage enough celebration or recognition of achievement.  The teams here were no different. As the teams started on their quests to master their allocate sub-system, little victories were silently occurring all the time, but you couldn’t tell.  The UI team had a little shout when then got the Smart TV to connect to their web server and display a page, but the sensor team managed to get an LED to flash on and off via the ESP8266 and there was nothing, not a whisper. This little victories are so important to celebrate, they enable the whole team to realise that we are making progress, they add an element of fun to proceedings and they make us feel good and bond as teams.  So with a little encouragement and praise we are making progress, there are more cheers happening all the time, we are starting to generate a buzz.

After a intense day of work we had our first show and tell at 3pm.  The UI team kicked off the presentations with a great looking screen with imagery they had created earlier in the day from around the biomes and some dummy data.  Finally, a round of applause and laughter, we’re making real progress, technically and socially.

20160216_130354_Richtone(HDR)

The Core systems team followed up with a visual less impressive but fantastic technical achievement demonstrating a working php based REST server and an agreement API architecture – no small feat.  More applause.

20160216_130410_Richtone(HDR)

Finally, the sensor team keep up the run of success, having moved on from the impressive display of a flashing LED, they showed how they had deployed a websever onto the ESP8266, connected to our sometimes flakey wifi and were able to get a reading from a light sensor on demand with a simple REST call. More applause and cheering!

20160216_130323_Richtone(HDR)

All teams have proven the fundamentals of the system architecture, now to pull it together.

A review meeting decide the next steps, now we know that all the basic concept could work, was to look into the realities of the physical environment we want to deploy into.  Is there a wifi signal, how strong is it, where should we place the sensors.  We don’t want to continue developing new features until we can prove that we have a working architecture from end-to-end – a walking skeleton.

And that was pretty much it for day 2, everybody disappeared into the biomes, smart phones out measuring wifi signals and seeing how high up into the biomes that could get. The good news is they found wifi, whether it’s strong enough for the ESP8266 we’re not sure, we’ll find out tomorrow……..

Oh, and git, we’ve had some commits! https://github.com/TheEdenOfThings

Great work everybody!!!!

Eden of Things – Day 1

20160215_132849
Standard

After 6 months of planning (honest there has been some planning), the first day of the Eden Of Things has arrived!

The Eden Project’s horticultural technologist, Michael Cutler, provided an idea based on measuring various aspects of the Eden Biomes with the aim of providing visitors with a different perspective on the Biomes lifecycle.

The Big Plan

Software Cornwall and Cornwall College are colloborating to provide a pilot solution for the Eden Project and work experience for students studying a computing degree.  We’ve setup a base room on site and will be here for 1 week creating a solution.

The solution comprises of three components:

  • a number of sensor stations that are free standing (no wires) based around an ESP8266.
  • a data collection and cataloguing server
  • a data presentation system able to consume the data from the data collection server and display it in interesting ways on a smart TV.

Day 1

The first day is all about induction, admin, team building and understanding the customers goals and context for the tasks. Not to mention getting all the base tech in place to enable the week to happen.

It takes a surprising amount of equipment to run the week, about an S-MAX full.  Luckily, although the Core building at the Eden Project is at the bottom of the site, we could get right to the door.

With a few last minute withdraws we ended up with 13 ready to start at 9.30am.  The day starts with the obligatory introductions and health and safety, then we get into the fun stuff.  Scrum tennis gets everybody talking and interacting.  It’s a great game, everytime I run it different things happen. What happened this time:

  • Individuals became a team.  They talked, interacted, focused on a problem.
  • We learnt that failure is OK.  The team tried different approaches don’t always work, but don’t discount ideas, give it a go, but be sure to limit it, control the risk by monitoring and discussing after a defined trial period – a sprint!
  • When we try something new, it should be expected that we will fail first time.  Round zero, the number of points produced was zero. Acting as a team, not allocating blame, but inspecting and adapting the process is key.
  • Look for the bottlenecks, externally motivation doesn’t make the machine work faster.  No amount of encouragement or pressure can make the team work faster.  We need to look at what is slowing us up and work out how to remove or reduce it’s effect.  In scrum tennis we moved closer together, formed a circle, etc.
  • Don’t be bullied into accepting unrealistic targets.  Calling them aspirational or anything else doesn’t make them achievable.  The team need to agree and set it’s targets based upon experience and knowledge of the task.  When pushed, don’t just accept a bigger target to please the customer, be realistic and set expectations at the beginning.

What a great list, learnt through experience, no amount of lecturing could have got these messages home with the effectiveness of this simple game.

Next up, after team allocations and setting up workstations, was a session on how to code.  The key messages were focus on testing, strive to do TTD and work as a team, use mob and pair programming.  The session was based upon the excellent http://cyber-dojo.org, testing Bluefruit’s Head of Development, Byran, by using php rather than his preference of c++.

With all the induction training done, the team earned the much coveted Software Cornwall polo shirt!  A spot of lunch and then a customer brief from Edens Horticultural Technologist, Michael Cutler.

20160215_132849

The facts about what we – humans – are doing to our planet are stark, we’re all in this together and need to work together to look after our fragile planet and ensure it remains hospitable for our species.  This project’s real aim is to help with education, demonstrate the fragility and effect of variations upon the eco-system in the Biomes; ultimately to contribute to helping change visitors behaviour and make them more aware of they’re actions and choices and the effect this has upon our planet.  It’s a grand aim, but the team is looking forward to the challenge of using technology for good and seeing what insights can be gleaned and how we can help with the Eden Projects mission.

And they’re off!  The thinking and coding begins. Most of the day has already based, so there is only an hour or two left for getting the ground work done, links to GitHub established and user stories reviewed and plans produced.

Just enough time and the end of the day for our first retrospective.  The 4 L’s approach was taken to complete a 10 min retrospective.  Key findings:

  • We liked getting to code
  • We liked creating the team
  • We learnt about linux
  • We learnt about Raspberry Pis, Smart TVs and breadboard
  • We lacked tea and coffee
  • We lacked knowledge of PHP, linux
  • We longed for Raspberry Pis to be able to deploy code to the ESP-01
  • We longed for a tour of Eden

Some bits to fix for day two for the mentor and support team! Tea and coffee being number 1.

It was a great first day, many challenges cropped up, nearly all technical, but with a great mentoring team we managed to get everybody going.