Almost all enterprise visualization software (including Power BI, Tableau, Spotfire, Qlik etc.,) do NOT provide database writeback as a capability out-of-the-box.
One reason for this can be attributed to their origins in the BI platform ecosystem. It mainly consists of Online Analytical Processing (OLAP) systems that are read-optimized. Think of your enterprise data warehouse that is designed to read large volumes of data in the shortest possible time for faster reporting. These systems are not optimized for faster writing. In fact, it is NOT a recommended practice to update existing transactions in a data warehouse.
Contrast this against your Online Transaction Processing or OLTP systems (such as your ERP and CRM) that are write-optimized. The priority of these systems is to capture transactions in the shortest possible time so that the system can support a huge transaction volume.
Since visualization tools evolved from the former, they acquired the familial trait and focused little on writeback for years.
Why Writeback in BI & Analytics?
When you look at the reporting & analytics use cases, there are several scenarios that can be benefit using a seamless writeback capability. Let us look at five (5) examples:
- Persisting notes and comments added to a report. Annotations help report creators tell better stories. Report consumers can leverage features like commenting for interacting with other stakeholders and initiate conversations at a data-level.
- Integrating aggregated performance data with a benchmark (e.g., sales vs. budget). In an organization, detailed transaction data is typically available in CRM/ERP systems, while benchmark data such as budgets are maintained at a higher level in spreadsheets or planning systems. It is common for report designers to integrate them and deliver performance metrics with variances. Many times, the need arises for easy persistence of these integrated views for downstream processing.
- Creating new forecasts or projections – This is a very common scenario in functions such as sales where projections are created on a daily or weekly basis. Many users key-in their projections based on recent performance, and the values are consolidated and rolled up to the organization level.
- Adding or editing data directly in a report – The ability to directly add or edit specific data values in a report has its own use cases such as data enrichment, cleansing or validation. Whenever any edits happen on a report, the need to write back to a database automatically comes up.
- Storing calculations created by a routine or a procedure – Reports often include calculations created on the fly - such as variances and derivative KPIs. With the evolution of in-memory computing, it has become a standard practice to handle such calculations in-memory even for large datasets. This has given rise to a genuine need to write back such calculated KPIs for downstream use & processing.
With the lack of solid writeback capability, the above scenarios were not supported by most reporting tools. This had several unintended consequences.
The consequences of a poor or missing writeback capability
The absence of a solid writeback capability in BI tools led to the following consequences:
- Application & process silos emerged for analytical use cases that required any type of writeback. A good example for this is business planning & forecasting tools, the needs of which have been met by IT-centric third-party products or home-grown applications.
- Since these IT-centric products did not offer much speed & flexibility to users, the role of Excel in supporting these processes grew exponentially over time.
- When organizations driving transformations look to automate such Excel-based processes, they look to solutions such as bringing these Excel marts, say, into Power BI. However, the lack of a solid and user-friendly writeback threatens the success of such change management and digital transformation efforts.
The ability to write back in a modern BI stack is the new necessity. Let us look at an example.
The need for writeback in the modern BI stack – an example
Consider the sales forecasting process in most organizations. It looks mostly like this:
- Export the data from a (Power BI or another tool) report on to Excel
- Cleanse it, insert/delete some rows and columns
- Insert calculations
- Key in or update forecasts for the next cycle
- Add some comments, ask others to chip in with their inputs in Excel
- Validate the numbers, consolidate & publish final report management (in Excel or PPT)
For a modern BI stack to handle this, it’s users must be able to key in data, comment on data, and have data-centric conversations with their peers right within the reporting platform.
We are already moving in the right direction
Software vendors have started realizing the need for writeback in analytical applications.
For example, Snowflake, the popular cloud data warehouse vendor, has introduced HTAP (Hybrid Transaction & Analytical Processing) that supports applications optimized for both reading and writing back data.
Microsoft Power BI offers writeback capabilities through Microsoft Power Apps and Power Automate. However, setting this up needs considerable effort and skills that require business users to seek IT help. This is where a product like Inforiver can make the process more seamless.
Inforiver for seamless writeback in Power BI
Inforiver provides out-of-the-box writeback capability to Power BI users using a no-code user experience. Key highlights include:
- Support for unstructured writeback (you need not create database tables ahead of time in order to write back)
- Writeback to databases (such as Azure SQL and Snowflake) & online folders such as OneDrive
- Write back specific measures (instead of writing back all the measures in the report)
- Support for delta vs. full writeback, so that the whole data set need not be written back each time. Only the new rows or changed rows need to be inserted/updated
- Option to write back along with (or ignoring) totals & subtotals
- Write back row, cell & data level comments
- Write back filtered data (e.g., data for a specific sales channel in EMEA)
- Writeback based on user authorization & permissions
- … and more
Does your Power BI report support writeback? If not, attend our webinar on Manual Data Input, Comment & Writeback in Power BI