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/

10 comments

  1. Hey Stalin, Do you have to run AzCopy everyday or is a one time thing? If it is daily, can it be automated.

    1. Hi Filimon,

      Yes, we have to run AzCopy whenever data changes in Azure Synapse. For one client, We have copied from on-premises to Synapse everyday night. So we run this AzCopy after that job is completed.

      1. Ok Stalin.

        Is there any way to automate the change from synapse to SQL either with AzCopy or without?
        Also Did you face issues passing inputs directly to Power Automate instead of Azure SQL?

        1. We can use the Azure Data Factory to load data from Synapse to SQL. We can call Power Automate directly from PowerApps but not better architecture. Users may not see the latest data When they try to edit them in PowerApps since Synapse to SQL is not near real update.

Leave a Reply

Your email address will not be published.