A major Canadian University wanted to automate the process of reporting endowment fund expenditures to faculties. The proceeds of endowment funds are used to support academic scholarships, bursaries, and research projects, and are a major component of large academic institutions and their fundraising efforts. Efficient reporting of these funds, their income, expenditures, and unspent balances allows faculties to support the students, scholars, and projects as intended by the donors, while remaining fiscally responsible and avoiding overspending. The existing highly manual and time-consuming process meant that the fund expenditures and balances were often out of date and funds with infrequent activity could easily be forgotten. Unspent funds were an unfortunate but common occurrence. Automated Endowment Fund Reports would alleviate many of these issues. 

The first objective of this project was to allow faculty members to run reports themselves for those faculties/departments to whom authorization has been granted. Another objective of this project was to include data from the distribution of the funds, such as the recipients of funding support, to streamline the university’s reporting obligations to the donors and agencies that contributed the funds.  

An initial proof of concept phase was envisioned to persist the required fund financial information into the existing data warehouse, and then create a Power BI dataset to support Faculty and Department fund details with fund financial information. Drawing from the Fund Financial dataset, the client requested a series of reports be developed to replicate what was currently being prepared manually in Excel workbooks.   

The funding data, including balances, disbursements, and recipients, has a level of sensitivity that required the solution to implement granular permissions of the data – it was important that most faculty and staff members had access to only the data in their faculty or department, and other staff at higher levels had full access to all the data. Imaginet implemented row-level security in the Power BI semantic model and used user-level permissions defined in Excel to allow permission changes without coding changes to the semantic model.  

Imaginet was engaged to help drive this project, working with the business user to create the business case stories and detailed requirements. We worked with the university IT department to define the layout & schedule for the required financial data extracts, and automated processes to import the fund financial information into the data warehouse. We enhanced the existing Power BI dataset with the new data and developed the requested fund reports.   

Previous Work and Updates: 

Imaginet had previously been engaged to develop standards and procedures for the data warehouse, along with building out the data warehouse with information from the Fund Management source system, thereby having experience with the existing fund data. 

What We Did for This Project (Automated Endowment Fund Reports):

Imaginet worked with the subject matter experts (SME) in the university to identify their current processes and data sources used to compile the existing Excel-based Fund Financial reports. From these requirement gathering sessions, we defined a report specification to request a custom report to be developed by the university IT department. 

When the requested report was built, the IT department scheduled it to email the report output as a text file. There was an existing Logic App built by Imaginet that monitors the mailbox and saves attachments to cloud storage (Azure Storage Account blob containers), and we updated the Logic App to process this report in the same manner.  

Once the fund report files are placed into cloud storage, the existing Azure Data Factory was updated to import the data in these new report files into tables in the data warehouse in a SQL database. Some new tables and related code in the data warehouse was built to support this new data. We also added processes in the data factory to import the fund permissions from the Excel workbook where they are managed by administrative users. 

With the new fund data and permissions in the data warehouse, we modified the existing Power BI semantic model to include the new fund tables and user permissions and added the Power BI roles and custom filters to the semantic model to implement the row-level security. New measures in the semantic model were also created to support the reporting requirements. 

With the new data in the semantic model, we developed of a series of paginated reports structured with Date, Faculty, Department, and Fund filter and drill-down functionality to expose fund financial details and drill through report functionality to expose fund terms-of-reference and award details.  This delivered to the user a one-stop-shop for all things fund related. 

Throughout the project, we worked with users and executives in the university to validate all the measures and formulas, provided training and orientation with the new reports, to support the launch of the new reports to selected users.  

Why Automated Endowment Fund Reports Were Helpful

There was an immediate return on investment with this solution as now the business SME and other key business users can run financial fund reports based on selectable date, faculty & department parameters, and the immediate availability of information. Before our solution, it could take a full day or more to prepare the equivalent report in Excel. The visibility of inactive and unused funds for faculties and departments was much improved. The university was also able to provide their donors and funding agencies with much better disbursement information with far less effort and without long delays.  

Conclusion: 

The client has a solid and extensive endowment fund financial dataset they can use to build out additional fund financial reports, along with some existing financial reports they can use for reference or enhance themselves.   

Throughout the project, Imaginet provided training to the client on the backend Azure Data Factory processes, the Power BI dataset (semantic model & measures), and Paginated Report development leveraging the Power BI Financial dataset as the data source.  This has provided the client with the tools and knowledge to extend the solution and develop reports themselves. 

Our team has been working hard these last few months, and we are excited to share our success with you. Don’t forget to subscribe to our newsletter to stay updated on our most recent Analytics & Data Engineering projects. Fill out the form at the bottom of the page if your data is unreliable, you want to upgrade your data factory, or you need a data collection upgrade.