Here is a list of important questions regarding Power BI forecasting and planning processes 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 Writeback 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 planning solutions in Power BI:
- Visuals connected to a central model via web service
- Visual Only
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 other Microsoft technologies like MS SQL or SSAS or other proprietary platforms. The only end-to-end solution using that approach is https://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, tooltips, 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 addressing the following questions when you are looking to implement planning, write-back and forecasting in Power BI:
1. Where and how is the planning data stored?
Planning typically requires 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 the 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 by 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 a real-time response -even in complex scenarios- to ensure that the technological 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 Power BI 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:
- Is using a central model that supports concurrent use in the first place?
- 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.
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 dimensions e.g. time and product.
- Support of Custom Tooltips: 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.
6. Available planning logic?
Planning is typically more than just entering and aggregating a few planning assumptions. In addition to detailed 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 the 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 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 for 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.
