Writeback from a Power BI report to your data warehouse

Power BI is used to gain insights into business processes and to support the creation of business processes. This is a one-way flow of information, in which a user cannot, out of the box, simply send information back to, for example, an underlying data warehouse. However, this may sometimes be desirable for a business process.

Within the Power Platform, it is possible to create a Power App that you can place on the canvas in a Power BI report and that allow a user to submit data. However, if you want to send this data back to one of the premium connectors, such as an Azure SQL database, you'll need a premium license for each user. This license fees are in addition to the Power BI license and can quickly add up to a significant amount. 

Within the Azure ecosystem, however, it is also possible to create a feedback option for users without using Power Apps. One way to do this is by using an Azure Function. With Consumption hosting, you only pay the minimum cost each time you invoke the Azure Function, regardless of the number of users. In this case, we can use the Azure Function’s Managed Identity as a service account for authorization when the target is a service within the Azure environment, such as an Azure SQL database or an Azure Analysis Services instance.

In this blog post, I'll provide an example of how you can use an Azure Function for this purpose, based on the following use case:

At Company X, all purchase invoices with a total amount exceeding €1,000 must be reviewed by a financial controller. The data warehouse runs on an Azure SQL database. The report is located in a Power BI workspace and is connected to a semantic model hosted in Azure Analysis Services.

This is an example use case; for this solution, it doesn’t matter where the data warehouse or the semantic model is hosted, as long as it is accessible to the Azure Function. If we stay entirely within the Azure environment, the advantage is that we can use the System Managed Identity (SMI) for authentication; however, if the data warehouse runs outside of Azure, for example, access will need to be configured differently. 

Required Components

For this use case, we need the following components:

• Azure Function App
• Azure SQL Database 
• Azure Analysis Services
• Power BI workspace

Azure Function App

We'll create an Azure Function App under the Consumption hosting plan. Then we only pay when we call the function. We use PowerShell Core as our runtime stack.

After creating the Azure Function App, we need to enable SMI so we can use it for authorization. You can find it under Settings -> Identity. We'll need this as soon as we begin further developing our data warehouse.

Azure SQL Database

Our data warehouse is hosted in an Azure SQL database. We don't want users to be able to directly modify records in our data warehouse via the Function, so we're creating a new schema writeBack with a table POWriteback. We can then grant our SMI permissions for this specific schema, and in our semantic model, we can link this table to the PO data to retrieve these changes.

CREATE SCHEMA [writeBack]
GO
CREATE TABLE [writeBack].[POWriteBack] ([recordKey] varchar(25), [Content] varchar(25))
GO

We want to access this SQL from the Function, so we need to allow them to communicate with each other. To do this, in the SQL Server settings, under Security -> Networking the option Allow Azure services and resources to access this server on.
 

Now we need to add the SMI to the SQL database and grant the appropriate permissions. To add a user from Entra ID, use the FROM EXTERNAL PROVIDER option. For the username, enter the name as it appears in Entra ID, enclosed in square brackets. We grant permissions specifically on the newly created schema writeBack.

CREATE USER [] FROM EXTERNAL PROVIDER
GO
GRANT INSERT, UPDATE ON SCHEMA::[writeBack] TO []
GO

Azure Analysis Services

In this example, our semantic model is hosted in AAS. With the necessary configuration, this solution can also be used when the model is hosted in a different architecture, such as Fabric Capacity or Premium Per User. The relevant sections of our model look like this:

• Purchase Invoices: This table contains the purchase invoices from our source system.
  • recordKey: the primary key for an invoice.
• Purchase Invoice Write-Back: In this table, we store the statuses that we write back to the database. By separating them from the Purchase Invoices With this table, we can allow users to load only the statuses into the model, which is much faster than the complex logic of the Purchase Invoices to load the table completely.
  • recordKey: foreign key to Purchase Invoices
  • status: the current status of an invoice
• Purchase Invoice Write-Back Status: This table lists the possible statuses we want to assign to an invoice. It is used in a slicer to construct the Azure Function call.
  • status: a list of possible statuses
  • order: the order in which we want to sort the statuses

