This story starts 6 years with me moving to Canada. I attended our first Parent Council meeting 5 days after arriving and settling into our new home. During the Principals Report, they spoke about a Literacy Research Program they were participating in with the J. P. Das Centre based within University of Alberta. Through his story, I identified pain points they were experiencing with data collection and validation. At the end, I shot my hand up and said, that's kind of what I do on a day to day basis at work, maybe I could help.
It sounded simple when I first listened to his story. How hard can it be to create an Excel workbook that they enter data into! 3 Years later, 12 Schools and over 3000 hours volunteer time and one Excel template later, I was introduced to Mike McMann, Superintendent at Fort Vermilion Public Schools.
Over the last three years, that Excel workbook has developed, data extraction using #PowerAutomate has improved and reporting through #MSPowerBI has taken on a life of its own however, I knew there was a better way, utilizing more of the #PowerPlatform.
Developing the Solution
Having worked through the business process over the previous three years, I had identified the areas of improvement required and which services within #PowerPlatform would achieve these. However, with school divisions having a high transient workforce, I had to keep in mind that any new process adopted had to be user friendly with minimal training requirements and change management.
Dataverse
This was to become my data warehouse in a snowflake schema. The solution has over 30 dimension tables and 2 fact tables. The second fact table was a requirement once I extended beyond the original solution concept and added additional functionality. There are four primary dimension tables, two Student tables for Current Year and Archive and two Teacher tables for Current Year and Archive. This had always been a problem with the Excel workbooks where we needed to export information from our Student Information System in csv format and past it into new workbooks at beginning of year. With students and teachers moving throughout the year, maintaining these sometimes lead to data quality issues.
Dataflow
With the schema defined, the first improvement was to set up a synchronization between our Student Information System and #Dataverse. I did not want #PowerApps connecting to it directly on the API due to number of potential calls so I created two Dataflows, one for Students and one for Teachers. Both connect to separate #PowerAutomate HTTP Request automations called each night.
These call a query defined within the Student Information System to retrieve Student Information, Teacher Information and Class Assignments. Once ETL has been competed, the query is mapped to the relevant #Dataverse tables. The Current Year tables for both Student and Teacher have "Delete Rows that no longer exist" selected to ensure that only latest assignments are available within the #PowerApps where Archive tables maintain history of Students and Teachers for reporting within #MSPowerBI.
Additionally, I wanted to ensure that previous results within the Excel workbooks were migrated to #Dataverse using #Dataflow. There are 16 of these connecting to existing #Sharepoint sites where the workbooks were originally saved.
PowerApps
The biggest challenge was to make a very simple interface within Excel translate into a #PowerApps that would also provide a measurable improvement to the current business process. There are three apps within the solution, Administration (Model-driven), Assessment (Canvas) and Connectivity (Canvas).
Administration
This #PowerApps is focused on back end administration of dimension tables as well as data integrity fault finding and rectification. Dimension tables such as the one shown below facilitate calculations within the Assessment App as well as delegable queries based on School and Role
Assessment
This #PowerApps is the heart of the solution for School Board Executive, School Administrators and Teachers within the division. All data surfaced within the app is delegable queries with no locally stored collections created within the app.
The App is designed round a singular page broken into various horizontal and vertical containers which contain additional containers and elements within them. Control wise here are some of the numbers however, the total controls on one screen is 7449 currently.
30 + Containers
40 + Galleries
15 Forms
40 + Buttons
The App has been deployed to over 200 users and although load speeds to first screen vary dependent on device platform and users within the app concurrently, our average speed is about 6 seconds currently.
The biggest challenge to overcome with this #PowerApps was the Security. Security defined within the Student Information System could not be matched with the #Dataverse Security Model. Additionally, the App had to dynamically be able to show galleries and buttons based on the signed in user.
The biggest challenge to overcome with this #PowerApps was the Security. Security defined within the Student Information System could not be matched with the #Dataverse Security Model. Additionally, the App had to dynamically be able to show galleries and buttons based on the signed in user. The image above would be what a Teacher sees when opening the app. They are able to see all the Students assigned to them within Class Assignments. Clicking on any Student will display the history of Assessments those Students have done.
Teachers also have the ability to add and edit Assessments as well as add and edit Interventions from this view.
On selecting an assessment type, the relevant form is then made visible. The combo boxes items are queries based on the Student Chronological Age, the period in which the assessment is being conducted and other specific based filters per assessment type. This ensures that only relevant scoring can be selected ensuring data quality.
Additionally, embedded #MSPowerBI Reports are made available. This enables users to get further analysis of the data without having to leave the application.
Users also have the ability to request #MSPowerBI Paginated Reports from the app. These requests are process within #PowerAutomate and exported reports are delivered to user via email as well as being uploaded to School specific #Sharepoint sites.
Connectivity
This #PowerApps was the extension to the original solution concept which, resulted in the additional fact table within #Dataverse being required. All data surfaced within the app is delegable queries with no locally stored collections created within the app.
The image above would be what a Teacher sees when opening the app. They are able to see all the Students assigned to them within their Homeroom Assignments. The app enables students within the class to be called to a designed computer within the class to complete a Connectivity Survey. Once a teacher clicks on a student, a start screen is displayed to the Student for them to begin.
Once they start, questions will be shown to the student. These Questions are filtered to the Student based on the Survey being conducted as well as the grade the student is within. Additional filters such as School specific or Course specific questions can be displayed per student.
Once the Student has completed the survey, it is retuned to an end Survey page where the Teacher must enter a pin. This then ensures the student conducting the survey can not get access to any app functionality. Additionally, the Teacher can choose not to conduct the survey within the classroom but, send the student the survey through Teams by selecting Teams Chat in the Collection Method dropdown. These requests are process within #PowerAutomate where Adaptive Cards are generated then sent to the student.
School Board Executive and School Administrators have additional functionality within the app where they can build Surveys and Question banks.
Question layouts as well as filters, ordering and response options can be created and shared. Question responses call also be flagged, enabling immediate notification to School Administrators through #MSTeams and adaptive cards with, deep linking enable to open the submission details within the app.
Teams
Both Assessment and Connectivity have been published to our Organization Store enabling users to utilize the apps within #MSTeams. This also enables additional functionality for both moving forward within bots and activity feed notifications.
Power BI
There are a number of #MSPowerBI Reports within the solution which comprise of Reports, Paginated Reports and a Scorecard.
A number of these #MSPowerBI Reports are embedded within the Assessment Application. All reports utilize Role Level Security and our three primary reports, AERR Literacy, AERR Numeracy and AERR Connectivity have incremental refresh applied within them ensuring that data added to the applications is immediately visible to stakeholders within the School Division.
Additionally, new public Annual Education Results Reports will be embedded within our website in the next coming months which, will enable visitors within our website to view our latest reporting towards Literacy, Numeracy and Connectivity results.
Let me wrap it up
We have now deployed our solution within the School Division to over 400 employees. I hate to think how many hours I've spent developing this however, I know how many hours this saves School Administrators and Teachers across the division and these hours we get back using #PowerPlatform are going to where they make a difference, teaching, not administration.
This is just the start of this journey and through feedback and utilizing other features it will be fun to see where it goes. So the question for others is why would you not Adopt #PowerPlatform ?
Over the coming months, I'm going to deep dive some of the elements of this solution into separate posts and share some learning I've picked up during the process.
One final thing, this solution within the Fort Vermilion School Division was built, deployed and currently maintained by one person, me a Citizen Developer.