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

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.

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.