Write-back Options in Power BI – Not Everything is Equal

Over the last 7 years, Power BI has developed into the predominant self-service analytics solution. It was built for analyzing data, but the visualization and data modelling capabilities also make Power BI write-back the perfect platform for collecting data down to very sophisticated xP&A (Extended Planning & Analytics) processes.

The key arguments here are not just the unsurpassed visualization options that also enable dramatic improvements for planning processes but also the ease of deployment and development. The only thing that a user with a Power BI planning process requires is a web browser. As opposed to spreadsheets, Power BI reports are secure and protected by default. Moreover, in difference to legacy FP&A applications, planning process designers just require widely available Power BI knowledge not fairly rare (and expensive) expertise from proprietary legacy planning application subject matter specialists. 

7 years into the birth of the solution a number of vendors now claim to offer “Power BI write-back solutions” but there are significant differences in the approach with equally differing use cases that I will cover in this blog. 

What Does Write-back Mean in Power BI?

Power BI Write Back is a solution that allows users to directly edit data within Power BI, enabling changes to be made to databases while maintaining records in the source system. 

Approaches to Write-back in Power BI

Power BI currently supports 3 options:

1. Write back options for manually adding records to a write-back table or “Columns from Example” in Power BI Desktop
2. Write back stored in a visual
3. Write back from Power BI to a central data store

The diagram below outlines this nicely:

Let’s see how these approaches differ (and the differences are huge!)

1. Power BI write-back table

The Power BI write back table enables users to edit single records developers in Power BI desktop this is useful for small tables with a few records e.g., report subtotal categorizations.

2. Write-back to a visual

A relatively new option that has been around for a few years is write back to a visual. This is useful in single user scenarios, for example, for simulating a small subset of metrics or to store visual parameters. This approach is also used in visuals that aim to offer an “Excel experience” in Power BI where users have the option to edit data and formulas. 

This can be beneficial in single user scenarios and custom calculations report requirements but – despite the vendors’ claims – is not suitable for enterprise planning scenarios. Reason: only very limited amounts of data (at maximum a few thousand records – a tiny fraction of the million that Excel can handle…) can be stored and processed in the visual.

Another limitation here is that the only option to write back is just that one Power BI visual. There is no “live access” possible from other client tools e.g. Excel and the only option for a user is to export static data into an Excel workbook which more often than not ends in the dreaded “Spreadsheet Hell”.

So, users are back to the dilemma that they were initially trying to solve. Some vendors offer more or less smart syncing options where the data from the visual is synchronized to a data store table. Even if this is well implemented (which the ones that  I am aware of are not, as they synchronize denormalized tables which sooner or later leads to total model chaos, particularly with changes in dimensions, like a changing account group mapping), this cannot cater to real-time enterprise planning requirements. 

3. Write-back to a central data store

This brings me to the final option: writing back to a central data store – in my opinion, the only feasible option for enterprise scenarios.

These “real “enterprise planning scenarios” require concurrent write back by many users to a central data model. Preferably a widely supported database standard that enables access with different frontend options like SQL Server. Here access (AND write-back rights!) needs to be restricted by detailed (and often dynamic!) security and workflow rules. Of key importance is that ALL users get real-time access to model details at that particular point in time without having to synchronize a variety of sync tables.  

Examples of solutions that use this approach are PowerApps for record-based CRUD (editing table records: create, read, update, delete) requirements, or specialized solutions like Acterys. 

The benefits of this approach are close to unlimited processing power and data volume handling capacity. In case a cloud-based data store is used, the additional advantage is being able to flexibly control that. For example, ramping capacity up during planning times and reducing it when it’s not needed.

Furthermore, users benefit from being able to have a frontend of choice e.g., using standardized budgeting forms in Power BI but then also having the option of having a live connection from Excel for ad-hoc analysis.

Finally, this method caters for enterprise grade, advanced planning capabilities like:

• Entry on any level of the model top down/bottom up with flexible allocation options e.g. “like last year”
• Advanced forecasting methods defined in standards like r/Python
• Workflow logic either in the data model or provided by standard solutions like Power Automate.

Acterys takes this even further by taking care of the modelling process: that many argue are the most important part of any analytics and planning project. This is done by either completely automating model creation/update for major finance, CRM and billing systems or providing business users with the option to build and edit planning models generically from any source like an existing Power BI model. 

—

In conclusion: Power BI offers unparalleled options for enhancing processes that require planning and write-back, but it is very important to understand the different flavors as covered in this blog. Write-back is not always, truly write-back.

Single user scenarios can be covered as a “visual only” option any process that requires more complex scenarios will always require a proper central data model (“star schema”). In an ideal world, logic e.g. for calculations is as integrated as possible in the Power BI model or its data source to avoid “model in a model” scenarios that lead to complex maintenance, limited usability, and restricted front-end options.  

Want to see how Acterys’ write-back & planning capabilities can meet your enterprise-grade planning and analytics needs? Start your free trial now or book a meeting with our team.

Key Takeaways

1. Power BI has evolved into a leading self-service analytics solution with extensive capabilities, making it suitable for complex planning and analytics processes.
2. The Power BI write-back table is suitable for small tables with a few records, while write-back to a visual is useful for single-user scenarios but not for enterprise planning.
3. Writing back to a central data store is the most feasible option for enterprise planning scenarios, allowing concurrent write-back by multiple users with real-time access to data.
4. Solutions like Acterys use the central data store approach, providing unlimited processing power, data volume handling, and advanced planning capabilities.

FAQs

1. Why is Power BI used for xP&A processes?

Power BI provides ease of deployment and development, making it an ideal platform for collecting and analyzing data for complicated planning processes.

2. What are the three write-back options in Power BI?

Power BI supports three write-back options: 

• a write-back table
• write-back to a visual
• write-back to a central data store.