2.2.2 Modeling Release Notes, October

Improvements to Calculations

Below are two calculation options in the Application Properties page.

• Calculations Run in the Background for Views and Reports

• Calculations Execution Delayed and Batched for Better Performance

Calculations Run in the Background for Views and Reports

Views and Reports can run a calculation when the user clicks Save. Until now, when the calculation was run from a view or report, it always ran in the foreground, which prevented users from continuing to work. In this release, admins can now set an application-wide setting to run calculations in the background for all views and reports that trigger calculations. Those calculations must have been created with the option Run in Background set to Yes. After the calculation has completed its processing, an email is automatically sent to the user that triggered it.

Our recommendation is to set this option to Yes.

1. Select Manage, Application Administration, Application Settings. Scroll down to the Calculation Property section. After making changes, click Save. Then logoff and login again to see the effect of the changes.

2. Select Model, Calculation to ensure that the calculation was set up with Run in Background set to Yes.

3. You can see a list of all calculations and their current background setting by choosing Model, Setup. Select the model, then scroll down to the Calculations section.

4. Verify that the calculation is set up to run when the user clicks Save from a view or report by reviewing the view or report properties.

5. When the user clicks Save in the view or report, they see the following message:

Calculations Execution Delayed and Batched for Better Performance

Views and Reports can run a calculation when the user clicks Save. In this release, admins can now set an application-wide setting to run calculations that are triggered from commonly used views or reports once every few minutes during heavy usage. This setting is dependent on Enable Calculation Execution in Background for Views and Reports is set to Yes.

This setting optimizes calculations by running them at a set interval rather than every time a user triggers them. This is useful for common reports or views that are accessed and used by multiple people who are saving data and triggering calculations to be run frequently. If users are not saving data, the calculation is not run.

For example, during budgeting season, 10 department heads are inputting their plans using the same report.

• Every time they click Save, a calculation is run. This is inefficient because the calculation is being triggered to run frequently.

• When Smart Batching Interval for Calculations is set, the calculation is not automatically run when the user clicks Save. It is delayed.

• Instead, the calculation runs at its next interval.

This setting is effective with the following conditions:

• The users are using the same application and model.

• The users are running and saving data using the same report or view.

• The users are using the same parameters or variables for the calculation in the report.

When you specify a delay time, choose from None, 1, 5, 10, 15, 30, or 60 minutes.

The audit log shows that users are saving and triggering calculations to be run, but the calculation does not run until it is scheduled to do so. In the example below, it is set to run every 5 minutes.

After the calculation runs, each user that triggered it receives an email confirmation that the calculation is completed.

The administrator can see the details of the calculation and the users that have triggered the calculation by selecting Manage, Application Administration, Process Management.

Improvements to Breakback

Breakback has been improved in three areas:

• Support for Attributes when Holding

• Expanded Audit Log Entry

• Honoring Locked or Scaled Cells in Reports

Support for Attributes when Holding

In this release, you can use Attributes to specify which dimensions and members should be included in a Hold when you run a Breakback operation. The Attribute cannot be part of the view or report, but you can use it in the Hold area of the Breakback screen. Using Attributes to Hold dimension members is a quick way to control data updates with Breakback.

Users can still select individual dimensions and dimension members in the Hold. However, because it is easy to assign members to an Attribute, the ability to select the Attribute for applying a Hold is less cumbersome.

This feature is available in the Excel and Web versions.

In the following example, Region is an Attribute on the Company dimension.

Members of the Company dimension have been assigned to Regions.

In the following report, the Manufacturing location on the West coast of the United States is investing in a new manufacturing building, so you want to increase the budget for Fixed Assets, Property/Plant/Equipment in Q1. West is a Region attribute.

1. Select the cell for Q1 and account 1610.

2. Click Breakback.

3. Specify that you want to increase the allocation by 25%.

4. In the Hold area, click Add Dimension and add all the Regions that should not be increased. (Only West will be increased.) If you make a mistake, click the Member Select icon to make corrections.

   • Company.[Region] East

   • Company.[Region] North

   • Company.[Region] South

   • Company.[Region] EMEA

   • Company.[Region] APAC

   • Company.[Region] Africa

   • Company.[Region] LATAM

     Note:

     _Attribute_Default (all members that are not mapped to an attribute) cannot be used in the Hold area.

5. Click to Run Calculation.

6. Click Run Breakback. Here is the resulting data.

Expanded Audit Log Entry

Administrators can now see detailed Breakback operations in the Audit Log. Select Manage, Application Administration, Audit Log.

When the user performs a Breakback operation, the audit logs displays an entry for a successful run of Breakback. Expand the cell to see the details about the operation, such as the model used, member selection it was based on, type of Spread, Value of the change, and Calculation status.

After the Breakback operation, an entry shows that Get Data was subsequently run on the View, or that Refresh was run on the Report.

The admin can filter the Action, Breakback to see all the Breakback operations.

