Using Model Design

This document describes how Dynamic Planning integrates with Structured Planning application. This is the traditional method of integrating with Structured Planning.

• Dynamic Planning pulls data from the Structured Planning

• Users can analyze and report on the data.

• Users can drill through rollup data in Dynamic Planning to see the original data aggregated from Structured Planning.

• Users can modify the data within Dynamic Planning and then write the data back to Structured Planning.

Definitions

• Dynamic Planning: Dynamic Planning is a multidimensional engine that connects to the Financial Reporting cube in Structured Planning. It does not connect to the Sales cubes.

• Horizon: An internal name that provides a pathway to access Structured Planning for Metadata download.

• Soap: An internal name that provides a pathway to access Structured Planning for Data download.

• Tenant Code: This is the code for Structured Planning and represents the way you identify which application you want to download from. The Tenant Code is not necessarily the same as the Tenant Name for Structured Planning.

• Source model: A model called HACPM_Financial. It connects to the specified Structured Planning application and stores the entire set of metadata that exists in that application. It is the conduit to get data into the Master/Target model.

• Master model: A model with a name of your choice. It is also called the Target model as it is the target of the metadata and data to be copied from the Source model.

See: 

• Connecting to the Structured Planning
• Setting up the Source Model in Dynamic Planning
• Validation Report for Duplicate Members
• Setting up the Target/Master Model using a Map

Target Model Design

Decide which dimensions from HACPM_Financial you want to use in your Target model, and then decide what type of dimensions there will be. Here are the two types:

• Key: Dimensions and members will change; generally, there will be more dimension members.

• Value: Dimensions and members that don’t change frequently and are consistently populated with data; generally, there are fewer dimension members.

Ensure that your application has enough capacity to store the Target model you intend to define.

• Multiply the Member Count for all Key dimensions together. This result will give you the minimum Master Model Keys value. For example, if your Key dimensions have the following member counts: Account = 80, Company = 10, Department = 60, Product = 30, and Project = 40, then your Master Model Keys value must be equal to or greater than 57,600,000.

• Multiply the Member Count for all Value dimensions together. This result will provide the minimum Master Model Value Block Size value. For example, if your Value dimensions have the following members: Scenario = 3, Time = 15, and Measures = 2, then your Block Size must be equal to or greater than 90.

• Go back to Manage > Application Administration > Application Settings and increase the values, if necessary, to Master Model Keys and Master Model Value Block Size to accommodate the capacity that you expect to have in your Target model. Save your changes.

Model > Setup

1. In SpotlightXL, navigate to Model > Setup. This clears the screen of information that you may have been viewing before.

2. Now select “New Model” and type in the name of the Target model.

3. Set the Type to Master.

4. Change Data Tracking is an optional feature that marks leaf-level data as dirty whenever it is changed, such as through user input, calculations, or data load. When the next full aggregation (Aggregation, None) is performed, only the rollups with dirty leaf-level data are aggregated. This makes full aggregations faster.

   Enabling Change Data Tracking is helpful in cases where users are updating one or two departments' data and need to run a full aggregation to see the data rolled up against all departments. Once Change Data Tracking is enabled, the application will track which data blocks are changing. When a full aggregation is run, instead of running the aggregation for the entire model, the system will run aggregation only for the blocks that changed (in this case, the departments that changed and the dependent rollup members), so the total aggregation process will take less time.

   There are steps to complete if you want to Enable Direct Access to PCR. Access the Using Direct Access to Structured Planning Financial Reporting for more information.

5. Next define the dimensions that you want in your Target model in the Dimension and Type list.

   • List the names of the dimensions from the Source that you want to use. You can use the same names as Structured Planning application or you can change the names. You will map one name to the other when you do the Mapping.

   • List the names of any new dimensions you want to use in your Target model.

   • Specify the type for each of the dimensions: Key or Value.

6. Click Save. The Master model is then created, and the Created On and Created By fields are populated.
   At this point, you have a Source model that has been generated. And you have a Master/Target model that has been created at the highest level (dimensions only), but not generated. You cannot generate the Target model until members are defined.

Model > Map

Here you specify which members of the Source dimensions will be copied to the Target/Master. You need to be familiar with the detailed outline of the Source and know what you want to achieve in your Target outline before continuing this process. You do not have to enter each members' name because you can use filters to designate which members to copy.

1. The Target Model is the name of the Master model you created in the previous step.

2. The Name lets you give this mapping a name so you can re-use it.

