Write Back

Share:

Share on facebook
Share on twitter
Share on linkedin

How to Do Write Back & Planning in Power BI?

Data entry, planning, and write-back are key aspects of the performance management process not covered out of the box in Power BI.

Acterys was developed to address that by offering a comprehensive ecosystem for enterprise-grade business modelling, planning and consolidation that seamlessly integrates with the market-leading data discovery solution Power BI and Excel/Excel Online. The heart of the system is an OLAP engine that runs on a relational database (SQL Server on-premise or on Azure) that enables in-memory processing of the relational tables. A typical standard relational, disk-based approach used by other tools on the market can’t cater for processing large-scale multi-user write back.

What is Write-Back in Power BI?

Write-back allows changes in the analytical system to be reflected on the database in real-time. Power BI is an analytical BI tool and doesn’t support Writeback functionality out-of-the-box. However, tools like PowerApps and Acterys enable concurrent write back to a central data model.

Writeback is a crucial concept for analyzing and forecasting data. It allows the end-user to easily update the database without getting out of the analytical system.

Learn how Acterys helps writeback data to databases

Can you write-back in Power BI?

Yes, Power BI allows you the option to write back comments to existing tables in the database. For simple CRUD (Create, Read, Update, Delete) scenarios PowerApps is an option, for more complex requirements there are third-party solutions. Acterys for example not only automatically creates planning models and Power BI reports for the major accounting and SaaS platforms but also provides the most extensive range of planning capabilities to a central data model. 

Reasons for Writeback in Power BI

Writeback allows users to make changes to their data in Power BI and have those changes reflected in the reports and dashboards they create. This can be helpful for

  1. Collecting planning assumptions from global subsidiaries with immediate insights of the implications for the group as a whole
  2. Correcting mistakes and updating data,
  3. Creating and simulating different scenarios for example for driver based budgeting

The changes are either stored in the original database or in a separate write back table. Power BI’s mashup capability enables users to combine actual and plan data even if stored in different tables. Writeback can be used with both live and published datasets on Power BI. Equally it is possible to writeback to a Power BI data set accessed from Excel.

Benefits of Writeback in Power BI

1. The ability to writeback in Power BI is a powerful way to interact with your data and see the results of your changes in real-time.
2. It can be used to modify data in tables, charts, and other objects in your reports, as well as to create new calculated columns and measures.
3. Writeback can be a great way to test out hypotheses and explore your data, and it can also be helpful for troubleshooting issues or identifying trends.
4. Additionally, writeback can be used to save time when creating reports by automating the process of updating them based on changes in your data.

Write Back in Power BI with Acterys Power BI Sync

Take Acterys Power BI out for a test drive now!

Start Free Trial

Why Writeback and planning are Important for Power BI?

Power BI write-back tools allow users to input and update data from Power BI to the database. This improves the performance of the BI application as the same data can be visualized in real-time.

A few reasons writeback, planning and data entry are important for Power BI include:

  • They improve the data forecasting and visualizations
  • They increase data management
  • They save time and provide accurate reporting

The Acterys engine automatically generates optimal data warehouse structures either based on a variety of connectors (cloud-based accounting systems like Xero, QuickBooks, WorkflowMax, Dear Inventory, Hubspot, Stripe, Saasu etc.) or completely from scratch when users build their planning models as as required using the Acterys Modeller, Power BI Sync, copy and paste from Excel or by importing data via nearly any ETL tool (e.g. SQL Server Integration Services, Azure Data Factory, etc.).

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

Check out this helpful guide to select the right write-back approach for real-time enterprise-grade planning in Power BI

Read the Blog

How do the different planning options with Power BI compare?

Let’s compare different planning options available on the Power BI platform.

Power BI Edit Table

The simplest option is “Data Entry” which enables users to create simple new tables in Power BI. This is only available to report admins on the backend and doesn’t really cater for planning requirements as it is based on entering single records. Good for adding a new mapping for a few Power BI items but not usable for planning where you require multi-dimensional data (row/column/filters) entry not to mention more advanced options like top-down/bottom, allocations etc.

PowerApps

PowerApps is another option -for users happy with a cloud setup- to collect a few specific numbers/KPI’s on the base level and add records to a database. If that’s all you require perfect. Our customers frequently use it alongside Acterys, but it is unsuitable for matrix-based, planning and forecasting requirements. PowerApps also requires licenses that are available at a similar investment level to Acterys. Finally, PowerApps does not support guest users outside your domain. In Acterys you can add any user with their email address or Microsoft Account.

In the planning process, users are dealing with complex data models that include among others: financials, sales, HR details, etc. as well as sophisticated calculation logic. Working with these requires effective (including the support of special financial report structures) tabular entry and the option of entering data on any hierarchy levels with the respective distribution and allocation processing. All this is not supported in PowerApps and that is not what it is built for.

In the following table we are outlining how the different options to enter data in Power BI compare:

Power BI Write-back and Planning Comparison

Feature

Acterys

PowerApps

Other Write Back Tools (Power on, ValQ)

Getting Started

Initiation

Cloud based self-service sign on/ billing Download of an App or web service Manual purchase (Power on) from vendor (no self-service checkout)

Back end

Automatically generating optimal data warehouse structures

×

×

Processing write back/model changes in memory

×

×

Connectors to cloud-based accounting systems and SaaS

×

×

Audit trail and edit history that tracks all changes (who, what, when) for exact monitoring & control as well as simple undo.

×

Limited

AAD B2B support, single sign on

~

Front end

Modelling

  • Web browser-based modelling environment for business users to build and maintain models.
  • No install required.
  • Complete flexibility with dimensions: As many dimensions and hierarchy levels as required.
  • Cube Wizard Tool to support business users building new write-back models from any source
  • Copy & Paste from Excel. (Modelling in Acterys)

Limited to table edits (Power on), Power BI visual only (ValQ)

Instant Link (Option to avoid ETL and link parts of the data model as opposed to physically moving data but still read/write enabled) × ×

Planning / Writeback

Data Entry on Pivot Tables and Power BI visual

×

“Splashing”/data entry on any level of the dimensional hierarchy

×

Copy Wizard for data manipulation in Power BI & Excel

×

~

Write back on Excel report formulas in addition to Pivot Table

×

×

Comments

×

What is the best way to implement write-back in Power BI?

There are multiple ways to write back data from Power BI to your database. A few options available for writeback are:

  1. Power BI Edit Table
  2. PowerApps
  3. Acterys Power BI Tool

All these options allow you to write-back data from Power BI in real-time while updating the data visuals.

Can Power BI write-back to Excel?

Yes, you can export Power BI to Excel as static records of the data displayed in the current visual or as a formatted report (Acterys Reporting). In many cases, though, the better alternative is to establish a live connection to the Power BI data set in Excel  using “Analyze in Excel” or “Add Pivot Table” from Excel. This provides the users with a live link to the data that automatically updates when the data updates.

Can Power BI write-back to SQL?

Yes, Power BI can write back to SQL. The ability to write back to SQL is a key feature of Power BI, as it enables users to combine the power of BI with the flexibility of SQL. This allows users to create custom reports and analyses that meet their specific needs.

Another automated approach to write back to SQL server from Power BI is by using Acterys Power BI Sync. It offers features like advanced data table edit and real-time two-way writeback for all data available on Power BI and SQL server.

Acterys Offers Writeback with Power BI

Acterys provides the ability for Power BI write back to the database or to SQL Server. For further details on how the solutions compare and how Acterys can assist your business modelling and planning processes, please contact us.

Want to See How Planning at Hyper Speed Feels Like?

Take Acterys Power BI out for a test drive now!

Start Free Trial