Using Write Back

To enable write back capabilities from Dynamic Planning back into Planful Structured Planning, Consolidation, and Reporting applications, perform the following:

• Set up a Data Load Rule in Planful Structured Planning, Consolidation, and Reporting applications to load the data.

• Use this Data Load Rule in Planful Structured Planning, Consolidation, and Reporting applications in conjunction with a Map in Dynamic Planning to facilitate the movement of data.

• Specify settings required to enable Web Services in both Planful Structured Planning, Consolidation, and Reporting applications and Dynamic Planning.

Now that you have copied metadata and data from Structured Planning, Consolidation, and Reporting applications to the Dynamic Planning application, you have the option to use your new model read-only or read-write. There is not a live link between Structured Planning, Consolidation, and Reporting applications and the Target model, so any changes you make in the Dynamic Planning model remain only in that model unless you explicitly write them back to Structured Planning, Consolidation, and Reporting applications. Power user access is required for these steps.

To set up your environment for write-back:

• Structured Planning, Consolidation, and Reporting applications: set up access to the Web Service

• Structured Planning, Consolidation, and Reporting applications: set up a Data Load Rule that will accept incoming data from Dynamic Planning

• Dynamic Planning: set up the URL address pointing back to Structured Planning, Consolidation, and Reporting applications

• Dynamic Planning: set up a Data Map to map dimensions and members back to Structured Planning, Consolidation, and Reporting applications

• Dynamic Planning: set up a Calculation to run the Data Map

• Structured Planning, Consolidation, and Reporting applications: run a report to validate that data has been changed

With this setup, you will be able to run the Calculation whenever you want to write data back to Structured Planning, Consolidation, and Reporting applications.

Structured Planning, Consolidation, and Reporting Applications: Configure Access to Web Services

In Structured Planning, Consolidation, and Reporting applications, login and select the Application that will receive data updates from Dynamic Planning.

In Practice: Maintenance > Configuration Tasks

1. Select Web Service Access.

2. On the Web Service Access page:

3. Under API, select Enable.

4. Look at the State Free Authentication URL and copy/paste it into Notepad or another document for safekeeping.

5. In the Available Users box, specify the username that will have access to both the Structured Planning, Consolidation, and Reporting applications and the Dynamic Planning application. Then use the right arrow to move the username into the Email User(s) list so that you will receive an email at the conclusion of each write-back.

   
   

6. Click Save and Complete.

Structured Planning, Consolidation, and Reporting Applications: Configure a Data Load Rule to Accept Data from Dynamic Planning

Be sure that you are still logged in. Now you will create a data load rule that will accept incoming data from Dynamic Planning.

In Practice: Maintenance > Data Load Rules

1. Click New Data Load Rule. Then specify:

   1. Rule name

   2. Load Type: Web Services

   3. Load Item: Data

   4. Load Sub Item: GL Data

      
      
      

2. Then click Next or Select Sample Input File.

   Specify the following items:

   1. Row Containing Header: 0

   2. Number of Rows to Skip: 0

   3. Number of Source Fields: the number of segments in your Structured Planning, Consolidation, or Reporting applications (not your model) plus three (Fiscal Year, Fiscal Month, and Amount)

   4. Do not change the other fields on this page.

      
      

3. Click Next or Define Overall Rule Settings.

   Specify the following items:

   1. Reporting: Common Currency

   2. Credit/Debit: No for negative numbers, Yes for positive numbers

   3. Data Type: MTD or YTD (whichever you chose in your initial download Map)

   4. Calculate Trial Balance: No

   5. Currency Conversion: either Yes or No

   6. Scenario Type: All

   7. Scenario: select the Scenario containing the changes that are to be written back

   8. Frequency: Month

   9. Time Mapping: Down Rows

   10. Automatic Cube Refresh: Yes

   11. Roll Annual Balances: No
       Here is an example:
       
       

       Do not change the “Defaults” and “Clear Data Combination” sections.
   12. 
       
   13. 
       
   14. 
       

4. Click Next or Manipulate Input File.

   No changes on this page.

5. Click Next or Define Data Mappings.

   From the list boxes, select the segments that correspond to the dimensions in your Map, in alphabetical order. You do not need to map Scenario, Reporting, Time, or Measures. The last 3 columns must be Fiscal Year, Fiscal Month, and Amount.

6. Click Next or Load Data.

   Select the Scenario you chose in your Map or that data has been changed in.

7. Click Finish.

Planful Financial Model Setup

Once you have configured your Web Services API and Data Load Rule in Planful Structured Planning, Consolidation, or Reporting applications, you will need to update some settings in Dynamic Planning to ensure your Planful Structured Planning, Consolidation, or Reporting applications Financial model is set up properly for write back. You will configure your CPM Settings as well as run a Metadata Download in Model Design.

