Using SpotlightXL- External Source Model Subtask

External Source Models offer the ability for the end user to interact and collaborate on source models that are connected to overall corporate master models. ESM lets users make changes directly to the data in the source model. Contributors can then reload the data into the corporate model whenever desired.

With data source integration, you can import data from an external source into Dynamic Planning. Data can be imported from an Excel worksheet, a CSV (comma-separated values) file, or a TXT file using copy and paste (Ctrl+C or Ctrl+V) actions. Data can also be loaded using integration tools such as Boomi.

External Source Model Benefits

The External Source Model feature set is enhanced to support a wider variety of operational Dynamic Planning use cases like Sales Operations, Marketing Operations, Customer Support Operations, and Professional Services Models. Key benefits include:

• Enhancements to support operational Dynamic Planning use cases.
• A formula engine that supports multiple functions like arithmetic, logical, date, and text. These functions allow you to perform data transformations and to define the formulas on the ESM model.
• Ability to load data from a wider variety of data sources by using data transformations at data load time instead of being required to have a perfect data load file. Contributors can transform data with formulas and functions:
  • Support for text functions like Left, Right, Concatenate, Len, Trim, Find, Lookup, Text
  • Support for arithmetic functions like Sum, Avg, Round, Floor, Ceiling, RangeLookup, + - * /
  • Support for date functions Year, Month, Day, DaysCount, DaysFactor, Days, WeekNum, Date, EOMonth, EDate, SUM, + -
  • Support for logical operations with IF, AND, OR, ISBLANK, < > =
  • Support for functions that work with multiple data types, like MIN and MAX
  • Lookup functions to refer to assumptions from driver models
• Enhanced map capabilities when loading the data from ESM to the Master model: to filter the data that is being loaded, and to detect and dynamically modify the dimension hierarchy by appending missing metadata members.
• Enhanced Drill Through functionality for loading data into a Master model from multiple different ESM models.
• Ability to define formulas that are dependent on dates (such as start_date and end_date).
• Ability to manage ESM models from Model Manager (web).
• Ability to use Views on ESM models to interact with the data that is in the ESM models.
• ESM support in both SpotlightXL and Model Manager (web).

List of Key External Source Model Features

• Formula Engine
• Map Enhancements
• Model Manager
• Drill Through
• Views on the ESM model

External Source Model Process

Using SpotlightXL or Model Manager, you can set up and work with external data sources. The following process shows 5 steps in the basic flow to working with external data.

To walk through the basic steps using SpotlightXL, see How to Create and Load External Source Models.

To learn how to transform data using expressions and formulas, see External Source Model Field Types, Expressions, and Formulas.

How to Create an External Source Model Using Model Manager?

Define a Source Model with fields and types reflecting the structure of the external data source you are copying data from.

• Your external data source is assumed to be a two-dimensional list of transaction-style records where each row provides a piece of data along with fields that describe that data.
• Your external data source may contain a lot of fields that you do not need and you can indicate that they be ignored.
• Your external data source may contain information that is in a different format than you want, so you can transform that data using formulas and expressions.

Power and Contributor users can create an External Source Model from the Model Manager Grid or List layouts. Reviewer users cannot create an ESM model.

1. In Spotlight Web, navigate to the Dynamic Planning > Model.
2. Click the + Circle icon from the Grid or List layouts to create a new model.
   
   
   
3. Click the tabular icon to indicate that you want to create an External Source Model.
   
   
   
4. Give the new model a name and an optional description.
   
5. Click the Save icon.
6. Proceed with adding fields. See Creating New ESM Fields Using Model Manager.
7. Click the Save icon again when done.

How to Create Fields in the External Source Model Using Model Manager?

Before creating new Source Model Fields:

• You cannot create new fields if a Source Model Map exists because an existing Source Model Map contains mappings from the existing list of fields.
• You cannot create new fields if the Source Model contains data. You must clear the model first.

1. Login to Spotlight Web.
2. Select Model.
3. Scroll to find the External Source Model you want to view.
4. Click the gray text that says External Source Model.
   
   
   The list of existing fields, if any, appears.
   
