2.1 Modeling Release Notes, May

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.

3. Click Install.

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

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.

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.

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.

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

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

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

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

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.

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>

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>

 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

The following Known Issues are specific to Excel-based Reporting:

Resolved Issues