Using SpotlightXL-Model Maps Subtask

Overview

The Map page (accessed by selecting the Model task and the Map subtask) provides functionality to transfer data from Planful Structured Planning, Consolidation, or Reporting applications to a SpotlightXL model, from one SpotlightXL model to another, within a model, and from a SpotlightXL model to Planful Structured Planning, Consolidation, or Reporting applications.

Actions Available

A description of the actions available for the Map subtask is provided below.

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

Save - Save a newly defined map.

Delete - Delete a selected map.

Spreadsheet Fields

The spreadsheet fields on the Map page are described below.

Target Model - The model you want to copy data or metadata to.

Name - Enter the name of the map. To keep track of maps, name the map the same as the target model.

Description - A description of the map.

Type - Specify the type of data you want to map. There are 2 types of data; metadata and data.

• Metadata - copy only metadata from the source to the target model.
• Data - copy only data from the source to the target model.
• Both - copy both metadata and data from the source to the target model.

Source Model - The model you want to copy data or metadata from.

Transfer - Applies to dimension members. Select from the following:

• Leaf - transfer leaf-level dimension members only to the target model.
• All - transfer all dimension members from the source to the target model.

Write Back Id - Required to write data back from a model to Planful Structured Planning, Consolidation, or Reporting applications.

See: Writing Data from Dynamic Planning Back to Planful Structured Planning, Consolidation, or Reporting Applications

Access Token - Required to move data from one Dynamic Planning application to another (within the same environment). For example, you have one application used to manage all corporate finance models and another application to manage all department finance models. Every quarter corporate pulls departmental budgets and forecasts from the department application finance models to the corporate application finance models to generate a consolidated profit and loss statement and balance sheet reports.

See: How to Generate an Access Token & How to Move Data From One Dynamic Planning Application to Another

Target Dimension / Lookup - Customize dimension member labels for display within a model or an analysis.

Source/Target Dimension - Enter the dimension that exists in the source or target model that you plan to copy data or metadata from and to.

See: How to Create Dimensions as Attributes

Source/Target Filter -

• AllMembers – Map all members from the source model to the target model.
• LevelAndAbove – A specified level number as well as all members above that level in the hierarchy will be mapped.
• MemberAndBelow – Members at a specified level where a member name exists as well as all members below that level in the hierarchy will be mapped.
• FixedMember – The dimension displays data from a set member based on member name. For example, the scenario dimension displays only a 2013 Budget scenario and all other scenarios (such as Actual and What If) are not mapped.
• DimensionFilter – The dimension is filtered from view in the model so that the dimension and all members are hidden in the target model. Specify a dimension name (for example, Project) in the Value field.
• None – This dimension either does not exist in the source model and needs to be mapped to a FixedMember in the target model or the dimension does not exist in the target and the entire dimension will be mapped to None. All members will be excluded in the target model.
• Leaf Members - Map leaf-level members of a specific dimension. You might use this option, when for example, you want to create a model with all leaf-level members of the Company dimension only. You do not want rollup members.

Source/Target Value - Based on the source or target filter. There are two options:

• The value is not specified for the dimension so all members are displayed (None).
• The value is specified meaning that all data associated with that value is displayed.

Various combinations of dimension values represent a unique combination. The list below details options available for the Source Target 

Value based on Source Filter selections.

• If Source Filter = AllMembers then Source Value = None
• If Source Filter = LevelAndAbove then Source Value = Level Number. The levels start with the root member (top member = Level 1) and then level 2 and 3.
• If Source Filter = FixedMember then Source Value = Member Name that exists in source model
• If Source Filter = MemberAndBelow then Source Value = Member Name at some level that exists in the source model.
• If Source Filter = DimensionFilter then Source Value = A specified dimension (such as Product).
• If Source Filter = None then Source Value = None
• If Target Filter = AllMembers then Target Value = None
• If Target Filter = LevelAndAbove then Target Value = Level Number. The levels start with the root member (top member = Level 1) and then level 2 and 3.
• If Target Filter = FixedMember then Target Value = Member Name that exists in source model
• If Target Filter = MemberAndBelow then Target Value = Member Name at some level that exists in the source model.
• If Target Filter = DimensionFilter then Target Value = A specified dimension (such as Product).
• If Target Filter = None then Target Value = None

Match Criteria - Specify common Match Criteria when creating a map. Select the ‘Common’ option to move all common data from the source to target model.

One-to-One Mapping

A One-to-One mapping happens when the source filter and value are equal to the target filter and value. In the image below, all members from each dimension (Account, Company, Scenario, and Time) in the source model are mapped to all members of each dimension in the target model. This is a one-to-one mapping.

Another example of a one-to-one mapping is a Fixed Member mapped to another Fixed Member of the same value as shown below.

One-to-Many Mapping

A One-to-Many mapping happens when the source filter and value is less than the target filter and value. For example, let’s say you want to map the data for one scenario dimension to all scenarios. There are several reasons you might want to do this. For example, you want all scenarios to start with Actual data so you map Actual to your 2014 Q1 scenario, 2014 Q2 scenario, and so on as shown below.

One-to-many mappings for fixed member types are also supported. For example, move budget data to different budget scenarios to perform variance exchange analysis or move data from one department to multiple new departments so that it can be used as a baseline.

Using Attributes with Maps

Using the Attribute Default Member

A default member in Dynamic Planning is associated with all missing members in the attribute hierarchy. This applies when mapping attributes and attribute hierarchies from the Structured Planning, Consolidation, or Reporting application to Dynamic Planning.

_Attribute_Default is the default member that will be added to the attribute. If a leaf dimension member is not mapped to any attribute members then all those leaf dimension members will be mapped to this default member.

Attributes and Attribute Hierarchies are supported for MemberAndBelow filter type only.

Using Attributes in Maps for Leaf Data Transfer

For information, see Using Attributes in Maps for Leaf Data Transfer.

Using Attributes in Maps for Metadata Transfer

For information, see Using Attributes in Maps for Metadata Transfer.

Use Variables and Variable Expressions in a Map

You can use both variables and variable expressions in Maps, Formulas, and Scopes.
To see details about how expressions work, their syntax, and how to chain them together, see Example Substitution Variable Expressions.
Variables and Expressions are supported in both SpotlightXL and Spotlight.
Note: The Variable values must be different for both the source and target model.

Variable Example, Get One Fixed Month

The following example shows two variables used in a map. One is a variable on the source model (HACPM_Financial) and one is a variable on the target model (Drill-Thru).

In SpotlightXL, select Model > Map:

In Spotlight, select Model, select a Map, and click the pencil icon to open the Mappings screen:

Expression Example, Get One Fixed Month and 12 Months of Trailing

Many users already use variables in their maps, as described in the section above. You can now simplify the maintenance of the maps by using fewer variables and adding expressions. For example, if you want to get data for a particular month, and the 12 months trailing, use just one variable for the month and expressions for the preceding months.

• @TargetMonth@
• @TargetMonth@.Lag(1)
• @TargetMonth@.Lag(2)
• ...
• @TargetMonth@.Lag(12)

Here is the map shown in SpotlightXL:

Here is the map shown in Spotlight Model Manager: