# Pupils' Data

Anne Gregor, Michael Wilmes, Christian Kiock

► To cite this version:

Anne Gregor, Michael Wilmes, Christian Kiock. Pupils' Data. 10th Next Generation of Information Technology in Educational Management (ITEM), Aug 2012, Bremen, Germany. pp.171-176, 10.1007/978-3-642-38411-0_16. hal-01468473

HAL Id: hal-01468473

https://inria.hal.science/hal-01468473

Submitted on 15 Feb 2017

HAL is a multi-disciplinary open access
archive for the deposit and dissemination of sci-
entific research documents, whether they are pub-
lished or not. The documents may come from
teaching and research institutions in France or
abroad, or from public or private research centers.

L'archive ouverte pluridisciplinaire HAL, est
destinée au dépôt et à la diffusion de documents
scientifiques de niveau recherche, publiés ou non,
émanant des établissements d'enseignement et de
recherche français ou étrangers, des laboratoires
publics ou privés.

<figure><img src="image_1.png" /></figure>

Distributed under a Creative Commons Attribution 4.0 International License
# Pupils' Data:

## Implementing a Management Information System to Optimise
Planning in Berlin Schools

Anne Gregor¹, Michael Wilmes² and Christian Kiock¹

¹2k Consultants, Berlin, Germany
info@2k-consultants.com

²Senatsverwaltung für Bildung, Jugend und Wissenschaft, Berlin, Germany
michael.wilmes@senBJW.berlin.de

**Abstract.** Parents are able to choose the school their children will attend. Thus, a core task of the Berlin Senate Administration Office for Education, Youth and Science (SenBildJugWiss) is having optimally-resourced schools with the adequate number of teachers at the start of a school year. The project's mission was to obtain accurate figures by efficiently processing data of approximately 320,000 pupils from 700 schools. This processing included reporting on the various target groups of the SenBildJugWiss. The solution was to implement the OLAP-Database Palo as a management information system (MIS) tool. By doing so, it was important that an initial version was completed fairly swiftly to demonstrate main functions for future users, to highlight necessary adjustments for integration into the existing system, to define key performance indicators (KPIs) and their data sources, as well as creating the desired reports. One of the major challenges was creating a homogeneous data structure and harmonising the search and query language (SQL) tables. The outcome is a MIS that best fits the requirements, including meeting strict data protection regulations. The created reports provide, inter alia, the data delivery status required - the amount of pupils per class and schools eligible to gain certain levels of resources.

**Keywords.** Information management system; educational management; planning optimization; pupil data.

# 1 The problem

## 1.1 The initial situation

As a result of the School Structural Reform, parents are able to choose the school their children will attend. Thus, a core task of SenBildJugWiss is having optimally resourced schools with an adequate number of teachers at the start of a school year.

Resources are planned by analysing detailed pupil data [1]. Unfortunately, these analyses only deliver deficient and unreliable information due to a heterogenic IT-system and a lack of IT-integration with some departments of the SenBildJugWiss. Therefore, despite being available in SQL-data bases (DBs), it was not possible to
aggregate the relevant data efficiently. Depending on the know-how of individuals,
analyses were carried out using MS Excel. Consequently, interdisciplinary reports
were either sparsely accessible or took a long time to be created [2].

## 1.2 The legal framework

**The Berlin Data Protection Act, Berlin Education Act, and Berlin School Act.**
There are numerous legal data protection regulations that fundamentally affect both the design and functionality for software components of an IT platform as well as corresponding SenBildJugWiss organisation processes. Details for automating search of personal data as well as adhering to technical standards are defined in Paragraphs 64 to 66 of the Berlin Education Act [3] and in the Berlin School Act [4], containing, inter alia, the following details:

* Legitimacy for pupil data processing in automated files, possible data to be collected and the point of time of their erasure.

* Functions responsible for data processing.

* Spatially separating personal computers (PCs) used for analysis from those used for lessons.

* Depersonalising specific pupils' data categories already entered by the schools using a software tool.

* Administering an automated pupil DB in SenBildJugWiss using unique indices instead of pupils' names (pseudonymisation).

* Authorisation for searching for pseudonymised pupil data.

When processed automatically, the data is separated according to its purpose and is done in such a way that only respective authorised personnel can access it [5]. Furthermore, there are detailed requirements for maintaining data processing systems; thus, the Data Protection Officer is an important stakeholder [5].

**The Berlin Employee Representation Act.** This act contains details of tasks relating to specific employee representatives. The Women's Officer and the Representative of Disabled Persons are among them [6]. The following clauses (amongst others) needed to be applied in the project:

* Paragraph 79: The duty to obtain consent from employee representatives regarding measures of the SenBildJugWiss.

* Paragraph 59: The participation of the Main Personnel Council, especially in IT projects.

* Paragraph 74: Conclusion of a works agreement. This is a helpful tool that formally documents permission to execute the project and records details that have been agreed regarding the procedure [6].
In view of the above, the chairman of the Main Personnel Council is one of the
most crucial stakeholders.

