2.1.1 Modeling Release Notes, June

SpotlightXL Add-In InstallShield MSI Format for 32-bit

For users who are installing the SpotlightXL Add-In with the InstallShield MSI format, this option is now available in 32-bit format. Previously, it was available only in 64-bit format.

Naming convention for InstallShield MSI formats:

64-bit

https://s3-us-west-1.amazonaws.com/modeling-add-in/msi/SpotlightXL_<ReleaseMonthandYear>_MSI.exe

Example:

https://s3-us-west-1.amazonaws.com/modeling-add-in/msi/SpotlightXL_June2017_MSI.exe

32-bit

https://s3-us-west-1.amazonaws.com/modeling-add-in/msi/SpotlightXL_<ReleaseMonthandYear>_MSI_32.exe

Example:

https://s3-us-west-1.amazonaws.com/modeling-add-in/msi/SpotlightXL_June2017_MSI_32.exe

https://s3-us-west-1.amazonaws.com/modeling-add-in/auto-install/SpotlightXL_ClickOnce.exe

For complete information on installation, see SpotlightXL Installation.

Login and Logoff Button shows Release Name

In this release, users can see which version of SpotlightXL they are using by hovering their mouse over the Login or Logoff button. This is helpful for users to know which version of the SpotlightXL Add-In they have installed.

For users who opted for the ClickOnce installation method, the Add-In is automatically updated, so this feature is helpful to see the version and release information.

Breakback

Overview

Breakback is a spreading method where data is provided at a parent level and then allocated down to leaf levels. It is an alternate way of inputting data by entering a total amount at a consolidated level and allowing Modeling to allocate that amount back to all the participating cells related to the consolidated cell. For example, in budgeting, managers need to allocate limited resources across multiple departments over a period of time.

Since Modeling 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 run Breakback at a leaf level as well. 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.

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.

• 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 same levels and leaf members similar to the member that you are running breakback on. For example, if you want to reference 2016 Plan for running the breakback on 2017 Forecast, 2016 fiscal year should have same levels and exact count of leaf members as 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.

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

Hold is currently applicable for multiple dimension members within the same dimension. For example, you can run Breakback on 2017 and hold Jan-17 and Feb-17, or Q1-2017 and Q2-2017.

Adding multiple dimensions to a Hold is not recommended. For example, holding the East Region for Q1 2017 where East belongs to the Region dimension and Q1 2017 belongs to the Time dimension will yield incorrect results. So, in this release, Hold is recommended only on one dimension.

Breakback from View or Report

You can run Breakback from a View or Report, in SpotlightXL or Spotlight.

A button has been added to the ribbon:

• The view or report must have the Enable Save field set to Yes in the view or report properties.

• If you want to run a calculation after the Breakback operation is completed, the view or report properties must include the name of the Calculation in the Calculation on Save field.

• Breakback honors the current setting of Display Label or Code in your report or view.

• Breakback automatically saves the changes it makes to the data. There is no option to return to the previous values.

• Breakback is not available from the Default view or report that appears when you choose New View or New Report.

• Breakback is not available from a cell that contains a formula in a report. Breakback must always begin with a data cell selected.

• Breakback is not supported in reports or views with attributes.

Use Cases

Using Breakback based on Current Data with $ Amount (Increase or Decrease)

In the following example, we want to increase the revenue targets in the 2016 Forecast. There is an existing forecast for Direct Sales & COS per product.

Here is a view of the leaf level data from this view, prior to Breakback.

Click to enlarge

1. With cell C4 selected, select the Breakback button on the ribbon.

2. Select the Current spread.

3. Select the Amount radio button and enter the new target.

4. Click Run Breakback.

Here is the resulting data. The data has been increased in all regions and throughout the products proportionally.

When you drill down to specific regions or products, you can see that the values have changed proportionally from the leaf level up.

Click to enlarge 

Using Breakback based on Current Data with % Change (Increase or Decrease)

In the following example, 2017 Budget has data and we want to increase the amounts by 3%.

1. With cell D3 selected, select the Breakback button on the ribbon.

2. Select the Current spread.

3. Select the Percentage and Increase radio buttons and enter 3 as the percentage increase.

4. Click the Run Calculation button to run the calculation associated with this view after the Breakback is performed.

5. Click Run Breakback.

Here is the resulting data.

Using Breakback Based on a Reference

Two examples are provided:

• Create next year's forecast based on current year's budget.

