Table of Contents
Microsoft Power BI is a powerful analytics and business intelligence tool that helps users connect, model and visualize business data and integrate those visuals into commonly used apps and services. Businesses looking to make data-driven decisions rely on the platform for meaningful solutions and insights. But even if you’re using Power BI planning and forecasting to streamline your financial planning and analysis (FP&A) process, there’s probably more you can do to maximize its impact.
We’ll dig into 13 important questions to consider when using Power BI for planning, including the two main types of planning solutions in Power BI. We’ll also discuss the data integration and underlying data models you should know about if you plan to use Power BI for business data integration and management.
How Do You Implement Planning, Forecasting and Write-Back in Power BI?
Power BI isn’t just an analytics platform. With the right approach, it becomes an extremely versatile and powerful platform for planning, forecasting and write-back.
Currently, there are two main types of planning solutions in Power BI:
- Visuals connected to a central model via a web service
- Visual only
What’s Visual-Only Personal Storage?
Visual-only personal storage uses data storage that’s attached to the specific user for sharing data that can only be exported to Excel or text files.
For either client-server or visual-only, read on for key questions you should ask, especially when looking to implement planning, write-back and forecasting in Power BI:
Where and How Is the Planning Data Stored?
The planning process typically requires tight integration with the systems that contain the actual data that you want to use for comparison. Enterprise planning scenarios require a closer look at the underlying technology:
- Are large-scale scenarios (more than 100 concurrent write-back users) 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 to enable peak performance?
You want to avoid extensive moving of data between data stores, particularly if they are proprietary, without the support of commonly used ETL tools and front ends. The visual-only approach is typically focused on simulations by a single user. As data isn’t 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 means data visibility isn’t limited to Power BI but can be used with any front end with up-to-date data access in a single, “one version of the truth” data model. This can include Excel, workflow processors or any other reporting or analytics solution.
How Can You Link to Your Existing Systems?
Check how easy it is to link to or integrate data from the relevant systems in your process, such as finance or marketing. Is a best practice Power BI model automatically created? Ideally, this process occurs even with data entry forms and reports.
With proven connectors to key systems (enterprise resource planning, marketing, etc.), you can avoid extensive efforts to write custom extraction transformation and loading (ETL) packages and ongoing updates. Make sure the integration:
- Has a reasonable number of customers and a high number (50-plus) consolidated entities used in the Power BI planning and forecasting process.
- Includes automated processes (not just manual handling of text files) for ongoing updates that don’t require a from-scratch custom project. Customization might be necessary for some specialized systems but not for common accounting systems, particularly for small and medium-size businesses (SMB).
Keep in mind that these integrations are quite complex and shouldn’t be underestimated, even for SMB systems.
What’s the Underlying Data Model?
The data model is the heart of your planning application. Verify that your modeling approach uses a central model that supports concurrent use and can be easily adapted by business users without requiring specific IT skills.
If you need to go beyond simple simulation by a single user, a shared data model will be required. The preferred choice is Power BI, which provides the option to use different components such as hierarchies (product, customers, etc.). Re-creating everything from scratch in a proprietary planning app is typically not optimal.
Additionally, check whether the vendor provides commonly required calculation logic (e.g., three-way forecasting) or if you can use existing logic in the model, e.g., DAX measures. If functionality is part of a proprietary (web) application, it’s typically much harder to customize than solutions built on widely used standards.
How Does It Integrate with Power BI?
Different approaches integrate with Power BI differently. When looking at solutions, ensure that the approach supports key Power BI features. You want to avoid solutions that run legacy web apps in iframes in Power BI or, worse, offer a separate application with a user interface similar to Power BI.
Make sure that your solution supports:
- Inbound and outbound filtering: With inbound filtering, clicking on an object causes all other visualizations on Power BI reports to adapt accordingly. With outbound filtering, a selection in a Power BI slicer will filter the planning data entry screen.
- Power BI drill down: Power BI offers flexibility for users to define drill-down paths as needed, even between different dimensions such as time and product.
- Custom tool tips support: Integrate report pages that automatically adapt to the context of the selected object.
Who’s in Control of Your Data?
Specialized IT skills are expensive and rare. You typically want to avoid a solution that requires technical specialists — for example, working with proprietary data stores or maintaining common tasks. Ongoing model maintenance should run within Power BI or other environments accessible to business users.
What Is the 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”). They’ll also want 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.). Consider the provided calculation logic and extensibility apart from data entry.
Having a proven logic is crucial. Ask about data entry and allocation options, such as Power BI demand forecasting or three-way planning logic (the relationship between income statement, balance sheet and cash flow). You want to ensure basic three-way processes are covered, such as calculating the implications of higher revenue on your balance sheet and cash flow.
What Are the Appropriate User Rights When Using Power BI?
It’s essential to understand the authentication platform in place. Are standards such as Microsoft Entra ID (formerly Azure Active Directory) supported, allowing existing network users and groups to be used? Or do you need to maintain a separate security layer within the planning app? Planning requires defining read and write permissions. Does the solution support this?
Security is paramount, but wasting time maintaining security layers beyond corporate standards usually isn’t justifiable, especially when you’re using proven standards with governance options, such as multifactor authentication or biometric credentials.
What’s the Role of Data Governance in Power BI?
Does the solution provide full transparency with detailed audit trails for every change, including who entered or changed data?
One of the key reasons for moving from a spreadsheet to a professional planning solution is transparency. Enterprises can’t afford unintended changes or edits that can be traced..
Cloud, On-Premise or Both?
Make sure that the planning solution supports on-premise and cloud deployment.
Planning data is as sensitive as it gets. Even with the best governance, many companies aren’t prepared to have cloud aspects involved in the solution. Confirm that both deployment options are supported.
How Do You Provide Quality Assurance?
From a quality perspective, you should check whether the solution is listed in Microsoft’s AppSource. Only custom visuals and apps listed there have gone through an extensive quality check by Microsoft.
Get Started with Power BI Planning and Forecasting
Now that you know the top questions to consider before adopting a solution, you’re ready to make informed decisions about your Power BI predictive forecasting and planning process.
Remember that you don’t have to solve all of these challenges on your own. Plenty of resources are available, whether you’re looking to improve sales forecasting in Power BI, create more accurate time series forecasting or seek additional data visualization and forecasting options.
If you need support answering questions about Power BI or your financial planning, budgeting and forecasting process, we’re here to help. Connect with us to learn how the smart, fast and effective xP&A suite of tools can help you better understand your business and enable strategic growth.