**The SGB IX- Rehabilitation and Participation of Disabled Persons and Berlin Equality Act.** The Representative of Disabled Persons is empowered by Paragraph 95 of the SGB IX [7]. The Women’s Officer acts according to Paragraphs 16 and 17 of the Berlin Equality Act [8]. Paragraph 3 of the Berlin Equality Act requires active contribution to an equal proportion of both genders in professional functions which, of course, applies to this project too.

**DIN EN ISO 9241 and Workstation Regulation.** DIN EN ISO 9241 requires user-friendly software design, and workstation regulation ensures access and use of ergonomic hardware equipment.

# 2 The project “Pupils’ Data”

## 2.1 Mission and objectives

The mission of the project was to:

* Digitalise data of approximately 320,000 pupils from 700 schools.

* Efficiently automate processing and analysis of this data for delivery of figures to SenBildJugWiss.

* Create a reporting system in the context of business intelligence, i.e. implementing a MIS in the existing IT system.

The objectives were to:

* Improve planning.

* Optimise use of resources: teachers and budget.

The project was undertaken by a range of team members. The team members and their functions are described in Table 1.
<table><caption>Table 1. Project team members and their functions.</caption><thead><tr><td>Role in the Project Team</td><td>Function</td></tr></thead><tbody><tr><td>Project Leader</td><td><ul><li>Head of Department “eGovernment@School” SenBildJugWiss</li><li>Overall responsibility for project management</li></ul></td></tr><tr><td>Project Coordinator</td><td><ul><li>Department “eGovernment@School” SenBildJugWiss</li><li>Organisation of communication</li></ul></td></tr><tr><td>Project Officer</td><td><ul><li>Department “eGovernment@School” SenBildJugWiss</li><li>Planning and managing the project, focusing on data protection</li></ul></td></tr><tr><td>Project Consultant</td><td><ul><li>2k Consultants (external)</li><li>Project expertise in the public sector, including knowledge on developing a data warehouse, implementing business intelligence using Palo in the public sector, monitoring and identifying KPIs</li><li>Interfacing with the software provider Palo-DB</li><li>Supporting project management</li></ul></td></tr><tr><td>Data Protection Expert</td><td><ul><li>Data Protection Officer SenBildJugWiss</li><li>Ensuring data protection adherence</li></ul></td></tr><tr><td>IT Experts<br>DB System Management</td><td><ul><li>Head of IT and another IT employee of the Department “eGovernment@School” SenBildJugWiss</li><li>Integration of the Palo-DB into IT systems including ensuring correct interfaces between systems using lightweight directory access protocol (LDAP) within data protection (user access management, and authentication)</li></ul></td></tr><tr><td>IT Expert<br>Data Warehouse</td><td><ul><li>Department “eGovernment@School” SenBildJugWiss</li><li>Process implementation, and report design</li><li>Future internal capacity to use Palo</li></ul></td></tr><tr><td>Software design</td><td><ul><li>Jedox AG (external)</li><li>Designing Palo OLAP-DB</li></ul></td></tr></tbody></table>

# 3 Realisation of the project and results

## 3.1 Design

The project began by creating a test environment for executing trials isolated from the operating system. This test environment has been permanent and will also be used for making any further changes to the Palo-DB. Furthermore, a pilot version of the Palo-DB for explorative prototyping [9] was created. Due to information gathered, a prototype was developed and was also examined in the test environment. After having achieved satisfying results, the trial underwent live operation in some departments of the SenBildJugWiss. The roll-out for all users was planned to start after successful completion of the trial.
## 3.2 Information flow

**Preparation of data imports into Palo-DB from schools.** As authorised personnel, head teachers enter the data into an MS Excel file. A special software is then applied that verifies the completeness of the data and changes the MS Excel files into two common separated values (CSV) files in order to anonymise the data by separating them from any identifying attributes. This software was especially developed for this purpose by the Fraunhofer Institute. Both files are sent via an encrypted e-mail to the person responsible at the SenBildJugWiss.

**Preparation of data imports into Palo-DB from SenBildJugWiss.** Before the files are used, the person responsible authenticates the sender e-mail via sender certificate. The two CSV files per school are imported into two separate SQL-DBs. These files are copied to another server belonging to the SenBildJugWiss. The names of pupils are replaced with unique indices.

**Data Import into Palo-DB.** The Palo-DB is opened. The now anonymous data in SQL format, further MS Excel and SQL files are manually linked to the Palo-DB. All necessary data from these files are loaded into the Palo-DB, which is stored on a SenBildJugWiss server. The date when data would be used was determined during the project. Subsequently, defined reports are able to be created by permitted users via the cockpit (user interface) of the DB.

User authorisation for the Palo-DB is managed using the user access system of the SenBildJugWiss-server. Access rights are administrated using their central directory service. To ensure unity, roles were set up identically in the Palo-DB and an interface was programmed using the LDAP standard. Furthermore, the administration of new passwords and user permissions is not applicable. The user can only access data according to the respective rights of the schools or district.

