May 31, 2019 Acterys

Power BI Planning & Forecasting – 9 Important Questions To Consider

The benefits of a unified analytics & planning solution as opposed to separate, disparate applications are clear cut:

  1. users work in only one environment
  2. have some of the most advanced analytics features immediately available with their planning data without any further data movement and
  3. With Power BI the benefits of the Microsoft ecosystem: integration with business productivity (Office) and cloud Azure. In addition to that, the open extensible architecture (custom visuals, r and Python integration) as well as an open marketplace provides unparalleled options to build on proven solutions and customize exactly to requirements.

Since Acterys started our Power BI integration 3 years ago and with the experience of hundreds of customer projects,  we have learnt a fair bit what works in that context. So, in this article I will cover some of the differences in approach and important questions to consider. We are admittedly not unbiased, but I have tried to cover the key points here as objectively and comprehensively as possible – if something as missing/incorrect please let us know.

Planning Options

Currently there are 3 types of planning solutions in Power BI:

  • Visuals connected to central model with standard in/outbound filtering
  • Iframe Web Apps and
  • Visual Only.

The central data model options are using a server-based data storage layer that caters for concurrent multi-user usage. Typically, in either MS SQL or SSAS. With the Iframe Web Apps it differs: – some are using SQL some are using their own proprietary data stores. The final category uses local data storage, specific to the user that can be exported to Excel or text files.

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.

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 it 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.

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.

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.

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.

7.    User rights

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.    Governance

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 AND on-premise

Ensure that the planning solution supports on-prem 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.

, ,
×