Share:

Share on facebook
Share on twitter
Share on linkedin

Power BI Planning & Forecasting – Important Questions To Consider

Here is a list of important questions regarding Power BI planning and forecasting that you should know about if you are using or going to use Power BI for business data integration and management.

How to Implement Planning, Forecasting and Write back in Power BI?

Power BI is the leading analytics platform – with the right approach it can be turned into an extremely versatile and powerful platform for Planning, Forecasting and Write back.

Currently there are 2 main types of imoleplanning solutions in Power BI:

  • Visuals connected to a central model via web service
  • Visual Only

    Power BI Planning Architectures
    Power BI Planning Architectures

Client-Server approach

This architecture uses a central data model that supports concurrent multi-user write-back. Storage is typically, in either in other Microsoft technologies like MS SQL or SSAS or in other proprietary platforms. The only end-to-end solution using that approach is https://www.acterys.com The Important question to ask with this architecture is if the solution makes use of all the Power BI features like in/outbound filtering of selections, Power BI drill down, tool tips etc. or if it’s just an existing web application that just runs completely independently within Power BI (Iframe Web Apps).

Visual Only Personal Storage

This category uses data storage that is attached to the specific user, for sharing data can only be exported to Excel or text files.

For either approach, we recommend to address the following questions when you are looking to implement Planning, Write-back and forecasting with Power BI:

1.    Where and how is the planning data stored?

Planning typically requires a tight integration with the systems that contain the actual data that you want to compare the plan to. For enterprise planning scenarios it is relevant to look at the underlying technology:

  • Are large scale (> 100 of concurrent write back users) scenarios supported?
  • Does the solution enable real time response even in cases with very complex calculation logic?
  • Does the solution support in-memory handling of data which is typically the best way to guarantee peak performance in enterprise scenarios with real-time response requirements.

Why is it relevant?

You typically want to avoid extensive efforts to move data around between different data stores particularly if they are proprietary without support of commonly used ETL tools and front ends. The visual only approach -mentioned above- is typically focused on simulations by a single user. As data is not stored on a central server, the only way to share results is exporting to Excel workbooks or text files. An open, accessible data model will ensure that visibility of the data is not limited to Power BI but can use any front end with up to date data access in a single, one version of the truth data model. This can range from Excel, workflow processors to any other reporting or analytics solution. Planning requires real-time response -even in complex scenarios- ensure that the technology approach can handle this.

Power BI Planning Data Integration
Power BI Planning Data Integration

2.    How can you link to your existing systems?

Closely related to the previous point: check how easy it is to either link or integrate data from the systems that are relevant in your process (typically finance/marketing) and if a best practice Power BI model is automatically created (optimally even with the data entry forms and reports).

Why is it relevant?

Proven connectors to the ERP/marketing or other systems typically help to avoid extensive efforts to write custom ETL (extraction transformation and loading) packages and ongoing updates. Make sure the integration is:

  • Proven with reasonable numbers of customers and with high (>50) numbers of consolidated entities used in the planning/forecasting process,
  • Includes automated processes (not just manual handling of text files) for ongoing updates and does not require a custom project where everything has to be developed from scratch. For more specialized systems this might really be necessary but common accounting systems particularly in the SMB space should be covered. Keep in mind that these integrations -even for SMB systems- are quite complex and shouldn’t be underestimated.

3.    What’s the underlying data model?

The data model is the heart of your planning application. Check if the used modelling approach:

  1. Is using a central model that supports concurrent use in the first place?
  2. Can it be easily adapted by business users without specific IT skills?

Why is it relevant?

If you need to go beyond simple simulation by a single user a shared data model will be required. The preferred choice here is, that this model is Power BI with the option that you can use all parts of it like hierarchies (product, customers, etc.).   Recreating everything from scratch in a proprietary planning app is typically not optimal. Also check if the vendor provides you with the commonly required calculation logic (e.g. 3-way forecasting covered further down below) and can you use your existing logic in the model e.g. DAX measures?

If functionality is part of a proprietary (web) application, it is typically much harder to customize than solutions that are built on standards like widely used data stores, support of workflow standards like MS Flow, integration with Office (e.g. Excel) productivity etc.

4.    How does Acterys integrate with Power BI?

The different approaches vary greatly in the way how they integrate Power BI. When looking at solutions ensure that the approach really supports key Power BI features and is not just a marketing gimmick where a legacy web app runs in an iframe in Power BI or worse a completely separate application with a UI similar to Power BI.

