Using SpotlightXL- Model Administration Subtask
  • 16 Minutes to read
  • Dark
    Light
  • PDF

Using SpotlightXL- Model Administration Subtask

  • Dark
    Light
  • PDF

Article summary

Validation

The Model Validation report validates selected source and target models and provides information on invalid objects as well as member formulas. This is useful for models that are sourced from Planful Structured Planning, Consolidation, or Reporting applications that may have changes that impact a Dynamic Planning model. An example of this might be a change to rollup members in a hierarchy or deletion of an object in SpotlightXL such as a substitution variable used in reports. User can run this report for any model type (for example Master or Analytic).

The objects that are validated in the model are maps, formulas, views, reports, and lookups.

To run the Model Validation report, select the Model task and the Model Administration - Validation subtask. Then, select the model for which user want to run the report. If no data is displayed, the Model is valid and requires no modifications. An example report is shown below.
Fields displayed are based on the individual model. In the report below, there are several invalid or missing lookup objects for the Account Lookup. User can access the Lookup page by selecting Model task and the Lookup subtask and then selecting Account Lookup. Modify the source or target lookup information. Save and rerun the Model Validation report.

In some cases, member values are recommended if a close relationship exists in the name.

Tip
Use Scope for Model Validation reports.


Validation Report for Duplicate Members

Once users have downloaded Metadata from Structured Planning, Consolidation, and Reporting applications, they should look to see how Dynamic Planning will handle duplicate member names. The Structured Planning, Consolidation, and Reporting applications allows the use of a Member Code within one hierarchy and the same Member Code within another hierarchy. For example, Member Code “Project Manager” could be used within a Company hierarchy of “IT” and also under a Company hierarchy of “R&D.”

Dynamic Planning requires that all the member codes be unique.

When it encounters duplicate member codes, it renames one by concatenating Member Code and Member ID with an underscore between them. See New Member Code below.

ModelingImagesDataIntegration-CoreFunctionalSpecDataIntegration-CoreFunctionalSpec7.png

Power or Contributor user access is required for these steps.

Model > Model Administration > Validation

Navigate to Model > Model Administration > Validation. In the example below, HACPM_Financial is selected from the Model list box. The report runs. See example below. The status shows Failed if Dynamic Planning had to modify any member names due to duplication.

If the user does not like the New Member Code, there is no way to change it in Dynamic Planning. They need to change the segments in Structured Planning, Consolidation, and Reporting applications to avoid the duplication.

image1492zzzzz123234569012312345690122345678901238.png

Duplicate Members Report

This report displays duplicate dimension members sourced from the Structured Planning, Consolidation, or Reporting applications. Access the Validation report by selecting the Model task and the Model Administration – Validation subtask. For Model, select HACPM_Financial.

ModelingImages451to500duplicatmem.png

Duplicate members are appended with the Member ID. To eliminate duplicate members, update the member codes in Structured Planning, Consolidation, or Reporting applications and then perform a model refresh.

Provides information on each formula downloaded from Planful Structured Planning, Consolidation, or Reporting applications to Dynamic Planning. Formulas that could not be converted to Dynamic Planning are identified so that they can be manually converted and applied.

The table below describes the spreadsheet fields on the Member Formulas subtask.

FieldDescription

Dimension

The dimension where the formula is applied.

Member

The dimension member the formula is applied to.

Formula

The formula syntax.

Translation Status

Provides information on whether the formula was successfully translated from Planful Structured Planning, Consolidation, or Reporting applications to SpotlightXL.

  • Cross-Dimensional - The formula contains more than one dimension. Multiple dimension formulas are not supported.

  • Invalid Formula - The formula is invalid.

  • If blank, the formula was translated successfully.

Lookup

With Lookups user can customize data labels for display within a model or report. Lookups are applied when added to a map and that map is executed. The lookup replaces the original (such as source) code values for a dimension with the updated (such as target) values. User must select Metadata or Both for Map "Type" when the model is created to use Lookup functionality because it only applies to metadata.

Once user creates a model and apply lookups to that model map they cannot update the lookups. If user generate a model that has an updated lookup table, the model will be unusable and they have to rebuild it. User can incrementally add new lookup members (such as new accounts, products, companies) to a lookup if they are refreshed from the Planful Structured Planning, Consolidation, or Reporting application.

Note:
Lookups cannot have leading or ending spaces in the Target Lookup name as doing so will truncate the label and will not display the intended Target name.

Actions Available

A description of the actions available for the Lookup subtask is provided below. Access the Lookup subtask by navigating to Model > Model Administration > Lookup.

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

Save - Save a defined lookup.

Delete - Delete a selected lookup.

