Using Breakback

Breakback is a spreading method where you provide data at a parent level and then Dynamic Planning allocates it down to leaf levels. It is an alternate way of inputting data by entering a total amount at a consolidated level and allowing Dynamic Planning to allocate that amount back to all the participating cells related to the consolidated cell.

Since Dynamic Planning does not allow data to be entered at a parent level and saved, without Breakback, you would have to enter each leaf-level data item and then run an aggregation. Breakback lets you start with the top level and provide the desired target data or percentage change in the current data, then it allocates the data down, saves the changes, and optionally runs a calculation.

You can also run Breakback at a leaf level. Only the leaf member that is selected is changed, but if there is a parent level on one of the other axis of the view or report, the data is spread throughout that hierarchy.

Breakback Types

Breakback provides three spread methods, along with the ability to hold (or exclude) some members from changing.

• Current: This method uses existing values to create new values proportionally. For example, you can allocate updated sales targets out to regions. The Current method is limited to 500,000 leaf combinations per Breakback operation from views and reports
• Even: This method distributes a value evenly. Use this method when you have no historical values and nothing similar. For even distributions, you can allocate only a value, you cannot use a percentage increase or decrease. For example, your company is expanding to a new product line and you have no other products to base the values on. Or you have a new department and want to allocate some funds to the department. Use the Even method sparingly as it is limited to 1500 leaf combinations per Breakback operation
• Reference: This method distributes a value based on one or more referenced dimension members. Use this method when you have no exact history but you have a dimension member that is similar or that provides a good starting point for portioning the data. When using this method, the referenced member must be visible in the view or report

Referenced members should have the same levels and leaf members similar to the members that you are running breakback on. For example, if you want to reference the 2016 Plan for running the breakback on the 2017 Forecast, the 2016 fiscal year should have the same levels and exact count of leaf members as the 2017 fiscal year.

• Examples of Reference Breakbacks:
  • View last year’s budget and then create a current year forecast based on those historical values
  • Base the budgeted equipment expense on the FTE count in the departments
  • Breakback from a new product based on the distribution of an existing product

The Reference method is limited to 500,000 leaf combinations per Breakback operation from views and reports.

• Hold: prevents the values in some cells from being changed by the Breakback operation. For example, increase sales in all regions but Hold the East region steady. Hold is available within all 3 spread methods

Breakback and Data Locking

If your application is enabled with Data Locking, Breakback will treat Data Locks as Holds in the following circumstances:

• Breakback runs manually from a View honors the Application Setting, View Property, and Data Locking
• Breakback run manually from a Report honors the Application Setting, Report Property, and Data Locking
• Breakback run from a calculation honors the Application Setting, Model Property, and Data Locking

In these cases, if Data Locking is set to Application Level, then Breakback will not modify data in member intersections that are designed as locked. If Data Locking is set to None, then Breakback will proceed without regard for data locking.

Breakback applies a Hold for locked members and member combinations. For more information, see How to Lock Dimension Members.

Running Breakback from a Calculation

You can run Breakback operations from a Calculation, thus automating your top-down allocations and making them easier to maintain.

Use SpotlightXL to define Breakback operations in a calculation. Once defined, you can run the calculation from SpotlightXL or Spotlight.

Breakback Scalability in a Calculation

Calculation Breakback Basics

Breakback requires several pieces of information:

• Model name
• Parent-level data intersection from which to allocate data to children
• Spread method
• Type of change to make: amount or percentage increase/decrease
• Value of the change to make
• Data intersection to use as a reference for the Breakback (optional)
• Hold members (optional, child members that will be skipped in the Breakback)

You can provide the required information using a Breakback button on the Calculation ribbon.

This button functions similarly to the Breakback button in Views and Reports. The main difference is that you must also specify the parent-level data intersection; in views and reports, this is the data cell that your cursor is on before you select Breakback.

Calculation Breakback, Value Input Example