• Allocate equipment expense based on the FTE count in the departments.

Create next year's forecast based on current year's budget

In the following example, we have 2017 Plan sales data for all products and regions. We want to copy that data into 2018 Forecast, then increase Units by 3% and increase ASP by 5%.

1. Select Cell E6 for Forecast Units in 2018.

2. Select the Breakback button on the ribbon.

3. Select the Reference spread.

4. Click Add Dimension Member and specify the Scenario Plan to be the basis. Click Add Dimension Member again and specify the Time 2017 to be the basis. These are the two dimensions that are dissimilar from the intersection represented in cell E6.

5. Under Amount, type in the total amount from 2017 Plan as a baseline. You cannot increase the percentage of 2017 until you have established the baseline.

6. Click the Run Calculation button to run the calculation associated with this view after the Breakback is performed.

7. Click Run Breakback.

Now we have the same data as in 2017.

8. Run Breakback again from cell E6 and specify a 3% increase.

Now we have a 3% increase in Units for the 2018 Forecast.

Perform similar steps to increase the 2017 Plan ASP in the 2018 Forecast by 5%.

Breakback equipment expense based on the FTE count in the departments

In the following example, we have Full Time Equivalents Forecast for 2018 for all departments. Corporate wants to allocate a total IT and Facilities expense amount down to the departments based on FTE.

1. Select Cell C4 for Equipment Expense for All Departments.

2. Select the Breakback button on the ribbon.

3. Select the Reference spread.

4. Click Add Dimension Member and specify the Account member for FTE.

5. Specify the total expense of $1.5M.

6. Click Run Breakback.

Here is the resulting data.

Using Breakback to Spread Data Evenly for a New Department

In the following example, a new product is being added to the 2017 Plan and we want to allocate the Units expected to sell during the year evenly.

1. Select Cell B11, 2017 for new product "Hybrid MTB - Men."

2. Select the Breakback button on the ribbon.

3. Select the Even spread.

4. Under Amount, specify the amount you want to spread. In this case, somewhere between the current two products in this category.

5. Click the Run Calculation button to run the calculation associated with this view after the Breakback is performed

6. Click Run Breakback.

Here is the resulting data.

Using Breakback with a Hold

In the following example, revenue targets are increased for Mountain Bikes in 2016 but Quarter 1 is not increased.

1. Select Cell C10, Mountain Bikes for 2016. Notice that the Q1 value is 561,844 and Q2 is 578,869.

2. Select the Breakback button on the ribbon.

3. Select the Current spread.

4. Under Amount, specify the amount you want to spread. In this case, 2.5M.

5. Under the Hold box, select Add Dimension Member.

6. Specify the Time dimension and select Q1 2016. (In Excel, you can also click the Select Range button and simply click on the cell containing the member you want to Hold.)

7. Click the Run Calculation button to run the calculation associated with this view after the Breakback is performed.

8. Click Run Breakback.

Here is the resulting data. Notice that Q1 remains at 561,844, and Q2 has increased to 626,865. Q3 and Q4 also had proportional increases.

Using Breakback with Locked Dimension Members

In the following example, only the East Region is available for input. The other members of the Region dimension are locked and the Application Settings for Views have Dimension Based 'Locked' set to View level.

Here is the Dimension Based Properties table of the Model, Dimension page.

The view shows grayed out regions where the data is locked.

If you start on cell C5 and select the Breakback button on the ribbon, and try to run a Breakback operation, an error message appears.

Using Breakback on the Web

Breakback is available in views or reports in Excel or on the Web. In the following example, the Unit Volume for 2017 Accessories is increased by 2% from a report in Spotlight.

1. Select the cell for 2017 Accessories.

2. Select the Breakback button on the ribbon.

3. Select the Current spread.

4. Select the percentage area and enter 2 in the percentage box.

5. Select Increase.

6. Click the Run Calculation button to run the calculation associated with this report after the Breakback is performed.

7. Click Run Breakback.

Here is the resulting data.

Limitations and Design Considerations

• Breakback is available from views or reports, from Excel or the web interface.

• Breakback can be used on any dimension in the model.

• The Breakback operation itself is run in the foreground. If a calculation is run automatically, it will run in the foreground or background, based on how it was defined.

• Even spreads have a limitation that only 1500 leaf combinations can be included in the Breakback. If more than 1500 leaf combinations are included in your selection, Breakback will not allow you to run it.