Spreadsheet Fields

The spreadsheet fields on the Lookup page are described below.

Name - Enter a lookup name or select an existing lookup.

Source - The member name in Dimension setup. See the Create a Lookup example below for more detailed information.

Target - The name of the dimension member that will be displayed in Dynamic Planning.

How to Create a Lookup?

  1. In SpotlightXL, select the Model task and the Model Administration > Lookup subtask.
  2. Select New Lookup from the list box to create a new lookup.
    lookupredone.png

  3. The Source represents the member name in Dimension setup.
  4. The Target represents the name of the dimension member that user want to display in Dynamic Planning. Instead of selecting an already defined lookup, select New Lookup from the Name list box.
  5. Enter a name for the lookup.
  6. Copy and paste values into the spreadsheet or enter Source and Target labels.
  7. Click Save.

Create a Reverse Lookup

Reverse Lookups facilitate writing data back from the Dynamic Planning application to the Structured Planning, Consolidation, or Reporting applications when lookups have been applied to a model.

Reverse Lookups are the inverse of the original Lookup created. For example, Account Lookup has Source (Code from the Structured Planning, Consolidation, or Reporting application) to Target (Name from the Structured Planning, Consolidation, or Reporting applications), which updates all dimension values to Name for presentation in Dynamic Planning. Reverse Account Lookup will have the inverse. It will include Source (Name from the Structured Planning, Consolidation, or Reporting applications) to Target (Code from the Structured Planning, Consolidation, or Reporting applications).

How to Create a Reverse Lookup?

  1. In SpotlightXL, go to Model task and the Model Administration > Lookup subtask.
  2. Apply the lookup to the Writeback Map. For example, the map might have Source Model as FinanceMaster and Target Model as HACPM_Financial (the proxy for Planful Structured Planning, Consolidation, or Reporting applications).
  3. Apply the Reverse Lookup to the newly created map ensuring Writeback ID is created (same name as the Data Load Rule created for Writeback in the Structured Planning, Consolidation, or Reporting application).
  4. Execute the map.

Range Lookup













Substitution Variables

Substitution variables are used to replace dimension member values in a report. They can be used anywhere on a report; a cell, a column, or a range of cells. A substitution variable contains a variable name and a value. For example:

  • Substitution Variable Name = AR1

  • Substitution Variable Value: Jan-2024

The substitution variable name is replaced by its value when the report is run, thereby making reporting flexible. For example, if user created a report column that uses the AR1 substitution variable when the report is run the name (AR1) is substituted by its value (Jan-2024).

User can use substitution variable functionality to make mass updates to a report design. For example, highlight a range of cells and change the Company for which that range pertains to. Or copy and paste one period and add another period by highlighting the range of cells and updating the period, which eliminates the need to Capture Data.

Defining Substitution Variables

Define substitution variables for a model. There is no limit to the number of substitution variables user can define per model. Substitution variables are model-specific. Before using substitution variable in reports, user must define them.

To define substitution variables select the Model task and the Model Administration > Substitution Variables subtask.

Tip
This feature can be used as a report as it provides a list of substitution variables associated with a model and dimension or all models for any dimension or all models and all dimensions. Up to 10000 rows are displayed. If the selection results into more than 10000 rows, an application message will appear indicating that user can filter on a specific model and dimension.

The spreadsheet fields on the Model Variables page are described below.

Model - Select the model to define variables, or, select All Models.

Dimension - The dimension to which user want to apply the variable, or select All Dimensions.

Substitution Variable - Enter a variable name. When variables are added to a report, they are enclosed with the "@" symbol (for example, @CurrPeriod@).

Value - Select the value to substitute.

User can select ‘All Models ’ and ‘ All Dimensions ’ to update the value for all substitution variables. Or, select ‘All Models’ and a particular dimension (for example, Scenario) so that the system will display all substitution variables across all models defined against the Scenario dimension. At that point, user can update the values.

How to Add a New Substitution Variable?

  1. In SpotlightXL, select the Model task and the Model Administration > Substitution Variables subtask.

  2. Select the Model for which user want to define variables.

  3. Select the dimension to which user want to apply the variable. Value is displayed based on the dimension members.

  4. In the Substitution Variable cell, enter a name for the substitution variable.

  5. In the Value cell, select the list box and select a dimension member or enter a value directly in the cell.

  6. Once all substitution variables are defined, click Save.

How to Delete an Existing Substitution Variable?

  1. Select the Model task and the Model Administration > Substitution Variables subtask.

  2. Select a Model and Dimension.

  3. Delete the required row and and click Save.

Note:
All Models & All Dimensions options are only updating values.