This example uses similar choices as are demonstrated in the help topic for Breakback from a view or report. See Using Breakback based on Current Data with % Change (Increase or Decrease). We want to increase the Expense (account 6000) amounts by 3%. Here is the view before the calculation is run, with the Breakback Cell highlighted.

How to Add Breakback to a Calculation in SpotlightXL?

1. In SpotlightXL, select Model > Calculation.
2. Select a model from the Model drop-down.
3. Give the Calculation a name and click Save.
4. Under Type, select Breakback.
5. Under Name, type None.
6. Click Save. Notice that the Variables section is now populated with several variables.
7. Select the cell with all the Variables and licck the Breakback button on the ribbon.
   
   
   
8. Select the model from the Model drop-down. By default, the model specified in the calculation is selected.
9. In the Breakback Cell area, specify the parent-level data intersection. This is the highest level parent that you will base the allocation from. The allocation will go to the leaf-level children.
   
10. In the Spread area, select Current. For information on spread types, see Breakback Types.
11. In the Value area, select % Increase.
12. Select Input because we will manually input the % increase that we want by typing 3 in the % box.
    
    
    
13. Skip the Hold area and click Update. Now you are returned to the calculation where you can add more commands.
14. In the second row of the calculation, select Aggregation from the Type column.
15. Under Name, type None and click Save.

Your calculation is ready to run.

After running the calculation and refreshing the View, here is the resulting data.

Calculation Breakback, Value Reference Example

You can set up Breakback to refer to a data intersection in your model as the basis for the allocation. For example, if you do not know the exact value of the % Increase or the Value Input, or if you want the % Increase or Value to be variable, you can specify a data cell from the model to reference for the value.

Using a Cell in the Model as the Value for the Allocation

1. Use a report to set up a form for a user to enter the amount to use or the amount to increase or decrease.
2. Use the cell intersection coordinates from the report as the reference for the Breakback in the calculation.
3. Use one Breakback row in the calculation for each amount entered by the user.
4. Add the name of the calculation to the report Calculation on Save so that each time the user saves, the Breakbacks are calculated.

Example

The following example uses a report Sales Increase % to let users input values for regional sales increases. The values inputted by the user are used in the Breakback calculation to increase Sales values in the model. The report automatically runs the calculation when the user saves their input values.

1. Use a report to set up a form for a user to enter regional sales increases.
   
   
   
2. Note the data intersection from the report using Design Manager.
   
3. In the calculation, add a Breakback row, and use the cell intersection coordinates from the report as the reference for the % Increase.
   
   
   
4. Use one Breakback row in the calculation for each amount entered by the user.
   
5. Note the name of the calculation.
6. Add the name of the calculation to the report Calculation on Save so that each time the user saves, the Breakbacks are calculated.
   
   
   

Using Substitution Variables in Breakback Operations from a Calculation

You can use substitution variables in place of member names in Breakback operations within a calculation.

This feature is supported both in SpotlightXL and Spotlight.

When defining Breakback operations in a calculation, you can use the member select icon to select a Substitution Variable instead of a fixed member name in the Breakback cell.

You can use Substitution Variables in the Reference Spread also:

You can use Substitution Variables in the Hold section also:

Substitution Variables are also available in Breakback in a Calculation in Model Manager on the web.

You can use the member select icon to select a Substitution Variable instead of a fixed member name in the Breakback Cell.

You can use Substitution Variables in the Reference Spread also:

You can use Substitution Variables in the Hold section also:

Breakback Loop in Calculation

You can set up a Breakback Loop comprising Breakback Scope to generate data for any model based on the available data instead of creating multiple calculation steps.

The Breakback Loop helps to achieve the following:

• Breakback Loop will leverage the existing Breakback engine and enhance it to support complex use cases with a simple setup.
• Breakback Loop will help customers configure complex top-down processes, make top-side adjustments, push the changes across multiple dimensions, and allocate the amounts based on historical trends.
• Breakback Loop will simplify the configuration of complex top-down processes.
• Breakback Loop will reduce the ongoing model and calculations maintenance.
• Breakback Loop will merge multiple breakback steps into one, simplify the calculations and improve their maintenance.
• Breakback Loop will collect all leaf members falling within the scope range for each loop dimension and iterate through them.

You can set up a Breakback Loop comprising a Breakback Scope to generate forecast data for any model based on the available data instead of creating multiple calculation steps.

For example, previously, if you want to generate a forecast for 24 months for 100 customers having multiple dimensions based on the actual data, you have to define all dimension attributes in the Breakback manager and create 2400 Breakback steps. This took a lot of effort and time. With the Breakback Loop, you can generate the same forecast for 100 customers by just creating one Breakback Loop calculation comprising one Breakback Scope. The functionality saves time and effort and increases flexibility.

To enable this functionality, application admin users have to set the Enable Breakback Loop property to Yes . There is no need to contact the customer support team; application admin users can enable this functionality directly from the application.

Once you set the value of this property to “Yes”, you will be able to see “Breakback Loop” in the Type drop-down list for creating a calculation step.

You will also see the Breakback Scope column enabled in the Scope sheet.

Breakback Scope property is an optional property in the Scope.

• Users will be using Scope for various reasons, and it is applicable for Aggregation and Formula types in the calculation.
• If users are using Scope for any other type in the calculation other than the ‘Breakback Loop’, there is no need to populate or select any values in the Breakback Scope property.
• Breakback Scope property is only applicable for the Scopes users will use in the
• the Breakback Loop type.
• Breakback Scope property should not impact the Scopes that are already created in the application.
• If users want to use Breakback Loop, they need to create the Scope with appropriate selections in the Breakback Scope property.
• Breakback Scope property drop-down contains Breakback Cell, Spread: Reference, Value: Reference, and Hold values. It is not mandatory that all these values have to be selected for a given scope. Based on the use case and need, users can select a combination of these values. For example, a Scope may only have a Breakback Cell, or a Scope may have a Breakback Cell and Spread Reference, etc.
• For the dimensions that users want to loop through, they need to set up the Scope and select the appropriate Breakback Scope property. For example, if users want to configure Breakback for 24 months, then one option is to use Breakback type in the calculation and create a 24 step calculation. Another option is to use the Breakback Loop type in the calculation and define the Scope. In the Scope, set the Time dimension. Based on the Scope setup, the Breakback engine will loop through for each month, that is, 24 months.
• Any dimension that is defined in the Scope will work as a loop dimension for the Breakback engine. The only exception is for dimensions where the Breakback Scope property is set to Hold.
• Hold primarily holds the dimension members from the execution. The Hold in the Breakback Loop works the same as how Hold works in Breakback. The benefit users will have from defining Hold in the Scope is to make updates from one place.
• For users to use Hold, they have to define the hold dimension members in the Breakback configuration when they configure Breakback in the calculation.
• Hold is primarily to hold the dimension members from Breakback during the execution.
• Currently, defining the hold members in the Breakback manager in the calculation is mandatory. Hold in the Scope will only override the values. In future releases, this limitation will be relaxed.
• Hold dimension and the dimension on which users want to loop should not be the same. For example, if a user is looping through the Time dimension, defining the Hold on Time dimension is not supported.
• Dimensions in Breakback Scope must be limited to those that are loop parameters. There is no need to define all dimensions in the Scope.

How to Create a Breakback Loop Calculation Step?

1. In SpotlightXL, navigate to Model > Scope.
2. In the Scope sheet, set the Dimension, Filter Type, Filter Value, and Breakback Scope values. The Breakback Scope drop-down list will contain Breakback Cell, Spread: Reference, Value: Reference, and Hold values.
3. Save the scope.
   
