Design and Implementation of an Interactive Power BI Dashboard for Monitoring Academic Performance, Attendance and Student Risk in a College

All colleges keep records of their students. This comprises their score after each exam, attendance book daily and monitoring of the number of subjects they have failed in under the examination section. However, the issue is the data is rarely missing and is stored in separate files belonging to separate people. No one can keep a track of all the data throughout the semester.

Consequently, it takes considerable effort to answer even basic and simple questions like ‘what students are not doing well in the score and attendance aspects'. This means opening a few sheets and manually matching the student ID's. This is not something that will be done monthly by most teachers, so poor performance is usually identified only when the results come out.

Business intelligence tools were built to solve this kind of problem. One of the most popular of these is Microsoft Power BI. It is free to use, can import Excel and CSV files and does not require much coding to create a report that is interactive. In this project Power BI will be used to create a dashboard for a college. This is to present the academic achievement, attendance and backlogs at a glance.

Contributions:

• Four tables with 4 calculated categories for CGPA, attendance and backlogs.
• A selection of DAX measures which will allow key academic metrics to be displayed on one screen.
• An interactive 4 page dashboard, from an overview of the college, down to a specific department/year.
• Using rules to decide whether a student is to be considered as Normal, Monitor or as At Risk based on their CGPA, attendance and backlogs.

This paper will be organized as follows: Background information will be given in Section 2. The problem and objectives will be explained in Section 3. In section 4, the data and methodology will be discussed. The dashboard will be discussed in Section 5. The results will be presented and discussed in Section 6. Recommendations will be included in Section 7, limitations will be explained in Section 8 and a conclusion will be given in Section 9.

2. Background and Related Work

The concept of using data to understand and enhance education has been around for some time. Romero and Ventura (2010) have conducted a review of the literature of Educational Data Mining to look at how educational data, like performance and engagement, can be used to predict learners' performance and alert those who may need attention. Learning Analytics is the understanding and enhancement of learning based upon learner data (Siemens and Long, 2011). However, the learner data needs to be considered as a living resource, not a by-product, in institutions.

Previous research has been conducted on dashboards. A systematic review was conducted on dashboards for learning by Schwendimann et al (2017) and a review of dashboards for students and recommender systems was undertaken by Bodily and Verbert (2017). This shows dashboards are a well established research field but there are still questions regarding dashboards' impact on teachers/students' work.

In addition, there has been some research into the presentation of information. A dashboard should be "skimmable" and thus, include only the most important information (Few, 2006). The chart should be designed for one clear message to the reader (Knaflic, 2015). This has been tried during the development of the dashboard pages.

From a technical point of view, Ferrari and Russo (2016) offer a hands-on guide to loading data into Power BI, modelling it and finally applying DAX.

3. Problem Statement and Objectives

Problem Statement:

The data is highly fragmented in academic level within a college and it is analysed after the end of the semester. This makes it difficult to identify any underperforming students, poor attendance and increasing backlogs.

Objectives:

1. Create a unified data model for the students, courses, grades and semesters.
2. Determine the academic performance comprehensively by calculating CGPA, SGPA, average marks and pass rate.
3. Understand attendance and select those students who are not reaching the expected standard.
4. Monitor department and year backlogs and course failures.
5. Identify students at risk with multiple indicators.
6. Make suggestions and recommendations to the faculty.

4. Data and Methodology

4.1 Dataset and Data Model

The data for the dashboard was taken from a data set of 1500 students from 10 departments (Biotechnology, Business Administration, Civil Engineering, Commerce, Computer Science, Electrical Engineering, Mathematics, Mechanical Engineering, Physics and Psychology). There are students across all years (1-4) of the dataset with the following data:

• Student Status (Active, Graduated, On Probation or Dropped).
• Scholarship Type (None, Merit-based, Need-based or Sports).

This data has been organised into four tables. As illustrated in Table 1 below.

Table 1: Tables in data model

Tables have been joined together using the student identifier. This means that if you filter on the slicer by department and year level, then all the tables will be filtered. The data was imported and cleaned using Power Query. This included basic cleaning such as fixing the data types, removing blanks and duplicates and ensuring consistent column names.

