2.1 Modeling Release Notes, May
  • 15 Minutes to read
  • Dark
    Light
  • PDF

2.1 Modeling Release Notes, May

  • Dark
    Light
  • PDF

Article summary

SpotlightXL Add-In Installation Format using ClickOnce

The SpotlightXL Add-In installation is now available in Microsoft ClickOnce form. ClickOnce allows the user to run an installation program one time to install SpotlightXL and thereafter to receive updates to SpotlightXL automatically.

With this release, the SpotlightXL Add-In will be distributed in two ways.

Option 1: Current process, which was introduced in Release 2.0.2. The format of the Add-In is MSI and it is installed with InstallShield. Once we complete the release every month, we will distribute the latest Add-In in MSI, and users can download and install the Add-In. Users must still use Control Panel to uninstall the previous release before running the new InstallShield MSI file. The Add-In will be posted in the community and the support portal.

Option 2: This format is called ClickOnce. This format automates the process of updating the Add-In each month. Instead of uninstalling the old Add-In, downloading the new Add-In, and then installing the new Add-In each month, users set it up once and then allow the updates to be installed automatically going forward. Once we complete the release every month, we will distribute the latest Add-In in ClickOnce. When users start Excel, SpotlightXL will look for a new Add-In and then automatically uninstall the old Add-In and install the newly available Add-In.

Option 2 is recommended for all users. Below are some examples.

Example 1

User A installs the ClickOnce Add-In in May-17. Then when we release the June-17 Add-In, User A does not have to do anything because Excel automatically installs the latest Add-In.

Example 2

User B installs the InstallShield MSI Add-In in May-17. Then when we release the June-17 Add-In, User B has to uninstall the May-17 Add-In with Control Panel and then install the June-17 Add-In.

Example 3

User C is a new user and has not installed the SpotlightXL Add-In yet. User C can install ClickOnce in May-17 and receive automatic updates going forward. This option is recommended for all new users.

Example 4

User D already has the SpotlightXL Add-In installed from previous months, but wants to automate the Add-In installation going forward. User D has to uninstall their Add-In using Control Panel, and then install the May-17 ClickOnce Add-In.

Example 5

User E installs the ClickOnce Add-In in May-17 and gets the latest Add-In in June-17 automatically. If the user does not want to get the latest Add-In automatically in July-17, then User E can uninstall ClickOnce, uninstall the current Add-In, and then install the June-17 InstallShield MSI Add-In so that the process will be manual going forward.

Key Points

Option 1 is controlled by the user and very manual. It requires local admin privileges on the machine to uninstall and install the Add-In.

Option 2 is automated and does not need any local admin privileges. We highly recommend users to choose Option 2.

URLs

Below you can see the URLs for ClickOnce and InstallShield MSI:

Naming convention for ClickOnce:

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

Example:

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

Naming convention for InstallShield MSI:

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_May2017_MSI.exe

How to Use the ClickOnce Install

When installing ClickOnce for the first time:

  1. Download the SpotlightXL Add-In.

  2. Run SpotlightXL_May2017.exe.

  1. Click Install.

  2. When installation is complete, click Close.

Going forward, updates are installed automatically.

If you try to run SpotlightXL_May2017.exe a second time, the installer looks to see if you already have the latest release, and if so, displays the following message:

"The latest version of this customization is already installed."

Change Data Tracking

In this release, we are introducing Change Data Tracking. This feature marks leaf-level data as dirty whenever it is changed, such as through user input, calculations, or data load. When the next full aggregation (Aggregation, None) is performed, only the rollups with dirty leaf-level data are aggregated. This makes full aggregations faster.

Enabling Change Data Tracking is helpful in cases where users are updating one or two department’s data and need to run a full aggregation to see the data rolled up against all departments. Once Change Data Tracking is enabled, the application will track which data blocks are changing. When a full aggregation is run, instead of running the aggregation for the entire model, the system will run aggregation only for the blocks that changed (in this case, the departments that changed and the dependent rollup members), so the total aggregation process will take less time.

Change Data Tracking is available for all applications, but it is turned off by default for each individual model. You must turn this feature on for models that will use Change Data Tracking.

Here are the overall steps to use Change Data Tracking.

  1. Select a model.

  2. Run a full aggregation (Aggregation, None) on the model. This ensures that data is clean to begin with.

  3. Turn on Change Data Tracking for the model. Thereafter the feature works seamlessly.

  4. Whenever you aggregate going forward, do a full aggregation to get the full benefit of this feature.

How It Works

This feature tracks changes to the data blocks; that is, it marks leaf-level data as dirty whenever it changes. The changes may be due to actions such as saving data entries, running a calculation, or loading data from an external source.

When a full aggregation is run, it aggregates only those blocks that have changed. (Blocks are formed from the dimension intersections of all Value dimensions in the model.) If few changes have been made since the last full aggregation, it will run faster. Once the aggregation is completed, all the dirty flags are cleared and the data is assumed to be clean again.

Enabling Change Data Tracking

Either an administrator or a designer can enable Change Data Tracking for a particular 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. Enable Change Data Tracking. Select Yes.

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