## 3.3 Available reports

Users of the currently-created reports are the IT Centre of Competence, the 13 educational authorities, the twelve supervisory school authorities (as of March 2012), and the head teachers from all 700 schools. Table 2 shows the available reports, the KPIs and the dimensions that can be interrogated using filters.
<table><caption>**Table 2.** Available reports, KPIs and query functions.</caption><thead><tr><th>No.</th><th>Report</th><th>KPI</th><th>Query dimension</th></tr></thead><tbody><tr><td>1</td><td>Delivery overview</td><td>Total pupils<br>Pupils who are "Exempt from Purchase of Teaching Materials" (EPTM) or Non-German origin (NGO)<br>Number of classes<br>Average class sizes<br>Values from the previous year</td><td>Federal state, district, school</td></tr><tr><td>2</td><td>Delivery status</td><td>Delivery status per school<br>Comments may be entered</td><td></td></tr><tr><td>3</td><td>Delivery status detail</td><td>Delivery status per delivery per school</td><td>Deliveries 1-4, district</td></tr><tr><td>4</td><td>SenBildJug-Wiss tuition supply</td><td>Pupils who are EPTM and NGO</td><td>District, school</td></tr><tr><td>5</td><td>Pupil data</td><td>Status of forecasted number of pupils:<br>Total, additions, those changing schools, leavers, unchanged status, unknown status for change from secondary level (SEK) I to SEK II<br>Pupils per school type<br>Pupils per focal point of support<br>Pupils per delivery date<br>Contact data of schools<br>Values from the previous year</td><td>Status<br>Type of school<br>Type of focal point of support<br>Delivery date 1-4<br>District, school</td></tr><tr><td>6</td><td>Pupil data with classes</td><td>As report 5, but without "Pupils per focal point of support" and "Status"<br>Number of classes<br>Average class size</td><td>District, school, class level</td></tr></tbody></table>

## 4 Discussion and future prospects

All data protection requirements were met by the project. The Palo-OLAP-DB processes approximately 3.5 million pieces of data at a high calculation speed. The system can also be run on a server with a memory of 4 gigabytes. The DB works solidly and plausibility tests have yielded accurate calculation outcomes. The control cockpit
at the front end for end users is found to be user-friendly. Required information can
be retrieved via a mouse click and without intensive training being necessary.

By requiring local authorities to supervise schools, the essential KPIs for optimal
resource decisions are now currently available. As a result, KPIs are created from
cross-functional data sources due to linked files from various departments. Thus, a
further outcome of the project is that it contributes to an improved interdisciplinary
collaboration. Based on role-defined access to pupil data, the Supervisory School
Authorities (responsible for school buildings, districts and for teacher resourcing) are
able to coordinate more quickly with each other.

Live operation for further users is planned via a web solution. Further modules can
be added to the existing solution using the same IT infrastructure in order to gain
further efficiencies across the various educational administrative authorities in the
Federal State of Berlin.

**Acknowledgements.** This is a sub-project of the wider "eGovernment@School" project of the Berlin Senate Administration Office for Education, Youth and Science, run in collaboration with 2K Consultants.

References

1. Thomas, J., Wilmes, M.: Bürokratieabbau und der Einsatz moderner Informationstechnik als Aufgabe der Bildungsverwaltung? Bildung und Erziehung – Neue Managementkonzepte und Bürokratieabbau im Bildungswesen, 3, 313-324, (2011)

2. Jedox AG: Anwenderbericht Berliner Senat: Die Hausaufgaben für Europa sind gemacht. Jedox AG, Freiburg (n.d.)

3. Berlin Education Act: Accessible at: http://gesetze.berlin.de/default.aspx?typ=reference&y=100&g=BlnSchulG&p=64a, (2012)

4. Berlin School Act: Accessible at: http://gesetze.berlin.de/Default.aspx?vpath=bibdata\ges\blnschuldatenvo\cont\blnschuldate nvo.htm&pos=1&hlwords=Verordnungen%C3%90personenbezogene%C3%90Daten#xhl hit

5. Berlin Data Protection Act: Accessible at: http://www.datenschutz-berlin.de/attachments/346/BlnDSG2008.pdf, (2012)

6. Berlin Employee Representation Act: Accessible at: http://www.berlin.de/imperia/md/content/hpr/rechtsgrundlagen/persvg_berlin_mit wo_11. 07.11.pdf?start&ts=1326116332&file=persvg_berlin_mit wo_11.07.11.pdf, (2012)

7. SGB IX: Accessible at: http://www.sozialgesetzbuch-sgb.de/sgbix/95.html, (2012)

8. Berlin Equality Act (Landesgleichstellungsgesetz): Accessible at: http://www.berlin.de/imperia/md/content/sen-frauen/lgg.pdf?start&ts=1188374644&file=lgg.pdf, (2012)

9. Holey, T., Welter, G., Wiedemann, A.: Wirtschaftsinformatik (2nd edition). Olfert, Ludwigshafen, Germany, (2007)