5. Click ADD. The Create Fields box appears.
6. Click Add Fields. A new field is added with a default type Text. Under Label, enter a name for the field. Continue by adding additional fields.
   
   
   
   1. Type: Select Text, Numeric, Date, Formula, Constant Text, Constant Numeric, or Constant Date, based on the content and purpose of the column in your external data source.
   2. Label: Enter the name of the field. Fields will be used in the Source Map to identify data from the external data source (CSV, TXT, or other file) that will become dimensions and dimension members, or data.
      Best Practice: List the fields in the order that they appear in your external data source to make it easier to load the data.
   3. Format: For Date and ConstantDate types, select the layout that the date or Expression is formatted in.
   4. Expression: For more details on Data Types, Expressions, and Formulas, see External Source Model Field Types, Expressions, and Formulas. For ConstantText types, enter the text to place into this field. For ConstantDate types, enter the date to place into this field. For ConstantNumeric types, enter the value to place into this field.
   5. DataLoad: Yes indicates that this field is coming directly from the data source, and No indicates that this field is derived from a formula or constant.
      If you make a mistake you can click the trashcan icon to delete a field.
      
      Note:

      When you add a Formula field, you are adding a placeholder. You will fill in the formula separately.
7. Information on the options available in the Create Field box is provided below.
8. When you are finished, click Done.
   Now you see the fields you added to the list of fields. You can click each item to make changes to the fields.
   
   If you have added any fields of type Formula, you must fill in the formula before you can save all the fields. See Creating New ESM Formulas below.
9. When done making all changes to fields, click the Save icon.
   

How to Create Formulas in the External Source Model Using Model Manager?

If your Source Model contains fields of type Formula, you can create the formulas using Model Manager's interactive formula builder.

1. Login to Spotlight Web.
2. Select the Model task.
3. Scroll to find the External Source Model you want to view.
4. Click the gray text that says External Source Model.
   

The list of existing fields appears.

The right pane displays the formula builder options and editor. You can type in a formula where it says Enter formula here... or you can select from the drop-down list of functions and operators.

In this example, we will create a unique Customer # by combining other fields:

Customer # Preface - State - Phone Number

1. From the Functions list, select Concatenate. The basic syntax for this function is inserted into the editor.
   
   
   
2. To ensure that the formula will be assigned to the correct field, enter the field name before Enter Formula.
   
   
   
3. Select the text Field 1 and press Del. Type @ to see a list of field names to insert. Select Customer # Preface.
   
   
   
4. Select the text Field 2 and replace it with the second field name to use: State.
5. Select the ... after Field 2 and replace it with the third field name to use: Phone Number. Clean up excess brackets as needed.
   Now the formula looks like this:
   
   
   
6. To add the dashes between each field in the formula, add a comma and " - " as follows:
   
   
   
7. Click Set Formula.
8. Click Saved Formulas to see the list of formulas created and saved so far.
   
   
   
9. When done making all changes to fields and formulas, click the Save icon.
   

View Formulas in External Source Model

In your External Source Model data, you can view the formula associated with a calculation at a cell intersection in the formula bar of Spotlight Web and SpotlightXL. If your External Source Model data has a formula in the form of an expression, you can view the resolved formula associated with that expression in the formula bar.

Let us take the following example to understand the details. In any Source Model containing a formula or a formula with an expression, you can view the complete formula in the formula bar of Spotlight Web or SpotlightXL. For example, in the following image, you can see two formulas configured for the ESM model, Total Team Count and Total Team Budget, where Total Team Count uses a variable @CurrentMemberCount@  in the formula, while Total Team Budget has an expression #TotalTeamBudget#  associated with it.

When you view this ESM model in the Spotlight Web or SpotlightXL, you can view the complete formula details including the resolved variable for Total Team Count in the formula bar as shown in the image below.

The following is the image in SpotlightXL.

Similarly, for Total Team Budget, the application validates the value configured for the #TotalTeamBudget# expression and displays the complete formula including the resolved expression in the formula bar instead of the expression name as you can see in the image below.

The following is the image in SpotlightXL.

How to verify formula fields on the External Source Model page in SpotlightXL?

1. In SpotlightXL, select the Model task and the External Source Model > Source Model subtask.
2. Select a model and navigate to the available Formula fields.
3. Verify the formula details in the Expression column. For example, variables, expressions, and individual formulas.
   

