Dynamic Planning How to Create and Load External Source Models

Data can be loaded 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.

The examples provided use the following Excel worksheet as the external source data. The worksheet contains many transactions in the following format. All columns except column F are label cells (cell content begins with '). Column F contains numeric cells.

1. Define a Master Model with required dimensions, then load metadata for all dimensions. In SpotlightXL, use the Model task and the Setup and Dimension subtasks to complete this step. In Spotlight Model Manager, use the + icon and Dimension Browser options to complete this step. Click Save after each operation. After defining the Master Model and its dimensions, select Generate Model.

   

   

2. Define a Source Model with fields and types. The Source Model is used as an intermediary step to get data from your external source into your Master Model.

   Use the Model task and the External Source Model, Source Model task to complete this step. Fields will be used in the Source Map to identify data from the external data source and to manipulate or transform data elements as needed. To make it easier to copy and paste the external source data onto the Source Data page, the fields should be listed in the order that they appear in your external source data, with calculated fields following.

   After typing in the fields and selecting their type, click Save.

   For details on the field types, expressions, and formulas, see Source Model Field Types, Expressions, and Formulas.

   In the example below, the source data contains 6 columns (rows 5-10). The Master Model requires that the transaction date (Time) be broken out into separate Month and Year dimensions, so expressions are added that render these fields. Since the expression MONTH[Time] renders as a month number without a leading zero (such as month 1, 2, 3, 10, 11, 12) , a constant "0" is concatenated ahead of the month number (such as 01, 02, 03, 010, 011, 012). Then final Month ID then is the calculated as the right two characters of that string.

   

3. Define the Source Map, which will connect the Source Model to the external Source Data. Use the Model task and the External Source Model, Source Map subtask to complete this step. In the Type field, select Data from the drop-down.

   In the map, list each field from the Source Model that will map to a Dimension Member or a Value in the Master Model. The Master Model is the Target Model.

   Note:

   The Master Model must be generated or else it will not be available in the Model drop-down.

   

   When done, click Save to save the map.

   Optional : You can specify one or more Filter Fields to help you select which data will eventually be loaded into the Master Model. For example, if you want to specify that only data in the Source Model in a specific department is loaded, you can add the department to the Filter Field area.

   

4. Load the data into the Source Model by selecting the Model task and the External Source Model, Source Data subtask. Specify the Source Model name and the rest of the page is populated with the current status. Notice that the column headings correspond to the fields listed in the Source Model.

   Click the Load Data drop-down and select Load File. Locate the file you want to load.

   

   If your source file is an Excel worksheet, instead of using Load File, simply copy and paste the rows from the Excel spreadsheet into the Source Data screen. Notice that the fields that are derived from other fields are not filled in yet because they are not coming directly from your source data.

   

   When done, click Load Data. Dynamic Planning loads the data without notification, and the mouse cursor shows a spinning action. When the mouse cursor returns to normal, you are ready for the next step.

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

   

6. Now that the data is loaded to your Source Model, create a calculation to copy the data to your Master Model. A calculation type called ExternalSourceMap is used to move data from a Source Model to a Master Model. Use the Model task and the Calculation subtask to complete this step.

   Specify the ExternalSourceMap type and the name of the Source Map you defined in step 3. Then add Aggregation to the calculation to ensure the rollups are calculated.

   Note:

   The Source Map must be of type Data before you can save the calculation.

   

7. Click Run to run the calculation to copy the data in the Source Model to the Master Model.

8. Create a view or report to see the data that is now in your Master Model.

   

9. Optionally use Drill Through on any data cell in the Master Model to see the original transaction data it came from.