Using External Source Model Functions

External Source Models support the use of functions within formulas for ease of calculations.

You can transform or modify data coming from the external data source using formulas. In all formulas, fields used as the arguments in the syntax must be enclosed with square brackets [ ].

The following function types are similar to those supported in Excel.

• Multiple Data Types
• Text
• Numeric
• Math
• Logical
• Date

Functions for Multiple Data Types: Numbers, Dates, or Text Strings

• MIN(a1, a2, a3, ..., a255 ), returns the smallest or oldest in a series of up to 255 numbers, dates, or text strings. All arguments must be of the same data type. At least 2 arguments must be provided.
• See Example: Using EOMonth, EDate, Min, Max, and Text Functions
• MAX(a1, a2, a3, ..., a255 ), returns the largest or newest in a series of up to 255 numbers, dates, or text strings. All arguments must be of the same data type. At least 2 arguments must be provided.
• See Example: Using EOMonth, EDate, Min, Max, and Text Functions 
• DROPDOWN(“Parameter1”, “Parameter2”, “Parameter3”,...... ) function returns the data in a dropdown form, where you can select the text, numeric, and date field types from the dropdown.
  see Example: Using the Dropdown Function.
• DROPDOWN("Model::[ESM Model Name]","[Key Field Name]" ) returns the dropdown value with the Key Field Name from an existing External Source Model name. See Example: Dropdown function with External Source Model Field referenced values.

Example: Using EOMonth, EDate, Min, Max, and Text Functions

The following External Source Model uses six input fields and one functions.

Three fields are inputted:

• Account 
• Department 
• ID
• Joining Date
• Salary 
• Bonus

The other one field has function:

• Total uses the SUM function to specify salary and bonus calculation

The external data source contains the following fields.

After loading this data into the External Source Model, the five formula fields are calculated.

Example: Using the Dropdown Function

The DROPDOWN(“Parameter1”, “Parameter2”, “Parameter3”,.....) function returns the data in a dropdown form, where you can select the text, numeric, and date field types from the dropdown.

• When you use the dropdown on the text field, the parameter is defined as text in double-quotes separated by a comma.
• When you use the dropdown on the numeric field, the parameters are defined as normal numbers, decimal numbers without double quotes and separated by a comma.
• When you use the dropdown on the date field, the parameters are defined in date format in double-quotes separated by a comma. Date field type supports six types of formats (MMM-YYYY, MMM-YY, DD/MM/YYYY, MM/DD/YYYY, DD-MM-YYYY, and MM-DD-YYYY).

Following are the limitations for using the dropdown function:

• You cannot use duplicate values for the parameters
• You cannot use more than 100 parameters
• Parameter values must be case insensitive
• You can only enter those values manually which are already present in the dropdown.

For example, let’s say you want to list the numbers of units sold per region in a month as shown below.

You can select the region from the dropdown that is defined in the source model.

You can select the units sold from the dropdown that is defined in the source model.

Example: Dropdown function with External Source Model Field referenced values

This function returns the dropdown value with the Key Field Name from an existing External Source Model name.

For example, let’s say you have one existing External Source Model with Dropdown function, and you want to create a new External Source Model model with Dropdown function by reusing the existing External Source Model fields.

The existing External Source Model is named as DropdownSource. The following images gives you a view of DropdownSource model and the fields.

The following image display the source data of DropdownSource model.

If you want to use the DropdownSource fields in the DropdownTarget model, create the DropdownTarget model using the dropdown function with External Source Models field referenced values as shown in the below figure:

The DropdownTarget model can now use the existing DropdownSource fields and you can select the Units and Month field valued from the dropdown list as shown in the below figure:

Transferring Data from Analytical Model to ESM Model

You can now transfer data from Analytical Model to ESM. Previously, we could transfer Analytical Model data to Analytical Model, and ESM data to ESM or Analytical Model. In the Source Model drop-down list, you can now see Analytical and ESM options which you can map to the Target ESM.

In Practice: To Transfer Data

1. In SpotlightXL, select the Model task> External Source Model> Source Map.
2. Enter the name of the Source Map and add the description.
3. Select the type as Data for loading external source data.
4. Select the Analytical model you want to map the target ESM.
   
5. Select any of the following in the Maps To column:
   1. DimensionFilter: This filter indicates that there is no field present in the Source ESM to map to the Target ESM. You will have to specify a value in the Default Value Column.
   2. 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.
6. Run the Source Map to check the data in the ESM.