3. The Type indicates what should be copied from the source: the metadata, the data, or both. For first-time mapping, select Metadata or Both. The Data type is used for Write-back.

   Hints :

   • If you want to test your Target outline for effectiveness for the user first, select Metadata so you can experiment with the resulting outline. Note that Selecting Metadata/Leaf or Metadata/All will not require the Data Download step described below. You will only need to Generate the Model in order to move the metadata into the Target.

   • If you expect the data that will be copied to take a long time, you may want to copy just Metadata first, and then copy Both at a different time, such as overnight.

   • Eventually you will likely have more than one map defined for the model. But note that only one of those maps can include metadata as part of its definition (ie. type Metadata or Both).

4. The Source Model is HACPM_Financial.

5. Transfer = Leaf or All. Select Leaf. The All option does not apply to Data Integration with Structured Planning. The Structured Planning application store only leaf-level data, so there is no way to transfer parent data from Structured Planning application. Parent data is created only within Dynamic Planning using Calculations.

   

Source to Target Map Table

Here you must list all the dimensions from the Source (HACPM_Financial) under Source Dimension, whether or not you intend to use them, with exceptions noted below.

Hint: To get a list of all the Source dimensions, save your Map before you have specified any source dimensions, then go to Model > Setup, select HACPM_Financial, and then copy the dimension names to the Clipboard. Then open a new temporary workbook and copy the dimension names there as a holding place (remember to omit AttributeHierarchy and Attribute, and optionally omit BudgetEntity). Return to the original workbook, return to Model > Map, specify the Target Model and Map Name, then paste the dimensions from the temporary workbook.

Optional: Next, list all the dimensions that are new in the Target model (ie. not copied from the Source) under Target Dimension. Set Source Dimension, Source Filter, and Source Value to None. Target Filter must be set to Dimension Filter and Target Value should be one of the members of the dimension.

For Source Dimensions that you do not plan to use in your Target model, set Source Filter to Dimension Filter (to filter it out) and specify one member from the source dimension where the data will come from. Then specify None for the Target Dimension, Target Filter, and Target Value. If plan to read and write data back and forth between Structured Planning, and the Dynamic Planning application, the member you choose from the source dimension must be a leaf member. If you select a parent member, you will not be able to write back data to that member. In the screenshot below, IC Segment Main is a parent member, so you cannot write back data to it later.

In the first row of the table, there are drop-downs to assist you with selecting the best option for each mapping. In the second and following rows, there are no drop-downs but you can copy/paste the first row to the remaining rows.

Source Filter options

The Source Filter column defines both what metadata is copied into the Target and what data is copied.

Aggregation Operators

The following aggregation operators in Structured Planning application are recognized and used:

• + The value of the member is added to the aggregate value of the preceding sibling member(s).

• - The value of the member is subtracted from the value of the preceding sibling member(s).

• ~ The value of the member is ignored.

The following aggregation operators in Structured Planning application are not recognized and are not used. Dynamic Planning assigns them the operator.

• /

The following operator is available to you in the Dynamic Planning app. It does not come from Structured Planning:

• ! The value does not include the child member value in the consolidation to its own parent and it is not included in the rollup to any other dimensions either.

Source Value options

The Source Value column defines the starting point for the Source Filter choice. 

Target Filter options

The Target Filter usually is the same as the Source Filter, as listed below.

Target Value options

The Target Value column defines the starting point for the Target Filter choice.

When you save, the system checks the mapping for validity.

At this point, you still have a Source model that has been generated, you have a Target/Master model that has been created at the highest level (dimensions only), and you have defined the way that Dynamic Planning should populate the Target/Master with members, data, or both. The Target/Master model is not yet generated, does not contain members, and does not contain data.

Populating the Source Model - Data Download

Power user access is required for these steps.

Data Download is a Type option on the Calculation page (accessed via the Model task and Calculation subtask).

Select the Download Data (shown below) type, which will be executed when the Calculation is run. Dynamic Planning connects to Structured Planning, downloads data and fills in the data into the Source (HACPM_Financial).

Optimization

Data downloaded from the Structured Planning is time-consuming. We have optimized this process to keep track of the last date that data was downloaded and to download only what has changed since the last download date. This optimization is specifically for those using Scenario partitions in the Structured Planning. This is useful in cases where some data changes frequently (such as Budgeting data), while other data changes rarely (such as Actual data).

The Scenario partitions in Structured Planning have a Last Process Date. Dynamic Planning uses this date to track which data needs to be downloaded. Power-user access is required for these steps. This optimization is handled internally by Dynamic Planning, and no configuration settings are needed from the Power user.

This optimization is handled internally by Dynamic Planning and no configuration settings are required from the Power user.

Manage > Application Administration > Request Status

