As employees start returning to offices around the country, we’ve had a request from one of our clients to create Power BI reports to help keep their staff safe during this period.
Harnessing Power BI to track-and-trace
Power BI is Microsoft’s offering for visualizing the data that is at the core of many successful businesses. It allows interactive reports and dashboards to be rapidly produced that draw on data from many different sources.
For this particular assignment, our client had implemented a desk booking system by creating Office 365 appointments. This enables them to manage the reduced desk space they have available due to social distancing requirements.
As part of these arrangements, the client identified a need to be able to trace the location of employees in case any started to show symptoms of COVID-19, or received a positive test.
The brief we were given was to produce a set of dashboards that allows the location of any employee over a specified time period to be easily viewed. Crucially, the dashboards also identify any other employees who have been in the same locations, and therefore have an increased risk of exposure.
Sourcing the data
The first step to producing these reports was to produce a data model of each employee’s location each day. This data had to be obtained from the Graph API, which is a REST API for accessing data about Microsoft cloud services, in this case, Office 365 calendar appointments. You can experiment with the Graph API with the graph explorer tool here:
https://developer.microsoft.com/en-us/graph/graph-explorer.
Power BI offers a wide range of data connectors to allow you to combine data from many different sources into an in-memory data model. This is then used to produce the interactive reports. In this case, since the Graph API is an Open Data Protocol (OData) service, we first consumed this directly:
However, we quickly realised that consuming the OData feed directly, whilst feasible, was not going to be the simplest way to retrieve all the data required. This is due to many calls being needed to retrieve the full dataset, and because of some restrictions on credentials when using a Power BI scheduled refresh.
We decided the simplest approach was going to be to extract the Graph API data into Azure table storage. To this end, we wrote a simple Azure Function with a Schedule Trigger. This would simply call the OData endpoints and write the results into Azure Table Storage. This could then be consumed by Power BI. Using an Azure function eliminates much of the overhead of writing these sort of lightweight code snippets.
Modelling the data
Once we had sourced the data, it was a case of using Power BI Desktop to build a data model representing the employees, the desks they would be working at, the clusters the desks were grouped into and the times when the employees were working at these desks.
The model also includes the relationships between these data tables, and some DAX measures to do the heavy lifting in working out who has been exposed to who. We find that the data model is the crucial step of a Power BI solution – get this right and your users can self-service their own visualisations.
Visualization and deployment
The final step was to add some visualisations to enable the client to drill down on particular employees and locations. Once done, it was published to the client’s Power BI workspace, the appropriate users given permissions and a nightly refresh of the in-memory data model scheduled. The result is an easy-to-use system that quickly identifies any at-risk employees and their colleagues.
What next?
Power BI is a great tool for getting the most out of your investment in Office 365. Get in touch with us to arrange a free consultation on the possibilities for your organisation.
Find out more about the technologies and patterns used in this work at the following links:
https://powerbi.microsoft.com/en-us/why-power-bi/
https://docs.microsoft.com/en-us/graph/use-the-api
https://docs.microsoft.com/en-us/azure/azure-functions/functions-overview
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-data-sources
