2.2.2 Modeling Release Notes, October
  • 15 Minutes to read
  • Dark
    Light
  • PDF

2.2.2 Modeling Release Notes, October

  • Dark
    Light
  • PDF

Article summary

Improvements to Calculations

Two new calculation options have been added to the Application Properties page.

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.

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

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

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

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

Note:
The view or report no longer refreshes the data automatically after the user clicks Save.

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

Note:
The calculation does not continue to run at the set interval if there are no active users triggering it. If no users are clicking Save, the calculation is not run.

Improvements to Breakback

Breakback has been improved in three areas:

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.

  1. Click Breakback.

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

  3. 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.
  1. Click to Run Calculation.

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

There is also a new option in the audit log, Action. 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.

  • New in this release, 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

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.

 

Views

 

 

Save Data menu item

Breakback menu item

1

Enable Save is No

Calculation on Save is blank

 

Disabled

Disabled

 

 

 

 

1. Save Data and Breakback menu items are not provided.

2

Enable Save is No

Calculation on Save is specified

 

Enabled

Disabled

 

 

 

 

2. Data changes are not saved, but the calculation is run when the user clicks Save Data. Breakback is not provided.

3

Enable Save is Yes

Calculation on Save is blank

 

Enabled

Enabled

 

 

 

 

3. Data changes are saved, but no calculation is run when the user clicks Save Data. Breakback is provided.

4

Enable Save is Yes

Calculation on Save is specified

 

Enabled

Enabled

 

 

 

 

4. Data changes are saved, and the calculation is run when the user clicks Save Data. Breakback is provided.

 

 

 

 

 

 

 

Reports

 

 

 

 

5

Enable Save is No

Calculation Model is blank

Calculation on Save is blank

Disabled

Disabled

 

 

 

 

5. Save Data and Breakback menu items are not provided.

6

Enable Save is No

Calculation Model is specified

Calculation on Save is specified

Enabled

Disabled

 

 

 

 

6. Data changes are not saved, but the calculation is run when the user clicks Save Data. Breakback is not provided.

7

Enable Save is Calculation Model

Calculation Model is specified

Calculation on Save is blank

Enabled

Enabled

 

 

 

 

7 Data changes are saved, but no calculation is run when the user clicks Save Data. Breakback is provided.

8

Enable Save is Calculation Model

Calculation Model is specified

Calculation on Save is specified

Enabled

Enabled

 

 

 

 

8 Data changes are saved, and the calculation is run when the user clicks Save Data. Breakback is provided.

9

Enable Save is All Models

Calculation Model is specified

Calculation on Save is specified

Enabled

Enabled

 

 

 

 

9 Data changes are saved to all models, and the calculation is run when the user clicks Save Data. Breakback is provided.

10

Enable Save is All Models

Calculation Model is blank

Calculation on Save is blank

Enabled

Enabled

 

 

 

 

10 Data changes are saved to all models, but no calculation is run when the user clicks Save Data. Breakback is provided.

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.

Note:
This feature is also applicable in Reports. For example, if there is unsaved data and if user tries to open another report or clicks Design Report, then 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. A new 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.

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

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

  2. New 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.

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

  2. Click the Member Select icon.

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

  1. Click Select.

  2. 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 a new 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.

Note:
Data Download is performed when a calculation is run that calls for it.

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.

Note:
If, for any reason, the data is not being downloaded, process the Scenario partition in the Planning, Consolidation, and Reporting application, and then try downloading the data in Modeling.

Known Issues

K-55

Error with Breakback on a view or report with an Attribute defined with only a root member

3080

In some cases, strange artifacts appear on screen in Excel while saving a large report with freeze pane.

3079

In some cases, Excel 2016 crashes with cursor on a blank cell and selecting Design Manager in report design.

2971

Breakback does not hold the cells on the report which are locked.

2873

Breakback on rollup value from negative to positive may yield weird or incorrect results.

2611

Cascade Reports will not work as expected if the user deletes a Dimension or an Attribute from the Model.

2492

If a report which is saved in an excel work book(offline) is renamed then users need to reload that report into the Excel workbook. Best practice is to refresh all reports in the saved workbook before renaming any report.

2421

Excel "Compatibility Mode" is Unsupported

2354

Snapshot email link will not work for users with SSO integration if they are not logged into the application. As a workaround they should log into the application and then access the Snapshot email link.

2276

If user makes any changes to the report (ex:- formatting, adding rows or columns, deleting rows or columns) after running it and then take a Snapshot then user will not see all the changes made to the report and in some cases the snapshot will look weird. We recommend users to take Snapshots without making any changes to changes to the report after its executed

2113

Integration between Planning & Modeling applications from web interface is best supported in Google Chrome browser. Users cannot navigate from Planning to Modeling if Internet Explorer compatibility mode is turned on.

2080

If users are using Firefox browser, right clicking an item in the Folder structure may display an incorrect menu.

1845

If POV member defined is removed or renamed, it cannot be fixed in report design mode and error is displayed.

1492

Using special characters for the model name results in errors.

1464

When suppress rows/columns is enabled, If Dimension Members and Data Intersection are available in same column/row in a report then dimension members will be suppressed along with data intersections.

1457

In some cases Suppress blank rows/columns is not working as expected when Groups are expanded in the report.

1455

Suppress blank rows/columns is not supported in Spotlight (i.e. Web Interface).

1454

In Some cases Suppress blank rows / columns is not working as expected if groups and freeze panes are enabled in the Report.

1446

Unable to navigate to Design View after rename or remove HostAnalyze worksheet.

1406

Excel 2016 charts containing 3D Clustered Column, 3D Stacked Column, or 3D Stacked Bar are not supported.

1322

Unable to cascade with substitution variables.

1321

Substitution variables are not working as expected in Cascade Report.

1273

Cascade Report has inconsistencies with Filters.

1144

Freeze Panes location should not be outside of the sheet viewable range.

687

Modified Report definition by removing months column still retain after Refresh.

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

3161

Substitution variable override is not supported for worksheet with use of both display label and code settings

2518

Reviewer user has no access to Publish Excel-based reports.

2302

If there are any Snapshots in the workbook then Convert Entire Workbook option will not convert the workbook.

2203

Shared POV status is lost upon changing value of POV in Design Manager / Formatted Report and EBR.

2199

Filter member options are not available if only 1 cell from a model exists, in formatted report and EBR.

2097

When using Design Manager, Update button is enabled only after entering a valid New Value and hitting enter.

1887

It takes over 1 - 3 min to open 22 MB Excel workbook.

1755

Locking / Scaling is not applied when converting a Formatted Report to Excel-based Report.

1699

It may takes up to couple of hours to Capture Entire Workbook with 30 worksheets and ~7000 data cells or more cells

1698

Message shows 'Please contact administrator...' when select Refresh after session timeout.

1683

Specifying a relative cell address to a cell in another tab in EBR creates an absolute cell address.

1672

Provide an option to reset the Excel workbook so users can upload using a different Report Template.

1624

Unable to download the same report as it is being used by another process.

1550

Errors when Capture Data as SpotlightXL Formulas with a cascade report.

Resolved Issues

A-41

Saving a formula causes an exception error when referencing an invalid dimension member in the Formula Design.

B-72

External Source Model Clear and Load Data operations from data integration API calls are not being captured in the Audit Log.

3034

Deleting the Account dimension from a model causes an internal server error.


Was this article helpful?