Selecting this option opens the Request Status page, which shows the results of processes. After running Data Download, a new entry will appear at the bottom of the table. The type of operation is shown along with the start date and time, followed by its status. If the status shown is a partial completion, such as a percentage complete, click Refresh after a few seconds to track when the operation is complete.

If the status shown is a Failure, the error message is listed.

You can also use the Audit Log under the Manage, Application Administration menu.

At this point, you now have a Source model that has been generated and contains data, you have a Target/Master model that has been created at the highest level (dimensions only), and you have defined the way that Dynamic Planning should populate the Target/Master with members, data, or both. The Target/Master model is not yet generated, does not contain members, and does not contain data.

Clearing the Source Model

You use the HACPM_Financial Source model as an intermediary step to store financial data. You download data into HACPM_Financial and then use a map to copy the data into a Master model. After you have copied data into the Master model, you may not need it anymore. You can use the Clear Model menu command to clear data from HACPM_Financial.

For example, when you first set up your Master model, you may choose to download the last 5 years of Actuals data from Planful applications. After that, you will regularly download only this year's Actuals and Forecast data and next year's Budget data. The 5 years of older Actuals data is no longer needed in HACPM_Financial.

1. Select Manage > Application Administration > Application Statistics. In the image below, HACPM_Financial is a source model and contains data.

   

2. Select Model > Setup, then select HACPM_Financial from the Model drop-down. Notice that HACPM_Financial is a Source model.

3. Click Clear Model and then confirm that you want to clear the data.

   

4. After clearing the model, note that the model is still Generated. This is different than clearing a Master or Analytic model.

5. Select Manage > Application Administration > Application Statistics. You see that HACPM_Financial now contains no data.

   

Populating the Target Model

Power or Contributor user access is required for these steps.

Model > Setup > Generate Model

1. Select the Target/Master model from the list box.

2. Scroll down to the Maps section. You should see the name of your map, the name of the Source (HACPM_Financial), the name of the Target (the Master model), and the type of transfer.

   

3. Use Generate Model to move data from the Source to the Target.
   

   When you click Generate Model, Dynamic Planning first checks to see if the values in your Application Settings page are adequate to accommodate the dimensions and members you will be downloading from the Source.

• The value in Master Model keys must exceed the number resulting from the # of members in each Key dimension multiplied by each other. For example, if you are expecting your Key dimensions to have members of Account=80, Company=10, Department=60, Product=30, Project=40, then your Master Model Keys value must be equal to or greater than 57,600,000.

• The value in the Master Model Value Block Size must exceed the number results from the # of members in each Value dimension multiplied by each other. For example, if you are expecting your Value dimensions to have membership of Scenario=3, Time=15, Measures=2, then your Block Size must be equal to or greater than 90.

If Master Model keys and Master Model Value Block Size are set to an adequate value, then model generation proceeds: members and/or data are moved from Source to Target/Master according to the options you set in the Map.

While the model is being generated, the status message at the bottom left of the Excel screen blinks. When complete, it says Ready. No messages are posted to the Request Status page.

At this point, you have a Source model that has been generated and contains data, and you have a Target/Master model that is generated, contains members, and may contain data depending on the Map type that you used.

Viewing the Default Calculation

Power or Contributor user access is required for these steps.

After generating the model, scroll down on the Setup page and you will see that Dynamic Planning has created one default calculation for you, with the same name as the model.

Model > Calculation

• Select the name of your Target model from the Model list box.

• Select that same name in the Name list box. This is the calculation that Dynamic Planning created and it has only one purpose, Aggregation.

• Set Run in Background to Yes.

• To be notified by email of the calculation’s successful completion, type your email address into the Success Notification and Failure Notification fields. When you run the calculation, you will receive an email upon its completion.

• Then click Save.

• To aggregate the data, click Run.

• Click Refresh to see the Last Run Status, showing the Aggregation started. When it is complete, clicking Refresh will show the successful completion and the number of seconds elapsed.

• Now your Master/Target model is populated with both leaf-level and parent data, and it is ready to use in Views and Reports.

Model Statistics

After connecting to Structured Planning and creating both Source and Target models, you can view the statistics on the models. Power user access is required for these steps. 

Manage > Application Administration > Application Statistics 

• Under Source Models, you will see HACPM_Financial, its disk usage, dimension and member counts, and number of records. 
• Under Master/Analytics Models, you will see your Target model, its disk usage, dimension and member counts, and number of records. 
• Scroll down further under Models, and you will see new entries for the Target model you generated.

Maintaining and Refreshing the Model

Now you are ready to set up your environment for regular updates from Structured Planning. You will create two calculations to maintain the model:

• Core App DW Download

• Model Refresh

Power or Contributor user access is required for these steps.