Write Back into Azure SQL Database using Power BI in Power Apps
Before getting into the specific subject of this article heading, will just recap platforms being used here:
Power BI – is the cloud based self-service platform which is used for data visualization and analysis
Power App – is the no code / low code platform for developing business applications
Azure SQL DB – is the managed relational database service, used for storing and managing databases.
Power BI is being used extensively to connect of live data like Azure SQL to create easy-to-use dashboards, provides interactive reports, and delivers compelling visualizations.
However more often than not, people have repeatedly asks to update data through BI Visualization Tools. Though, it should be be done through business application while core objective of BI Solution has been in existence for report and information consumption layer
But, there are scenarios where end users still has requirements and needs that data should be updated through BI Interface with a seamless experience. Some of the examples be like updating Values related to Forecast, Budgets or adding Free Text Comments to the individual rows of records
New Power Apps Visuals Integration with Power BI comes to rescue for this requirements.
Using the PowerApps custom visual, you can pass context-aware data to a canvas app, which updates in real time as you make changes to your report.
Following is a simple step by step tutorial for the same:
1. Provision Azure SQL DB and configure a dummy database where we will use Comment is Blank and end user should have provision to give inputs into the comments
2. Create a Sample Report using Power BI Desktop using Azure SQL DB as source with SalesOrderDetail Table.
3. Create Sample Report using customer ID and line total and import Power Apps Visual
5. Select fields (CustomeID, SalesOrderID ) in this case to be used and passed as reference into Power Apps
6. Launch new Power Apps Creation Window from power BI Desktop, which will retain reference of the selected field for seamless interaction. Power App Screen is automatically created retaining Power BI Data Integration Reference and selected fields (Customer ID) are available here.
7. Add Edit Form for connecting with Azure SQL DB and add required fields like Sales Order ID and Comment (where Comments shall be updated). Also Update form Items formula as below to show selected values from customer ID;
Finally add Submit Button to update the form and some formatting changes for better look and feel
Finally Save and Publish Power App. Once Published it shows up in Power BI Desktop
Select a customer ID is selected from Power BI Visual, interactions are passed into Power Apps for updating of records.
Once Published same data input interactions are available on Power BI Portal for end user.
To summarize, this is how one can seamlessly interact with Power BI and Power Apps together while leveraging data management in Azure SQL so that data refreshes real time in the end user report as and when records are updated using embedded Power App.
Following resources can be referred to for more details :
very nicely put up. thanks for sharing Aneek. Chandramauli Govind Rahul Jain FYI
Great article Aneek Arora ! There is also a specialized solution that enables more comprehensive write-back to to Azure SQL database with 5 Power BI custom visuals: https://www.acterys.com It also automates creation of Azure SQL data models for various ERP and SaaS systems.
This is fantastic Aneek. In-Analytics data updates is the most desired feature in scenarios involving What-If Analysis, Planning and Budgeting and performance analytics (to capture comments and feedback). sravya potluri
Super helpful Aneek!