Write-back Options in Power BI

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)  Visual Planning Menu
  • Microsoft Power Apps Custom Visual
The ability to view and edit records from Power Apps Power BI  image

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.

image

image

Built for Budgeting Planning and Forecasting Is the solution specialized for Corporate Performance Management solutions, and targeted towards the Business Analyst?

image

image

Tabular Write-back Connect Power BI to a SSAS Tabular Model and enable write-back

image

image

Pivot Table (Matrix) Can end-users enter data in Pivot Table style in PBI?

image

image

Excel Integration Ability to view and change the same data in an Excel Environment

image

image

Access Control Row Level Security for both Read and Write

image

image

Goal Seek Goal Seek on SQL Data or DAX formulas

image

image

Text Commenting Edit, Modify text comments related to the data

image

image

Rich Editors Support for Drop-downs, Text Area, Check Boxes, Calendar Picker, Images etc…

image

image

Master Data Management Ability for end-users to add or modify Dimension Tables.

image

image

Drag and Drop Support for Visual Drag and Drop Features.

image

image

Spreading and Allocations Built in Spreading and Allocation Features

image

image

Workflow / Approval Support for Cycle Management, and Workflow Approvals.

image

image

Azure, Hybrid-Gateway Does the solution support storing Data in Azure SQL or on-premises with a Gateway?

image

image

Report Server Supports Power BI Report Server

image

image

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.