Aggregation with and without Scope

Change Data Tracking is intended to be used with full aggregations, that is, aggregations without scope (Aggregation, None). A full aggregation looks at all blocks to determine which blocks contain dirty data, and it aggregates those blocks and the dependent rollup blocks that need to be aggregated due to changes in the leaf-level data. Blocks without dirty data are skipped. After a full aggregation is completed, all the dirty flags are cleared.

You may have existing defined calculations that contain aggregations with scope. These aggregations ran faster in the past than full aggregations because they limited the number of blocks that would be calculated. For example, Aggregation, BudgetScope. If you run an aggregation with scope with Change Data Tracking Enabled, Modeling looks at the blocks within the scope to determine which blocks contain dirty data, and it aggregates those blocks. Blocks without dirty data are skipped. However, the dirty flags are not cleared after the scoped aggregation.

Best Practice

Perform full aggregations regularly. Because this feature performs optimized aggregations based on tracked changes to leaf data, it is best to aggregate regularly so the amount of dirty data stays within reason. If you never run a full aggregation, the aggregation performance will deteriorate with time. That is, it will become closer and closer toward a full non-optimized aggregation over time. Additionally, scoped aggregations do not clear the dirty flag. Therefore, we recommend performing full aggregations regularly or completely switching to using full aggregations instead of scoped aggregations whenever you need to aggregate.

Calculations with ClearRollupData

If you run a calculation containing the command ClearRollupData on a model, Change Data Tracking is effectively turned off temporarily for that model. Because all affected rollups and potentially their leaf members (if MemberAndBelow is used) are cleared, Modeling assumes that all rollups need to be aggregated again. Therefore, the next aggregation will not be optimized. Change Data Tracking will resume after the next full aggregation.

Calculations with ClearLeafData

If you run a calculation containing the command ClearLeafData on a model, all affected blocks are marked as dirty in that model.

Note:
In coming releases, this functionality may be optimized so that blocks are only marked as dirty if they actually changed. Since ClearLeafData marks data as 0, some existing leaf data may have already been 0. However, in this release, all cleared leaf data is assumed to be dirty.

Backup and Restore

When you run a Backup process, dirty flags are not included in the backup and the fact that Enable Change Data Tracking was set to Yes is not backed up. When you restore a model from backup, the model will have Enable Change Data Tracking set to No by default. You will need to do a full aggregation and re-enable the feature in the restored model.

Pausing Change Data Tracking

The following actions have the effect of turning Change Data Tracking off temporarily.

  • Running ClearRollupData on a model

  • Updating the hierarchy when the model is locked

  • Using APIs with modeling, model, clear, and data

Change Data Tracking will resume after the next full aggregation.

Forcing a Full Non-Optimized Aggregation

If you want to run a full aggregation of all blocks in the model, here is the best practice:

  1. Run a calculation containing ClearRollupData. Wait for the calculation to be completed before continuing.

  2. Run a full aggregation.

Change Data Tracking Impact

Operations that mark leaf-level data as dirty:

  • A user changes leaf-level data and saves it from a view or report

  • A calculation changes leaf-level data with a formula

  • Leaf-level data is changed from running a map

  • Leaf-level data is changed from a data load from the API or Data Load screen

  • Leaf-level data is changed in a Master model from data from HACPM

  • Leaf-level data is changed from a data load from an external source model

  • Leaf-level data is changed from an import

Operations that clear all dirty flags:

  • Running a full aggregation

  • Running a calculation containing ClearAllData on a model

  • Running a calculation containing ClearModel on a model

Operations that mark all affected data as dirty:

  • Running a calculation containing ClearLeafData on a model

Operations that pause Change Data Tracking and force the next aggregation to be a full aggregation:

  • Running a calculation containing ClearRollupData on a model

  • Updating the hierarchy

Operations that turn off Change Data Tracking:

  • Restore from backup

HACPM

HACPM is a special model that takes metadata and data from Host Analytics Applications and then acts as a source for moving that data into a Modeling model. Enable Change Data Tracking is not available on HACPM.

Ability to Delete Views and Reports from Folder Structure

With this release, you can delete views and reports directly from the folder structure. This feature is available in SpotlightXL as well as the web front end.

Note: If you delete a view or report that is used in an existing workbook, remember to also delete the tab in the workbook. Otherwise, when you click Refresh or Refresh All in the workbook, you will see an error message because the view or report has been deleted.

To delete a view:

  1. Select the Analyze task and the Data or Design View subtask.

  2. Click on the folder structure, select the view and right-click.

  3. Select Delete.

  1. SpotlightXL asks you to confirm that you want to delete the view.

  2. Click Yes or No.

To delete a report:

  1. Select the Report task and the Run or Design subtask.

  2. Click on the folder structure, select the report and right-click.

  3. Select Delete.

  1. SpotlightXL asks you to confirm that you want to delete the report.

  2. Click Yes or No.

Modify Descriptions from Model Setup Page

In Release 2.0.2, the Model Setup page was enhanced to include descriptions of artifacts associated with the model. In this release, you can edit the descriptions from the Model Setup page.

