Converting a meticulous financial submission process with a two-click API solution.
Entrance has successfully automated the financial submission process for a charter school network that serves around 34,000 students in Texas.
A Little Background on Our Client:
We worked with a public charter school network serving approximately 34,000 Pre-Kindergarten through 12th grade students across the state of Texas. They are committed to creating academically excellent schools to prepare students for college and beyond.
The finance team for our client is responsible for reporting various financial data to the Texas Education Agency (TEA) throughout the school year, most notably budgets during the fall and actuals at the start of the calendar year. The data is formatted as an XML file that is manually uploaded to an Operational Data Store (ODS) as required by TEA, and then promoted to Texas Student Data System (TSDS), where the data is validated and submitted to TEA.
The Challenge Facing Their Finance Team:
TEA is phasing out the XML upload process in favor of using an Application Programming Interface (API) solution leveraging Ed-Fi to allow for a more automated data reporting process. This change will render the finance team’s current submission process incompatible and obsolete. Because of the shift to automation, the finance team decided codifying their entire process would be feasible as well– from retrieving data from their financial software, to applying business rules to reconcile and format the data, to applying validation rules to enforce accuracy, to converting the data into TEA compliant Ed-Fi objects and finally promoting the data to the Landing Zone for final submission to TEA.
Entrance accepted the challenge to automate our client’s submission process while adhering to compliance rules as set forth by TEA.
Solving the Problem with Azure, ReactJS, and Microsoft CDN:
Entrance held a requirements-gathering session with the finance team to get an understanding of their current manual submission process, and also met with TEA staff to learn more about the data promotion process. From this, Entrance architected a lightweight ReactJS Single Page web application that would utilize a series of Azure Functions along with a Cosmos DB NoSQL database and Azure Queue Storage to transform the Trial Balance report data exported from our client’s financial software into consolidated, compliant records submitted to the Landing Zone.
The website was configured to be hosted via Azure Blob Storage and Microsoft CDN (content delivery network) and uses Microsoft identity platform for authenticating users.
One the user is authenticated, they are redirected to the PEIMS (Public Education Information Management System) Submission page to kick-off the first step of the submission process: retrieving the data from their financial software, that is then filtered, consolidated, and validated.
The user can enter the date range or reporting period for the Trial Balance data they wish to report on, and optionally choose account groups and/or account number ranges of the data to filter down to.
If they choose not to customize accounts, the application will select Net Asset, Revenue and Expense account numbers by default as these are the accounts that are targeted by TEA.
Upon clicking the submit button, three Azure Functions handle requesting the Trial Balance data from the financial software, reading the Trial Balance data from the financial software, and applying business rules to transform and validate the data, respectively. The Read Report Azure Function is queue-triggered and utilizes Azure Queue Storage messaging to facilitate retrieving the status of the Trial Balance report in the financial software on a loop until the report is available to be read. This design significantly reduces the risk of a function timeout and enables our client to keep costs down with the Azure Functions consumption plan.
To reduce the risk of data entry errors during these steps, the Filter Report process replicates the almost-twenty step manual process of parsing, filtering, mapping, ordering, totaling, and formatting the records seamlessly into code, saving the results targeted for submission in Cosmos DB. These records are then validated against a rules engine consisting of logic intending to catch fatal errors as defined by TEA data validation rules. The validation step was implemented as a safeguard to identify records that will not pass validation when promoted to the Landing Zone, allowing the finance team to correct issues and ensure only accurate data is submitted.
Upon completion on the above steps, the user is given an overview of the number of records that were downloaded and filtered from the financial software, the number of records that will be submitted to the Landing Zone, and the number of records that have validation issues, with the ability to download the records associated with each.
If the user is satisfied with the results, clicking the Approve button will start the Landing Zone Azure Function to begin formatting the records into Ed-Fi objects and uploading them to the Landing Zone. However, if the user needs to make corrections to the data, clicking the Reject button will end the current submission process, allowing the user to begin a new submission when ready.
The Landing Zone process will return the results of the submission: the total number of records slated to be promoted, the total number of records accepted into the Landing Zone, and the total number of records rejected. If there are any rejected records, the user is given the option to download the records with the error messages received from TEA.
Since the submission process consists of several steps, care was taken so that if a user navigates away, the process will continue to run, and the application will automatically load to the latest step upon the user’s return. The user can abandon, reject, or end the submission process at any time, and in doing so will be returned to the first step to select parameters to begin a new process.
In addition to automating the submission process, Entrance also provided screens for self-service management of account groups, organization codes and NARF mapping funds, enabling the finance team to edit these values as needed. Every submission run is saved in a log with details on who created the submission and when, the outcome of the run and an Excel download of the records submitted when applicable.
Users with the Key.Write permission can also edit the Landing Zone authorization client key and secret which is rotated periodically.