MENU
    Dynamic Planning Model Calculations
    • 19 Minutes to read
    • Dark
    • PDF

    Dynamic Planning Model Calculations

    • Dark
    • PDF

    Article summary

    Overview

    Create a Calculation, which identifies the order of execution for aggregations, formulas, maps, and more. Access the Calculation subtask by selecting the Model task and the Calculation subtask.

    The calculations displayed for the Master model are those member formulas defined in Planful Structured Planning, Consolidation, or Reporting applications and carried over into SpotlightXL. The translated formulas in Planful Structured Planning, Consolidation, or Reporting applications will be carried over to SpotlightXL and executed in the Calculation.

    Description of Ribbon Fields

    A description of ribbon fields and actions is provided below.

    Folders

    Organize your Calculations based on the models from which they were created. Add Calculations to your Favorites folder by right-clicking on the Calculation and selecting Add to Favorites. In the example below, the Balance Sheet and Model Refresh Calculations are organized in the Balance Sheet folder.

    ModelingImagesorganizecalcs.png

    Use the Search functionality at the top of the screen to search for Calculations, then use the up and down arrows to scroll through the results.

    Favorites and Recent folders are user specific. The Recent folder displays 10 recently accessed Calculations by user across all Models. The Favorites folder displays 10 Calculations. If you add more than 10 items, the most recent 10 are displayed.

    You can add folders, delete and rename folders as well as move Calculations from folder to folder by dragging and dropping.

    Note:
    Folders are not based on security role (Power, Contributor, Reviewer), so any user role can add, edit, rename, or delete a folder, or move any calculations they have access to from folder to folder.

    How to Add a Folder

    1. Select a model and click Add Folder.

    2. Enter a folder name and click OK.

    How to Delete a Folder

    1. Select the folder.

    2. Make sure there are no calculations in the folder as you cannot delete a folder that contains calculations.

    3. Right click and select Delete.

    How to Rename a Folder

    1. Select the folder.

    2. Right-click and select Rename.

    3. Enter the new name of the folder then click OK.

    Run

    Run the Calculation. Saving the Calculation will not execute the script.

    Variables Manager

    Select values for variables defined in Calculations.

    Tip
    Use a Scope with variables.

    See:

    Change Variable Values for Types that Share the Same Variables and Values

    Selecting a Value for a Variable

    Scheduler

    Manager Select the Schedule Pattern value field and click Scheduler Manager to schedule a Calculation to run at specific time intervals. Once the Calculation is complete, notifications are sent to selected users.

    You can select Weekly, Monthly, or Daily frequencies.

    See: How to Schedule a Calculation

    Refresh

    After a selection is made, click Refresh to view updated data.

    Description of Spreadsheet Fields

    A description of spreadsheet fields and actions is provided below.

    Model

    Select the model for which you want to define the calculation.

    Name

    Select an already defined calculation or select New Calculation and enter a name for the calculation.

    Run in Background

    Yes - Run processes in the background (for example, an aggregation or calculation that will take a long time to execute). See the Request Status page, accessed by selected the Manage task and the Request Status subtask, for process details.

    No - Do not run processes in the background.

    Note:
    The default setting for terminating the background running of Calculations is set to four (4) hours. If any process takes more than four hours to finish, that process will be terminated and an appropriate message will be displayed in the Calculation execution status.

    Current Status

    This field is updated when you select No for Run in background and provides status information on the results of the run (for example, successful).

    Last Run

    Time This field is updated when you select No for Run in background and provides run time and date information.

    Last Run Status

    This field is updated when you select No for Run in background and provides information on the time to complete the most recent run.transparent.gif

    Schedule Pattern

    Click the Schedule Manger action to select a schedule pattern. The Schedule Pattern field is populated based on the selections you make in Scheduler Manager. It reflects the frequency, time, and day pattern for which you want to run the Calculation.

    Schedule pattern is displayed in following format:

    Daily : "Calculation scheduled to run Daily on every 1 day(s) at <Time> <AM/PM>"

    Weekly : "Calculation scheduled to run Weekly on following day(s): <name of day(s)>, at <Time> <AM/PM>. Beginning on <Begin time> and ending on <End time>"

    Monthly :

    "Calculation scheduled to run Monthly on following day(s): '< n> ' at <Time><AM/PM>."

    where:

    <Time> denotes the time at the process is scheduled to run

    <name of days(s)> denotes the names of the days in a week when the process is scheduled to run.

    <Begin time and End time> denotes the start and end times for a process to run.

    <n> denotes day number in a month when a process is scheduled to run.

    Success / Failure Notification

    The Success Notification and Failure Notification fields are also populated based on the users you select in Scheduler Manager.

    Description

    Provide detailed information about the Calculation.

    Type

    The type you select depends upon the type of task you want to execute. Options are described under Calculation Types.

    Name

    The name that identifies the type.

    Model

    Select the model you want to modify.

    Variables

    Add substitution variables created on the Model task Substitution Variable subtask.

    Description

    Add detailed information for each step within the Calculation.

    Calculation Types

    Best Practices

    As a best practice, always use the drop-down to select the calculation type. If you use the drop-down in the Type column, then the Name drop-down will be populated for you. If you simply type the calculation type, then the Name drop-down is not populated.

    Note:
    For the second and following rows of the calculation, if there is no drop-down in column A, then copy the drop-down from the first row, column A in the Type section to the rows beneath.
    ModelingImages01to50image45CalcTypeCopy.png

    Aggregation

    Aggregation is performed for the entire model unless a Scope is defined. Aggregation Type is case-sensitive.

    AppendMetadata

    Allows you to add new members to an existing dimension. Until this release, appending metadata was only available from the user interface (Model, Dimension, Append actions).

    Tip
    If you are refreshing data in a model and the source model has metadata changes (i.e. added a new product, department, project etc.) then AppendMetadata will be very helpful.

    Appending metadata differs from a full or partial Model Refresh. For example, let’s say you are loading data from Planful Structured Planning, Consolidation, or Reporting applications and you want to refresh that data on a monthly basis. There is no need to clear and recreate the entire model, which is what a full refresh would do. If you perform a partial refresh, only the mapping of type Data is performed meaning that new a dimension member (like a new Department) is not mapped so the Calculation will fail due to the new member.

    This feature also differs from the Append action you can select for a dimension because selecting Append allows you to enter new dimension members directly into Dynamic Planning, but does not load them from another source like Planful Structured Planning, Consolidation, or Reporting applications.

    How to Use Append Metadata in a Calculation

    1. Access the Model task and the Calculation subtask.
    2. Select the model in which you want to append metadata.
    3. Under Type, select AppendMetadata as shown below.

      ModelingImagesimage28.png

    An example of typical full refresh Calculation steps are shown below. Notice the full refresh clears the model and generates it again while the partial refresh appends the metadata.

    ModelingImagesimage29.png

    An example of typical partial refresh Calculation steps are shown below.

    ModelingImagesimage30.png

    Note:
    The AppendMetadata type will only append new members (both leaf and rollup) to the target model.
    Tip
    If you are mapping attributes from the Structured Planning, Consolidation, or Reporting applications then the attribute will be completely refreshed. In other words, all changes in the attribute including the mappings to the dimension members will be refreshed in the target model

    Breakback

    You can run one or more Breakback operations from a Calculation, thus automating your top-down allocations and making them easier to maintain. Use SpotlightXL to define Breakback operations in a calculation. Once defined, you can run the calculation from SpotlightXL or Spotlight.

    Breakback requires several pieces of information:

    • Model name

    • Parent-level data intersection from which to allocate data to children

    • Spread method

    • Type of change to make: amount or percentage increase/decrease

    • Value of the change to make

    • Data intersection to use as a reference for the Breakback (optional)

    • Hold members (optional, child members that will be skipped in the Breakback)

    You can provide the required information using the Breakback button on the Calculation ribbon.

    ModelingImagesBreakbackCalcRibbon.png

    This button functions similarly to the Breakback button in Views and Reports. The main difference is that you must also specify the parent-level data intersection; in views and reports, this is the data cell that your cursor is on before you select Breakback.

    For examples of how to set up Breakback in a calculation with a Value Input or Value Reference, see Running Breakback from a Calculation.

    ClearAllData

    Removes all data within a cleared model.

    ClearExternalSource

    Clears all the data in an External Source Model (ESM).

    ClearLeafData

    Clears all leaf level data. ClearLeafData is different from ClearRollupData and ClearAllData in that ClearLeafData action does not remove data. ClearLeafData updates leaf data with the value 0 (zero).

    ClearModel

    Returns a model to a state of "Not Generated" where it can be deleted.

    ClearRollupData

    Removes all roll-up data, not leaf level data.

    DownloadData

    Downloads data from Planful Structured Planning, Consolidation, or Reporting applications to SpotlightXL based on the maps defined for the model. Select the Manage task and the Application Administration - Request Status subtask to view download results.

    As a best practice, we recommend downloading metadata and data using a Calculation with the option to run in the background set to Yes.

    All setup must be complete prior to downloading data. For example, you need to define a model and target dimensions. At least one Power user must be defined before data can be downloaded.

    Data Download will download only data based on the union of all “Data” type maps that are defined for the model. This is done to ensure that the process is efficient and no extraneous data is downloaded in the process.

    Before data is downloaded from PCR, the system checks to see when the partitions in PCR were last processed. If there was any change in the PCR partitions since the last data download, the system downloads only the changes. This optimization speeds up the data download process.

    Note:
    The system checks to see if the Scenario and Time dimensions have a filter defined in all maps. The All Members filter is not supported for Scenario and Time dimensions. It is recommended that you include a valid filter for Time and Scenario (for example, FixedMember, MemberandBelow).

    DownloadMetadata

    Downloads all metadata from Planful Structured Planning, Consolidation, or Reporting applications to SpotlightXL.

    ExternalSourceFormula

    Runs all formula associated with an External Source Model. This option is typically used with models that use a Lookup table for a set of drivers or user-inputted weights. For example, define a field in the Source Model for Sales drivers by Region where the drivers are provided in a lookup table for each region. You can run the formulas for the sales drivers in the Source Model by specifying ExternalSourceFormula and the name of the Source Model.

    ExternalSourceMap

    Copies data from an External Source Model to a Master Model. The External Source Map must be of the type Data. Mappings are not case-sensitive.

    Note:
    If the copy operation fails due to invalid dimension members, a listing of up to 100 invalid members will be added to the calculation notification, emailed to the user running the calculation, and the map execution will stop. After reviewing the email, the user has the opportunity to review the map, data, and master model and take necessary actions to correct the errors. The calculation can then be run again until no invalid members are found. Note that mismatched upper and lower case characters will be ignored when mapping fields in an external source model to dimension members in the master model.

    See: How to Load an External Data Source into a Master Model (Legacy) or How to Create and Load External Source Models (New) .

    Formula

    A Formula is a mathematical expression to calculate a value within a single dimension. Formula type and name are case sensitive.

    GenerateModel

    Generates a model that is in a "Not Generated" state.

    LockModel / UnlockModel

    Select the LockModel or UnlockModel type as a line item in a Calculation. Performing the lock or unlock in a calculation saves time as it automates the process. For example, you might want to unlock a locked model, generate a formula, run aggregation, and then lock the model once again. If this is the case, your Calculation might look like the following:

    ModelingImagesimage26.png

    When the Calculation runs, it checks whether the model is locked or not before executing each line item/step. If the model is locked, the calculation will be terminated and you will be notified.

    Map

    Map is applicable when the calculation results from a mapping (similar to reference account mapping in Planful). Map Type and Name cells are case sensitive.

    Name Options Available for Each Calculation Type

    For each calculation Type, the Name column has a drop-down of available options. In the Type column, you must use the drop-down to select the calculation step you want to run, and then the Name drop-down will be populated for you. If you simply type the calculation type, then the Name drop-down is not populated.

    TypeName

    Aggregation

    None or list of Scopes available, if any

    AppendMetadata

    None

    Breakback

    None

    ClearAllData

    None or list of Scopes available, if any

    ClearExternalSource

    None

    ClearLeafData

    None or list of Scopes available, if any

    ClearModel

    None

    ClearRollupData

    None or list of Scopes available, if any

    DownloadData

    None or list of Scopes available, if any

    DownloadExchangeRates

    None or list of Scopes available, if any

    DownloadMetadata

    None

    ExternalSourceMap

    List of External Source Maps available, if any

    Formula

    List of Formulas available, if any

    GenerateModel

    None

    LockModel

    None

    Map

    List of Maps available, if any

    UnlockModel

    None

    Note:
    If you manually type the entry in the Type column instead of using the drop-down, then the Name drop-down will not be displayed. As a best practice and for ease of use, we recommend to use the drop-downs for Type and Name.

    Creating a Calculation

    1. In SpotlightXL, select the Model task and the Calculation subtask.

    2. Select a model from the Model list box.

    3. Enter a name for the calculation or select an existing calculation from the Name list box.

    4. Select a type.

    5. Enter a descriptive name in the Name list box to identify the type.

      In the image below, 3 formulas are populated from the Planful Structured Planning, Consolidation, or Reporting application model. The formulas are executed in sequential order starting with Aggregation.

      ModelingImages201to250image236.png

      The page below shows how a formula translates to the Calculation. The FinanceMaster Account Formula is created on the Formula page. It is then added to the Calculation page as shown below. In this example, the Calculation will process the account and company formulas and then calculate year to date data. The FinanceMaster Account Formula calculates gross sales using the Royalty Calculation, which multiplies gross sales times .03. It adds asset acquisitions, asset disposals, and asset transfers to obtain a total for Prop Plant & Equip Change in Bal.

      ModelingImagesModeltoCalc.png

    6. Select the model you want to modify.

    7. Click Variables Manager. The Variables Manager screenappears. Select a new variable value. See the "Selecting a Value for a Variable " topic below.

    8. Click Save.

    9. Click Run to execute the Calculation.

    Sample Calculations

    1. Year to Date (YTD) – perform the following steps:

      1. Aggregate the model

      2. Execute the YTD formula

    2. Refresh data from Planful Structured Planning, Consolidation, or Reporting applications – to refresh Dynamic Planning models with the most up-to-date data and metadata from the Planful Structured Planning, Consolidation, or Reporting applications, consider the following example:

      1. DownloadMetadata

      2. Download Data

      3. ClearModel

      4. GenerateModel

      5. Aggregate

      6. Formula – YTD

    3. Perform a multi-step allocation – perform a calculation which might be a series of steps that need to occur.

      1. Map – move data to calculate headcount as a percentage of total

      2. Formula – calculate allocation percentage

      3. Map – move shared services data to the same level as allocation percentage

      4. Formula - calculate allocated expense (Allocation % * Shared Services expense)

      5. Formula – calculate contra allocation expense (Shared Services expense * -1)

    Clear Data Performance Optimization

    The ClearAllData Calculation step has been enhanced to process the data quickly, saving a lot of time working with extensive data calculations. Previously, the calculation step used to take a considerable amount of time to clear models with a large amount of data.

    Running Calculations in the Background for Views and Reports

    Views and Reports can run a calculation when the user clicks Save. By default, when a calculation is run from a view or report, it runs in the foreground, which prevents users from continuing to work. However, Power users can set an application-wide setting to run calculations in the background for all views and reports that trigger calculations. Those calculations must have been created with the Run in Background field set to Yes. After the calculation has completed its processing, an email is automatically sent to the user that triggered it.

    Our recommendation is to set this option to Yes.

    1. Select Manage, Application Administration, Application Settings. Scroll down to the Calculation Property section. After making changes, click Save. Then logoff and login again to see the effect of the changes.

      ModelingImagesManagecalcbackground12.png

    2. Select Model, Calculation to ensure that the calculation was set up with Run in Background set to Yes.

      ModelingImagesManagecalcbackground22.png

    3. You can see a list of all calculations and their current background setting by choosing Model, Setup. Select the model, then scroll down to the Calculations section.

      ModelingImagesManagecalcbackground32.png

    4. Verify that the calculation is set up to run when the user clicks Save from a view or report by reviewing the view or report properties.

      ModelingImagesManagecalcbackground41.png

      ModelingImagesManagecalcbackground51.png

    5. When the user clicks Save in the view or report, they see the following message:

      ModelingImagesManagecalcbackground62.png

      Note:
      The view or report no longer refreshes the data automatically after the user clicks Save.

    Using Scope with Aggregation in Calculations

    With scope, you can aggregate only what you require. Process and refresh time is reduced.

    Note:
    The most common aggregation Scope applications are for Scenario and Time. It is also common to use variables in a scope. See How to Use Variables and Variable Expressions in a Scope.

    The following image shows that a scope named Download 2015 is created on the November model. The Actual dimension is filtered for the Scenario dimension. For time, all members except for 2015 are filtered. This scope will be added to the DownloadData Type on the Calculation so that only Actual scenario data for 2015 is downloaded from Planful Structured Planning, Consolidation, or Reporting applications to SpotlightXL.

    ModelingImages451to500468.png

    The following image displays the set up to download only 2015 Actual data from the Structured Planning, Consolidation, or Reporting application to Dynamic Planning.

    ModelingImages451to500487.png

    The following image displays the aggregation for the 2015 Budget Scenario.

    ModelingImages451to500481.png

    Using the Variables Manager

    The Variables Manager allows Contributors to make choices of what areas of the model to calculate. For example, a variable called @CurQtr@ could be used to calculate a formula for only the current quarter rather than every quarter in the Time dimension.

    Selecting a Value for a Variable

    To select a value for a variable in a Calculation:

    1. Select Model, Calculation.

    2. Select the Model you wish to modify (e.g., 2013 All Products).

    3. Select the cell for the variable you wish to add a value to, (i.e., a variable for an existing Map). In the example below, cell D10 is selected and has two variables: @CurScenario@ and @FYYear@.

    4. Click Variables Manager.

    5. Select a variable from the list (e.g., @CurScenario@ ) then double-click the New Value row for the variable. A list of substitution variables and scenarios appears.

    6. Highlight the new value you wish to assign to the variable then click Select . In the example shown, we are replacing the value Actual for variable @CurScenario@ with 2012 Budget.

    7. Click Update. The new value is added to the variable, replacing the previous one.

      ModelingImages451to500466.PNG


    Note: The Variable names must be different for both the source and target model of a Map.

    Change Variable Values for Types that Share the Same Variables and Values

    1. Select the cells for the variable you wish to add values. In the example below, cells D10 and D11 are selected and share the same value 2013 for variable @FYYear@.

    2. Click Variables Manager. The Variables Manager screen appears.

    3. Select a variable from the list (e.g., @FYYear@ ) then double-click the New Value row for the variable. A list of substitution variables and scenarios appears.

    4. Highlight the new value you wish to assign to the variable then click Select. In the example shown, we are replacing the value 2013 with 2014.

    5. Click Update. The new value is added to the variables in both lines, replacing the previous one.

      ModelingImages451to500467.PNG

      Note:
      Skipping of lines, as defined in the Map (#skip# ), is supported.

    Using the Calculation Variables Manager #SKIP# Option

    Power and Contributor users can use the SKIP option for variables in a calculation. This functionality is available from both Model Manager and SpotlightXL.

    Use the SKIP option when you have a Map or Formula that uses variables (usually multiple variables). You can build one master map or formula to accommodate data loading or formulas for a whole series of data intersections. Then when the calculation is run that executes the formula or map, you can skip those data intersections that do not need to be loaded or calculated.

    Consider the following formula with two variables and the calculation that runs it. We will set the calculation to skip the @NextQtr@ variable.

    ModelingImages451to500451.pngModelingImages451to500452.png

    1. Login to SpotlightXL.

    2. Select the model and calculation that uses the formula or map variable(s).

    3. Click the Variables Manager button on the menu ribbon.

      ModelingImages451to500variables.png

    4. Select the variable that you want to skip, click the member selection icon, then select Skip.

      ModelingImages451to500453.png

      After you select Skip, you see #SKIP# as the New Value of the variable. Select a value for other variables at this time also.

      ModelingImages451to500454.png

    5. Click Update when you are done with your changes.

      This calculation will now calculate Sales as ASP * Units for the member intersections defined in the formula. In the Time dimension, only Q3 2018 will be calculated.

    Providing Calculation Access to Contributors

    To provide Contributors with the ability to create and modify calculations:

    1. Navigate to Manage, Application Administration, Application Settings.

    2. Scroll down to the Calculations heading as shown below.

      ModelingImagesManagecalcaccess1.png

    3. Set the Enable User Group Permissions property to Yes.

      Power users can assign Groups to the Calculation. Only Contributors belonging to the assigned Groups will be able to access, modify, and run the Calculation. For example, a Power user assigned the Test1 Calculation to Contributors of Group A as shown below.

      ModelingImagesManagecalcaccess2.png

      Now, notice that Group A is not assigned to the Test2 Calculation.

      ModelingImagesManagecalcaccess3.png

      When a Contributor logs into the application and accesses the Calculation page, only Test1 is available as shown below.

      ModelingImagesManagecalcaccess4.png

    Scheduling a Calculation

    The Schedule Pattern field is populated based on the selections you make in Scheduler Manager. It reflects the frequency, time, and day pattern for which you want to run the Calculation.

    The Success Notification and Failure Notification fields are also populated based on the users you select in Scheduler Manager.

    To schedule a Calculation:

    1. Access the Model task and the Calculation subtask.

    2. Select the Model and Calculation you want to run.

    3. Click the Scheduler Manager action.

    4. Select the Frequency, time (At), and Begin and End options.

    5. Select the month/day/week interval.

    6. All Dynamic Planning users are listed. Select the users you want successful (Succeeds) and unsuccessful (Fails) process information sent to.

    7. Click Save.

    Note:
    You can send notifications to only those users added to Dynamic Planning.

    Data Downloading with a Scope

    Use Scope to trim your data download based on your particular requirements (for example, download 2019 but do not include 2020); subsequently, your data refresh times are reduced.

    Note:
    The Scope is used for all common data download dimensions except Attribute, AttributeHierarchy, and BudgetEntity.

    See: Scope

    Before data is downloaded from PCR, the system checks to see when the partitions in PCR were last processed. If there was any change in the PCR partitions since the last data download, the system downloads only the changes. This optimization speeds up the data download process.


    Was this article helpful?