Writeback to Synapse using PowerApps (Part 2)

This blog will discuss how to achieve write back (writeback) functionality when data is in Azure Synapse and no additional information is needed to display it in PowerApps. We have been discussing writeback functionality through

1. Power BI writeback functionality comparison
2. Writeback to Synapse using PowerApps – workaround (Part 1)

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.

In the previous blog, we discussed copying the data to Azure SQL so that any additional information can be displayed from SQL. For example, Power BI displays some analytical visualization/graphs about companies. Based on the company selection business expects to display some additional information. In this case, we can store any additional information in Azure SQL from Synapse.

Solution

Since PowerApps has no connector to Synapse, we can’t directly connect. Workaround to have an Azure SQL Server in the middle, which is only used for writeback.

Architecture

1. Using AzCopy moves the data from on-premises to Azure Synapse
2. Databrick saves data to Synapse
3. Power BI uses DirectQuery to Azure Synapse
4. Power BI passes the context to Powerapps. PowerApps display the information through Power BI context
5. PowerApps has a connector to Azure SQL to have writeback
6. Power Automate automatically triggers for any update that happens
7. Power Automate update Synapse
   1. New table with proper keys
   2. Power BI joins this new writeback table and displays it

Power Automate Steps

1. Power Automate get triggered when an update event happens in Azure 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 uses the “Execute a SQL query” action

So what is the difference (Part 1 & Part 2)

No need  to copy data from synapse to Azure SQL if no additional information is needed to display in PowerApps. We use Power BI context to display data in PowerApps. So Azure SQL will be used only to store write back data.

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.
• In this approach, we minimize copying the data from the source.