Using SpotlightXL- Model Administration Subtask

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.

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.

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.

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.

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.
   
   
   
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.

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.
   

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.

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:

• Application-Level Data Locking for Views
• Application-Level Data Locking for Reports
• Application-Level Data Locking for Models

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.

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.

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

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.

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

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.

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.

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.

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.