CPM Setting Configuration

Add Horizon and SOAP Uri/Login/Password/Tenant details for use during write back.

1. In SpotlightXL, select the Manage task and the Application Administration > Applications Settings subtask.

2. Enter the following information:

   1. Horizon Uri = https://demo06.planful.com (API server for each environment will be different, validate with Support to ensure proper Uri if you are encountering issues)

   2. Horizon Username = your user name for logging into HACPM Structured Planning, Consolidation, or Reporting applications

      • Example – jdoe@planful.com

   3. Horizon Password = your password for logging into HACPM Structured Planning, Consolidation, or Reporting applications

   4. Horizon Tenant Code = tenant code for your application that you are writing back to

      • Example = DWMAINKK6

   5. SOAP Uri = paste the State free authentication URL from Web Services Configuration section above

      • Example - https://demoha.planful.com/PlanfulAPI/PlanfulAPI_StateFree.asmx

   6. SOAP Username = your user name for logging into HACPM Structured Planning, Consolidation, or Reporting applications

      • Example = jdoe@planful.com

   7. SOAP Password = password created during Web Services Configuration

   8. Value Block Size = 1000 (there is no need to change this value)

3. Click Save.

Metadata Download

Download metadata from the Structured Planning, Consolidation, or Reporting application to Dynamic Planning. Perform the following actions:

1. In SpotlightXL, select the Manage task and the Metadata Download subtask.

2. Click Run.

3. Once the download completes select the Model task and the Dimension subtask to validate the dimensionality.

   1. Select a dimension that has been downloaded from Planful Structured Planning, Consolidation, or Reporting applications (such as Account) and validate the members are displayed as expected.

   2. Any time you change metadata in Planful Structured Planning, Consolidation, or Reporting applications, execute the Metadata Download action to ensure that Dynamic Planning is in synch.

Write Back Process in Dynamic Planning

There are two steps that need to be performed to complete the write back process.

• Create a valid Map from Dynamic Planning to Planful Structured Planning, Consolidation, or Reporting applications.

• Create a Calculation to execute the Map.

Valid Map

Create a map from your source model to the Structured Planning, Consolidation, or Reporting application's Financial model to complete the write back process.

1. In SpotlightXL, select the Model task and the Map subtask.

2. Configure the map with the following properties:

3. Target Model = HACPM_Financial

4. Type = Data

5. Source Model = <model where data is originating from>

6. Transfer = Leaf or All

7. Write Back Id = name of Data Load Rule created in Planful PCR Applications

8. Click Save.

Best Practices

• Ensure that the metadata for HACPM_Financial has been downloaded from your Planful Structured Planning, Consolidation, or Reporting applications so that the mapping is valid (for example, all members in the source model exist in the target model).

• Map all segments identified in your Planful Structured Planning, Consolidation, or Reporting applications.

• Ensure that the members for which you are mapping are consistent between HACPM_Financial and your Source model. For example, if you have 2 members (Office Supplies and Rent) under a parent called “Expenses” you will need to have the same two members in both the Source and the Target. See sample map below.Sample map definition in Dynamic Planning. Write Back Id is the name of the Data Load Rule defined in Structured Planning, Consolidation, or Reporting applications (such as Sample Rule).

  

Support for Translations in Write Back from Dynamic Planning to PCR

You can create a Model with Source Segments, map them to the Financial Model and write back to PCR (Structured Planning, Consolidation, Reporting) using a Translations Data Load Rule. The write back map allows you to include Source dimensions as shown in the following example.

• Source Dimension must be a dimension from the source model.

• Attributes are not supported.

• You must select AllMembers, MemberAndBelow, LeafMembers or FixedMember as the value Source Filter.

• The values for Target Dimension, Target Filter, and Target Value must be None.

Additionally, it is not required to map all the PCR dimensions in the Target Dimension column. You can skip them by selecting the No Translation for Unmapped Target Segments check box in the Data Load Rule Write Back ID In this example (image above) the Data Load Rule type is Translations and Account and subAccount are the source dimensions in the MasterModelForWriteback for which the Translations are setup within PCR (shown in the image below). You can map Source dimensions using Translation Setup.

For more information on DLR and Translation Setup, refer to the Data Load Rules and Translations Setup.

Let's say, two Source Dimensions are merged and mapped to the target; Account and subAccount Source Dimensions are mapped to the Account. When you load data for the 28 DYN Income account Source dimension and 28 DYNIncomeSubAcc subAccount for the WritebackDLRTranslations model and run write back, the Account DNY_Account1 in PCR gets updated automatically.

The following scenarios provide a few examples where you can use Translations:

• Mapping two source Segments to one Target Segment - In the above example, the Account and subAccount source segment are mapped to Account.

