Enhance Self-Service Power BI with Microsoft Power Automate f

I was recently architecting a solution for a customer that required a combination of self-serve and IT governed Power BI reporting.  They wanted a simple a process to create, edit, and manage their content – without the danger of unvalidated reports being published into the production app workspace.

The customer’s existing process was very manual and disjointed.  Many versions of one Power BI Data model existed across a variety of workspaces and users’ machines, resulting in data silos across the business. Whilst the IT driven reports contained governance and a defined process, self-serve found themselves in a bit of a mess.  What if there was a more robust, automated way of managing this?  Ultimately, the reporting process and user experience needed to improve.

Based on the requirements, time and budget, I set about designing a self-serve and IT integrated Proof of Concept (POC), containing three layers of report approval:

• Self-Serve Analyst – User empowered to create and modify report content, but not able to publish.
• Report Approver: User who approves changes to reports, before they are published
• Report Publisher: IT Admin user responsible for publishing reports, after the approval phase.

Pre-Requisites

Prior to creating the proof of concept, I undertook the following tasks:

• Create 3 users with Power BI Pro Licenses
  • Self-Serve Analyst: SMETestUser@ebecs.com
  • Report Approver: cgreen@ebecs.com
  • Report Publisher: AdminTestUser@ebecs.com
• Create a Power BI app workspace – adding the Self-Serve Analyst user as a member (view content) and Report Publisher as an Admin.
• Login to Microsoft Power Automate with my credentials.  All users can access Automate, as long as they have an O365 organisational account.f

POC

I was tasked with picking the best tools and technologies for the POC.  Remember, the customer wanted something simple – with little coding overhead.  I decided on the following:

• Microsoft Power Automate
  • 2 Automate’s:
    • Approval Automate, triggering an email to the SME.  Once approved, the report is moved into a new folder for publishing.
    • Standard Automate, to inform Admin user (IT) that a new report is ready to publish from Power BI Desktop.
• SharePoint location for the App Workspace
  • Each Workspace contains a SharePoint folder, where files can be created, modified and deleted.
  • Version control and exclusive check outs are applied within the SharePoint location.
• Power BI Workspace
  • Admin and Member permissions applied:
    • Admin can publish, delete and modify existing reports, as well as control the users who have access.
    • Members have read-only access to the reports, meaning they cannot publish either.

Fig 1.0: Power BI Governance Approval Process

Outcome

Some customers will use IT to manage, deploy and provision reports, whilst others will empower self-serve users to own the content.  It completely depends on the type of organization and business decisions that are being made.  I have demonstrated one way in which Power BI reports can be governed and this worked very well for the intended customer.  We could take this a step further by removing the need for a Report Publisher and in fact, automate report deployment using PowerShell scripts.  The customer in question specifically wanted a Report Publisher, as it provided another person to check over the usability and functionality before deploying to a production environment.

By utilising codeless functionality within Microsoft Power Automate and leveraging the out of the box SharePoint location, a fully governed, semi-automated solution was built in a matter of hours.  The simplicity of the process means the customer no longer have issues with overwritten or lost Power BI datasets.  BI developers are often fixated with writing reems of code or engineering a solution that is sometimes ‘too clever’, when sometimes a simple, structured approach is all that is required.

Next Steps

If you would like to know more about Approvals in Microsoft Power Automate and general Power BI Governance, please contact us at enquiries@ebecs.com.

Twitter:      @DataVizWhizz
LinkedIn:   https://www.linkedin.com/in/callum-green-a907058b/