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.
   

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.

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.

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.