Dynamic Planning How to Load an External Data Source into a Master Model (Legacy)

This task describes how to load data from an External Data Source into a Master Model. Dynamic Planning uses an intermediary model called a Source Model to make it possible to load data from outside sources. 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.

There are two overall approaches to loading external data:

• Loading data into a generated Master Model with all of its dimensions predefined. See Loading Data into an Existing Generated Master Model.

• Loading data and creating a new Master Model at the same time, including creating all dimensions and dimension members from fields in the external data source. See Loading Data and Creating a New Master Model.

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

Loading Data into an Existing Generated Master Model

1. Define a Master Model with required dimensions, then load metadata for all dimensions. Use the Model task and the Setup and Dimension subtasks to complete this step. Click Save after each operation. After defining the Master Model and its dimensions, go back to the Setup page and click 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 (CSV, TXT, or other file). The fields should be listed in the order that they appear in your external source data. Type is either Text or Numeric. After typing in the fields and selecting their type, click Save.

   

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.

   The fields you provide here should be the same fields listed in the Source Model definition. Specify if each field contains information that will map to a Dimension Member or a Value. Specify the Target Dimension (the dimension in the Master Model).

   Optional: You can also specify one or more Filter dimensions to help you review the data on the Source Data page.

   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.

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, Source Model Map, and Target Model (the Master model) defined in steps 1, 2, and 3. Notice that the column headings are pre-populated with the fields listed in the Source Map and 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.

   

   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.

   If you specified Filter dimensions in the Source Map, you can use them now along with the Refresh button to review the data on the Source Data page.

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

   

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

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

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

   

   

Loading Data and Creating a New Master Model

1. 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 a 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 (CSV, TXT, or other file). The fields should be listed in the order that they appear in your external source data. Type is either Text or Numeric. After typing in the fields and selecting their type, click Save.

   

2. 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 Model field, specify the name of the new master model you want to create. In the Type field, select Both from the drop-down.

   The fields you provide here should be the same fields listed in the Source Model definition.

   • In the Type column, specify if each field contains information that will map to a Dimension Member or a Value.

   • In the Target Dimension column, specify the name of the dimension that will be created in the Master Model.

   • In the Dimension Type column, specify if the new dimension will be of type Key or Value. You MUST have at least one Key and one Value dimension. A Key dimension type will most likely change, unlike a Value dimension (for example, Time) that does not normally change.

   • In the Target Value column, for DimensionMember rows, specify at what level in the hierarchy this dimension member should be created. Level 1 is parent of Level 2. Level 2 is a parent of Level 3, and so on. For DimensionValue rows, specify the name of the dimension member that this value will be associated with.

   • Optional: You can also specify one or more Filter dimensions to help you review the data on the Source Data page.

   

   When done, click Save to save the map.

3. 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. The Source Model Map and Target Model (the Master model) defined in steps 1, 2, and 3 will be automatically populated. Notice that the column headings are pre-populated with the fields listed in the Source Map and 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.

   

   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.

   If you specified Filter dimensions in the Source Map, you can use them now along with the Refresh button to review the data on the Source Data page.

   If you make a mistake and want to load data again, go back to the External Source Model, Source Model subtask, select the Source Model name from the drop-down, then click Clear Model. Clear Model removes data from the Source Model.

4. Now that the data is loaded to your Source Model, you are ready to create the new Master Model. Use the Model task and the External Source Model, Source Model subtask. Select the Source Model name from the drop-down. Click Generate Model. Generate Model clears already generated model data, builds the dimension hierarchies, aggregates source model data, and copies the data to the Master Model.

   

   A notification appears on the screen that the operation has been queued for processing.

5. Select Model, Setup, and select the name of the Master Model from the Model drop-down. The model has been created, has a type of Master, with the dimensions created as specified in the External Source Map, and an aggregation calculation has been run.

   

6. Take a quick look at the default View for the new model, and Zoom In on a few dimensions to verify that the data was loaded properly.