Some universal best practices may help improve your model's performance and ease of use. Use these guidelines to design and build a solid foundation. Model design is not a one-size-fits-all process. A model must meet your specific planning needs. It must also mirror the infrastructure of your business.
Before You Begin
- Model Management Access > Model
- Model Management Access > Organization Structure > All Levels
How You Get There
From the nav menu, click Modeling. Then, use the menus to get the part of the model you want to update.
Levels are the organizational structures of your business. For example, levels represent departments, profit centers, cost centers, or geographical regions.
Use Level Attributes Instead of Level Dimensions
An attribute is a logical grouping with a list of possible values. Use attributes to tag accounts, dimensions, and levels. Create attributes to represent different rollup hierarchies for your organization.
A dimension is an accounting characteristic with a list of possible values. Use dimensions in all types of sheets. A product dimension can represent the product lines and products sold by your corporation. A job dimension can represent the job titles assigned to employees. A level dimension can provide an alternate rollup structure for your organization.
You can associate attributes or custom dimension to levels. Use these points to help you choose:
- Level attributes are the faster way to do almost exactly what level dimensions can do.
- Level dimensions can do more than attributes in some unique cases.
If you're leaning toward dimensions, try attributes first. It's easy to switch from attributes to dimensions. It's more difficult to switch from dimensions to attributes.
If you are switching from dimensions, audit reports and formulas throughout your model. You must make necessary adjustments to preserve historical data.
Leverage Version Availability
Version availability is a level setting. The setting prevents you from accessing the level in certain versions. It's a way to retire a level without losing data.
Make the level unavailable on the current plan version to:
- Avoid the mistakes and confusion you risk when you only add “DO NOT USE” to the level name.
- Preserve historical data in older versions. When you delete a level, you delete the data associated with the level for all versions, even locked versions.
The setting works with actuals versions too. With actuals versions, specify the month and year to start hiding the level.
Remove 1-1 Levels
1-1 levels force hierarchical structures to match. For example, the USA geographic hierarchy is Country > State > City. The UK hierarchy is Country > City. A 1-1 level structure forces a mid-level in the UK to match the USA. The planning model supports a “ragged” hierarchy. Remove forced 1-1 levels to improve usability and performance.
|Forced 1-1 Level Hierarchy||Better Level Hierarchy|
Use Account Prefixes
To simplify formula debugging, use prefixes for your custom and metric account codes. Use M_ for all metric accounts and C_ for all custom accounts. This makes it easier to find these accounts in the formulas.
Use Calculated Accounts
Think about how calculated accounts can replace ad hoc formulas and shared formulas. Any data point that is a number or a grouping of other accounts can be a formula-driven account. If you find yourself writing a lot of formulas in sheets or reports, you can save time and effort by creating a calculated account. A calculated account uses a formula to automatically evaluate data. The values are then available to the entire model.
Replace Shared Formulas with Master Formulas
Use master formulas on custom and general ledger accounts if the formula is the same for all levels and versions (including actuals).
Use Custom Instead of Metric Accounts
Metric accounts recalculate at every level. Don't slow down the performance for simple mathematical equations. Use custom accounts for simple sums and differences. Reserve metric accounts for ratios.
Use IFF and DIVF
Replace IF and DIV with IFF and DIVF, respectively. IFF and DIVF reduce the number of evaluations the system must do.
Avoid Top Level (+)
Top Level(+) in formulas forces a recalculation of the rollup at every level. Instead either:
- Create an assumption account equal to that part of your formula. Then, reference the assumption in the formula.
- Use the Allocation Wizard to define the rules if allocations use the formula.
Custom dimensions allow you to add your own dimensions to the standard dimensions. Standard dimensions are levels, accounts, time, and versions.
Keep It Clean
Schedule time to remove unused dimensions and dimension values from your model. This improves performance and eliminates distractions.
Use List Dimensions
Understand the pros and cons of list dimensions. In general, use list dimensions when possible.
|List Dimension||Hierarchical Dimension|
|Can't use as level dimensions.||Can use as level dimensions.|
|Evaluates faster.||Evaluates slower.|
|Can use with attributes to create alternate rollups.||Rolls up according to a single hierarchy.|
Allows you to hide dimension values from specific versions with version availability.
Version availability is a dimension value setting. It prevents you from accessing the dimension value in certain versions.
|No version availability.|
Organize Your Versions
- Use folders to organize your plan versions.
- Move current versions to the top of the version list to save time. Your team won't need to scroll past years of historical plans to get to the current plan.
TIP: Save some effort and drop the current plan versions in 2nd position. Then drag the older one down.
Update the Left Scroll Limit
Schedule time to update your Left Scroll Limit. This reduces the size of the version for increased performance. It also removes irrelevant time periods from your sheets. See Move the Left Scroll Limit.
For default versions, you can't edit the left scroll limit. To work around this, temporarily make another version the default. See Change Your Default Plan Version.
Simplify with Virtual Versions
Create virtual versions for:
Constant currency reporting. Virtual versions combine the data from any version with the exchange rate from another version. See Create Constant Currency Reporting with Virtual Versions.
- Quick maintenance for reports that compare versions. Setup virtual versions for reporting content, such as Current Forecast and Prior Forecast. Then, build your reports using the virtual versions. When you update the virtual version's base version, all your reports and dashboards automatically update too. See Using Virtual Version to Reduce Regular Maintenance.
Synchronize with Your Source System
Consider letting your system of record define your models structure. The platform supports meta-data synchronization from your source system through the Integration capability. When you add a new account, level, or dimension to your source system, it automatically appears in your model. This approach is best if you plan at the same level that you capture data in the source system. You can also do a mix of approaches.
See Using Loaders.
Contact us to add Integration to your model if you don't already have it.
Reserved keywords and characters for naming dimensions and levels.
- Do not use "+" at the end of a dimension or level name. The “+” syntax is reserved for formulas.
- Avoid using commas in metadata, this can be problematic in formulas.
- Naming a dimension with a year, month, week, or day will impact your ability to use the dot notation. E.g.
- Avoid using the same name for dimensions and levels.