We want our Function App to also Purchases, Invoices, Writeback refresh the table so that a user can update their changes in the reports themselves. To do this, we need to add the SMI as an admin on the Azure Analysis Services instance. You cannot find the SMI in the Azure GUI, but you can add it manually in the format app:@.

Power BI

A location where you can host the final report, such as a Power BI Service Pro workspace, so you can collaborate with your colleagues. This does not affect the script's functionality in any other way, so this point will not be discussed further.

Azure Function PowerShell script

Now that we’ve configured all the components and the underlying permissions, we can create the Function to write data back to our data warehouse from a Power BI report. We’ve chosen a PowerShell Core stack for our Function App, so we’ll also write the scripts in PowerShell. We’ll use an HTTP trigger to invoke our Functions.

Writeback to the data warehouse

Input

Users must be able to submit input so that we can then process it. To do this, we need a key to identify the record associated with the status and to specify which status it should be assigned. We can do this using the code below.

param($Request, $TriggerMetadata)

# Configuration
$global:server = ""
$global:database = ""

# Get query parameters
$RecordKey = $Request.Query.RecordKey
$Content = $Request.Query.Content
$sqlParameters = @{
    "@RecordKey"=$RecordKey
    "@Content"=$Content
}

Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
    StatusCode = [HttpStatusCode]::OK
    Body = $body
})

In the Configuration block, we set the global variables for the server and database so that we can easily change them. From the web request, we retrieve a RecordKey which indicates which record the status belongs to, and Content is the new status we want to assign. We combine these two parameters in a hash table sqlParameters so that we can easily process them later in the code and remain flexible regarding the number of parameters we want to use.

We also want to provide feedback to a user when the code is complete. We do this by Push-Output Binding. This returns a response that we will develop further so that a user knows whether the status assignment was successful.

Authentication

To begin with, we need to retrieve an authorization token for Azure SQL databases from our SMI in the code. To do this, we make an API call to the Azure IMDS. It is also possible to use the Az modules to request a token via Connect-AzAccount and Get-AzAccessToken. However, the downside of the Az module is that it has a fairly long startup time, which would result in a user having to wait a long time. By using IMDS, we avoid this startup time.

function Get-AccessToken {
    param([string]$resource)
    try{
        $tokenAuthUri = $env:IDENTITY_ENDPOINT + "?resource=$resource&api-version=2019-08-01"
        $tokenResponse = Invoke-RestMethod -Method Get -Headers @{ "X-IDENTITY-HEADER" = $env:IDENTITY_HEADER } -Uri $tokenAuthUri
        $accessToken = $tokenResponse.access_token
 return $response.access_token
    } catch {
        throw "Failed to acquire managed identity token: $($_.Exception.Message)"
    }
}

param([string]$resource)
We use $resource as a parameter for the function. This is the Azure resource for which you want to request a token. By making this a parameter, we can reuse the function to request tokens for different resources. A token is valid for 1 hour. We already know that our function’s execution time is very short, so we don’t need to worry about this expiration time.

$tokenAuthUri = $env:IDENTITY_ENDPOINT + "?resource=$resource&api-version=2022-09-01"
An Azure Function App that uses a System-Managed Identity has an environment variable IDENTITY_ENDPOINT which contains the IMDS endpoint. We combine this with the Azure resource for which we want to request a token, and we specify a valid version of the API to use. This gives us the endpoint we will call to retrieve a token.

$tokenResponse = Invoke-RestMethod -Method Get -Headers @{ "X-IDENTITY-HEADER" = $env:IDENTITY_HEADER } -Uri $tokenAuthUri
This is how we make the API call. For authorization, we provide the environment variable IDENTITY_HEADER which Azure automatically creates when an SMI is active. This indicates that we are authenticating using the SMI. We then return the access_token from the $tokenResponse.

Everything is enclosed in a try{ } catch{ } block to handle errors and return them in the Function logging.

Executing SQL in the data warehouse

