Using Filter Field Section

Using Filter Fields to Limit the Data Load

If you want to load only a subset of data in the Source model, use the Filter Field section of the Source Map to indicate which rows of data to load. In the following example, the map indicates that only rows with Department Aerospace should be loaded from Budget to Revenue.

Here is the resulting data.

Formula Field added to Filter Fields in Maps

Formula fields are also available as filter fields in the ESM Maps. You can apply the Formula fields as filters while moving data from one External Source Model to another External Source Model, Master, or Analytic Model.

For example, if you want to load only a subset of data based on a specific calculation in the Source model, use the Filter Field section of the Source Map to indicate which rows of data to load. In the following example, the map indicates that only rows with Department Aerospace that have the Project End as 02/01/2020 should be loaded from Revenue to Budget. In this example, Department is a Text field, and Project End is a Formula field.

Once you run this Map, the data corresponding to the Aerospace Department with Project End as 02/01/2020 is segregated in the output.

Here is the resulting data.

To move data from the source ESM to target ESM you must create a calculation and then run the External Source Map. For more information on creating a calculation, see Run Calculation with a Map

When a map is run, Reverse Filtering is applied to preserve all existing data in the target ESM that does not satisfy the filter condition. So, when you select formula filters in the map, the data that meets the filter condition will be appended to the existing data in the output.

For example, if you want to load data only for the rows of the Aerospace Department that have the Project End calculated as “04/01/2020”, you have to update the filter field in the Map as shown in the image below.

In the following example, you can see that only rows with the Department as Aerospace and that have the Project End calculated as "04/01/2020" are loaded from Revenue to Budget and appended to the data existing in the output.

Business Example

Below is an example of data mapping from an ESM to an Analytical Model. Here, you can load the rows with Gateway Cycle as the Customer Full Name field value. The data can be loaded from Purchase Information to Outline Purchase Information.

Here, Customer Full Name is the formula field that should be mapped to the Customer field in the Target dimension. The formula condition being:

CONCATENATE ([Customer First Name], “ “, [Customer Last Name])

To move data from Purchase Information to Outline Purchase Information, you must create a calculation and run the Purchase Map. For more information on creating a calculation, see Run Calculation with a Map

Based on the Calculation, rows with the Customer Full Name as Gateway Cycle are loaded from the Purchase Information to Outline Purchase Information and appended to the data existing in the output. So, when you run the calculation and select the Scenario Dimension as 13, the uploaded data corresponding to the Gateway Cycle as the Customer’s Full Name Product will be updated in the output.

Business Value

With formula filters in the maps, you have the flexibility to transfer Formula field-specific data from one ESM to another instead of copying all the existing data.

It significantly reduces the effort and time previously involved to segregate data based on Formula fields and enhances the existing filtering with more options to group data.

In Practice: To use Formula Field in Maps

1. In SpotlightXL, select the Model task and the Map subtask.

2. Select the name of the Source Model defined on the Source Model page.

3. Enter a name for this map and add the description.

4. Select the type as Data for loading external source data.

5. Select or specify the name of the ESM you want to map the Source ESM to.

6. For Append Missing Dimension Members , select No when mapping to another external source model.

7. For the Filter field, specify the name of the field in the Source ESM to filter the data you want to load. Under the Value column, you can specify the value name.

8. Specify the name of the formula under the Filter field and the value.

9. For the Fields column, specify field names from the Source Model that will be mapped to fields in another model.

10. Select any of the following in the Maps To column:

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

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

11. In the Target Field column, select the name of the field in the target ESM.

12. In the Default Value column, specify the text, date, or number to be updated into this field.

    For information on the steps to create an ESM to ESM map and run a Calculation, see the Transferring Data from One External Source Model to Another External Source Model topic.

Filtering Data with ESM Filters

You can filter data with ESM filters in the application.

'Enable Filters in ESM Source Data' to yes to filter data.

When you enable the flag, you can see the 'Include in Filters' column in the source Model screen. To provide value for the filter, you can either copy-paste or type the value. Also, you can only enable the Include in filters column for a maximum of 5 fields.

In Practice: Enabling the Filter Flag

1. In SpotlightXL, select the Manage screen.

2. In the Application settings drop-down, select Application Administration and then click on Application Settings.

3. Scroll down to enable the Enable Filters in ESM Source Data flag. The value for this flag is set to No by default.

In Practice: Filtering data in Source Data load

1. Select the Model task, and from the Source Model drop-down, select Source Model.

2. Define Source Model Name in the Source Model page.

3. Define Field and type in the Source Model page and click Save.

4. You will see the Include in Filters column available.

5. You can set this column value to Yes for the specified fields.

In Practice: Loading data in the Model

1. Select the Model task, and from the Source Model drop-down, select Source Data.

2. You can either copy-paste the values or enter them manually to load the data.

3. For the Filter field, specify the name of the field in the Source ESM to filter the data you want to load. Under the Value column, you can specify the value name.

4. After you refresh the page, you will see the filtered data based on the specified field.