Source Map

The Source Map is a two-dimensional data table, while the Master Model or Analytical Model is multi-dimensional. To integrate the data effectively, you need to map the fields from the Source Model to the dimensions and members in the Master Model or Analytical Model. 

Define the Source Map, which will make it possible to load the External Source Model into a target Master Model or into a target ESM.

When mapping to a Master model :

Specify if each field in the source contains information that will map to a Dimension Member or a Value.

Specify the Target Dimension (in the Master Model) and that dimension's type.

When mapping to another external source model :

Specify if each field in the source contains a Value that will be copied to a field in the target ESM.

If the target ESM has extra fields, specify None and DimensionFilter for the Field and Maps, and specify the default value for the target field.

Options Available on the Source Map Page

Information on the options available on the Source Map page is provided below.

Name - Enter a name for this map or select it from the drop-down.

Model - The name of the target model. When mapping to a Master model :

Select or specify the name of the Master Model you want to map the Source Model.

When mapping to another external source model: Select or specify the name of the ESM you want to map the Source ESM to. In the drop-down list box, the ESMs are listed separately under a sub-heading called ESM.

Source Model - Select the name of the Source Model defined on the Source Model page. This is an external source model.

Type - Always use the type Data for loading external source data.

Append Missing Dimension Members - When mapping to a Master model: Select Yes or No. If the data loading process detects a leaf member that does not exist in the Master Model, selecting Yes will automatically add that member to the target dimension.

When mapping to another external source model: Select No.

Filter Field and Value - Specify the name(s) of one or more fields in the Source ESM to use for filtering the data you want to load. Under the Value column, you must specify a leaf-level field. For example, if your external source data contains transactions for departments of Sales, Finance, and Manufacturing, you must name the specific departments you want to load. You cannot specify to load all departments under a particular rollup. You specify the fields on the Source Map page. Ensure that there is one blank row between the Filter Field section and the Field List section on this page.

Field and Maps To - When mapping to a Master model: The Field list represents fields from the Source Model that will be mapped to dimensions in the Master Model.

Available types:

DimensionMember: indicates that this field in the external source maps to a dimension member name.

Value: indicates that this field in the external source is a value to be stored at the intersection of the dimension members listed in each transaction in the source data.

When mapping to another external source model: The Field list represents fields from the Source Model that will be mapped to fields in another External Source Model.

Available types:

DimensionFilter: indicates that there is no field in the Source ESM to map to the field in the Target ESM. If you select DimensionFilter, you must specify the Default Value column.

Value: indicates that this field in the external source is a value to be copied as is to the specified field in the target ESM.

Target Dimension - This column is available only when mapping to a Master model.

Select the name of the dimension in the Master Model to correspond with this field. For example, the Field "Net Income Accounts" corresponds with the Target Dimension "Net Income" in the Master Model.

Select None if you are mapping a Value to the intersection of all previously listed dimensions (which were mapped from fields). If the Target Dimension is None, so should the Target Member be None.

Target Member - This column is available only when mapping to a Master model.

Select LeafMembers if the text in this field of the external data source corresponds to a member name in the target dimension.

Select None if you are mapping a Value to the intersection of all previously listed dimensions (which were mapped from fields). If the Target Member is None, so should the Target Dimension be None.

Alternatively, you can type in the name of the dimension member.

Match Criteria - This column is available only when mapping to a Master model.

Match Criteria Common loads data only when it can find a match between the fields in the source data and the leaf members in the Master Model. Select Common if you want the Data Load process to skip any Leaf Members it does not recognize.

Target Field - This column is available only when mapping to another ESM.

Select the name of the field in the target ESM to correspond with this field in the source ESM.

Default Value - This column is available only when mapping to another ESM.

Leave this column blank if Maps To is Value.

If Maps To is DimensionFilter, then specify the text, date, or number that should be placed into this field for all rows in the target ESM.

Example of mapping external data to an existing Master Model.

The following map accomplishes the same goal so long as the data load file contains 'Actuals' in the Scenario field:

Support for DimensionFilter in External Source Model Maps

You have the option to use a mapping type of DimensionFilter in External Source Model maps. ESM maps are used for being able to copy data from an ESM to a Master model. The DimensionFilter map type is useful for identifying how to map external source models to Master models in situations where there is not a one-to-one mapping between fields in the external source and dimensions in the Master model. Master models may have more dimensions than fields in the ESM, but all dimensions in the Master model must be represented in the map.

For example, assume you have an external source model with three fields: Company, Department, and Account; and you have a Master model with four dimensions: Company, Department, Account, Time. Since the Time dimension is not available in the external source model, you can still map the ESM to the Master model by using the DimensionFilter map type to identify which leaf-level member of the Time dimension all the data in the ESM relates to.

How to Use DimensionFilter?

1. In SpotlightXL, go to Model > External Source Model > Source Map.
2. Open the required external source model map.
3. Identify which dimensions are not represented in your external source model.
4. In a blank row in the map, select the dimension in the TargetDimension column. You can use the drop-down.
5. In the Target Member column, type the name of the leaf-level member that all data in the ESM relates to. 
6. Select DimensionFilter in the Maps To column.
   

Support for Variables in External Source Model Maps

You can use variables in ESM maps for Filter Fields Values and Target Members. Additionally, you can use variables in the following scenarios: 

• Moving data from one ESM to another (variables can be used on Default and Filter Fields of type text and date) 
• Moving data (on target members) from ESM to the Master and Analytic models

In the image below, the @enterprise@ variable value is used for the Filter Field Sales Segment. Using the filter field allows you to move only a subset of data from the Source model. Use the Filter Field section of the Source Map to indicate which rows of data to move. In the following example, the map indicates that only rows of the Sales Segment associated with the enterprise variable should be moved from the ESM to the target model. For example, SalesEnterprise1, SalesEnterprise2, and so on.

In the example below, a variable is used for the month and an expression for the preceding months within the field mapping of an ESM to an Analytic Model map. The Target Member in the Target Model will be populated with data from Jan and the 5 months trailing (from the Source Model).

You can select variables from the Variable Manager window if you are moving data from ESM to the Analytical or Master model. You have to enter the variable value manually when you are moving data from one ESM to another as shown in the below image.

The SKIP functionality is supported when you are using variables in ESM. Use the SKIP option when you have a Map or Formula that uses variables (usually multiple variables). You can build one master map or formula to accommodate data loading or formulas for a whole series of data intersections. Then when the calculation is run that executes the formula or map, you can skip those data intersections that do not need to be loaded or calculated.

How to Define a Source Map?

1. Click Model.
   
2. Navigate to External Source Model > Source Map.
   
3. Select the Source Model from the dropdown list.
   
4. Enter a Name for the map.
   
5. Optionally, provide a Description for the map. 
6. Select the Target Model from the drop-down list.
   
7. In the Field column, select the required field.
   
   Note:

   The available fields depend on the selected Source Model.
8. Select the required option from the dropdown list in the Maps To column.
   
   Note:

   • Ensure you select at least one numeric field and one text field for data transfer to the analytical or master model.
   • For numeric fields, use the Value option.
   • For text fields, date fields, constant text fields, etc., use the DimensionMember option.
   • Use the DimensionFilter option to exclude specific fields from data transfer to the target model.
9. Select the Target Dimension, Target Member, and Match Criteriafrom the dropdown lists.
   Notes:

   • The Target Dimension refers to the dimensions in the selected Target Model.
   • For numeric fields, enter the exact Leaf Member name in the Target Member field (which must be a Leaf member).
   • Select YES for Append Missing Dimension Members if you want to create dimension members missing in the source model. Selecting NO will show an error if the dimension member is missing.
10. Click Save.
    

How to Run a ESM Calculation?

1. Navigate to Model > Calculation.
2. In the Model field, select the target model.
   
3. Enter a Name for the calculation.
   
4. In the Type column, select ExternalSourceMap.
   Note:

   The Type column should always be set to ExternalSourceMap.
5. Select the map name in the Name column.
   
6. Click Save and then click Run.
   
7. Click Refresh.