Early January 2018, Microsoft launched a new custom visual that enables users to enter data in Power BI. Basically, the custom visual opens up a Power Apps solution, checks permission for the end-user, and let end-users add, modify and delete data directly in the SQL Database. Now there are multiple ways to let end-users analyze and edit data in Power BI:

What is Write-Back?

Write-back has been around for a long time with mixed adoption by BI professionals. Microsoft introduced SSAS Multidimensional (OLAP) write-back partitions in SQL Server 2000, and still today many BI professionals are not aware of the feature.

Write-back is referred to as the ability for end-users to change and update numbers and text in the data mart or data warehouse, and immediately see the impact of the changes. This enables a rich user-experience to deploy solutions for Planning, Projections, Budgets and Forecasts.

Types of Write-Back

For Power BI you now have two options for your write-back needs:

Power ON’s Visual Planning	A complete Corporate Performance Management Solution for Power BI with Master Data Management, Goal Seeking and Visual Planning (Drag and Drop)
Microsoft Power Apps Custom Visual	The ability to view and edit records from Power Apps Power BI

That Microsoft introduces this new feature, show strong indications for the need and request for write-back features in BI solutions.

Comparison

The need and use for write-back can be very different. Traditionally Corporate Performance Management solutions like Cognos TM1, Hyperion, Anaplan, Adaptive and Host Analytics has been the tools of choice for handling write-back. The challenge with these solutions is that most of them are specialized to the finance function and have limited support for custom dimensions. All of them have limited visualization capabilities compared to Power BI. Other uses for write-back has been updating Inventory Levels, creating Sales Forecasts for Dynamics CRM or SalesForce, or just having the ability to add missing data points for the data mart or data warehouse.

Visual Planning is focused on helping organizations migrate from the legacy CPM solutions to a rich BI Solution like Power BI, while Power Apps have different areas it supports very well.

In order to make it easier for you to choose, we have put together a table that compares the two different approaches to write-back:

þ Fully Supported

ü Somewhat

ý Not Supported

*Feature*	Description	Power ON Visual Planning	Microsoft Power Apps
SQL Write-Back	Ability to edit data directly in a SQL table, and immediately observe the results.
Built for Budgeting Planning and Forecasting	Is the solution specialized for Corporate Performance Management solutions, and targeted towards the Business Analyst?
Tabular Write-back	Connect Power BI to a SSAS Tabular Model and enable write-back
Pivot Table (Matrix)	Can end-users enter data in Pivot Table style in PBI?
Excel Integration	Ability to view and change the same data in an Excel Environment
Access Control	Row Level Security for both Read and Write
Goal Seek	Goal Seek on SQL Data or DAX formulas
Text Commenting	Edit, Modify text comments related to the data
Rich Editors	Support for Drop-downs, Text Area, Check Boxes, Calendar Picker, Images etc…
Master Data Management	Ability for end-users to add or modify Dimension Tables.
Drag and Drop	Support for Visual Drag and Drop Features.
Spreading and Allocations	Built in Spreading and Allocation Features
Workflow / Approval	Support for Cycle Management, and Workflow Approvals.
Azure, Hybrid-Gateway	Does the solution support storing Data in Azure SQL or on-premises with a Gateway?
Report Server	Supports Power BI Report Server

Deployments

Both solutions are equally simple to setup and deploy. They both take advantage of the powerful Custom Visuals framework in Power BI. Basically, connect the custom visual to either Power Apps or Visual Planning framework, and add the dimensions to the visual you would like to write-back to.

Criticism

The reluctance from the BI Community to implement write-back in BI solution is founded on the belief that ‘no end-users should edit data’ in the Data Warehouse. Rightfully so, if done the wrong way. The solution, however, is pretty straight forward: Create a Scenario Dimension, and make Actual Data Read-Only(!!!). Now end-users can edit forecasts, budgets and projections through an interface connected to the model in either Power Apps or Power ON’s Visual Planning.

Conclusion:

If you would like to enable write-back to your BI solution today or would start migrating the CPM solution, please send us an email, or go to support link and submit a request for a demo.

Blog