• Current and Reference spreads have a limitation of 500,000 leaf combinations. If more than 500,000 leaf combinations are included in your selection, Breakback will not allow you to run it.

• Breakback will not run if the model is locked.

• Breakback honors dimension members that are locked in a view or report. However, in a report, items locked with Format Manager are not honored.

• Breakback honors permissions defined for Designers and Reviewers. If a user does not have access to a particular branch or member of the hierarchy, Breakback will not change the data in that branch or member.

• Breakback is not supported in reports or views with attributes. You should first remove attributes from the view or report, then run the Breakback.

• If your report uses substitution variables in a calculation, Breakback is not supported for this report.

• Cascaded reports normally do not allow saving, but when using Breakback, you can save to a cascaded report.

• Rollup operators other than plus (+) are not supported in Breakback. Our recommendation is to run Breakback only on members where the rollup operator is plus (+). For example, if you are running a Breaback on All Products in the Product dimension, then all the members under All Products should use the + rollup operator.

• When you are running Breakback, Modeling aggregates the data for the combinations on which you are running Breakback. However, you need to run a full aggregation on the model before using it for analysis and reporting. For example, if you are running a Breaback on the Q1 2017 member in the Time dimension, Modeling aggregates the data for Q1 2017 after running Breakback, but it will not aggregate the data at the 2017 fiscal year level. You need to run a full aggregation, so that the data is aggregated at the 2017 fiscal year level. You can run the full aggregation in two ways:

  • Manually; you may want to run a series of Breakbacks and then run an Aggregation calculation manually afterward.

  • As part of the calculation that is saved in the view or report and automatically run after the Breakback, so long as the calculation contains a full aggregation; use this option only if you are also using Change Data Tracking as described in Best Practices.

• Administrators can see Breakback operations in the Audit Log.

Best Practices

The Model should be aggregated prior to Breakback.

Users should turn on Change Data Tracking, to reduce the amount of time it takes for calculations to be completed. When a series of repeated Breakback operations are performed (for example, in forming a 2018 Forecast across multiple accounts, departments, and products), and if a calculation is run for each one, Change Data Tracking will help to optimize your time.

Future Options

The following options will be added in future releases:

• Breakback will be an option to add to a calculation so that you can automate breakback operations. For now, you use the Breakback user interface to run it once and the next time you use the Breakback user interface, you start from scratch again.

• Users will have the option to create a user-defined spread.

• Reports and views with attributes will be supported in Breakback operations.

"Next Run Time" Column in Schedule Manager

Administrators can now easily see when calculations are next scheduled to run by looking at the Next Run Time column of the Schedule Management page.

1. Login to the application as an administrator.

2. Select Manage > Application Administration > Schedule Management.

3. In the Model drop-down, select the model you want to view, or select All Models.

Notice the column "Next Run Time." This is an informational column only. To change the next run time, click Scheduler Manager in the menu ribbon.

Change Data Tracking Available for All Applications

In release 2.1, we introduced Change Data Tracking, and you had to call Host Analytics Support to enable it. With this release, the feature is automatically available for all applications for all existing and new users. Administrators or Designers will only need to turn on Change Data Tracking for any model they want to use this feature with.

Enabling Change Data Tracking for a Model

1. Login to the model. The model must be of type Master or Analytic.

2. Ensure that there are no calculations running on the model and that there are no active users or processes that are modifying data.

3. Run a full aggregation (Aggregation, None) before the feature is turned on. Wait for the aggregation to be completed before continuing. Failure to complete this step will result in incorrect rollup values.

4. Go to Model > Setup.

5. Select the model from the Model list box.

6. Select Yes for the Enable Change Data Tracking value.

7. Click Save.

If, in the future, you decide that you do not want to use Change Data Tracking, you can set Enable Change Data Tracking to No for the model.

For further information about Change Data Tracking, refer to the 2.1 Release Notes.

Load Users / User Groups API

We have added an optional configuration to get the names of users or user groups for a tenant, add users and user group mappings, and delete users.

There are 4 variations:

GET: /api/ui/user/getRaw

Returns a List of User Objects for that Tenant.

POST: /api/ui/user/saveUsersRaw

Returns: nothing or error message.

GET: /api/ui/group/getRaw

Returns a List <String> of group names:

POST: /api/ui/user/deleteRaw

Returns: nothing or error message if user does not exist in tenant.

Refer to the Modeling API Library for further information on these APIs.

Known Issues

The following issues are specific to Excel-based Reporting.

Resolved Issues