How to Validate Formula in the Formula Bar?

1. In Spotlight Web or SpotlightXL, select a View and navigate to any cell intersection that has a calculation associated with it.
2. View the formula in the formula bar.
   

Limitations

• If the Enable Save property is set to Yes and the Calculation on Save property is configured with a calculation, then the formula configured in the calculation will be displayed in the formula bar. For any other calculation where the Enable Save property is not set to Yes, then the formula will not be displayed.
• If the Enable Save property is set to Yes and the Calculation on Save property is configured with a calculation. Still, if the application runs a different calculation, then the formula displayed in the formula bar and the formula configured in the calculation will not match.
• If multiple formulas are configured for any intersection, then the application will display only one formula in the formula bar.
• How the formula is displayed in the SpotlightXL varies slightly from how it is displayed in the Spotlight Web. The formula in the SpotlightXL starts with an additional =@SpotlightFormula in SpotlightXL.

External Source Model Field Types, Expressions, and Formulas

External Source Models support the use of expressions and formulas in laying out the Source Model and in calculations.

• Data Types

Data Types

The following data types are supported.

• Text
• Numeric
• Date
• Formula
• Constant Text
• Constant Numeric
• Constant Date

Change Constant Field Values without Clearing External Source Model Data

You can directly change values for the Constant fields available in the External Source Model (ESM) without clearing the model data. For example, if you have four constant fields along with other fields in your ESM and you want to change the values for two Constant fields, then you can directly change the values in the Expression column for these constant fields without clearing the model. You can view all the updated values in the Constant fields Source Data Load.

In addition, you can view all the changes made to the Constant fields and Formula fields in the Audit Log, thereby easily tracking all changes made to the model data at one place.

Let us take the following example to understand the details.

In any Source Model containing fields of ConstantText, ConstantNumeric, and ConstantDate type, you can change the existing cell values with new values. For example, in the following image, you have a Source Model containing

Department Name, Department ID, Department Date, and Department Manager fields that are of type ConstantText, ConstantNumeric, ConstantDate, and ConstantText respectively.

You can change the value of any Constant fields based on your requirement and update the fields with new values.

Once you change the required values in your source model, you can create a new calculation for your ESM and run the calculation to view the changes in the Source Data Load page.

While creating a new calculation, you must select ExternalSourceFormula as the value in the Type column and select your source model as the value in the Name column. After creating the new calculation, you must Save and Run it.

Once you run the calculation, to verify the updated values you can navigate to the Source Data Load page. You can view all the new values updated for the Constant fields in the Source Data Load page when you enter values in the respective Data Load fields, and click Refresh.

You can also view all the new values updated for the Constant fields in the Source Data Load page without creating a calculation. You can click the Load Data option, and then click Refresh to view the updated values.

Whenever you update any value for the Constant fields, you can track all the changes made to your model in the Audit log as shown in the image below.

How to Change Constant Field Values on the External Source Model Page in SpotlightXL?

1. In SpotlightXL, select the Model task and the External Source Model > Source Model subtask.
2. Select a model and navigate to the available Constant fields.
3. Select a Constant field and enter a new value in the Expression column.
   
   
   Note:

   You can update values in the Expression column for multiple Constant fields.
4. Click Save.

   

How to Create a New Calculation for ESM?

1. In SpotlightXL, select the Model task and the Source Mode > Calculation subtask.
2. Select any model and specify the required calculation name in the Name field.
3. In the Type column, select ExternalSourceFormula from the drop-down list.
4. In the Name column, select your source model name from the drop-down list.
5. Click Save, and then click Run.

How to Verify Changes to Constant Fields in the Source Data Load Page?

1. In SpotlightXL, select the Model task and the External Source Model > Source Data subtask.
2. Enter the required values in the Data Load fields and click Refresh.

How to Track Changes Made to the Model?

1. In SpotlightXL, select Manage > Application Administration > Audit Log.
2. Click the required cell in the Details column and navigate to the formula bar.
3. Verify all the changes made to the values of the Constant fields.

Limitations

• You cannot add new rows to the existing model or delete existing rows without clearing the model.
• You cannot change the Field column values from the existing model without clearing the model.

Loading Data from an External Data Source into a Master Model

• There are five tasks to successfully load data from an External Data Source into a Dynamic Planning Master Model.
• Create the Master Model and define its dimensionality. See Spotlight Web > Model tab and Using the Dimension Browser for Hierarchy Management.
• Define the structure of the Source Model using the Source Model page. See the Source Model below.
• Define a mapping of the external data source to the Source Model using the Source Map page. See the Source Map below.
• Load the data into the Source Model using the Source Data page. See the Source Data below.
• Copy the loaded data from the Source Model into the Master Model using a calculation. See the External Source Map step in a calculation.

For an example of working with External Source Models, see How to Create and Load External Source Models.

For a detailed use case, see Step-by-Step Use Case for Forecasting Revenue Spread Over Time.

Loading Data to the ESM Models through SpotlightXL and Spotlight Web

There are three ways to load data from an ESM model to another model:

• Manual Entry: Data can be entered manually field by field from the source data page of the target model.
• Bulk Entry: Bulk Entry allows for the efficient loading of large datasets by importing them directly from the source data page using file formats like CSV or TXT.
• PCR (Planning, Consolidation, and Reporting) Data Load Entry: Data can be loaded from the ESM model to the target model using planning, consolidation, and reporting tools that provide dedicated interfaces for managing and transferring data.

These options provide flexibility in transferring data between models, allowing you to select the most suitable approach based on your specific needs and requirements.

Source Data

Source Data is used to load data into the Source Model or to review data already existing in the Source Model. To see if your Source Model contains data, click Refresh. If your Source Model does not contain data, fill in the fields provided. Then either select the Load Data, Load File option to load data from a file, or copy and paste data from your external data source into the grid of cells under the column headings.

Options Available on the Source Data Page

• Source Model - Select the name of the source model defined on the Source Model page.
• Total Records - A read-only field that shows how many records are in the Source Model.
• Current Page - If your Source Model contains more than 5000 records, you can page through the data by selecting a page number from the drop-down.
• Current Status - A read-only field that shows the current status of the load operation.
• Last Run Time and Last Run Status - Read-only fields that show the last time a load was run and its status. Expand this row to see the details.
• Field - Columns are provided for all field names entered on the Source Model page. If there is data in the Source Model, it appears automatically. Or you can click Refresh.

To load more data, copy and paste data in this area before clicking Load Data.

How to Load Data from Source Data Page in SpotlightXL?

1. Go to Source Model > External Source Model > Source Data.
   
2. Select the newly added Source Model name from the Value dropdown menu.
   
3. Enter the data in the added fields.
   
4. Once the data is added, click Load Data. After the loading process finishes, click Refresh to update the view and see the newly populated data fields.

How to Load Bulk Data from the Source Data Page?

1. Click the Load Data dropdown and select Load File.
   
2. The Source Model Data Load window appears.
3. Click Browse to locate the file that needs to be loaded.
4. Select the required Separator from the dropdown list.
   Note:

   The Separator is only applicable when loading TXT files.
5. Click Load Data.
   Note;

   You can upload CSV, Excel, and Text files.
   
6. A confirmation message will appear, stating that your request is being processed, and you will receive a notification once the process is completed.
   
7. Upon receiving the notification, refresh the screen to update the displayed information.

Loading Data to the ESM Models through DLR (Data Load Rule)

You can load data to your ESM models from PCR (Planning, Consolidation, and Reporting) by using the DLR (Data Load Rule) feature. To create a new Data Load Rule for an ESM, you must have an already existing external source model.

How to Load Data through a DLR from Structured Planning? 

Creating a New Data Load Rule:

1. Navigate to Maintenance > DLR.
   
2. Click Data Load Rules.
   
3. Click New Data Load Rule.
   
4. Enter a Name for the DLR.
   
5. Select any Load Type from the dropdown list. Below are the recommended options:
   • File Load 
   • Web Services
   • Copy – Paste
6. Select External Source Model from the Load Item dropdown list.
   
7. Under Load Sub Item, select the name of the ESM.
   
8. Click Next.
9. On the Select Sample Input File section, if you are loading from a file, specify its file type. These are the options available: 
   • .csv
   • .txt
   • .xls
   • .xlsx