4.2 Derived categories

We did not report these raw scores (e.g. CGPA 7.4; attendance 72%) but rather grouped the scores into ranges:

• CGPA Categories = CGPA less than 6.0, CGPA 6.0 to 6.9, CGPA 7.0 to 7.9, CGPA 8.0 to 8.9, CGPA 9.0 to 10.0
• Attendance Categories = Attendance less than 60%, Attendance 60% to 74%, Attendance 75% to 84%, Attendance 85% plus
• Backlog Categories = No backlog, 1 backlog, 2 backlogs, 3+ backlogs
• Risk Categories = Based on above three indicators, categorised as Normal, Monitor or At Risk

4.3 DAX metrics

The values shown on the cards and charts are based on DAX metrics which are listed below in Table 2.

Table 2. Main measures used in the dashboard

These are calculated measures and not fixed columns, which means that they will be recalculated every time a slicer changes. This is how the report is interactive.

4.4 Risk Identification

Not all students with a low CGPA will be at risk. Considering this, we analyzed CGPA, attendance and backlogs for risk. Those with a low value with multiple factors will be in a higher risk group. Overall there are three risk groups: Normal, Monitor and At Risk.

The scatter chart in attendance and risk shows attendance and cumulative gpa for all students. This makes it easy to identify outlier(s) if there are any.

5. Dashboard Design

This report is dark navy and has a navigation bar down the left. It has the same layout on each page and makes it easy to locate each page's contents. There are four buttons: Overview, Academics, Attendance and Insights. The slicers are either at the top or side of each page and allow filtering of data. The slicers are: Department, Year level, Gender, Semester, Student status.

5.1 Page 1: Overview

A quick overview of the whole college is given on the first page. There are four cards that display the following data: Total Students, Average CGPA, Average Attendance and Pass Rate. The charts below show the distribution of CGPA, students broken down by scholarship type, distribution of student status and the average CGPA per department (figure 1). This allows the head of department to view this page and quickly have an overall understanding of how the college is doing.

Figure 1. Overview page of the dashboard

5.2 Page 2: Academic Performance

This page provides more details about results. The cards will display Average SGPA, Average Marks and Failed Courses. The charts show semester-wise SGPA as a line, course-wise average marks, department-wise average SGPA, the grade distribution and the share of pass and fail results (Figure 2). The line chart shows whether performance is moving up or down from one semester to the next, and the course-wise chart shows which subjects pull the average down.

Figure 2. Academic Performance page

5.3 Page 3: Attendance and Risk

We believe that this is the most useful page for everyday use. Average Attendance, Low Attendance Students, Total Backlogs and At Risk Students are indicated on the cards. The charts depict attendance by department, attendance distribution, backlog distribution, the distribution of the students based on their risk status and a scatterplot of attendance vs. CGPA (Figure 3). A teacher can select a department and year level to see instantly how many students are short of attendance or carrying backlogs.

Figure 3. Attendance and Risk page

5.4 Page 4: Insights and Recommendations

The last page has no charts. It outlines the main findings about student achievement, attendance, active backlogs and student risk in simple language and then provides three recommendations with a brief summary of the overall objective (Figure 4). We included it because a dashboard with numbers only is open for interpretation by the reader, and many readers don't have the time for that.

Figure 4. Insights and Recommendations page

5.5 Issues identified during the report creation

Whilst the report was fairly straightforward to create there were some minor issues that would have led to misinterpretation. Firstly the percentage format was incorrectly applied to the attendance card. This resulted in the percentage being displayed as 7791% instead of 77.91%. Secondly the semester names were in alphabetical order (Fall before Spring), meaning the line chart appeared to be decreasing when it wasn’t. To resolve this the semester x-axis will need to be ordered correctly using the date. Lastly the attendance ranges were in order of size. Whilst these are not major technical errors they would have caused confusion and therefore all visuals were carefully checked against the underlying data.

6. Results and Discussion

6.1 Key KPIs

Table 3 shows the key KPIs from the dashboard showing the values for all 1500 students without any slicers applied.

Table 3. Main indicators from the dashboard