Honoring Locked or Scaled Cells in Reports

If you attempt to perform a Breakback operation in a view or report that contains locks, here is how they are handled:

• Breakback will not run if the model is locked.

• Breakback honors dimension members that are locked in a view or report and will disallow them from being changed.

• In a report, if cells are locked or scaled with Format Manager, Breakback will disallow them from being changed but only if the cell that is locked is the same cell that is selected when you click Breakback.

• Best Practice: We recommend that you leverage dimension-based locking to lock the data, and to leverage scaling or report-level locking from the Format Manager primarily for your reporting needs.

Usability Improvements

Views and Reports have two important usability improvements:

• Smart Menu Items in Views and Reports: Save Data and Breakback

• Avoiding Data Input Loss in Views and Reports, Excel only

Smart Menu Items in Views and Reports: Save Data and Breakback

Views and Reports each have Property settings for saving data and running calculations. In this release, these settings will now affect whether the Save Data and Breakback buttons are provided or not on the menu ribbon.

This change affects both Excel and Web versions.

When Enable Save is Yes, and when Calculation on Save and Calculation Model have valid values, both Save Data and Breakback are enabled on the ribbon.

Breakback is dependent on the Save Data property. If Save Data is Yes, Breakback is available.

Save Data is dependent on the Calculation on Save property in Views and the Calculation Model property in Reports. Save Data in reports is always on if Enable Save is set to All Models.

The following table shows how the Save Data and Breakback menu items are affected by the view and property settings.

Avoiding Data Input Loss in Views and Reports, Excel only

In Views or Reports with data entry capabilities, Modeling will now prompt the user if they have not saved data changes and they choose an option that would otherwise discard those changes. Previously, if a user had unsaved data changes, and they used an option such as Zoom In, Drill Through, Refresh, Breakback, or opened another view, their changes would be lost. Now Modeling detects that there are unsaved changed and asks the user to confirm if they want to continue.

This feature is available in Views and Reports in Excel.

For example, if there is unsaved data (highlighted with yellow background), and the user clicks Zoom Out, they see the following message.

Or if the user tries to open another View, if there is unsaved data, they see the prompt.

Substitution Variable and Expression Support Added in Excel-based Reporting

In Release 2.1.2, we introduced the capability for users to change the value of substitution variables at run-time. Reports defined with substitution variables and substitution variable expressions make report maintenance easier.

In this release, we are making the same functionality available with Excel-based Reports. You can set up the substitution variables and expressions in a formatted report and then Capture it to convert to a Excel-based Report. Or you can set up the substitution variables and expressions directly in the Excel-based Report. The Substitution Variable menu item has been added to the ribbon to make it possible for users to change the value of the variable while using the report.

The process is the same as for formatted reports.

1. Admin or designer creates one or more substitution variables in the model.

2. Admin or designer creates one or more Excel-based Reports and inserts the substitution variables.

3. Admin or designer optionally specifies substitution variable expressions in the design of the report.

4. In this release, users can now change the value of the substitution variable using the Substitution Variables menu item from the Excel-based Report ribbon.

5. With the cursor anywhere in the report, click Substitution Variables. The Substitution Variables box appears. It shows the name of the variable and the current value.

6. Click the Member Select icon.

7. From the member selection box, select the month that you want to see in the report.

8. Click Select.

9. Click Refresh to refresh the report.

The report now displays data for Jul-17. Using this method, the user can easily move back and forth between months, quarters, or years, without updating the definition of the report and without the need for the designer or administrator to update the substitution variable value permanently.

Clear Model Option Added for External Source Models

Clear Model is an option added to the Model, External Source Model, Source Model menu ribbon. This menu command clears all data from the Source model.

This menu command is only for Administrators and Designers.

Here is a reminder of the overall process when working with External Source Models:

• Define a Source Model with its fields and types.

• Define a Map that defines how data from the Source Model will be moved to a separately defined Master Model.

• Generate the model to populate the Master Model with data from the Source Model.

In the process of testing the data integration from the external source model, you might need to clear the data from the Source Model:

1. Select Model, External Source Model, Source Model.

2. Select the Source Model from the Model list box.

3. Click Clear Model. You are asked to confirm before the data is deleted.

In a future release, the menu command Generate Model will be moved from the Source Map page to the Source Model page.

Data Download Optimized

When master models are defined to take data from the Planning, Consolidation, and Reporting application, the download process to retrieve that data is now optimized. This optimization is specifically for those using Scenario partitions in the Planning, Consolidation, and Reporting application.

Data downloaded from the Planning, Consolidation, and Reporting application 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 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 Planning, Consolidation, and Reporting have a "Last Process Date." Modeling uses this date to track what date needs to be downloaded.

This optimization is handled internally by Modeling and no configuration settings are required from the administrator.

Known Issues

The following known issues are specific to Excel-based Reporting.

Resolved Issues