Chapter 1

Time

1.01-01 Never use SELECT with Time

This goes against the Sustainable element of PLANS and the hard coding can cause issues when updating the timescales of the
model. Modules with Time formatted items to be used as sums and lookup are the better option

 

Exception:
1.01-01a Generic Time periods: It is OK to use SELECT with Generic Time periods such as Actual Period, Current Period, YTD, YTG, ALL Periods

 

Related to Rule:
2.02-14 Avoid using SELECT

 

1.01-02 Use the Model Calendar by default

As the Model Calendar is dynamic, there are a lot of advantages in using it for the majority of modules.  Choose suitable time settings (past and future years) that cover most of the requirements and use the model calendar to set these in the most part.  For the exceptions outside of the “norm” use time ranges for efficiency of calculations and for keeping the model size under control. But remember, Time Ranges may need to be updated manually on a year end rollover.

 

Best Practices article:
Time Range Application

1.01-03 Use Current Period

Utilising the Current Period within the Time Settings allows the use of CURRENTPERIODSTART(), CURRENTPERIODEND() and can be used to create lookup modules to hold the current period automatically.

 

Exceptions:

  • 1.01-03a Daily Timescales: The lowest granularity for Time Settings is weekly, so if the Current Period needs to reflect a daily timescale, the Current Period setting cannot be used
  • 1.01-03b Non-Admin updates: Should you need non- Workspace Administrators to update the setting, a formatted line item can be used instead
1.01-04 Consider All Periods

Consider the use of All Periods.  This is effectively the Top Level for time and whilst increasing the model cell count very slightly it does allow for flexibility in modelling.  This is especially useful if you need to reference the same calculation many times

1.01-05 Exclude timescale subtotals by default

Turn the "include" settings off by default and only include these if absolutely necessary.  These additional settings (Quarter totals, Year to date, Year to go etc.) will be included in all modules with the model calendar as the time dimension. Thus they will perform calculations on these subtotals.  Consider if you really need these values for all modules with the Model Calendar.

Note, you can specify different "include" settings for different Time Ranges, although by default Time Ranges will inherit the Model Calendar settings.

 

Related to Rules:

1-01-02 Use the Model Calendar by default 
1-01-07-Time-Ranges 

1.01-06 Time Range Naming

Keep the naming short using the FYxx-FYyy format. This format allows the administrator to see the scope and name of the Time Range in the module blueprint without referring back to the Time Range itself; it fits within the column of the blueprint. This aids auditing and analysis

However, this does mean that the Time Range may need to be renamed if the range changes, so if your Time Range needs to be incremented each year, and it is clearly documented, an alternative would be to use a generic name ("History Years").  It requires less maintenance, but is not as clear

 

Best Practice article:
Time Range Application

1.01-07 Time Ranges

Use Time Ranges to optimize the modules where the default model calendar is not appropriate.  Consider the dimensionality for the data and set up the Time Range accordingly.

 

Best Practices article:
Time Range Application

1.01-08 Daily Timescales on large timescales

Review the need for daily granularity for long timescales, specifically if the daily timescale spans five years or more.  Use time ranges to restrict the daily calendars if possible and also use PREVIOUS rather than CUMULATE to optimise calculation efficiency.

 

Related to Rule:

2.02-10 Using PREVIOUS vs. CUMULATE

 

Best Practices articles:

Versions

1.02-01 Use "Current" in Version settings

Utilising the Current (and Actual) check box within the Version Settings allows the use of CURRENTVERSION() and ACTUALVERSION() in calculations, in SELECT statements and  ISCURRENTVERSION and ISACTUALVERSION() for Boolean checks.  Current version also acts like a top level for Versions; if you have a source module with versions (and Current ticked), in a target module without versions, the value from the current version will automatically be returned without needing additional functions or SELECT statements

 

Related Rule:
2.02-14 Avoid using SELECT

1.02-02 Do not use formulas in the version settings

Whilst this is a simple construct and it might be OK for simple models; it effectively adds size to models every time versions are used in a module even if you don't want the variance. Also, in modules with 3 or more dimensions if you have calculation line items and aggregations the results may not always be give the expected result due to different calculation priorities.  Finally, you cannot amend the summary method for a version formula within the versions setting

1.02-03 Edit To / Edit From

