TECHNOLOGY PARTNERS

Enterprise Grade Planning & Reporting

Instant Source System Integration, New Excel online and desktop add in, Fastest time to results, now for Netsuite.

How to Write Back & Plan in Power BI?

First published November 27, 2020 | Acterys

Data entry, planning, and write-back are key aspects of the performance management process Power BI (out of the box) does not provide.

Acterys seamlessly integrates with the market-leading data discovery solution Power BI (and Excel/Excel Online) offering a comprehensive ecosystem for enterprise-grade consolidation, business modelling and planning.

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. Typical standard relational, disk-based approaches used by other tools can’t cater for processing large-scale multi-user write back.

What is Write-Back in Power BI?

Write-back allows real-time changes from the analytical system to the database. The analytical BI tool, “Power BI” doesn’t allow out-of-the-box Writeback functionality, however, Acterys allows Power BI write back to the database cache.

Writeback is crucial for analysing and forecasting data allowing the end-user to easily update the database in one interface. Also, in some cases, you can perform Power BI write back with Power Apps too.

Why Power BI Writebacks are Important

Power BI Write-back tools allow users to input and update data from Power BI to the database, improving performance allowing data visualisation in real-time.

Here are a few reasons Write-backs are important for Power BI:

  • Improved data forecasting and visualisations;
  • Increased data management; and,
  • Time saving and 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, Saasu etc.); or,
  • completely from scratch when users build your own dimensions and cubes in the Acterys Cube Wizard, Web Modeller, copy and paste from Excel or by importing data via nearly any ETL tool (e.g. SQL Server Integration Services, Azure Data Factory, etc.).

Comparing Planning options available on the Power BI Platform

Power BI Edit

This “Data Entry” option enables users to easily create simple new tables in Power BI. It’s only available to report admins on the backend and only for entering single records, so doesn’t really cater for planning requirements.

Good for adding new mapping for a few Power BI items but not usable for planning when requiring 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 cloud setup users 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.

While planning, users are dealing with complex data models like: financials, sales, HR details, etc. as well as sophisticated calculation logic. Working with these requires effective tabular entry (including the support of special financial report structures) 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.

Power BI Write-back and Planning Comparison

FeatureActerysPowerAppsOther Write Back Tools (Power on, ValQ)
Getting Started
InitiationCloud based self-service sign on/ billingDownload of an App or web serviceManual 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 modeller 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✔×✔