10. If you select the .txt file, specify the Column Delimiter from the available options, for example, Comma, Tab, and Semicolon.
11. By default, {CR}{LF} is selected in the Row Delimiter field.
12. Select None from the Text Qualifier drop-down.
    
13. Select Data File under File Information. Notice that the Header section is pre-populated with information about your external source model and the number of fields it expects.
    Note:

    For the Number Format, we support only Comma as the Thousands Separator and Dot as the Decimal Separator.
14. Click Next.
    
15. In the Define Overall Rule Settings screen, fill in the following details:
    • Select External Source Model details, in the General tab.
      
      Note:

      • On the Define Overall Rule Settings section, you can select the Include in Clear Data checkbox DLR to handle clearing the existing data and loading the new data.
        • If you leave everything unchecked, the system will retain everything in your ESM and append the additional data to your load file.
      • If you want to clear all data before the load, use the Clear Model command from the SpotlightXL External Source Model menu, or Clear Model from the More menu in Model Manager.

      
    • Select Model and Calculation from the Calculations tab.
      
      Note:

      Users will be able to select a calculation from any model that needs to be executed right after the data load rule (DLR) is successfully executed. This will automate the calculation process every time the ESM DLR is run, and users will receive a notification once the calculation is complete.
16. The Manipulate Input File section displays the added model information. Click Next.
    
17. In the Define Data Mapping section, select the Source Column and the Map to data.
    
18. Click Next. On the Load Data section, select the Data File.
    
19. Click Finish and click Refresh once the data load is completed. The derived fields are populated.
    

Details and Limitations

• Data Manipulations in a Data Load Rule are currently not supported for External Source Models.
• The Data Load Rule & the External Source Model are locked during the processing. The ESM cannot be updated or deleted, data cannot be loaded or moved from the same or any other Data Load Rule, and calculations cannot be triggered on a locked model. Similarly, the Data Load Rule cannot be updated or deleted while it is locked.
• A data integrity check happens before the actual data load and in case of any exceptions, the data load is aborted.
• Existing Web Services APIs (Boomi), Load Data & Transfer Data are extended to External Source Models.
• Clear Data on the Data Load Rules screen is currently not supported for External Source Models. Use the Clear Model menu command from the specific ESM in Model Manager, or use SpotlightXL to clear all the data in an External Source Model.

Best Practice Recommendations

• We recommend using only one DLR per ESM. Loading data to an ESM via multiple DLRs should be avoided.
• If the Power user would like to reload the data by clearing all the data in the ESM then we recommend using the Clear Model functionality for the specific ESM from Model Manager or SpotlightXL.
• Loading data via DLR is not supported if the Dynamic Planning application is not integrated with the PCR application. Please contact Planful Support for additional info on this.
• We recommend no more than 120 fields in one ESM model.
• We recommend no more than 5 million records in one ESM model.
• Define the ESM model and required formulas, load some sample data, and then validate the data and formulas. Once everything is working as expected, you can define the map to move the data from ESM to the Master Model. At any given point in time, users can update the formula expressions; however, if there is data loaded into an ESM model or if a map is defined, then making structural changes to the ESM model (such as adding a field, changing the type of the field, deleting the field) is not allowed. You must clear the data before making changes to the fields.
• When using copy and paste or when using a file to load data into an external source model, all the existing data in the model will be cleared and the new data loaded.
• Contributor and Reviewer users do not have the option to create a view on an ESM model. Only Power users have the option to create views.
• To create an ESM view, it is mandatory to select a map from the associated master model and also to select a POV dimension which will serve as a filter in run mode. For Contributor and Reviewer users, the filter dimension from the associated master model will filter the dimension members based on the dimension security defined for the Contributor and Reviewer users.
• ESM views where saving data is enabled allows users to load up to 10,000 records. If there are more records, then the recommendation is to leverage filter dimensions to filter the data input into smaller chunks.
• Use a single quote to input the dates in SpotlightXL.

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.

Source Model

Define a Source Model with fields and types reflecting the structure of the external data source you are copying data from.

• Your external data source is assumed to be a two-dimensional list of transaction-style records where each row provides a piece of data and fields describing that data.
• Your external data source may contain many fields that you do not need and you can indicate that they be ignored.
• Your external data source may contain information that is in a different format than you want, so you can transform that data using formulas and expressions.

