Transferring Data from One External Source Model to Another External Source Model

Overview

Modeling supports copying data from one External Source Model to another External Source Model. Just as you can copy data from an External Source Model into a Master or Analytic model using an ESM map, you can use the same method to copy data from one ESM to another. You must first define both the Source and the Target ESMs using the Source Model page, then you can define the Source Map to transfer the data. When the map is run, all data in the Target ESM is deleted before the data transfer from the Source Model is done.

A common use case for this feature would be to store your data load inputs in one ESM and your calculated/formula fields in a second ESM. The formula fields in the second ESM depend on data from the first ESM, so you can copy the requisite fields to the second ESM, which automatically calculates the formula fields. After verifying the data load, then load the data from the second ESM into your Master model.

Example Source ESM

In the examples in this section, the Source Model is Budget. The Source Model has 8 fields. Here is the data in the Source Model.

Example Target ESM

In the examples in this section, the Target ESM is Revenue. The Target ESM also has 8 fields but some fields are different than the Source Model.

Defining a Map from a Source ESM to a Target ESM

The Source Model is a two-dimensional table of data organized into fields. The Target ESM must contain at least as many data-input fields as the Source Model. You will map the fields from the Source Model to the fields in the Target ESM. You must provide a mapping for all the data-input fields in the Target ESM; data-input fields are those marked as Yes for Include in Data Load.

1. Go to Model > External Source Model > Source Map.

2. Enter a name for the map.

3. From the Model drop-down list box, select or specify the name of the Target 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.

   
   

4. Select the Source Model from the Source Model drop-down.

5. Select Data for Type.

6. Set Append Missing Dimension Members to No. This option applies only to mapping to Master or analytic models.

7. The Filter Field is optional. When filters are defined in an ESM to ESM map, only the filtered data is moved from the source ESM to the target ESM. For example,let's say a filter is defined on a Department Segment field and Dept1 is the value. When this map is executed from a Calculation, the map will only move the Dept1 data from source ESM to target ESM. Additionally, the Dept1 data only in the target ESM will be cleared before the data is moved from source to target

8. Now specify the fields from the Source Model that map to fields in the target ESM. Not all fields in the Source must be mapped, but you must provide a mapping for all the fields in the Target ESM that are marked as Yes for Include in Data Load.

9. For fields in the Target ESM but not in the Source Model, specify Field: None, Maps To: DimensionFilter, the field name in the Target ESM under Target Field, and the text, number, or date to be placed into the target model under Default Value.

   
   

10. Save the map.

Using a Calculation to Load Data into the Target ESM

To copy data from the Source Model to the Target ESM, you must use a calculation to run the External Source Map. When the map is run, all data in the Target ESM is deleted before the data transfer from the Source Model is done, if there are no filters defined in the map. If there are filters defined in the map, the data in the target ESM will be deleted based on the filters and the source data will also be filtered based on the filters defined.

Calculations are stored within a Master or analytic model. We suggest that you create the calculation inside the Master model that the ESM data will eventually be loaded into.

1. In SpotlightXL, go to Model > Calculation.

2. Select the Master model associated with your ESMs from the Model drop-down.

3. Give the calculation a name.

4. Create the first step of the calculation by selecting ExternalSourceMap from the Type drop-down.

5. Select the name of the map you created in the previous task from the name drop-down.

6. Save the calculation.

7. Run the calculation.

   
   

When using this calculation to execute the ExternalSourceMap, the existing records in the Target ESM are deleted, and then records from the Source Model are copied to the Target ESM. To verify the data in the Target ESM:

1. In SpotlightXL, go to Model > External Source Model > Source Data.

2. Select the Target ESM from the drop-down and click Refresh. If there were any formula fields in the Target ESM, they are calculated and displayed on the screen.