4. Navigate to the Calculation sheet.
5. Select Breakback Loop from the Type drop-down list.
6. Enter the required Breakback Scope name in the Name column and add the required variables in the Variables column.

Sample Use Case

Let's say we have a sample model where salespeople are forecasting the revenue by region, and there are other dimensions in the model such as department, business unit, time, scenario, and measures.

There are four regions in the model (i.e., East, West, North, and South), and salespeople forecast the revenue for 24 months. Once the sales forecast is inputted, department managers have to plan for expenses in each department based on the sales forecast.

In this use case, revenue is forecasted at the region level, and expenses are forecasted at the department level.

To solve these types of use cases, users can consider multiple options. One option is to create separate models with corresponding dimensions and then consolidate the data for analysis and reporting purposes. Essentially the revenue is forecasted in a separate model, and expenses are forecasted in another model. By leveraging the Map functionality, users can move required data into these models.

Another option is to have both revenue and expense forecasted in the same model by leveraging the Breakback engine.

For example:

Step 1 - Let salespeople input their forecast for the regions that they are responsible for.

Step 2 - Create breakback steps in the calculation to push the revenue forecast data, which is at the region level to departments and business units level so that expenses forecast can be based on the sales targets.

Step 3 - Have department managers input the expenses at the department level by considering the sales targets.

The aspect of this process is how to achieve Step 2 simply and straightforwardly. Leveraging Breakback is an ideal approach; however, as the salespeople input the data for each region for 24 months, the engine then has to take that data and push the data into all the departments and business units based on the previous year's forecast, or current actuals, or a combination of both. So in practice, users have to create at least 24 breakback steps for each region. So to solve this use case, we need 24 * 4 = 96 steps.

If there are more regions and if there are more months, then the complexity increases drastically.

Also, suppose users want to make changes every month, like adjusting the time horizon or adding more regions. In that case, there will be a significant amount of changes needed in the breakback steps and calculation.

The Breakback loop will simplify these kinds of complex use cases by leveraging the looping mechanism.

So, we want the breakback to loop through 4 regions and 24 months in this use case, which means both Time and Region are the loop dimensions.

The first step in creating the Breakback loop step is to determine the dimensions and dimension members that qualify for the looping. Every use case will be different, so based on the use case, users must define the loop dimensions and dimension members.

One simple assumption that users can make here is to solve the use case with Breakback step, and understand what's changing (i.e., dimension and dimension members) for each step in the calculation, and then consider configuring those dimensions and dimension members loop dimensions.

Steps involved in creating the breakback loop for the above use case.

Step 1 - Create the Scope

As we have identified Time and Region as the loop dimensions, we need to include only these two dimensions in the Scope.

While defining the Breakback step in the calculation, users must provide Breakback Cell, Values Reference, and Spread Reference. Similarly, the Scope will also accept the same information.

We will discuss these options below.

In the above use case, we want both Region and Time dimensions to be included in the loop, and we also want the same for both Breakback cell and Value Reference.

Users can use FixedMember or MemberandBelow as the filter type options.

This Scope will result in 4 regions * 24 months = 96 breakback steps internally.

Step 2 - Create Breakback Loop step in the calculation

Creating Breakback Loop step in the calculation is exactly the same as how users create the Breakback step in the calculation. The only additional step is to select the Scope that was created in the previous step.

Breakback Loop with Spread Reference

Below Scope will consider running the breakback for 4 regions * 24 months by leveraging the 2020 spread.

Breakback Loop with MemberAndBelow for Region dimensions vs. Fixed Member

Below scope will loop through all the regions under ‘All Regions’ rollup, for example, 4 * 24 months = 96 steps.

An additional benefit in configuring the Breakback loop is that when a new Region gets added, it automatically gets included in the loop without any manual intervention from admin users.

Breakback Loop for 3 quarters in a year

Below Scope will loop through 9 months * 4 regions = 36 steps.

Breakback Loop for 3 months in a year