ESM (External Source Models) Canvas

Dynamic Planning External Source Model

The External Source Model (ESM) in Dynamic Planning enables the import of data from external sources into the system. ESM promotes collaboration and interaction between users and source models connected to the master or analytical models. You can easily load data from an Excel worksheet, a CSV file, or a TXT file by simply using the copy-paste (Ctrl+C or Ctrl+V) actions. 

Source Model

The Source Model serves as a vital intermediary step in the data transfer process, enabling the smooth flow of data from the external source into a Master Model or Analytical Model. To facilitate the copying of data from an external source, you need to create a Source Model that accurately reflects the organization and content of that source. 

The external data source consists of a two-dimensional list containing transaction-style records. Each record in this list comprises a data element and associated descriptive fields that provide additional information about the data.

It is common for the external data source to include fields that are not relevant to your intended purpose. In such cases, you can specify the fields to be ignored while copying.

Furthermore, the external data source may present data in a format different from what you require. To address this, you can utilize formulas and expressions to transform the data into the required format.

In Practice: Create an ESM Source Model

1. Within SpotlightXL, from the Tasks drop-down, select Model.
   
2. Go to Source Model > External Source Model > Source Model.
   
3. Enter a new name for the Source Model in the Value field.
    
4. Optionally, you can provide a Description of the ESM Model.
5. Enter the field data under the Field column, and select the corresponding Type from the drop-down menu.
   
   Note:

   The Field data type must be compatible with the selected Type, which can be Text, Numeric, Date, Formula, ConstantText, ConstantNumeric, or ConstantDate.
   
6. Specify the format for the Date fields in the Format column.
   Note:

   The Format column is only applicable to Date field types and supports specific formats.
   
7. In the Expression column, enter the required formula for the Formula field.
   Note:

   The Expression column is only relevant for Formula field types, and you can only use predefined formulas provided in SpotlightXL.
   
8. Optionally, add Variable Name, Type, Format, and Value to create variables that can be used throughout the model. If multiple constant fields have the same constant value, you can assign a variable name. Changing the variable name will update the corresponding value throughout the model.
   
   Note:

   Variable Names must start with @ and support specific values like ConstantText, ConstantNumeric, ConstantDate, and ConstantMonth.
9. Optionally, add the Expression Name and Value to be used across the model. It allows for convenient changes to be made to the same expression name across multiple locations in the models simultaneously.
   
   Notes:

   • Expression Names must begin with # and are meant explicitly for Formula values.
   • Text fields within expressions should always be enclosed in square brackets. For example, SUM([Numeric1], [Numeric2]).
10. Click Save.
    
11. When you save the model, the system automatically populates the Include in the Data Load column. In this column, Yes indicates that the corresponding data comes directly from the source, while No means that the data is derived from formulas or constants.
    
    Notes:

    • In addition to using the Source Model as an intermediary step, you can create a new Master Model or Analytical Model to transfer the data from the Source Model. 
    • Alternatively, you can transfer the data from the Source Model to an existing Master Model or Analytical Model if one is already available. 
    • This flexibility allows you to manage and organize the data according to your specific requirements and existing models.

Source Data

The Source Data facilitates the loading of the External Source Model into a Target Master Model or ESM. It acts as a translation mechanism, mapping and transforming the fields and data from the source model to fit the structure and requirements of the target model. By configuring Source Data, you define the rules for data transfer, including field inclusion, mapping, and any necessary transformations. This ensures accurate and seamless data transfer between the source and target models.

Loading Data to the ESM Models through SpotlightXL

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.

In Practice: Manually Load Data from Source Data Page

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.

In Practice: Bulk Load data from 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.

In Practice: Load data through a DLR from PCR

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.
14. 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.
15. Click Next.
    
16. Optionally, 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.
    
    Note:

    If you leave everything unchecked, the system will retain everything in your ESM and append the additional data to your load file.
17. The Manipulate Input File section displays the added model information. Click Next.
    
18. In the Define Data Mapping section, select the Source Column and the Map to data.
    
19. Click Next.
20. On the Load Data section, select the Data File.
    
21. Click Finish.
22. Click Refresh once the data load is completed. The derived fields are populated.
    

Source Map

The Source Model 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. 

In Practice: Defining 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.
    

Run 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.