Event Trigger Power BI Dataset / Dataflow Refresh via Azure SQL Database

Problem Statement :

Is it possible to refresh Power BI Dataflow / Dataset on any event occurrence in Azure SQL Database.

Prerequisites :

1. Power BI Dataset / Dataflow
2. Azure SQL Database
3. Azure Function ( In this blog, we would be leveraging PowerShell Core Runtime Stack )

Solution :

Note : We would be leveraging Azure SQL Database External REST Endpoints Integration via which we would be triggering an Azure function and Power BI REST API.

1. In my previous blog Refresh Power BI Dataflow / Dataset from Azure Data Factory / Synapse Pipeline via Service Principal (AAD Token Generation)- Part 1, we trigger the Power BI Dataset / Dataflow refresh REST API by generating an AAD Token of the Service Principal which has access on the needed Power BI Dataset / Dataflow.
2. We would be following a similar route in the current flow ; wherein we would be triggering a Managed identity enabled Azure Function to generate a AAD Token which we would in turn use to trigger the Power BI Dataset / Dataflow Refresh API.

Note : The AAD Token can have an expiration time between 60 – 90 min. So rather than preserving that as a Database Scoped Credential, we can generate it at run time by triggering the Azure function (in this scenario)

3. Assuming we have a PowerShell Core Runtime Stack Function App set up, update the requirements.psd1 ( present within App files section of App Function) to leverage Az modules.

4. Create a Function of HTTP Trigger type and Function Authorization Level

Enable Managed Identity for the Azure function, which we would need further to provide it access on the needed Power BI Dataset / Dataflow.

Add the below code (for AAD Token creation) in run.ps1 file within Code + Test Section and Save it.

using namespace System.Net

# Input bindings are passed in via param block.
param($Request, $TriggerMetadata)

Connect-AzAccount -Identity

$AADToken=(Get-AzAccessToken -resourceUrl "https://analysis.windows.net/powerbi/api")

# Associate values to output bindings by calling 'Push-OutputBinding'.
Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
    StatusCode = [HttpStatusCode]::OK
    Body = $AADToken 
})

5. Once the function is created within the Azure function, login into app.powerbi.com and provide the App function Member access within the workspace hosting the Power BI Dataset / Dataflow that we need to refresh.

6. Login to the Azure SQL database via which we need to trigger the Power BI refresh and execute the queries in the below provided sequence.

a) 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<<>>'

b) 
CREATE DATABASE SCOPED CREDENTIAL [https://<<AzureFunctionName>>.azurewebsites.net/api/<<FunctionName>>]
WITH IDENTITY = 'HTTPEndpointHeaders', SECRET = '{"x-functions-key":"<<FunctionKey>>"}'

Where FunctionKey value is as below :

7. Now for the current use case, the scenario is to trigger a Power BI dataset refresh when any data is inserted within a table in the Azure SQL Database.

a) Creating a sample table 

CREATE TABLE dbo.DataSharkX
(
C1 VARCHAR(50),
C2 VARCHAR(50)
)




b) Creating a Stored Procedure which we can leverage within the Database Trigger

CREATE PROCEDURE dbo.DataSharkXSP
AS 
DECLARE @Appurl NVARCHAR(4000) = N'https://<<AzureFunctionName>>.azurewebsites.net/api/<<FunctionName>>';
DECLARE @Apppayload NVARCHAR(max) = N'{ }';
DECLARE @Token NVARCHAR(max);
DECLARE @Appret int, @Appresponse NVARCHAR(max);
DECLARE @PowerBIurl NVARCHAR(4000) = N'https://api.powerbi.com/v1.0/myorg/groups/<<WorkspaceID>>/datasets/<<DataSetID>>/refreshes';
DECLARE @PowerBIpayload NVARCHAR(max) = N'{
"notifyOption":"NoNotification"
}';
DECLARE @PowerBIret int, @PowerBIresponse NVARCHAR(max);

--To Trigger the Azure Function in order to generate the AAD Token at run time

EXEC @Appret = sp_invoke_external_rest_endpoint 

    @url = @Appurl,
    @method = 'POST',
    @payload = @Apppayload,
    @credential = [https://<<AzureFunctionName>>.azurewebsites.net/api/<<FunctionName>>],
    @response = @Appresponse output;

    
SET @Token= (SELECT * FROM OPENJSON(@Appresponse,  '$.result') WITH

  (
    "Token" NVARCHAR(max)
  ))


DECLARE @PowerBIheaders NVARCHAR(4000)=N'{"Authorization": "Bearer '+ @Token + '"}';

--To Trigger the Power BI DataSet refresh REST API

EXEC @PowerBIret = sys.sp_invoke_external_rest_endpoint 

	@method = 'POST',
	@url = @PowerBIurl,
	@payload = @PowerBIpayload,
	@headers = @PowerBIheaders,
	@response = @PowerBIresponse output;


SELECT @PowerBIresponse,@PowerBIret



c) Creating a Database Trigger 

CREATE TRIGGER [DataSharkXTrigger]

    ON [dbo].[DataSharkX]

    AFTER INSERT  

    AS

    BEGIN

	EXEC dbo.DataSharkXSP 

    END

Result / Output :

Creating an Event to trigger the Database Trigger , thereby generating an AAD Token based on Appfunction to trigger the Power BI Dataset refresh.

INSERT INTO dbo.DataSharkX
Select '1','1'

Response:

{“response”:{“status”:{“http”:{“code”:202,”description”:””}},”headers”:{“Cache-Control”:”no-store, must-revalidate, no-cache”,”Date”:”Tue, 29 Nov 2022 16:04:39 GMT”,”Pragma”:”no-cache”,”Content-Type”:”application\/octet-stream”,”strict-transport-security”:”max-age=31536000; includeSubDomains”,”x-frame-options”:”deny”,”x-content-type-options”:”nosniff”,”requestid”:”<<>>”,”access-control-expose-headers”:”RequestId”,”request-redirected”:”true”,”home-cluster-uri”:”https:\/\/<<>>-redirect.analysis.windows.net\/”}}}

Note : To refresh a dataflow, replace the @PowerBIurl variable within the Stored Procedure with the below value :

N'https://api.powerbi.com/v1.0/myorg/groups/<<WorkspaceID>>/dataflows/<<DataflowID>>/refreshes'

Also the Refresh API call is asynchronous. Hence, we do not know whether the dataset/dataflow refresh has actually succeeded. The successful execution of the Stored Procedure doesn’t mean that the refresh was a success.