Using Substitution Variables in a Calculation

User can use the substitution variable as value in a Calculation. For example, in the Calculation below the variable is used for Time dimension.

In the example below, substitution variables are created for the Time dimensions of the Insurance Revenue model. 

How to Use Substitution Variable in a Calculation?

  1. Select the Model task and the Model Administration > Substitution Variables subtask.

  2. Select the Model for which user want to define variables.

  3. Select the Dimension where you want to apply the substitution variable, which applies only if there are already existing substitution variables defined for that dimension.

  4. In the Substitution Variable cell, enter a name for the substitution variable.

  5. In the Value cell, select the list box and select a dimension member or enter a value directly in the cell.

  6. Once all substitution variables are defined, click Save.

    Note:
    The model and dimension fields will be automatically filled based on the selected properties when you save.
  7. Select the desired report where the substitution variables are intended to be used.

  8. In the image below, no substitution variables are currently applied. Select the specific cell where the substitution variable needs to be applied.


  9. Click Design Manager.

    Note:
    Make sure to select both the header and all data cells to ensure that all relevant intersections are updated to use the substitution variable.
  10. The Time dimension is already displayed in the Dimension list box, but user can select any dimension. Click the Select Member icon.

  11. Substitution variables as well as dimension members are displayed in a hierarchy as shown below.


  12. Select the required values and click Update.


  13. Once all substitution variables are applied, click Save or Save as.

  14. Now, click Report > Run.


  15. The substitution variables are defined in the report.

Best Practices

  • Establish a naming convention for substitution variables.

  • Keep substitution variable names consistent from one model to the next.

  • Do not overly encrypt substitution variable names.

Data Locking

The Data Locking page provides a way to specify data in one or more models that should be locked. Data locks protect data from being overwritten by data entry, Breakback, formulas, and calculations. Data locking differs from dimension security in the following ways:

  • Data that is locked is visible to the user but cannot be modified by anyone. Data locking is global.
  • Data that is protected with dimension security is not visible to the user and cannot be modified by the user. Dimension security is specific to the users for which it is defined.

Data Locking can be applied to Views, Reports, and Models.

Note:
Data Locking in Models is an opt-in feature. Contact Planful Support if user want to use Model Data Locking. Model Data Locking prevents any map, formula, calculation, data load, or API call from modifying the data that is locked.

Users with Contributor or Power access can use the Data Locking page. To access this page, go to Model > Model Administration > Data Locking.

Data Locking is a two-step process:

  • Set up the data locking definitions via Model > Model Administration > Data Locking.
  • Turn on or off all data locking in the application on the Manage > Application Administration > Application Settings page. This step must be done by a Power user.

To learn how to turn Data Locking on or off, see:

Spreadsheet Fields Described on the Data Locking Page

The spreadsheet fields on the Data Locking subtask are described. Navigate to Model > Model Administration > Data Locking.

Model - Select the model to apply data locks to. Select All Models to view all data locking definitions.

Note:
When viewing All Models, user cannot make changes. They must select a particular model to make changes to its data locks.

Data Locking in Views - Indicates whether Data Locking is turned on or off for Views. Application Level data locking globally applies to all the Views and all Models in the Dynamic Planning application and for all users. None indicates that data locking is not enforced in Views. This indicator is read-only. Only Power users can change this setting in Manage > Application Administration > Application Settings.

Data Locking in Reports - Indicates whether Data Locking is turned on or off for Reports. Application Level data locking globally applies to all the Reports and all Models in the Dynamic Planning application and for all users. None indicates that data locking is not enforced in Reports. This indicator is read-only. Only Power users can change this setting in Manage > Application Administration > Application Settings.

Data Locking in Models - Indicates whether Data Locking is turned on or off for Model actions. Application Level data locking globally applies to all model actions that modify data, such as maps, formulas, calculations, data loads, or API calls. This applies to all models in the Dynamic Planning application and for all users. None indicates that data locking is not enforced in Model actions. This indicator is read-only. Only Power users can change this setting in Manage > Application Administration > Application Settings.

Note:
Data Locking in Models is an opt-in feature. User must contact Planful Support if they want to use Model Data Locking.

The Data Locking table lists models with the names of their dimensions as column headings.

dimlocking.png

Populate the Data Locking table by selecting the member(s) in each dimension that user want to lock. If they select a parent-level member, all leaf level members under that parent will be locked, but the parent-level members will not be locked.

Note:
When viewing data locks for All Models, user cannot make changes. They must select a particular model to make changes to its data locks.

The following example locks all data in member combinations that include any leaf members in the 2021 hierarchy in the Time dimension.

idle.png

