Dynamic Planning_External Source Models

How to Create and Load External Source Models

Purpose: This guide provides instructions to create and load External Source Model data into Master Models using SpotlightXL.

How to Create the Master Model

1. In SpotlightXL, choose Select Task → Model.

2. Go to Select Subtask → Setup.

3. Enter a Model name.

4. Set Type = Master.

5. Enter your Dimensions.

6. For each dimension, choose Type = Key or Value.

7. Save the Master model — do not generate yet.

8. From Select Subtask, choose Dimension to add hierarchies.

9. From the Dimension drop‑down, select the dimension to edit.

10. Enter Member Code, Member Parent, and Rollup Operator for each dimension.

11. Return to Select Subtask → Setup and click Generate Model.

How to Define a Source Model

1. In SpotlightXL, choose Select Task → Model.

2. Go to Select Subtask → External Source Model → Source Model.

3. In Source Model, enter a name under Value.

4. In Field, list the dimensions created earlier in the exact order they appear in the external data file.

5. For Type, pick a type for each dimension. If a specific format/formula is required, enter it in Expression and click Save.

Note: Expressions won’t save if the Field and Expression differ (case‑sensitive).

How to Define a Source Map

1. In SpotlightXL, choose Select Task → Model.

2. Go to Select Subtask → External Source Model → Source Map.

3. Enter a Name for the map.

4. In Model, select the Master Model you created.

5. In Source Model, select the Source Model you defined.

6. Set Type = Data.

7. In Field, list every field defined in the Source Model.

8. In Maps To, pick the appropriate level each field maps to.

   • Ensure exactly one “Value” is selected under Maps To; it must correspond to a Numeric type in the Source Model.

9. Specify the Target Dimension (dimension in the Master Model).

10. Select a Target Member for each field and click Save.

How to Load Data

1. In SpotlightXL, choose Select Task → Model.

2. Go to Select Subtask → External Source Model → Source Data to load data into the Source Model.

3. Pick your Source Model.

4. Go to Load Data → Load File, browse to your file, and select Load Data.

5. After the load completes, click Refresh — derived fields will be populated.

How to Create a Calculation (to move Source data into the Master Model)

1. In SpotlightXL, choose Select Task → Model.

2. Go to Select Subtask → Calculation (this will load data into the Master Model).

3. Enter a Name for the calculation.

4. For Type, add ExternalSourceMap; in Name, enter the Source Map name.

5. Add a second Type = Aggregation (to include aggregation in the calc).

6. Click Run.

7. Click Save.