Model Administration

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

With Lookups you 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. You must select Metadata or Both for Map "Type" when the model is created to use Lookup functionality because it only applies to metadata.

Once you create a model and apply lookups to that model map you cannot update the lookups. If you generate a model that has an updated lookup table, the model will be unusable and you will need to rebuild it. You 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 you want displayed in Dynamic Planning.

Create a Lookup

In Practice

1. Select the Model task and the Model Administration > Lookup subtask.
2. Select New Lookup from the list box to create a new lookup. In the image below, Account Lookup is selected, which displays data and the code for a dimension member used in a model.
   
   
3. The Source represents the member name in Dimension setup. In the image above, PRJFTRTE is a dimension member of the Account hierarchy. For example, the Planful Structured Planning, Consolidation, or Reporting application Account hierarchy with the PRJFTRTE member is shown below.
   
   
4. The Target represents the name of the dimension member that you want displayed 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.

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

In Practice: Create Reverse Lookup

1. Create a Reverse Lookup using the 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.

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

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

You 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 you can define per model. Substitution variables are model-specific. Before using substitution variable in reports, you 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 for which you want to define variables, or, select All Models.

Dimension - The dimension to which you 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 you want to substitute.

You 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, you can update the values.

Adding a New Substitution Variable

In Practice

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

2. Select the model for which you want to define variables.

3. Select the dimension to which you want to apply the variable. Values are 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.

Deleting an Existing Substitution Variable

In Practice

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

2. Select a model and dimension.

3. Select and delete the substitution variable.

Using Substitution Variables in a Calculation

You can use the substitution variable as value in a Calculation. For example, in the Calculation below the current scenario variable is used. The value can be a substitution variable.

In the example below, substitution variables are created for the Time and Scenario dimensions of the FinanceMaster model. The intention is to have a 12-month rolling forecast.

In Practice

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

2. Select the model for which you want to define variables.

3. Select the dimension to which you want to apply the variable. Values are 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.

7. Select the Analyze task and the Data subtask.

8. Select the FinanceMaster model, which is the model the substitution variables were defined for.

9. Manipulate the data as needed using actions.

10. Select the Report subtask.

11. Notice that member values are displayed when labels are selected. In the image below, there are currently no substitution variables used. Select a block of cells or just one cell to select a substitution variable.

    

12. Click Design Manager.

    

13. The Scenario dimension is already displayed in the Dimension list box, but you can select any dimension. Click the search button ().

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

    

15. In this case, Scen1 is selected for the Actual scenario (cell B4), Scen2 is selected for the Actual scenario (cell C4), Scen3 is selected for the 2013 Forecast Q2 scenario (cell D4), and so on until all 12 periods are substituted.

    

16. Once all scenarios are substituted, select Jan-13 (cell B5) and click Design Manager.

17. Select Per1 and click Update. Select Jan-13 (cell C5) and click Design Manager. Select Per2 and so on until all 12 periods are substituted.

    

18. Once all periods and scenario substitution variables are applied, click Save.

19. Enter a report name.

20. Click on a dimension member label to view the substitution variable assigned.

    

21. Select the Run subtask to run the report with substitution variables.

    

22. When defining additional reports, you can substitute the variables defined as long as the report belongs to the same model. Or, use Substitution variables for existing reports. When in Design mode, select the column or row label and click Design Manager.

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.

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 you want to lock. If you 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 you specify which data cannot be modified when data locking is enabled. Application Settings let you enable data locking.
Data Locking can be independently applied to:

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

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

Examples of Data Locking

Lock Closed Periods in a Forecast Scenario

Lock Prior Year Historical Values

Lock the Budget Scenario After Data Input is Complete

Lock Specific Accounts from Data Input

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