Writeback to Synapse using PowerApps – Workaround (Part 1)

This blog will discuss how to achieve write back (writeback) functionality when data is in Azure Synapse.

Business Requirement

In some cases, data are in Azure Synapse and need write-back functionality. Power BI has a connector to Azure Synapse for reports and visualization, but PowerApps does not.

Please refer this blog for what is writeback and multiple ways to achieve the functionality or requirement.

Solution

Since no connector to Synapse, we can’t directly connect from PowerApps. I came up with the workaround to have an Azure SQL Server in the middle, which has a subset of data that is only required for writeback. This solution works perfectly since power automates writes to Synapse in near real-time. 

Architecture

  1. Using AzCopy moves the data from on-premises to Azure Synapse
  2. Another AzCopy (or via other Integration) from Synapse to Azure SQL Server
    1. Copy only the data needed for writeback
  3. PowerApps has a connector to Azure SQL to have writeback
  4. Power Automate automatically triggers for any update event
  5. Power Automate update Synapse

Power Automate Steps

  1. Power Automate get triggered when an update event happens in SQL
  2. Send this data to another table (History) for Auditing
  3. Get a record from Synapse for that Id
  4. Verify record exists for that id
    1. Insert new row/record if not exists
    2. Update the record if exists

Sample Flow (Click an image to enlarge)

Formula Used on the Flow

empty(outputs(‘Get_rows_from_Synapse’)?[‘body/value’])

Note: Insert and Update to Synapse steps uses “Execute a SQL query” action

 

Other Option

In some cases, you may have Azure Databricks for data analytics just before Synapse like below

Conclusion

For Power BI, the data source will be only Azure Synapse and user interaction is very smooth when using the “PowerApps embedded in Power BI” method.

 

Related Articles

https://learntoilluminate.com/2021/06/power-bi-write-back-functionality-comparison/

https://learntoilluminate.com/2021/08/writeback-to-synapse-using-powerapps-part-2/

6 comments

Leave a Reply

Your email address will not be published.