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