Track Flows using Power BI

[LauraGB]

Introduction

This post is in three parts. The first part is to create the dataset in Power BI, the second part is to add actions to the flow and the final part is to create a simple report in Power BI.

Creating a Hybrid Dataset

For the first part we need to start in Power BI, which can be found at https://powerbi.microsoft.com. Power BI uses workspaces to organise datasets, reports and dashboards, with a free license you will only have access to My Workspace, that will work for this exercise.

1. Click on My Workspace and then Datasets.
2. In the top right hand corner, click +Create and select Streaming Dataset.
3. Choose API as the source of the dataset and click Next.
   
   
   
   
4. Enter in a name for the dataset.
5. Enter in the value names and data types.
6. Turn on Historic data analysis to make the dataset a Hybrid of a streaming and push dataset. This means the data will be kept rather than only a temporary store.
7. Click Create and the pane will show a message that the dataset has been created.
8. Click close to show the dataset in the list.
   
   

Add Actions to Flows

For every flow you want to track you’ll need to add two parallel actions, one for a successful run and one for a failed run. This will allow you to track not only how many flows ran but their final result.

1. Edit the flow and click to add an action at the end of the flow and search for Power BI and select Add rows to dataset.
   
   
2. Rename the step to Add Success Row to dataset.
3. Select the workspace, dataset and the table is RealTimeData.
4. Enter in the values for the dataset. To make reporting easy we are going to split the when the flow happened into date and hour. The expression formula for the date and hour are:
   
   Date   substring(utcNow(),0,10)
   Hour  int(substring(utcNow(),11,2))
   .
   Enter Success for the response
   
   
   
5. Add a parallel branch to the previous step and create a step identical to the previous step with a response of Failed.
   
   
6. This step needs to run if the flow is not successful.
   Click on the ellipse in the top right of the action and select Configure run after.
   Untick is successful and tick has failed, is skipped and has timed out.
   Click done to save the changes. The arrow into the action will change to red.
   
   
   

7. Save your flow. Repeat the above steps on the other flows you want to track.

Creating a Report

In this post we will create a simple report to show the flow activity.

1. Back in Power BI and the dataset list and click on Create Report.
   
   
   
2. In the Visualizations pane select Stacked column chart.
3. From the fields list, drag Hour to Axis, Response to Legend and Flow to Value.
   
   
4. A column chart will show the number of flows per hour. You could plot different charts using days, different flows etc
   

Conclusion

This method could be expanded to track multiple flows and could track different aspects of the flow including times it took approvals to happen or how often approvals are requested.

Let me know how you used this idea.