Blog
#PowerBI Status with #PowerAutomate Refresh all in one #PowerApp

From the day Microsoft made the “PowerBI – Refresh a dataset” Power Automate connector available, and having seen this video by GuyInACube https://guyinacube.com/2019/10/24/use-microsoft-flow-and-other-options-to-refresh-your-power-bi-dataset/,  I’ve had the grand idea of achieving three things using the Power Platform.

  1. Automating the Power BI dataset refresh at the end of an ETL process, rather than relying on the scheduled refresh.
  2. Creating a Power App so that I can see the status of all of my datasets across any workspace.
  3. Having the ability to trigger a refresh myself within the Power App.

The final app looks like the image below and works brilliantly for me, but let me walk you through how to go about building something like this.

PowerBI Blog

From an architectural perspective, the end solution looks like this.

  1. All of the data starts on-premise – we use SSIS to create packets of data that are used by Power BI and push these to Azure SQL. We have literally hundreds of jobs and they run all day long.
  2. When the data arrives in Azure, I am going to use Power Automate to trigger the Power BI Refresh. Therefore removing the need for scheduled refreshes. This means end users don’t need to wait for the next refresh to happen!
  3. Create a custom connector using the Power BI Rest APIs to retrieve the refresh history from all workspaces, which will be called by Power Automate and write the data back to SQL.
  4. Surface the data, stored in SQL, within Power Apps to show when data processing was completed between on-premise and Azure and also the refresh date-time for each dataset in Power BI. This enables me to see all of my dataset status’ in one place.
  5. Enable a manual refresh capability with the Power App – Just in case!

So how do we go about building all of this?!

As I mentioned earlier, I was inspired by the availability of the Power BI refresh connector in Power Automate and also the use of the SQL table and updating a processing date as described in this video. https://guyinacube.com/2019/10/24/use-microsoft-flow-and-other-options-to-refresh-your-power-bi-dataset/

Step 1. Create tables in Azure SQL db

The first table is one that you use to store Power BI Workspace Group IDs and Dataset IDs. The purpose of this table is to use it to trigger a dataset refresh. More on that in a bit!

CREATE TABLE [dbo].[PBI_FlowUpdate]([ID] [int] NOT NULL, [ProcessDate] [datetime] NULL, [lastupdate] [timestamp] NOT NULL, [DataSetName] [varchar](255) NOT NULL, [GroupID] [varchar](255) NOT NULL, [DataSetID] [varchar](255) NOT NULL,  CONSTRAINT [pk_FlowUpdate] PRIMARY KEY CLUSTERED ([ID] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]

The second table is where we will where we will store Power BI Metadata – Data that is retrieved using the custom connector and the Power BI Rest APIs

CREATE TABLE [dbo].[PBI_RefreshUpdate]([ID] [int] IDENTITY(1,1) NOT NULL, [RefreshType] [varchar](255) NULL, [StartTime] [datetime2](7) NULL, [EndTime] [datetime2](7) NULL, [GroupID] [varchar](255) NULL, [DataSetID] [varchar](255) NOT NULL, [Status] [varchar](255) NULL,  CONSTRAINT [pk_RefreshUpdate] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]

Step 2. Create Custom Connector

Microsoft have a whole site on Rest APIs and the particular one to be used here is the “Get Refresh History in Group” https://docs.microsoft.com/en-gb/rest/api/power-bi/datasets/getrefreshhistoryingroup

I have blogged about setting this up previously and more detail can be found here. https://powerfulbi.co.uk/blog/f/refresh-a-powerbi-dataset-from-powerapps-with-flow

Step 3. Get Dataset data using Power Automate

Using the Custom Connector described in Step 2, I have set Power Automate to run at 15 minute intervals to go and grab the latest refresh datetime and status from my Power BI workspaces. This data is stored in the 2nd table that was created in Step 1.

Step 4. Set up an Power BI Refresh trigger in Power Automate.

This is a simple flow where we use the first table created in Step 1. The concept here is that when the ProcessDate is updated, then this flow will automatically trigger and refresh the Power BI dataset by using the Group and  Dataset IDs.

This Flow is also the one that we use at the end of our ETL process. What we have done is add a final step after loading data in Azure to update the ProcessDate. 

It’s a simple update statement “Update dbo.PBI_FlowUpdate Set ProcessDate = GetDate() where ID = ‘x’ ”  Adding this step has allowed us to turn off scheduled refresh entirely.

Step 5. Creating a Flow to trigger when using a Refresh Button in a Power APP.

Similar to the principle of the previous step. This flow takes the “ID” that is displayed in the App and passes it as a variable to be used in a SQL query.. In essence, this Flow updates the ProcessDate in the SQL table and then the Flow from Step 4 will automatically be triggered!

Step 6. Create a view that uses the data within both SQL tables and use the view to surface the data in the APP.

SELECT a.[ID] ,[ProcessDate] ,[lastupdate] ,[RefreshEnd] ,[RefreshType] ,[Status] ,[DataSet] ,a.[DataSetID] ,a.[GroupID] FROM [dbo].[PBI_FlowUpdate] aleft outer join [dbo].[PBI_RefreshUpdate] ron a.datasetid = r.datasetid and a.groupid = r.groupid

Step 7. Build the App.

Start by selecting a Canvas app and “Start with Data” – Use the view that you created in Step 6 and build out the APP in a way that works for you. 

If you’d like some help on this one, or have any questions, then reach out to us via the contact us section on the website