Dynamic Planning Model Maps

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.

For example, let’s say you have the map below where you are moving 4 projects from the source model (January Release) to the target model JanuaryReleaseV1).

Match Criteria is blank. The result after running the calculation would be the model below that contains the 4 projects you moved from the JanuaryRelease model to the JanuaryReleaseV1 model.

Now, let’s say you add 2 additional projects to the JanuaryReleaseV1 model. So, the JanuaryRelease model has 4 projects and the JanuaryReleaseV1 model now has 6 projects. Because the number of projects differ between the two models, the calculation will result in an error when you run it. To avoid the error, select Common for Match Criteria on the Map page as shown below.

How to Create a Model Map

In Practice

1. In SpotlightXL, select the Model task and the Map subtask.

2. Select the Target Model, which is the model you want to copy data or metadata to.

3. Enter the name of the map (for example, the name of the Target Model).

4. For Type, select Metadata to copy only metadata from the source to the target model. Select Data to copy only data from the source to the target model. Select Both to copy both metadata and data from the source to the target model. Lookups apply to Metadata. Make sure you select Metadata or Both if you plan to use Lookup functionality.

5. In the Source Model cell, select the model to copy data or metadata from.

6. For Transfer, select Leaf to transfer leaf-level dimension members only. Select All to transfer all members from the source to the target model.

7. The Write Back ID is optional and only required to write data back from a model to Planful Structured Planning, Consolidation, or Reporting applications. See: Write Back Process in Dynamic Planning.

8. The Access Token is optional and only required to move data from one Dynamic Planning application to another. See: How to Move Data from One Dynamic Planning Application to Another.

9. Use Target Dimension/Lookup to customize dimension member labels for display within a model or an analysis.

10. For Source/Target Dimension, enter the dimension that exists in the source/target model that you plan to copy data or metadata from and to.

11. In the Source/Target Filter cell, select from one of the following options:

    • AllMembers – Map all members from the source model to the target model.

    • LevelAndAbove – Members at 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 only displays 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.

12. For Source/Target Value, your selection is based on the source or target filter. There are two options; either the value is not specified for the dimension so all members are displayed (None). Or, the value is specified meaning that all data associated with that value is displayed. Various combinations of dimension values represent a unique combination.

One-to-One Mapping

A One-to-One mapping happens when the source filter and value is 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.

How to Move Data from One Dynamic Planning Application to Another

This feature works for data only. No metadata can be moved.

To move data from one application to another, perform the following steps:

1. Generate an access token. For steps on how to do so, see the Access Token documentation.

2. Access the Model task and the Map subtask.

3. In the Access Token field (shown below), enter the generated Token ID or common name you provided on the Manage > Token Management > Access Token page.

   

4. Enter source and target model information. For example, your source model is located in a Departmental application and your target model is located in the Corporate application.

5. Complete the remaining fields on this page and save.

How to Generate an Access Token

To generate an access token, select the Manage task and the Token Management > Manage Access Token subtask. Select a user from the list box. Click Generate and a token is provided in the Access Key field as shown below.

Once you generate an access token for a user, the Generate action is replaced with the Remove action shown below.

Because access tokens are encrypted, you can give your token a more common that is easy to remember. To do so, select the Manage task and the Token Management > Access Token subtask as shown below.

Enter the access Token ID provided in the Access Token field. In the Name field, enter a common and easy to remember name. You can use this common name when creating a map to move data from one application to another.

To view a user’s access token, access the User page by selecting the Manage task and the User Management subtask. The access token access key is provided as shown below.

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.

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

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