To execute the SQL, we create the following function Execute SQL . It takes the access token, the SQL query we want to execute, and the SQL parameters we want to pass as parameters. These are stored in the hash table created earlier sqlParameters. Here, we use the System.Data.SqlClient namespace to establish a connection sqlConnection to our data warehouse in Azure SQL via authorization with the SMI. The foreach loop iterates over all the parameters in our hash table sqlParameters and adds them one by one to our SQL command as parameters.

function Execute-SQL {
    param(
 [string]$accessToken,
 [string]$sqlCommand,
 [hashtable]$sqlParameters
    )
    $connectionString = "Server=tcp:$($global:server).database.windows.net,1433;Database=$($global:database)"
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = $connectionString
    $sqlConnection.AccessToken = $accessToken

    $sqlConnection.Open()
    $command = $sqlConnection.CreateCommand()
    $command.CommandText = $sqlCommand
    foreach ($key in $sqlParameters.Keys) {
        $value = $sqlParameters[$key]
        if ($value -is [int]) {
 $param = New-Object System.Data.SqlClient.SqlParameter($key, [int]$value)
        } elseif ($value -is [datetime]) {
 $param = New-Object System.Data.SqlClient.SqlParameter($key, [datetime]$value)
 } else {
            $param = New-Object System.Data.SqlClient.SqlParameter($key, $value)
 }
 $command.Parameters.Add($param) | Out-Null
    }

    $command.ExecuteNonQuery() | Out-Null
    $sqlConnection.Close()
}

We want users to be able to assign a status to a record and update it later. To do this, we'll execute a MERGE statement on the previously created data warehouse table [writeBack].[POWriteBack]. We'll use the function's parameters as input. Our sqlCommand becomes as follows

merge [writeBack].[POWriteBack] P
USING (SELECT @RecordKey AS [RecordKey], @Content AS [Content]) AS S 
 ON (P.[RecordKey] = S.[RecordKey]) 
WHEN MATCHED THEN
    UPDATE SET [Content]=S.[Content]
WHEN NOT MATCHED THEN
    INSERT ([RecordKey],[Content]) VALUES(S.[RecordKey],S.[Content]);

We merge the parameters RecordKey and Content which we get from the Function using the POWriteBack table. If there's a match, we update the status; otherwise, we add a new record.

PowerShell code

We'll put it all together and add some extra error handling so we can send this back to the user. The complete code will then look like this:

using namespace System.Data.SqlClient
using namespace System.Net

param($Request, $TriggerMetadata)

# Log the request
Write-Host "PowerShell HTTP trigger function processed a request."

# Configuration
$global:server = ""
$global:database = ""

# Get query parameters
$RecordKey = $Request.Query.RecordKey
$Content = $Request.Query.Content
$sqlParameters = @{
    "@RecordKey" = $RecordKey
    "@Content" = $Content
}

function Get-AccessToken {
    param([string]$resource)
    try {
        $tokenAuthUri = $env:IDENTITY_ENDPOINT + "?resource=$resource&api-version=2019-08-01"
        $tokenResponse = Invoke-RestMethod -Method Get -Headers @{ "X-IDENTITY-HEADER" = $env:IDENTITY_HEADER } -Uri $tokenAuthUri
        $accessToken = $tokenResponse.access_token
 return $accessToken
    } catch {
 throw "Failed to acquire managed identity token: $($_.Exception.Message)"
    }
}

function Execute-SQL {
    param(
 [string]$accessToken,
 [string]$sqlCommand,
 [hashtable]$sqlParameters
    )
    $connectionString = "Server=tcp:$($global:server).database.windows.net,1433;Database=$($global:database)"
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = $connectionString
    $sqlConnection.AccessToken = $accessToken

    $sqlConnection.Open()
    $command = $sqlConnection.CreateCommand()
    $command.CommandText = $sqlCommand
    foreach ($key in $sqlParameters.Keys) {
        $value = $sqlParameters[$key]
        if ($value -is [int]) {
 $param = New-Object System.Data.SqlClient.SqlParameter($key, [int]$value)
        } elseif ($value -is [datetime]) {
 $param = New-Object System.Data.SqlClient.SqlParameter($key, [datetime]$value)
 } else {
            $param = New-Object System.Data.SqlClient.SqlParameter($key, $value)
 }
 $command.Parameters.Add($param) | Out-Null
    }

    $command.ExecuteNonQuery() | Out-Null
    $sqlConnection.Close()
}