The Source Model is used as an intermediary step to get data from your external data source into a Master Model.

Options Available on the Source Model Page

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

Source Model - Enter a name for the source model.

Field - Enter the names of the fields. Fields will be used in the Source Map to identify data from the external data source (CSV, TXT, or other file) that will become dimensions and dimension members, or data. Best Practice: List the fields in the order that they appear in your external data source to make it easier to load the data.

Type - Select Text, Numeric, Date, Formula, Constant Text, Constant Numeric, or Constant Date, based on the content and purpose of the column in your external data source.

Format - For Date and ConstantDate types, select the layout that the date or Expression is formatted in.

Expression - For more details on Data Types, Expressions, and Formulas, see External Source Model Field Types, Expressions, and Formulas. For Formula types, enter the formula to apply to the data. This can be a simple calculation or a function. See the Formulas below. For ConstantText types, enter the text to place into this field. For ConstantDate types, enter the date to place into this field. For ConstantNumeric types, enter the value to place into this field.

Include in Data Load - This column is filled in by the system. Yes indicates that this field is coming directly from the data source, and No indicates that this field is derived from a formula or constant. 

You can update an ESM by adding new fields and deleting unwanted fields AFTER the data is loaded and the map(s) and view(s) are defined. 

How to Add a Field to an ESM in SpotlightXL?