• Mapping one Source Segment to one Target Segment - The subAccount Source segment is mapped to Account as shown in the image below. In the second image, the transaction lines for the segments are shown.

  
  

• Mapping two Source Segments to two Target Segments - The Account and subAccount source segments are mapped to Account and Company.

• Mapping one Source Segment to two Target Segment - The subAccount source segment is mapped to two dimensions.

Translation Requirements:

• Source Segment names must match dimension names in the source Writeback Model.

• Source Segment names must be unique across all Translations used by DLR.

• “Source Column X” must be unique across all Translations used by DLR.

Consistency between DLR and Write Back Map:

• The Measure in the map must be identical to the DLR Data Type.

Calculation

Once you have created the map for the write back operation that includes the Write Back Id for the Data Load Rule, you will need to add this map to a calculation. Perform the following steps:

1. In SpotlightXL, select the Model task and the Calculation subtask.

2. Create a new Calculation for the model you are writing back data from (for example, Finance model).

3. Add the map to the calculation.

   1. Type = Map

   2. Name = Write back map name (not the write back Id but rather the map created in the step prior)

4. Save the calculation.

5. Run the calculation. Note that you CANNOT add this calculation on save for either a View or Report at this time.

Set Up the Address Back to the Structured Planning, Consolidation, and Reporting Applications

In Dynamic Planning, login and select the Application that will send data updates to Structured Planning, Consolidation, and Reporting applications.

Manage > Application Settings

In the Soap Uri box, paste the State Free URL that you previously copied in “Maintenance > Configuration Tasks .” Be sure the Username and Password information is up-to-date. (If you have previously entered the username and password, the password field will be blank.) Click Save.

Dynamic Planning: Mapping Data Back to Structured Planning, Consolidation, and Reporting Applications
When you brought data into Dynamic Planning from Structured Planning, Consolidation, and Reporting applications, you specified how the data which had been downloaded into HACPM_Financial should map to the Dynamic Planning target model (called Integration):

• HACPM_Financial was the Source.

• Integration was the Target.

Here is the map used in Model > Map before downloading the data.

Now you need to map data from Dynamic Planning to Structured Planning, Consolidation, and Reporting applications so Source and Target are reversed.

• Integration is now the Source.

• HACPM_Financial is now the Target.

In Practice: Model > Map

1. The Target Model is HACPM_Financial.

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

3. Type must be set to Data for Write-back.

4. The Source is the name of the model that contains the changes you want to write back to Structured Planning, Consolidation, and Reporting applications.

5. Transfer must be set to Leaf.

6. The Write Back Id is the name of the Data Load Rule that you created in “Maintenance > Data Load Rules .” The Data Load Rule name is case-sensitive.

7. Leave Access Token blank.

   

Source to Target Map Table

Here you must list essentially the opposite of the map of you created prior to data download. The three columns that were on the left will now be on the right, and vice versa. The best practice, however, is to be specific in your map which data items you want to write back using Fixed Member, not to write back all members in all dimensions.

In the example below, instead of writing back all members of Account, Company, Department, Product, and Project, the map specifies only those members which have changed and that you want written back to Structured Planning, Consolidation, and Reporting applications. This way, you can control the flow of data between Structured Planning, Consolidation, and Reporting applications and Dynamic Planning.

Remember that HACPM_Financial has two dimensions, Intercompany and Reporting, that were filtered out prior to data download, and you need to specify the member that data will be written back to. The member specified must be a leaf member. In the example above, Intercompany: Default and Reporting: G/L Data (CC) are leaf members.

For all months in 2014, data for product D006341, account 4010, company 2100, department SLSCA, and Actuals will be written back. Here is a View that shows these member intersections.

Ability to Automatically Add Members When Writing Data Back From Dynamic Planning to Structured Planning, Consolidation, and Reporting

This feature is available upon request. Please contact Planful Support to enable.

Automatically add dimension members to hierarchies when writing back data from Dynamic Planning to Structured Planning, Reporting, and Consolidation. For example, you add new vendors in Dynamic Planning via an External Source Model. Then you send the data from Dynamic Planning to Structured Planning, Consolidation, and Reporting via a Model Map executed via a Calculation (in the Dynamic Planning module) and a Data Load Rule (in the Structured Planning module). Or, you add products in Dynamic Planning and write the additional products back to Structured Planning.

In Practice: Append Missing Dimension Members in a Model Map - Step 1

Enabling Write Back With Automatic Member Addition in Maps requires that you select Yes for the Append Missing Dimension Members option available only for Model Maps that are linked to the HACPM_Financial model. Follow the steps below.

1. In SpotlightXL, navigate to Model task and Map subtask.

2. Select the HACPM_Financial Model and the Map you created to write data back to Structure Planning. Verify the Map contains the Write Back Id for the Data Load Rule.

