Do's and Don'ts
This section describes a few best practice do's and don'ts when creating planning, budgeting, and forecasting models based on our experience and feedback from our partners and customers.
Do… | Don’t… |
|---|---|
Break up the process Consider each part of the planning process a different 'application', with a separate Power BI model and with one or a few separate storage tables. But reuse the same dimensional model (i.e. composite model or similar) | Create one huge model with one fact table Or add the planning capabilities into your reporting/analytics model. This will give you a model hard to manage and maintain, and unnecessarily big fact tables with poor performance |
Carefully consider the dimensions and the granularity in your planning models Use the same granularity on dimensions (especially date!) across both your planning and reporting applications | Plan and store values on a year-level, if all other planning is performed on a month level If you plan on months, do so for all values. If the value is static throughout the year (ex. social cost%), store this value on monthly level to simplify your DAX calculations. |
Use Kimball modelling I.e. star model with fact and dimension tables, with one-way relationships | Create snowflakes or bidirectional relationships in your model |
Build a mock-up Setting up mock-up reports in Excel together with the customer helps save time in the long run… | Assume the customer requirements Spend many hours setting up your Power BI model and applications without having the customer involved |
Walk through the calculation logic Build a "measure-tree" in Excel as a documentation of the model that is to come. Both input measures but also calculated DAX measures. This will visualize the calculation logic and show if you have granularity-issues that you need to address | Just do… Don’t create application after application without considering how they should consolidate or set up applications with minimal inputs without considering how to apply this input into correlated measures |
Consider when to use Import and Direct Query Use Direct Query in planning applications only, and only on tables that need to be live. Everywhere else, use Power BI Import mode to enhance performance. | Set all sources in Direct Query mode |
Headcount planning - use a Data Input Table Visual to manage new roles or employees and their metadata and the Reporting and Planning Visual to plan FTE headcount, salaries, and other employee measures per month | Use a table with start and end dates for personnel planning - Instead, use the Planning and Reporting Visual and input FTEs and salary per month. These could still be entered on a full year column if the aggregation strategy is set to average and your DateIntKey on month level |
Create at least two Scenarios - When starting your setup, this is important, or you might miss how the cross-joins and measures behaves when working with multiple scenarios | Filter on measures - Using measures as filters in Power BI might impact performance, as measures recalculate dynamically for each visual based on filter context, consuming significant resources. Instead, using columns or calculated groups for filtering is far more efficient and maintains better report responsiveness. |
Think about the naming - Will you use scenarios and/or measure keys to create logic in your model, i.e. "latest forecast"? In that case, this is extra important! Be aware of too complex setups When designing solutions for highly complex use-cases, consider what calculations need to be live for the planning-user (i.e. DAX) and what calculations could have delays, in which cases enabling logic in the database makes more sense (req. SLA3, customer managed Azure SQL). |
|