Power BI dashboards and reports are often created using a SQL database as a data source. In this article, we’re going to discuss 2 methods on how to write back to SQL from Power BI.
Power BI write-back to SQL: Use cases
We all use dashboards and reports on a regular basis to help give us insight into how our business is doing. Typically, by design, these are read-only, but what if you want to make changes to the data you’re viewing in a Power BI report, for example? Some common use cases for this are:
- Adding comments records in a report – e.g. you may want your sales team to provide comments and context to sales figures and have these saved to the database
- Providing status information – e.g. you could have a Power BI project status report in which users update the status of tasks
- Updating forecast values – e.g. your finance team has a dashboard showing actual vs target sales and costs and they need managers to update sales targets (or cost budgets) in their respective areas
In this article, we’re going to look at an example related to the last use case. We’re going to create a dashboard to display sales/cost data in a SQL database and provide 2 options for how general users can update the forecast information as conditions change without having to make changes in SQL Server directly.
Power BI write-back to SQL using Power Apps
The first method uses a special visualization type in Power BI – the Power Apps for Power BI visualization. When you add this visualization to a Power BI report, you are essentially embedding a Power App app into the report. This means that you can create edit forms that allow users to make updates to the underlying data source directly from Power BI.
To demonstrate how this works, we’re going to look at a simple example with the following components:
- Data source: we have a sample SQL database that contains a Forecast table and an Actual table. The data in the actual table is monthly summary data pulled from a full sales database, whilst the forecast data is going to be updated from a Power BI report.
- Power BI Report: the Power BI report contains some simple visualizations as well as the Power Apps edit form which allows users to make updates.
Step (1): Get the data
The data we’re going to use is in 3 tables (Actual, Forecast, Calendar) in a SQL database. Each table has a primary key.
Step (2): Create the basic Power BI report
For this example, we’ve just created 2 line/column charts and a date slicer. These show the actual vs target sales data and actual costs vs budget costs. For information on creating basic reports, check out our previous article.
Step (3): Add the Power Apps for Power BI visualization
When you add the Power Apps for Power BI visualization to a report, the following information is displayed in the visualization which explains the steps to take.
For this example, we’re going to add the following data fields from the Forecast table to the new visualization:
- DateKey
- Budget COGS, Budget Capex, Budget Opex
- Target Sales
Once these have been added, the Power App visualization will prompt you to select an existing app or create a new one. We’re going to create a new one, which opens a new canvas app in the Power Apps studio.
Step (4): Customize the app in Power Apps studio
When you click on ‘Create New App’, an app is created with a single page containing a data gallery. We’re just going to add a couple of extra controls to make this usable in our report:
- Edit form – this is so that users can edit/add data
- Submit button – this will save the changes in the form
Once we’re happy with the layout of the screen, we can save and publish the app and see it updated in our Power BI report.
With the edit form in place, users can now update the data in the Forecast table in SQL Server from the Power BI report.
Notes/limitations with the Power Apps for Power BI visualization
It’s important to consider the following when looking at using the Power Apps for Power BI visualization in your reporting solution:
- The maximum number of records that can be passed from Power BI to Power Apps visual using the “PowerBIIntegration” object is limited to 1000.
- The Power Apps visual can’t trigger a refresh of Power BI reports and Power BI data sources from within Power BI Desktop. In other words, when you edit and save data in the Power Apps edit form in our report example, the data write back from the app to SQL Server will only occur on the next scheduled refresh (or manual refresh).
- The Power Apps visual can’t filter the data or send any data back to the report.
- When you add the Power Apps visual to your report in Power BI Desktop, you should publish the report and continue the app creation and edit process from the Power BI Service UI.
Power BI write-back to SQL using SQL Spreads
As an alternative to embedding an edit form in our Power BI report, we can use the SQL Spreads Add-In for Excel to make direct updates to SQL Server.
In this solution, business users will view the Power BI reports/dashboards as normal, and make updates to the forecast data in Excel (through the SQL Spreads Add-In).
Step (1): Get the data
The data we’re going to use is the same 3 SQL tables (Actual, Forecast, Calendar) that we used in the previous method.
Step (2): Create the basic Power BI report
Again, the process of creating the basic Power BI report is the same as the previous method. The main difference, of course, is that we don’t need to add the Power Apps for Power BI visualization (meaning we have space for an extra visualization).
Step (3): Connect Excel to the database in SQL Server
With the SQL Spreads Add-in installed in Excel, we can connect to the Forecast table in our database and view, add or edit data in that table from within Excel.
Click on the SQL Spreads ribbon menu tab and then click on Design Mode. Next, click on the Edit Connection button in the SQL Spreads Designer pane and provide the details to connect to your SQL Server (more information on how to do this is here).
Once SQL Spreads is connected to SQL Server, you can then select the table you want to manage – in our example it is the Forecast table. As soon as you select the table, the data is retrieved from SQL Server and displayed as a table in Excel.
When you finished making changes, simply click on the ‘Save to Database’ button and the data will be written back to SQL Server.
You can now make changes to the forecast data or add new rows.
The data changes will reflect in the Power BI report the next time there is a scheduled refresh or manual refresh.
Summary
In this article, we’ve shown you 2 methods to use Power BI to write back to SQL Server.
The first method does this directly by using an embedded Power Apps edit form in the Power BI report. The second method does this indirectly by using the SQL Spreads Add-In for Excel to provide an easy way to update tables in SQL Server from within Excel.
The Power Apps approach gives a neat-looking solution but requires quite a bit of time and effort to set up. It also has some limitations, such as the 1000 record limit.
The SQL Spreads method, however, is quick and easy to implement and provides a comfortable interface for users to work in – Excel. It also scales very well, handling large volumes of data.
To find out more about SQL Spreads, visit www.sqlpreads.com and download the free trial.