3. For Append Missing Dimension Members , select Yes as shown below.

   
   
   

4. Click Save.

In Practice: Run the Calculation for the HACPM_Finanical Model - Step 2

The Calculation must include the Map where Append Missing Dimension Members is set to Yes.

1. In SpotlightXL navigate to Model > Calculation.

2. Select the HACPM_Financial Model and the map you’ve created to write data back to Structured Planning as shown in the image below.

3. Click Run.
   
   

   
   
   

4. Verify the Calculation completed successfully as shown below.

   

In Practice: Verifying the Dimension Members Were Appended in Structured Planning - Step 3

You will receive a detailed email indicating the segment updates to Structure Planning. 

It is a Best Practice to login to Structured Planning to verify the additional segments/dimensions added to the hierarchy via Hierarchy Management or the Planning Control Panel. New members added to Dynamic Planning and written back to Structured Planning are added to the DI-AutoCreated rollup shown below.

In Practice: Adding Appended Dimension Members to a Dynamic Report - Step 4

1. In Spotlight Web, go to Reports.

2. In Reports, access a Dynamic Report.

3. In this case, product members were added from Dynamic Planning to Structured Planning. Click the Product Dimension Member (in this case it is on the row axis).

4. Locate the DI-AutoCreated members in the hierarchy.

5. Select them to include in the Dynamic Report and Save.

   In the image below, the newly added members are used in a Dynamic Report on the Row axis.

   

Dynamic Planning: Set Up a Calculation to Run the Map

Now you must execute the write-back with a calculation.

In Practice: Model > Calculation

1. The Model is HACPM_Financial.

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

3. Set Run in Background to Yes.

4. Under Type , specify Map . Under Name , specify the name of the Map that you created in the previous section.

5. Then click Save.

   
   
   

6. Click Run.

   
   
   

You can view the status of the calculation by selecting Manage > Application Administration > Request Status.

Validating Data Changes in Structured Planning, Consolidation, and Reporting Applications

In Structured Planning, Consolidation, or Reporting applications, create a Dynamic Report to verify that the data has been written correctly.

Dynamic Planning: Using Attributes as Filters for Write-back to PCR

You can use attributes in Maps to fine tune which data should be written back to PCR. The attributes act as a filter to select the data intersections to be written back. Instead of writing back a whole hierarchy for a particular dimension, you can write back only those members of the hierarchy that have a particular attribute. The match criteria of Common is assumed when attributes are in the map.

Example 1: Write-back without Attributes

The following map shows how data is written back without attributes; this is the traditional method. All members of the Road Bikes hierarchy in 2014 with data for account 4010 are written back. As a reminder, the map is of type Data, the transfer type is Leaf, and the Write Back ID is the name of the data load rules file in PCR.

Here is the calculation that runs the map.

Here is the view (Analyze > Data) of the data in Dynamic Planning.

Example 2: Write-back with Attributes

Consider the following attributes on the Time and Product dimensions of the model DW.

Time contains an attribute called Seasons. Each month rolls up to a quarter and is mapped to a season.

Product contains an attribute called Material. Each bicycle part rolls up to its parent and is mapped to a type of material.

Here is a map with attributes added. Because the attribute is associated with a particular dimension, that dimension does not need to be explicitly identified as a Source Dimension. All members of the Bicycles hierarchy with Material attribute Steel, with data for account 4010 in the months of 2014 with Season attribute Winter, are written back. For data to be written back, it must have both the Steel product attribute AND the Winter time attribute. As a reminder, the map is of type Data, the transfer type is Leaf, and the Write Back ID is the name of the data load rules file in PCR

Here is the calculation that runs the map.

Here is the view of the data in Dynamic Planning. Green boxes show the dimension filters selected in the map. Red boxes show the attribute filters selected in the map. Yellow boxes show the data that should be written back to PCR.

Example 3: Write-back with Multiple Attributes

Using the same attributes on the Time and Product dimensions as in Example 2, here is a map with multiple attributes per dimension added. Because the attribute is associated with a particular dimension, that dimension does not need to be explicitly identified as a Source Dimension. All members of the Bicycles hierarchy with Materials attribute Steel OR Leather, with data for account 4010 in the months of 2014 with Season attribute Winter OR Spring, are written back. For data to be written back, it must have the Steel or Leather product attribute AND the Winter or Spring time attribute. As a reminder, the map is of type Data, the transfer type is Leaf, and the Write Back ID is the name of the data load rules file in PCR.

Here is the calculation that runs the map.

Here is the view of the data in Dynamic Planning. Green boxes show the dimension filters selected in the map. Red boxes show the attribute filters selected in the map. Yellow boxes show the data that should be written back to PCR.