PowerBI Project List
Using LEO Project (Learning Management System Usage Framework)
The Problem (To Solve)
Many years ago, I remember visiting a colleague in her office. She was of European origin with a charming demeanour and strong (and often very correct) opinions on online teaching. Needless to say, we got on extremely well.
I remember taking a peek at her screen, on one was our Learning Management System, the other a word document with a check list. It looked like some form of manual audit, trawling through each course and ticking whether certain resources existed.
My initial reaction was that of surprise: why isn’t this automated? Are we still in the 90s? Why go through hundreds of courses manually to check if they have resources? It’s time consuming and tedious. I remember telling my colleague: I’m going to find a way to automate this whole process.
The Solution
A few months later we gained access to a duplicate of the Learning Management System internal database (the data dump was hundreds of gigabytes in size). In addition it was not designed for reporting, it is one that’s designed to be the core or engine of a live system.
The data inside it was nearly incomprehensible. So I went to work in analysing it and designing queries: a great excuse to dust off my SQL skill set. I designed dozens of queries to extract useful data from the system. By using inner joins and a lot of trial and error I was able to design queries that extracted useful information from this database.
Of particular relevance was a query that scanned every single course that had existed in the system (20,000 plus at the time) and give a numerical breakdown of every resource type. With a single query I was able to extract the information that my colleague needed: something that was manual and took her weeks to do.
Over time I refined the query to add the course name and URL to the tabular data. The query was then saved as a database view.
I provided the data to colleagues in the faculty and they were able to use the data to produce basic PowerBI reports, but more importantly identify online courses that required a little ‘extra help’ and offer it to academics. It was immensely successful and an example of a great data driven decision.
PowerBI and Database Connection
At the same time, I was using PowerBI. What if we connected PowerBI to the database with the ‘view’ and drew the data into the reporting software? I can then group the teaching tools into categories and create visualisations to understand the data better.
After several iterations I created a report that I was able to present it at a University roadshow. My pet project had evolved into a full-blown enterprise project. Initially they called it ‘LEO audit’ but I took issue with the name, I suggested: ‘Using LEO’, which is far more ‘academic friendly’.
During one of the roadshows, I presented the report on the PowerBI app on an iPad, a senior manager raised his hand and asked: ‘Can I have this now, please?’. If this wasn’t confirmation for the demand for data I don’t know what is.
Examples of the report:
This is how it looked on an iPad: as intended to be seen:
Slicing and Dicing by Campus:
Learning Analytics: Self Service Course Reports
During my working in Higher Education I have explored the realm of Learning Analytics: Using data to drive important teaching/business decisions. I have been given the opportunity to present my work at conferences and I derived great satisfaction from the positive feedback of attendees.
Created from Moodle course log data, complete with timestamps and formatting. Used DAX formula to calculate durations of interactions:
Duration (Hours) = DATEDIFF(Sheet1[Time],IF([Index]=0,[Time],LOOKUPVALUE(Sheet1[Time],Sheet1[Index],Sheet1[Index]-1)),HOUR)/24
Duration (minutes) = DATEDIFF(Sheet1[Time],IF([Index]=0,[Time],LOOKUPVALUE(Sheet1[Time],Sheet1[Index],Sheet1[Index]-1)),MINUTE)/24
Add an indexed column that begins with 0
Interactive Reports
The most recent conference in question was the ACU Learning and Teaching Conference that was held in the North Sydney Campus of ACU. As part of a Learning Analytics showcase (part of the futures booth) I was tasked with creating numerous interactive PowerBI reports using teaching data derived from our Learning Management System.
Below is a sample exploration of the report.
Outcomes
The reports were loaded onto iPads and accessed via the official PowerBI app. What truly excited me about the app was that anyone could interact with the reports and ‘slice and dice’ the data available. I linked the reports to the ‘DIKW pyramid’, and the fact that reports could prompt users into investigating the data and discover patterns. This concept resonated with many of the attendees who were academics and taught online. It addressed an interesting problem that they encounter all the time: ‘Are my students interacting with the resources I have created?’.
University EFTSL Dashboard
Using Data supplied to the university from the government, I created a report detailing the number of students in every Australian University with a breakdown by full or part time, local or international.
Rank = RANKX( ALLSELECTED(‘EFTSL – Domestic and Overseas'[State]), CALCULATE( SUM(‘EFTSL – Domestic and Overseas'[TOTAL EFTSL] ) ) )
Here is how it looked like on the mobile app:
Proximal Attributes Project
The project is predicated upon the notion that, within the vast array and range of available data, there will be certain data points that are more indicative of eventual student success than others. Moreover, there is the understanding that some of these data points may well be linked. If this is the case then it means that: firstly, the University will have better identified which specific behaviours it is worth reporting upon; and secondly, the University can develop an appropriate and targeted response and thus be able to respond when an early stage , indicator has been triggered.
Example:
Each marker a student misses massively increases the chances of a student failing a course.
Marker 2 : Student does not log onto LEO (week 2)
Marker 3 : student does not attend class/tutorial (week 3/4)
Marker 4 : student does not submit first assignment (weeks 4 to 8)
Marker 5: student fails first assignment (week variable)
Database Table Locations
Marker 2: mdl_user_lastaccess and mdl_user (first access, lastaccess, lastlogin). Use a less than or equal to epoch time. What would week 2 be?
Marker 3: mdl_log = activities within timeframe (LOGIN WITHIN WEEK 3/4)
Marker 4: mdl_assign_submission and or mdl_assignsubmission_onlinetext
Or mdl_assignment
Marker 5: mdl_assign
Part of the project was to create a proof of concept to demonstrate that the database could be interrogated and produce the desired reports:
Consulting Project with Dr Lara Grollo
Improving student performance: An analysis of whether additional online resources assist tertiary STEM students to take corrective action against poor grades.
Using a combination of PowerBI and R, Student data was sanitised, modelled and analysed using a unique analysis methodology.
• Stage 1 was to produce a report in PowerBI for descriptive analytics.
• Stage 2 used the R data analysis language to conduct a multi-faceted analysis of the data.
• Stage 3 used Data Science techniques such as feature engineering and the application of the random forest algorithm for predictive analytics.
Student interactions with different online resources were measured and analysed and correlations were discovered between high interactivity with course resources and overall attainment.
Examples of visualisations from R:
Feedback:
“Baz helped me with data analysis for a project; “Improving student performance: An analysis of whether additional online resources assist tertiary STEM students to take corrective action against poor grades”, his skills with Power BI were fantastic. Baz was able to help us visualise our students online learning environment logs for a whole semester, this allowed us to identify key resources that students used in order to take corrective action and improve their grades.”
Data Steward Duties at ACU
• Define the data and identify assets within their own data domains. This ensures there isn’t conflict with other data elements.
• Create processes and procedures along with access controls to monitor adherence. This includes establishing internal policies and standards—and enforcing those policies.
• Maintain quality of the data using customer feedback, concerns, questions; internally reporting metrics; evaluating and identifying issues; and coordinating and implementing corrections regularly.
• Optimised workflows and communications.
• Monitored data usage to assist teams, shared best practice trends in data use, and provide insight into how and where teams can use data to help in day-to-day decision-making.
• Ensure compliance and security of the data. Data stewards are responsible for protecting the data—while providing information on potential risks and offering regulatory guidance.
• Creation of a data dictionary for use in the Learning Management System Usage Framework