Dynamic Planning External Source Model

Overview

With data source integration, you can import data from an external data 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.

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 Creating a Model and Using the Dimension Browser for Hierarchy Management.

• Define the structure of the Source Model using the Source Model page. See Source Model below.

• Define a mapping of the external data source to the Source Model using the Source Map page. See Source Map below.

• Load the data into the Source Model using the Source Data page. See Source Data below.

• Copy the loaded data from the Source Model into the Master Model using a calculation. See 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.

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

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

The fields entered here become column headings on the Source Data subtask page.

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.

In Practice: Adding a Field to an ESM

1. Let’s say you set up an ESM definition (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 Mode l and select the ESM you want to add the field to.

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, the field will be available to load data against.

In Practice: Deleting an ESM Field

To delete a field from an ESM:

1. Navigate to the Source Map page (Model > External Source Model > Source Map) and delete the field from the map.

2. Navigate to the Source Model page (Model > External Source Model > Source Model) and delete the field from the source model definition and save.

   Note:

   When a field is deleted, it is automatically removed from views and maps.
   Note:

   If you want to rename a field, delete the field and add a new one with the new name.

   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.

Source Map

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 to, 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 to.

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 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, 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 Target Dimension is None, so should 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 Target Member is None, so should 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, 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.

Using DimensionFilter

To use DimensionFilter :

1. Open the external source model map.

2. Identify which dimensions are not represented in your external source model.

3. In a blank row in the map, select the dimension in the Target Dimension column. You can use the drop-down.

4. In the Target Member column, type the name of the leaf-level member that all data in the ESM relates to. (In this release, you must type in the member name because the drop-down is not yet available.)

5. 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 Analytic Model map. The Target Member in the Target Model will be populated with data from Jan-18 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 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.

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.

Overview

Exploring the Variable Table

Field Descriptions

Exploring Variables

Exploring Expressions

Use Case

Tips and Tricks

Overview

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

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 to the master. For example, use a variable instead of a text field name fixed value like Jan-2020. The variable might be @Jan20@. When the calculation is run, the @Jan20@ variable becomes Jan-2020. Similarly, use a date variable such as @curMonth@.

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 ConstantDate type is expected.

When used in 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. Example 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. Variables 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 below.

Exploring Variables

Variable Values Dependent Upon Other Variables

Variables Used in Field Expressions

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

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-19 has a lead of 1 and Mar-19 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.

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

Below is information on the 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 prior to clicking Load Data.

In the screenshot below, notice that the fields that are derived from other fields are not filled in yet because they did not come directly from the source data. They are derived through the Expressions and Formulas on the Source Model page.

When done, click Load Data. Dynamic Planning loads the data without notification, and the mouse cursor shows a spinning action.

Click Refresh. Notice that the fields derived from other fields now contain information.