by Allison Fisher
I’m on a team of 4 Microsoft interns who spent the summer working on a tool to enhance Power BI’s hot-path data reporting capabilities. We work on the Azure SQL Database Telemetry Team, which deals with high volumes of data on a daily basis. Our goal was to build a live service health dashboard to correlate SQL Azure deployments with key High Availability and Disaster Recovery performance metrics, like login success rate and exception count.
In order to be actionable, our dashboard needed frequent refresh (every 5 minutes), and we also needed to be able to include data from different sources in the same report. We really wanted to be able to leverage the interactive slicing capabilities of Power BI in our report, which meant that we needed relationship detection to inform Power BI how to slice every element of the report together, regardless of the source.
We knew that the Power BI Designer Preview provided auto-relationship detection among datasets, and we knew the Power BI REST API allowed us to push the data as frequently as we wanted. However, there was no way to marry these 2 capabilities with the existing Power BI product.
Our tool, Post to Power BI, is a solution that allows users to join data from multiple sources and push it to PowerBI in a low-latency refresh loop.
First, we built a package in C# that makes all our queries, dynamically generates a single denormalized dataset, and pushes it to Power BI with the REST API. This worked great, but if the person running the program wanted to turn off their computer, or was away on vacation when something went wrong, the dataset would no longer refresh.
The exception data comes from an internal tool query, and the deployment data comes from a SQL query. But with our tool, we can display them both on the same graph, and we can slice by time and cluster over all data in the entire report. Post to Power BI enables users to gain new insights from their data by bringing together data from different sources in an interactive format.