Power BI custom tool tip filtering
Power BI custom tool tip filtering

Why does it matter?

Make sure that the solution supports:

  • Inbound and outbound filtering: one of the biggest benefits in Power BI is interoperability: You click on an object and immediately all other visualizations on the report adapt to that detail or in reverse: a selection in a Power BI slicer filters your planning data entry screen. Running a web app page that doesn’t really interact or make use of the core Power BI features is typically only a compromise.
  • Power BI Drill Down: Power BI offers complete flexibility for users to define drill down paths exactly as needed even between different dimension e.g. time and product.
  • Support of Custom Tool tips: the ability to integrate another report page that automatically adapts to the context of the selected object.

5.    Who is in control?

Check with your vendor how the solution can be maintained.

Why is it relevant?

Specialized IT skills are expensive and rare. You typically want to avoid that the solution requires technical specialists (e.g. working with proprietary data stores) for maintaining common tasks. Ongoing model maintenance (adding, changing models/elements, mappings etc.) should preferably run within Power BI or within easy to use web or other environments built for use by business users.

Driver based planning in Power BI
Driver based planning logic and visual planning in Power BI

6.    Available planning logic?

Planning is typically more than just entering and aggregating a few planning assumptions. In addition to detail data, users will likely require the option to enter and simulate on aggregated levels (“Top down”) with the option to break down these assumptions according to a desired allocation method (e.g. based on last year’s results, using a particular seasonality, etc.).  Apart from data entry consider the provided calculation logic and extensibility.

Why does it matter?

Having a proven logic as opposed to developing everything from scratch is crucial. Check what data entry and allocations options are available. This ranges from demand forecasting to 3-way planning logic (the relationship between income statement, balance sheet and cash flow).  You want to make sure that basic 3-way processes like calculating the implications of more revenue on the balance sheet and cash flow are covered.

User Rights Administration Write/Read in Power BI
User Rights Administration Write/Read in Power BI

7.    User rights when using Power BI

What’s the authentication platform e.g. are standards like (Azure) Active Directory supported where the existing, normal network users and groups can be used or do you have to maintain a separate additional security layer in the planning app? Planning does not just require the definition of read rights (what can I see) but also write (where can I enter data) rights. Does the solution support this?

Why does it matter?

As with any data these days but particularly in the financial space security is paramount. Wasting time maintaining additional security layers outside of corporate standards are not justifiable. Particularly around security using proven standards with extensive governance options (MFA, bio-metrics (facial recognition, etc.) used by millions of users is an important aspect.

8.    Data Governance in Power BI

Does the solution provide full transparency with detailed audit trails for every change from who entered or changed data to all changes in the data model?

Why does it matter?

One of the key reasons of moving from a spreadsheet to a professional planning solution is transparency: enterprises can’t afford unintended, undocumented changes without any way to track them. Make sure that these aspects are covered.

9.    Cloud or On-premise or Both?

Ensure that the planning solution supports on-premise and cloud deployment.

Why does it matter?

Planning data is as sensitive as it gets. Even with the best governance, many companies are still not prepared to have any cloud aspects involved in the solution. Ensure that both deployment options are supported.

10.   Quality Assurance

From a quality perspective it’s important to check if the solution is listed in the official Microsoft App Store for Power BI: Appsource Only Custom Visuals and Apps listed there have gone through an extensive quality check by Microsoft.

These are the top questions that you should look at before integrating your data with Power BI for planning and forecasting.

 

Recent Posts

Top 5 Tips & Tricks For Planning, Budgeting and Forecasting in Power BI & Excel In this blog I am

Planning and Analytics [Infographic]

It is not uncommon for companies to have separate planning and analytics processes or legacy solutions that result in more

Materialize and Synchronize Power BI with a Data Warehouse “Materialize” is a database concept that refers to storing the result

I can’t help smiling when I see legacy BI/Planning application vendors -that were not so long ago laughing away Power BI- coming out with their own “Power BI Integration” after Microsoft has turned around the industry. These are more often than not nothing but “a marketing spiel” to build on Power BI’s awareness and of very limited use in real life application scenarios.

Across many mid-sized and large companies, finance and operations teams are at crossroads. This was one of the findings from the recent BPM Pulse 2021 survey which revealed that only a third of companies had truly harmonized strategic, financial, and operational plans.