try {
    $accessToken = Get-AccessToken -resource "https://database.windows.net/"

    $sqlCommand = @"
merge [writeBack].[POWriteBack] P
using (select @RecordKey as [RecordKey], @Content as [Content]) AS S 
 on (P.[RecordKey] = S.[RecordKey]) 
when matched then 
 update set [Content]=S.[Content]
when not matched then 
 insert ([RecordKey],[Content]) values(S.[RecordKey],S.[Content]);
"@
    Execute-SQL -accessToken $accessToken -sqlCommand $sqlCommand -sqlParameters $sqlParameters

    $body = "Data saved successfully: RecordKey=$RecordKey, Content=$Content."
    $statusCode = [HttpStatusCode]::OK
} catch {
    $body = "Error writing to database: $($_.Exception.Message)"
    $statusCode = [HttpStatusCode]::InternalServerError
    Write-Host $_.Exception.Message
}

Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
    StatusCode = [HttpStatusCode]::OK
    Body = $body
})

We can now call this function from a Power BI report by entering the function's URL. You can find this URL under "Get function URL," and it has the following format.

?code=&recordKey=&content=

Since you can't include a URL body in Power BI, we include the invoice key and the new status as parameters in the URL.

Refresh Status Table

We also want to give users a button that allows them to update the status table themselves. This gives users the freedom to update the statuses more frequently without having to wait for the standard model refresh. We’re separating this from the other script because we don’t want to update the table after every status change. So, we’ll create a separate function within the same Function App for this purpose. We’ll reuse the Get-AccessToken function that we defined earlier. Then we can use the script below to update the required table. We use a parameter for the model and the table we want to refresh so that we can reuse this function. We’ve hard-coded the Azure Analysis Services instance into the function for now, but you could also add it as a parameter.

$refreshUrl = "https://$($global:region).asazure.windows.net/servers/$($global:server)/models/$modelName/refreshes"
$accessToken = Get-AccessToken -resource "https://*.asazure.windows.net/"
$refreshBody = "{""type"": ""Full"",""objects"": [{""database"": ""$modelName"",""table"": ""$tableName""}]}"

$response = Invoke-RestMethod -Method POST -Uri $refreshUrl `
  -Headers @{Authorization = "Bearer $accessToken"} `
  -ContentType "application/json" `
  -Body $refreshBody

$refreshUrl = "https://$($global:region).asazure.windows.net/servers/$($global:server)/models/$modelName/refreshes" This is the API endpoint for refreshes of our Azure Analysis Services. This is an asynchronous endpoint that returns a refresh operation ID. You can then use this ID to check the status of the refresh, and you can provide it to the end user, for example, for debugging purposes.

$accessToken = Get-AccessToken -resource "https://*.asazure.windows.net/"

is the function we created earlier to retrieve an authorization token. For an Azure Analysis Services token, we use the following resource: https://*.asazure.windows.net

in $refreshBody = "{""type"": ""Full"",""objects"": [{""database"": ""$modelName"",""table"": ""$tableName""}]}" We build the API body for our refresh call, specifying what needs to be refreshed.

in $response = Invoke-RestMethod -Method POST -Uri $refreshUrl -Headers @{Authorization = "Bearer $accessToken"} -ContentType "application/json" -Body $refreshBody We send a POST request to start the refresh.

We'll add some error handling and feedback for the end user, just like in the previous function, and then our complete code will look like this.

using namespace System.Data.SqlClient
using namespace System.Net

param($Request, $TriggerMetadata)

# Configuration
$global:region = "REGION"
$global:server = "SERVER"

# Get query parameters
$modelName = $Request.Query.modelName
$tableName = $Request.Query.tableName

# Functions
function Get-AccessToken {
    param([string]$resource)
    try{
 $tokenAuthUri = $env:IDENTITY_ENDPOINT + "?resource=$resource&api-version=2019-08-01"
        $tokenResponse = Invoke-RestMethod -Method Get -Headers @{ "X-IDENTITY-HEADER" = $env:IDENTITY_HEADER } -Uri $tokenAuthUri
        $accessToken = $tokenResponse.access_token
 return $accessToken
    } catch {
 throw "Failed to acquire managed identity token: $($_.Exception.Message)"
    }
}

try{
    $refreshUrl = "https://$($global:region).asazure.windows.net/servers/$($global:server)/models/$modelName/refreshes"
    $accessToken = Get-AccessToken -resource "https://*.asazure.windows.net/"
    $refreshBody = "{""type"": ""Full"",""objects"": [{""database"": ""$modelName"",""table"": ""$tableName""}]}"

    $response = Invoke-RestMethod -Method POST -Uri $refreshUrl `
 -Headers @{Authorization = "Bearer $accessToken"} `
 -ContentType "application/json" `
        -Body $refreshBody

 $body = "Refresh triggered for model $modelName table $tableName (RefreshID: $($response.operationId))"
    $statusCode = [HttpStatusCode]::OK
}catch{
    $body = "Error starting the refresh: $($_.Exception.Message) $refreshBody || $response || $refreshUrl"
    $statusCode = [HttpStatusCode]::InternalServerError
    Write-Host $_.Exception.Message
}

Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
    StatusCode = $statusCode
    Body = $body
})