1. Let’s say you set up an ESM definition in SpotlightXL (on the Source Model page accessed by navigating to Model > External Source Model > Source Model.
   
   
   
2. Now, let’s say you want to add a field called Status to the ESM Source definition as shown below.
3. Navigate to Model > External Source Model > Source Model and select the ESM you want to add the field.
4. Enter the new field and select the Type as well as whether or not it will be included in the data load. There is no need to delete maps and views to perform this step. The map and views associated remain unchanged.
   
   
   
5. Save and navigate to the Source Map page (Model > External Source Model > Source Map).
6. Select the additional field and complete the Maps To, Target Dimension, and Target Member fields. Then, Save.
7. Navigate to the Model > External Source Model > Source Data page and the field will be available to load data against.

How to Delete an ESM Field in SpotlightXL?

1. In SpotlightXL, go to the Source Map page (Model > External Source Model > Source Map) and delete the field from the map.
2. In SpotlightXL, go to the Source Model page (Model > External Source Model > Source Model) delete the field from the source model definition and save.

In addition to Refresh, Save, and Delete, this page also provides the option to Clear Model. Clear Model removes data from the Source Model. This option is typically used after you have created the Source Map and loaded data using the Source Data subtask, while you are testing your expressions and formulas, or after you have loaded data, verified it, and are ready to move on to the next batch of data.

Support for Variables and Expressions in External Source Models 

You can use variables and expressions in External Source Model (ESM) Formulas and Maps. Variable and expression usage can simplify ESM maintenance. For example, let’s say you use a #service end# named expression with an EDATE function. Maintenance becomes easier because you can simply update the #service end# expression with a new value in a single place and there is no need to modify any of the expressions where #service end# is used.

In SpotlightXL, navigate to Model > External Source Model > Source Model. The Variable Table and Named Expression fields are populated.

Exploring the Variable Table

In the Variable table portion of the screen, enter variables to be used in the map to transfer data to a source model and the master. 

You can also use a variable expression such as "@curMonth@+1" or "@curMonth@-7" instead of the field names. The order of field names is a sort order in the ESM definition.

Information on each of the fields in the variable portion of the table is provided below.

Field Descriptions

Variable Name:

Variables must start and end with the @ symbol. For example; @StartMonth@, @FixedTerm@, and @PlanStart@. The maximum number or characters supported for the variable name is 120 characters. The @ symbol cannot be used in the name portion nor are the following special characters supported in the variable name: /."$*<>:;|?]+[]{}()#%&=`-_,'

Type:

You can use variables of type ConstantMonth anywhere where the ConstantDate type is expected.

When used in the ConstantDate context, the value is automatically converted to the first day of the month. To add a month, use a Lead operator. To subtract a month, use the Lag operator. An example is provided below.

Format:

The Format field is applicable for ConstantDate and ConstantMonth types.

Value:

Value is the constant value. Variable values can depend on other variables. Variable values cannot refer to fields or named expressions. To fully explain how variable values work, several use cases are provided below in the Exploring Variables section.

Exploring Variables

Variable Values Dependent Upon Other Variables:

In the example below, the @FixedTerm@ is a variable which is being used in another variable (i.e. @HalfTerm@). The @FixedTerm@ variable value is set to 12. @HalfTerm@ variable is using @FixedTerm@ and the result is 6.

Variables Used in Field Expressions:

Variables can be used in field expressions for constant and formula fields. For example, the DATE expression value is Month (which is 12), Day (which is 31) and the next year substitution variable.

Exploring Expressions

Expressions consist of a Name and Value as shown in the image below.

The Expression Name must start and end with the hashtag (#). The Expression Name cannot contain special characters and the hashtag cannot appear within the name. The following special characters are not supported: /."$*<>:;|?]+[]{}()@%&=`-_,' Expression Values are formulas/functions that use fields.

Examples of expression names and values are provided below.

Example of an Expression Name and Value:

Example of an Expression Value that Uses the @FixedTerm@ Variable

Examples Where Expressions Use Other Expressions:

Example of a Named Expression Used in an ESM Field Expression:

When using a named expression in the Expression field, you can specify its name only. If the named expression is used, no functions or operators are allowed.

Rolling Month Use Case

Let’s say you have the following expressions and you want to simplify this setup.

To do so, create a named expression called #df# and refer to it from the field.

Using the named expression, the result will look as follows for the Jan-19 row:

However Feb-22 has a lead of 1 and Mar-22 has a lead of 2. So, the result will look as follows:

Tips and Tricks

If you have several fields to populate as shown in the table below, the following trick can reduce the amount of typing.

Enter the first expression fully. For the rest, do not enter anything inside the curly braces. Ensure that there are spaces around the column as in the example below.

Examples of Include in Clear Data Loads

Here are some examples of what happens with your existing data when you load new data into an External Source Model, using the Include in Clear Data checkboxes on the Define Overall Rule Settings section of the Data Load Rule.

Before the data load, here is the existing data.

Here the new data to be loaded.

Example 1: Include in Clear Data is checked for the Department column

When the Department field is checked, the DLR examines the new data load file to see which Departments are in it. In this case, the load file uses only one department: FIN. All data in the existing ESM with Department FIN is then cleared before the new data is loaded.

The resulting data is as follows.

Example 2: Include in Clear Data is checked for the Department and Company columns

When the Department and Company fields are checked, the DLR examines the new data load file to see which combinations of Department and Company are in it. In this case, the load file uses the following combinations: 1010 FIN. All data in the existing ESM with Company 1010 and Department FIN is then cleared before the new data is loaded.

The resulting data is as follows.

Example 3: Include in Clear Data is checked for all columns

When all fields are checked, the DLR examines the new data load file to see which fields have common values amongst all the rows. 

The two new records are loaded. The resulting data is as follows.

Arabic and Chinese character support for ESM Data Load

You can enter Arabic and Chinese characters in an external source model (ESM) Data Load Rule (DLR) and upload it to Dynamic planning.

Backup and Restore of External Source Models

Power users can back up and restore External Source Models and their artifacts. The Model Backup menu item is available only to Power users and can be found on the More menu of the External Source Models listed in Model Manager. The Model Restore icon is on the menu ribbon and is available only to Power users.

This feature is available only in Spotlight on the Web.

All backup and restore operations are recorded in the Audit Log.

You can back up the ESM two ways:

• Without Data: backs up the model metadata, ESM model definition including fields and formulas, ESM maps, and view artifacts.

• All: backs up the same items as Without Data and also backs up all of the data. If you do not have the option to backup All, then you need to request Backup Data to be enabled on your application.

Dynamic Planning creates a .ZIP file and saves it to the Downloads folder locally. The maximum size of the .ZIP file is 200MB or as configured for your application.

As with analytic and master models, you can restore the model as is or restore it with a different name. Restore is available on the menu ribbon. It restores all related artifacts and creates the external source model. The ESM will be in a generated state.