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:
|
A complete Corporate Performance Management Solution for Power BI with Master Data Management, Goal Seeking and Visual Planning (Drag and Drop) |  |
|
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 |
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.