We can also call this function again using the Function URL, and this time we'll pass the model name and the table to refresh as parameters.

?code=&modelName=&tableName=

Report Structure

Now almost everything is ready for our solution. We’ll create a slicer with *PurchaseInvoiceWritebackStatus[Status]* to select the status we want to assign to an invoice. And we have a table where, at the row level, we can click on a URL to update the status. To build the Function URL, we need to dynamically determine which invoice we’re working with and what the status should be, so we’ll create a metric for this.

[SelectedStatus] = SELECTEDVALUE('PurchaseInvoiceWritebackStatus'[Status])

This measurement value reflects the selected status. If no status or more than one status is selected, this measurement value is empty. This prevents users from accidentally selecting more than one status at a time.

[WritebackURL] = IF(NOT(ISBLANK([SelectedStatus])),
  MAXX('PurchaseInvoices',
    "{Function URL}?code={Function Authorization Code}"
    & "&RecordKey=" & [recordKey]
    & "&Content=" & [SelectedStatus]
  )
)

This measure creates the Function URL to write data back. As parameters, we pass back the invoice key and the selected status. We first check whether a status has been selected. If not, we do not display the URL icon using NOT(ISBLANK()). In the measurement’s metadata, set the data category to WebUrl; this tells Power BI that it’s a URL, allowing you to display it with the URL icon in your report instead of the full URL text.

[RefreshWritebackTable] = "{Function URL}?code={Function Authorization Code}"
  & "&modelName={Model Name}&tableName={Table Name}"

And with this measurement value, we can call the Function to retrieve only the status table Purchase Invoice Write-Back to refresh

Now we can combine everything and test the functionality. In our dummy dataset, invoices INV10001, INV10002, and INV10003 already have a status. We want to change the status of INV10005 to Approved and change the status of INV10002 from Under investigation to Rejected. Once we've made these two changes, we want to update the data in the report. By clicking the links in the relevant rows, we can update the statuses, and a pop-up will appear confirming that the update was successful. After that, we can update the data in our report with the yellow Refresh the status table button.

In conclusion

In this blog post, I provided an example of how you can use a low-cost Azure Function to write data back to a data warehouse and then make the changes available in a report.  By using security roles in your model, you can restrict access to this functionality to only the relevant group of colleagues. Depending on your organization’s architecture—such as where your semantic model is hosted and where your data warehouse runs—the scripts will need to be modified to establish a connection with those environments. Other architectural choices also influence the best approach for implementing this solution. For example, if your Azure environment runs within a private network and you already have a Gateway or Integration Runtime server running within that network, you can run the same solution in an Azure Automation Account with a Hybrid Worker.