Use these as a simple, administrator lead control for read and write access to versions.  For more granular control use Dynamic Cell Access

1.02-04 Always move switchover dates forward

Using switchover means the historic periods are effectively cleared out.  This does have the benefit of reducing model size as the historic cells are cleared, but switchover should be moved forward only.  Moving it backwards will blank out the respective period removing any values that were previously held

1.02-05 Try and avoid a high number of native Versions

Versions have in built functionality that is not available for normal lists.  However, a large number of Versions (e.g. 10+), can have performance implications due to the block structure and the lack of multi-threading available.  Review the need for high number of Versions, and if the native functionality is not critical or needed, consider using a normal list to represent Versions

 

Best Practice Article:

To Version or not to Version 

Users and Roles

1.03-01 Only give write access to lists when needed

Unless the end users need to edit the members of the list (e.g. add, delete), access does not need to be set.  Adding roles to lists increases the memory usage, so only use when necessary.  A user can edit data in a module without needing to have access to the list

 

Best Practice article:
Pre-Allocation in Lists (and Impacts to Model Performance

1.03-02 Performance and size can be dependent on the Users list

When Users list is heavily used, the number of users added can have a significant impact, be mindful of this and remove model access from any Workspace users when not required

1.03-03 Set a landing dashboard by role

Different roles should require a different entry point. Avoid a generic landing page if possible and create a specific dashboard tailored for each role

Contents

1.04-01 Keep modules hidden for end users’ roles

Users should only be entering data through dashboards, so remove Modules from the contents panel

1.04-02 Utilize Show Content On/Off for Different Functional Areas

To reduce maintenance, create separate Functional Areas for dashboards and modules (e.g. Reports and Report Modules).  Consider if you want new content to appear automatically for the end users.

For example; In the Contents tab, turn Show New Content "On" for all dashboard Functional Areas and "Off" for all module Functional Areas.  New dashboards will then be automatically added to the Contents when the role security is updated, but modules will not.

Turning the "New Content" to "Off", allows development of new content without disruptions to the end users.  you can then activate the new content at the required time 

Lists

1.05-01 Naming Convention

Hierarchies should be prefixed with a letter signifying the name of the hierarchy and a number indicating the level.  e.g. P1 Product Category, P2 Product Family, P3 Products.  The is no need to add "L" as a prefix to lists

 

Related to Rule:
1.08-01 Don't use Emojis

 

Best Practices article: 

Good Practice Naming Conventions

1.05-02 Always use a code

Using codes is more efficient for loading and using lists so strive to always have a code for lists.  This is especially important for numbered lists

 

Exception:

1.05-02a Static non hierarchy lists: For simple static lists (such as Yes/No) it is OK not to have a code, but even then, Y and N can be used as codes

Related to Rule:
1.05-04 Numbered Lists should always have a code

 

Best Practices article:
Hierarchy Management

 

1.05-03 Avoid using List Properties

List properties are the same as line items, but have many limitations, so keep it simple and have one place for calculations i.e. Module.Line Item

 

Exceptions:

  • 1.05-03a Reference module line items: Where the following exceptions are used, reference module line items through a formula where possible to keep the audit trail valid
  • 1.05-03b Numbered lists (and related actions): Assign actions, and associated filters require list properties
  • 1.05-03c Exports: List properties can be used as export labels
  • 1.05-03d Conditional Dashboard navigation (old UX): List properties are needed to facilitate navigation to different dashboards based on the selection in the list
  • 1.05-03e Dependent drop downs: List properties are needed to create driver and dependent lists

Best Practices article: 
Data Hubs Purpose and Peak Performance

1.05-04 Numbered List should always have a code

If you are using Create or Assign actions for Numbered lists, try and incorporate a "code creation" action, as part of another process

 

Exception:

1.05-04a Combination of Properties: If there is no code in the source, you will have to use combination of properties.  Try and avoid if possible as this is slower to import and more difficult to use without a code

 

Related to Rule:
1.05-02 Always use a code 

 

Best Practices article: 

1.05-05 Format the Display Name property as a list if possible

Format the Display Name property for Numbered lists as List Formatted, not text. This is more efficient, saves a line item, minimizes text fields and simplifies mappings.  However, it not recommended to create a separate list purely for this purpose; use an existing list if possible

1.05-06 Only use Top Level for ultimate parent

Only use Top level for lists that will need sums so not Currency codes, or True/False (Indicators), or children in composite lists

 

Best Practices article: 
Top Level Item and Parent Hierarchy

1.05-07 Avoid Top Level for large flat lists

The calculation for a top level on a large list cannot be split so as the list grows, the calculation becomes increasingly inefficient.  Consider if you really need the total. If you need to have the totals, look to add intermediate parent “totals” to make the calculations more efficient, of use SUM to aggregate for validations

 

Best Practice article: 
Top Level Item and Parent Hierarchy

1.05-08 Use Composite lists rather than ragged

Composite lists are more flexible and calculate more efficiently so try and "balance" the hierarchies whenever possible

 

Exception:

  • 1.05-08a Chart of Accounts, or financial reporting hierarchies: Chart of Accounts, financial reporting hierarchies are valid uses of non-composite lists
1.05-09 Create Placeholders in general lists

Add place holders to organise General Lists and add Subsets and Line Item Subsets at the bottom or the General Lists.  This will ensure that Subsets and Line Item Subsets can be easily identified when referring to them in the 'Applies To'.  It is Ok to use emojis for the placeholder lists

 

Related to Rule:
1.08-01 Don't use Emojis

1.05-10 Never delete list and reload the list as a daily occurrence

Clearing and reloading a list increases the structural changes within a model and will increase the likelihood of a of model save, thus increasing the import time.  It also removes pre-allocated memory block designed to increase efficiency. Use a unique key to update to values rather than delete and re-load. Also consider adding a TRUE field to the data source which will allow you to know which records have been imported

 

Best Practices articles

Data Hubs Purpose and Peak Performance
Pre-Allocation in Lists and Impacts to Model Performance

1.05-11 Do not embed Numeric values or Dates within the Code of a list

Dates and values are "data" and should not be part of a code.  Adding these elements to the code will in most cases, vastly increase the size of the list and consequently increase the mode size and reduce performance

 

Best Practices article:

Data Hubs Purpose and Peak Performance

1.05-12 Use formulas to derive properties of the list

Based off the code of the list it should be possible to derive the attributes; Calculating the values is more efficient than storing text fields

 

Best Practices article:

Data Hubs Purpose and Peak Performance

1.05-13 Use Flat lists to store Metadata (in a system module)

Avoid having hierarchies in Data Hub; these should only be applicable to the main planning models

 

Related Rule:
5.07-01 No Hierarchies

Subsets

1.06-01 Naming convention

Prefix the subset with the name of the list (e.g. P3 Products: Active Products)

 

Related to Rule:
1.08-01 Don't use Emojis

 

Best Practices article:
Good Practice Naming Conventions

1.06-02 Don't use subsets on large lists

Lists, and subsets take up space within a model, so if you need multiple subsets of the same list, consider whether they would be better as separate lists.  This is especially valid if the lists do not overlap and they are being fed from a Data hub.  For overlapping subsets or if there is a need to “consolidate” the value back to the master list then subsets are a valid construct for model efficiency

1.06-03 Avoid single item subsets

If possible, try and avoid single item subsets, if there is a top level in the list, a single item subset will always have two members. Consider using a Boolean flag in a SYS module or a LOOKUP line item against the desired item (to avoid using SELECT)

Line Item Subsets

1.07-01 Naming Convention

Prefix with LIS: then a description or the module used in the Line Item Subset.  For Line item subsets using multiple modules, use a generic name that best describes its purpose

 

Related to Rule:
1.08-01 Don't use Emojis

 

Best Practices article:
Good Practice Naming Conventions

1.07-02 Line Item subsets for Version Formulae

Use Line Item subsets to create different numeric formulae for each version to avoid multiple Ifs

 

Best Practice article:
Line Item Subsets Demystified
Decreasing the Length of Your Formulas
Variance Analysis With Native Versions Made Easy

Emojis

1.08-01 Don't use Emojis

Don't use Emoji or symbols in any naming of lists, modules, line items, or actions.  They can cause issues with integration and different browsers

 

Exception:

1.08-01a Placeholders: It is OK to use Emojis or symbols for placeholders in General Lists, Module Names, Functional Areas etc.