The following example locks all data in member combinations that include any leaf members in the 2021 hierarchy in the Time dimension and locks all data in member combinations that include Actual in the Scenario dimension.

2021.png

The following example locks all data in member combinations that include any leaf members in the 2021 hierarchy in the Time dimension and Actual in the Scenario dimension.

new2021.png

Data that is locked appears with a gray background in views and reports.

How Data Locking Works

Many different operations in Dynamic Planning can make changes to data. Data Locking lets user to specify which data cannot be modified when data locking is enabled. Application Settings let user to enable data locking.
Data Locking can be independently applied to:

  • Views
  • Reports
  • Models. This is an opt-in feature. User must contact Planful Support to request this option.

Only leaf dimension members can be locked. However, user can specify to lock a parent-level dimension member as an easy way to automatically lock all of the leaf members under that parent.

OperationWhich Application Setting Controls This OperationDescription

View, the user saves the data they entered

Application Setting > View Properties > Data Locking

Leaf data intersections that are locked are not saved. Leaf data intersections that are unlocked are saved.

View, calculation runs after the user clicks Save

Application Setting > Model Properties > Data Locking

See Model Calculation below.

View, the user runs Breakback

Application Setting > View Properties > Data Locking

Breakback applies a Hold for locked members and member combinations.

Report, the user saves the data they entered

Application Setting, Report Properties, Data Locking

Leaf data intersections that are locked are not saved. Leaf data intersections that are unlocked are saved.

Report, calculation runs after the user clicks Save

Application Setting > Model Properties > Data Locking

See Model Calculation below.

Report, the user runs Breakback

Application Setting > Report Properties > Data Locking

Breakback applies a Hold for locked members and member combinations.

Model, data is loaded using the Data menu item or an API

Application Setting > Model Properties > Data Locking

Leaf data intersections that are locked are not loaded. Leaf data intersections that are unlocked are loaded.

Model, data is loaded using the Import menu item or an API

Application Setting > Model Properties > Data Locking

Data intersections that are locked are not loaded. Data intersections that are unlocked are loaded.

Model, data is loaded from an External Source Model or an API

Application Setting > Model Properties > Data Locking

Leaf data intersections that are locked are not loaded. Leaf data intersections that are unlocked are loaded.

Model Calculation:

Application Setting, Model Properties, Data Locking

A calculation with multiple steps will run all the steps that it can run successfully.

  • Calculation ClearLeafData

Application Setting, Model Properties > Data Locking

Leaf data intersections that are locked are not cleared. Leaf data intersections that are unlocked are cleared.

  • Calculation ClearRollupData

None.

Since only leaf-level member intersections are locked, Rollup Data will be cleared.

  • Calculation ClearAllData

Application Setting > Model Properties > Data Locking

Leaf data intersections that are locked are not cleared. Leaf data intersections that are unlocked are cleared. All rollup data is cleared.

  • Calculation Aggregation

None.

Since Aggregation does not modify leaf-level data, and only leaf-level member intersections are locked, Aggregation proceeds normally.

  • Calculation Breakback

Application Setting > Model Properties > Data Locking

Breakback applies a Hold for locked members and member combinations.

  • Calculation Formula

Application Setting > Model Properties > Data Locking

Leaf data intersections that are locked are not modified by the formula. Leaf data intersections that are unlocked are modified by the formula.

  • Calculation Map

Application Setting > Model Properties > Data Locking

Leaf data intersections that are locked are not modified by the map. Leaf data intersections that are unlocked are modified by the map.

Model > Clear Model

None.

Data Locking does not prevent data from being removed with Clear Model.

Model > Generate Model

None.

Data Locking does not prevent data from being loaded with Generate Model.

Best Practices with Data Locking

  • We recommend to set the data locking property for Views and Report to Application Level.
  • For Model tasks, such as running a calculation, loading data from the interface or an API, the recommendation is to set to Application Level based on the need.
  • Once data locking is enabled, the leaf combinations defined in the data locking user interface will not be modified.
  • Once data locking for Model tasks is enabled, user can run an aggregation for a given year, even though it may contains some leaf members that are locked. When aggregation runs for that year, the locked leaf members are not modified, but it will rollup members will be aggregated in the model. This behavior is the same for other steps in the calculation.
  • Users can select the root member for any dimension in the data locking user interface. When the setup is saved, the root member selection is ignored as this is explicit. Selecting a root member or not selecting a root member is the same as selecting all the leaf members for the selected dimension for locking. As an example Scenario: Actual, Time: All Time is defined in the data locking, there is no need to specify the root member ‘All Time’ on the Time dimension because the data is locked for all leaf members combinations for Actual Scenario.

Was this article helpful?