An Automated BI Solution: Microsoft PowerAutomate

Simple automation can often deliver big improvements in the context of BI solutions. A great example of this is when using PowerAutomate, OneDrive for Business and Power BI. As part of a broader Data & AI strategy, the combination of these applications can deliver an impressive automated result, dramatically increasing the value of deploying simple BI solutions.

Opportunities for automation in simple BI solutions are often overlooked, as the perceived cost of such a project would outweigh the benefits. However, good data platform delivery involving rigorous assessment of business processes can help identify instances where automation will deliver scalable value. As a real-world example, a client had a set of Excel files that were sent each week from their ERP system via email. And someone would take those Excel attachments and manually perform some transformations in the files, before importing to Power BI and hoping that the reports would turn out ok. Such a process was not only time consuming, but also riddled with potential manual error, ultimately failing to deliver the insights and value that they were after in a BI solution.

How to Setup the Flow

Enter PowerAutomate and OneDrive for Business. PowerAutomate is an application that helps automate tasks by integrating workflow between different applications. In our case, implementing PowerAutomate was clear:

1. Create a Flow that automatically saves the Excel attachments from those emails into OneDrive for Business (whilst performing some renaming and archiving along the way for good measure)
2. Connect Power BI to those files in OneDrive

Designing the flow was simple, requiring only basic information such as:

• Where the email was being sent from
• The subject of the email
• What to name the Excel file
• Where to save the file

The Flow would then take this information into its ‘trigger’ and ‘action’ steps, forming a logical and repeatable workflow.

Once the flow was set-up and Power BI connected to OneDrive for Business, the previously manual process to deliver key business reporting was transformed into an entirely automated one. Time lost to handle-and-wrangle data was now able to be spent on core value activities and enhancing data visualization.  Higher quality reporting was now delivered, granting previously unseen insights into the business.

Understanding where automation can provide value in BI solutions is crucial, particularly in simple BI projects. As evidenced above, a simple combination of Microsoft Flow, OneDrive for Business and Power BI can be the difference between success and failure for a BI solution.