You have probably explored using Microsoft Power Apps for data writeback from a Power BI report. In one of our previous posts, we learnt how this approach is a bit harder than it should be for business users, and yet more restrictive for advanced use cases.
Since BI platforms & tools are optimized for reading large volumes of data – there is no integrated native solution that offers writing back data to SQL database destinations. Given this, you may also have explored custom solutions or third-party visuals for Power BI that provide write-back capabilities.
Yet, you mostly wonder which solution offers the right approach, and how to choose one that works best for you. You want the solution to be simpler, deliver value to your users, and stand the test of time by continuing to support your evolving needs.
Here are a few considerations that you need to keep in mind while evaluating a writeback solution in Microsoft Power BI.
We shall explore each of them in detail.
A structured writeback is one where you traditionally have the underlying database tables defined by IT and set up in advance so that you can write to it from your Power BI report. Most planning solutions available in the market today for Power BI (and other platforms) follow a structured writeback.
An unstructured writeback is one where you do not need to have the database tables created in advance. The application takes care of it for you. A main benefit is the user-friendliness of this approach, as you can add additional columns or measures even at the last moment. As a result, this cuts down reliance on your IT team and the time-to-realization of value is instantaneous.
Thus, if you have a strictly defined process and you know exactly what you need to get out of your writeback, the structured writeback would work for you. However, such a setup conflicts with the principles of the modern agile process and the end-user-friendly paradigms of the modern BI stack. On the other hand, if you have users who like to move at the speed of thought or have different requirements each day, an unstructured approach naturally works best.
Most solutions support writing back to a SQL server database. Furthermore, ensure that you go beyond this and verify if it supports writing to Snowflake, Azure SQL DB, OneDrive (in Excel), REST API URL destinations, etc.
For instance, let’s say you have a model with a large number of rows in your Power BI report, and you have created the forecast and written back to your database. In the future, if you need to update the forecast for a specific product in a specific region, you can use the delta writeback and avoid writing back all the records.
In a table or a matrix report, you will have one or more row or column totals & subtotals. When you write back, it is preferable not to persist subtotal and total records. It is because they are simply aggregation of other existing records. There may be some exceptions where you may want to write back to a database along with these totals. Ensure that your writeback solution in Power BI offers flexibility to write data – with or without totals.
If your table or matrix supports commenting, ensure that you are able to write back comments to your database or a file. It is not of much help for a user to input comments in your report only to realize later that it cannot be persisted outside your report.
While row-level commenting is very common in reporting solutions, cell-level or column-level commenting can elevate your reports to a different level. If your solution supports it, ensure that these can be written back too.
Often, we work with several measures while we forecast and simulate outcomes. For example, we may have sales actuals and forecast, quantity shipped and profit margins for the prior periods, and we may be forecasting sales for a future period. In such cases, it helps to write back only the forecast measure for the future period rather than writing back all the measures.
Think about this - If the CRM database containing the actuals gets updated after we do our write-back, our saved data would be out of sync. It’s best to write back only what we create or modify.
Sometimes, we may be working with a larger data set, but we may be updating only the records belonging to, say, two specific regions. In such cases, you can trigger writeback using a filter, so that records belonging to other regions are ignored. The ability to change this filter criteria on-the-fly becomes important too.
Of course, we do not want everyone in the organization to start writing back data from your report. Check if the writeback solution for Power BI provides a way to authenticate and authorize specific users to write back.
One key consideration you must have while evaluating the writeback solution in Power BI is to get an understanding of how long it would take to deploy the first writeback solution. Another question to explore is the lead time taken for writeback of subsequent reports – as there may be a lot of backend set up and preparation required each time depending on the solution deployed.
There are writeback solutions that ‘technically’ check the list, but navigating and performing an effective writeback using them is often a struggle. Ensure that the user experience is highly satisfactory. This is one of the most significant factors that guarantees whether your users would keep coming back to the report.
Inforiver for Microsoft Power BI delivers out-of-the-box and user-friendly writeback capability to Power BI users using a no-code user experience. Key highlights include:
Go ahead and use the above checklist to compare writeback solution options for Power BI.
To learn more, view our webinar on key writeback options using Microsoft Power BI.