In the example below, the descriptions highlighted with yellow background can be edited on the Model Setup page. Click Save when done.

Note:
Avoid editing the description of a calculation while that calculation is running. Any changes you make to the description will be reset to the previous description after the calculation execution has completed.

Update to Model Validation Report

Many users use Modeling to integrate with the Host Analytics Core applications. These users regularly refresh metadata and data in their model with metadata and data from the Core applications. To help users identify dimension members whose codes have changed in the latest metadata refresh, a separate table is being added to the Model Validation Report under the heading Member Code Changes.

To run model validation:

  1. Select the Model task and the Model Administration subtask.

  2. Select Validation.

  3. Select the model from the Model drop-down list, or click Refresh. This begins the validation process.

The resulting list shows where there are error conditions.

Excel-based Reporting Comparison to Views and Reports

Release 2.0.2 included Excel-based Reporting for the first time. Based on customer feedback, the following additional information is being provided regarding when to use this feature.

The table below highlights the similarities and differences between views, reports, and Excel-based reports.

ViewReportExcel-Based Report

Interactive query and analysis

Formatted report with restrictions on page, row, and column axes placement

Formatted report with no restrictions

Excel and web interface

Excel and web interface

Excel interface

No formatting

Support for most commonly used formats

Support for a wide variety of Excel formats

Reference data from one model

Reference data from multiple models

Reference data from multiple models

Save data to a single model

Save data to multiple models

No saving of data

Runs calculation process

Runs calculation process

No running of calculations

Drill Through data is supported

Drill Through data is supported

Drill Through data is not supported

Model and dimension security are supported

Model and dimension security are supported

Model and dimension security are supported

 

 

Publishing to the cloud is supported

AppendMetadata Update

The previous release had an issue with AppendMetadata that has been resolved in Release 2.0.3. When there were duplicate rollups in the Core Planning source, when loaded into Modeling, the second instance of the rollup was not being appended properly into the master model.

The images below show the behavior before and after the resolution provided in this release.

Click to enlarge

Click to enlarge

Master and Analytic Data Load API Change

We have added an optional configuration to add missing leaf dimension members to the corresponding dimension in the Master and Analytic Model Data Load API.

The flag in the API adds missing leaf dimension members, if any, and then loads the data. Specify true or false.

<add-missing-dimension-members>true</add-missing-dimension-members>

Note:
To add missing leaf dimension members, the <continue-on-error> property must be set to true.

Additionally, you can specify where the additional leaf dimension members should be added.

<missing-dimension-members>

<entry>

<key>dimensionname</key>

<value>rollupname</value>

</entry>

</missing-dimension-members>

Insert the <entry> ... </entry> section for each dimension that you want to specify where new members should be added.

Use Case 1:

This will continue the data load even if there are any missing leaf dimension members or invalid numbers found in the data load and will only load the data with the valid dimensions members.

<continue-on-error>true</continue-on-error>

Use Case 2:

This will continue the data load even if there are any missing leaf dimension members or invalid numbers found in the data load. Then it will add missing leaf dimensions members to the corresponding dimensions and will load the data.

<continue-on-error>true</continue-on-error>

<add-missing-dimension-members>true</add-missing-dimension-members>

Use Case 3:

This will continue the data load even if there are any missing leaf dimension members or invalid numbers found in the data load. Then it will add missing leaf dimensions members to Dimension1Name and Dimension2Name and will load the data. The sample payload is provided below:

<?xml version="1.0" encoding="UTF-8"?>
<load-data-payload>
<model-name>Base</model-name>
<delimiter>|_|</delimiter>
<aggregate-flag>true</aggregate-flag>
<continue-on-error>true</continue-on-error>
<add-missing-dimension-members>true</add-missing-dimension-members>
<missing-dimension-members>
<entry>
<key>Dimension1Name</key>
<value>RollupMemberName</value>
</entry>
<entry>
<key>Dimension2Name</key>
<value>RollupMemberName</value>
</entry>
</missing-dimension-members>
Note:
<missing-dimension-members> ... </missing-dimension-members>

 This section is optional. If this item is not included, the missing leaf dimension members will be added to DLR_DimensionName, if it exists. Otherwise, DLR_DimensionName is created and the additional leaf dimension members are placed there. This occurs in each corresponding dimension with new members.

Known Issues

2641

The InstallShield MSI installer is not working on Windows 32-bit Operating Systems.

2611

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

2610

Updating a Calculation Description when it is running is not supported.

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 renames, 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 Chart Problems.

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:

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.

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.

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's being used by another process.

1550

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

Resolved Issues

2636

When SpotlightXL is in a connected state and the user is doing Edit Links/Change Source in Excel, SpotlightXL produces errors.

2606

When Excel is being automated by third party software, the interoperability with that software fails.

2605

Excel-based Reporting does not work on 32-bit Excel/32-bit Windows.

2567

If there is a server error while a calculation is running, the calculation status is not cleared properly.

2553

Save As report is improperly validating the calculation saved in the report properties, even if the calculation is set to None.

2483

Performance issues exist when Attributes are mapped and the view is showing leaf members.


Was this article helpful?