Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Model Design Best Practices

Introduces you to some generic best practices for the structure of an Adaptive Insights model covering levels, accounts, dimensions, and versions.

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. Still, some universal best practices may help improve performance and ease of use. Use these guidelines to design and build a solid foundation. 

Before You Begin

Required permissions: 

  • Model Management Access > Model
  • Model Management Access > Organization Structure > All Levels

How You Get There

Compass.png From the nav menu, click Modeling. Then, use the menus to get the part of the model you want to update.

Levels

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.

See Level Dimensions and Attributes.

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.

See Change Level Availability in Versions.

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. Adaptive Insights supports a “ragged” hierarchy. Remove forced 1-1 levels to improve usability and performance.  

Forced 1-1 Level Hierarchy Better Level Hierarchy

United States

  • California
    • San Francisco
    • Los Angels

United Kingdom

  • UK Sub
    • London 
    • Brighton

Mexico

  • Mexico Sub
    • Mexico City

United States

  • California
    • San Francisco
    • Los Angels

United Kingdom

  • London 
  • Brighton

Mexico City

Accounts 

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.

Formulas 

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.
Slower Better
divf(acct.headcount,acct.headcount[level=Top Level(+)])

divf(acct.headcount, assum.TotalHC) 

-----

assum.TotalHC is an assumption account that equals acct.headcount[level=Top Level(+)]

Custom Dimensions

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.

Versions

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. Adaptive Insights support 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.


  • Was this article helpful?