The company that I worked for had an interesting problem to solve. They had recently “acquired” a consultant who provided custom reporting services to higher education institutions which was highly labor intensive and manual. We were asked to fully automate the workflow incrementally AND onboard existing clients
powerbi azure-storage azure-data-factory azure-web-app powerbi-embeded azure-functions azure-entra
What were the challenges?
- The existing process was very custom and manual in nature. The consultant custom transformed data files from the institutions, loaded them into an on prem database to perform transformations and imported the results into a power bi report.
- The report was then emailed or shared via powerbi service.
- The institution would then go back and forth with the consultant about to tweek the reporting bits. This was NOT scalable
engineering bits
- We setup a dotnet based website for presentation would leverage powerbi embedded with app owns the data strategy to render embedded reports
- consultant would get the data files from the clients and upload it to azure storage using the web app.
- we used azure data factory to orchestrate ETLing the data files into the OLAP data model needed for powerbi
- we setup custom dotnet service that would load / refresh powerbi report from the processed output files from azure data factory.
learnings
- powerbi is a PITA unless you know what you are doing. there are quite a few levels that you need to get it right for it to work as you expect. It greatly reduced the efforts to put reports in a consumable form, however scaling it has its challenges
- we delivered the workflow iteratively where initially consultants were given access to load the files directly into azure, which was later replaced by a webapp.
- we continued to deliver the reports by still running the most of the transformations on top of SQL server, thereby avoid releasing large changes with unwanted side effects.