Analytics – Release Notes v2.14.0.0

Overview

This release brings new functionality, security improvements and fixes across the Analytics API, Web UI and Background Jobs. Highlights:

• The Project Summary Report can be sorted, searched and exported to Excel.
• Secrets can be read from your own Azure Key Vault.
• ILAP Data Exchange (IDE) is rebranded as SchedEx.
• All components are upgraded to .NET 10.
• Database upgrades are more reliable.

This release requires Bicep changes and two manual clean-up scripts. Read Upgrade Instructions before you upgrade.

Pull Request: #3998

---

Component Summary

• Analytics API: Version 2.14.0.0
• Analytics Background Jobs: Version 2.14.0.0
• Analytics Web UI: Version 2.14.0.0

Infrastructure note: This release requires Bicep changes for the Analytics API, the Analytics Background Jobs and the Analytics Web UI. You must:

• rename the SchedEx parameters
• set a storage account for the Project Summary Report export
• set your own Hangfire dashboard password

Several other changes are optional. See Release 2.14.0.0 in the Analytics Bicep Change History for the full list before you upgrade.

---

Completed Work Items

8012 – Project Summary Report: sorting, search and Excel export

Sorting and searching in the Project Summary Report now run on the server, so the results cover the whole report and not only the page you are looking at. You can also export the report to Excel. Large exports are prepared in the background and kept in your storage account for three days by default.

⚠️ Note: The export needs a storage account, which is now always deployed. See the Analytics Bicep Change History.

8499 – Loading indicator in the Project Summary Report

While the report loads, the grid now shows a loading indicator. Before, it showed an empty grid, which looked like the report had no data.

7756 – Read secrets from your own Azure Key Vault

The SchedEx access token, the time-phasing access token and the Hangfire dashboard password can now be read from an Azure Key Vault you own. They no longer have to be deployed as plain-text application settings. The feature is off by default, and you can adopt it one secret at a time. If a secret cannot be read, /health reports it as Degraded and names the secret.

⚠️ Note: The Hangfire dashboard password no longer has a default. Your parameter file must set it, and the old sample password must be replaced with your own.

7791 – Rebranding: ILAP Data Exchange is now SchedEx

Four deployment parameters are renamed, and the SchedEx URLs change to schedex.com. See Upgrade Instructions. The old parameter names still work in this release, but support for them will be removed in a later release.

7984 – Upgrade to .NET 10

The Analytics API, Background Jobs and Web UI now run on .NET 10, because support for .NET 8 ends in November 2026. The Bicep templates set the new runtime (DOTNETCORE|10.0) when you redeploy. You do not need to change any parameter.

8268 – More reliable database upgrades

When the API applies database migrations at startup, a slow migration could get the app container stopped before it finished. The container then restarted and tried again in a loop. The apps now get more time to start and use /health as their health check, so a long migration finishes instead of looping.

You can also apply migrations yourself before you deploy: set migrateDatabaseOnStartup = 'false' and follow Migrating databases.

8484 – Database upgrade script fix

The database upgrade script (migrate-main.sql) failed with Invalid column name 'DownloadedAtUtc' on databases upgraded from 2.13. The script now applies cleanly.

8465 – Schedule revision references in the ReportSchedule REST endpoints

GET /api/ReportSchedule and GET /api/ReportSchedule/{id} now return these fields:

• ScheduleId
• OriginalBaselineScheduleId
• BaselineScheduleId
• CurrentScheduleId
• RevisedScheduleId

You can now link each schedule revision by its schedule id in your own data platform. These values are read-only and are ignored on POST.

8378 – Service token validation

Service tokens are now checked against the API address set in the configuration. Before, they were checked against the address in the incoming request. A request can no longer pass validation by changing its Host header, and tokens are validated the same way whether the API is reached directly, through Application Gateway or through a custom domain.

8389 – Background Jobs version in the information popover

The information popover in the Web UI now shows the Background Jobs version next to the Web and API versions.

8476, 8477 – Metadata columns in "Export all activities"

"Export all activities" from the schedule grid now includes every metadata field used by any activity in the schedule. Before, it took the columns from one activity, chosen at random, so fields could be missing and the column set could change between runs.

Schedules whose planning data has been purged now export the same metadata columns, read from the Parquet snapshot.

6846 – Non-string ILAP term values shown in the Web UI

ILAP term values that are not text, such as booleans and numbers, are now shown in the ILAP Terms view. Before, only text values were shown.

6847 – Unsupported ILAP terms removed

ILAP terms are supported only on the Schedule and Activity levels. Terms stored on any other level were never shown in the interface, so import conflicts on them could not be resolved. Remove them with the clean-up scripts in Upgrade Instructions.

7810 – Grid export fixes

When you export from the report schedule details grid, the Reference column now holds the correct value. When you export from the ILAP Terms sync grid, the planning object type is shown by name instead of as a number.

7968 – Time-phasing after revert

Reverting an import now removes that import's time-phased rows. Before, stale values could be added to the cumulative figures of the next calculation.

8360 – Custom domains: one certificate entry per certificate

Certificates for custom domains are now declared once in a new customDomainCertificates parameter. Apps that share a certificate, such as a wildcard, all point to the same entry. Before, each app created its own certificate, and the second app's deployment failed. This applies only if you use custom domains.

8256 – SQL Server firewall rules in Bicep

The new optional parameter sqlServerFirewallRules lets you add firewall rules to the SQL Server from your parameter file. For example, you can let a database administrator connect to run the database user grants. If you leave it out, nothing changes.

8251 – Swagger scopes no longer break the deployment

A deployment with fewer than two SwaggerAzureAdAuthOptions.Scopes failed after several minutes with an unclear template error. Any number of scopes now works, including none.

8257 – Sample parameter file compiles again

The eventhubConfig block in main.sample.bicepparam did not compile. The whole block is now commented out. eventhubConfig is optional.

8291 – Resource naming convention for new installations

The sample parameter file now builds every resource name from four values: organisation, workload, environment and region. Existing installations keep their current names, and no action is needed.

8245, 8259, 8260, 8261, 8262, 8263, 8288, 8306 – Reference security example

The reference security example and playbook have been extended and corrected:

• The SchedEx Autonomous Component can be placed in the Analytics virtual network.
• The Parquet export storage account is covered.
• Key Vault resolves privately once vaultcore.azure.net is also routed.
• The hardening script cleans up the firewall rules on both SQL servers.
• The connectivity diagnostics no longer report false failures.
• The playbook explains how to set up DNS on a corporate network and how to restrict access to the Application Gateway's public frontend.

8299, 8489, 8490 – Documentation as PDF

The installation guide and the full product documentation are now built as PDFs alongside each release.

Internal improvements

The following items improve our build, deployment and test tooling, and do not change the product: 8297, 8390, 8394, 8395, 8403, 8458.

---

Upgrade Instructions

1. Rename the SchedEx parameters and update the URLs

Applies to release 2.14.0.0 and later.

Rename these parameters. Move your current value to the new name.

Old name	New name
ideApiBaseUrl	schedExApiBaseUrl
ideApiAccessToken	schedExApiAccessToken
SendJobStatusToIDE	SendJobStatusToSchedEx
publicConfigurations.ideUiBaseUrl	publicConfigurations.schedExUiBaseUrl

Update the URL values.

Parameter	Production	Test
schedExApiBaseUrl	https://schedex.com/api/api	https://test.schedex.com/api/api
publicConfigurations.schedExUiBaseUrl	https://schedex.com	https://test.schedex.com

Keep these values as they are.

• schedExApiAccessToken: move your current token to the new name.
• SendJobStatusToSchedEx: move your current true/false value to the new name.

⚠️ Note: If your network restricts outbound traffic, allow schedex.com and test.schedex.com.

2. Apply the other Bicep changes

Read Release 2.14.0.0 in the Analytics Bicep Change History. At a minimum:

• check storageAccountParams, needed for the Project Summary Report export
• set your own hangfireCredentials password

3. Remove unsupported ILAP terms

This upgrade removes ILAP terms stored on any level other than Schedule and Activity, together with every value attached to them (see 6847).

The upgrade does not do this automatically, because it could make startup time out. Run the two scripts below instead. That way you can see exactly what will be removed before you remove it.

Before you start

• This cannot be undone. Make sure a backup is available in Azure. Azure SQL keeps automatic backups.
• Run both scripts against the main database and then against the archive database, four runs in total.

Step 1 — Report what will be removed

Run this first against each database and save the output. Once step 2 has run, this report cannot be reproduced. This script does not change anything.

SELECT
    f.Name   AS TermName,
    f.IlapId AS IlapId,
    CASE f.PlanningObjectTypes
        WHEN 0    THEN 'None'
        WHEN 16   THEN 'Resource'
        WHEN 64   THEN 'Resource Assignment'
        WHEN 72   THEN 'Schedule Object'
        WHEN 128  THEN 'Reporting'
        WHEN 4095 THEN 'All'
        ELSE 'Other combination (' + CAST(f.PlanningObjectTypes AS varchar(11)) + ')'
    END AS PlanningObjectType,
    (SELECT COUNT_BIG(*) FROM Planning_MetadataFieldValue v
      WHERE v.MetadataFieldId = f.Id) AS TermValues,
    (SELECT COUNT_BIG(*) FROM Planning_ActivityMetadataFieldValue l
      INNER JOIN Planning_MetadataFieldValue v ON v.Id = l.MetadataFieldValuesId
      WHERE v.MetadataFieldId = f.Id) AS PlanningActivityTerms,
    (SELECT COUNT_BIG(*) FROM Planning_ScheduleMetadataFieldValue l
      INNER JOIN Planning_MetadataFieldValue v ON v.Id = l.MetadataFieldValuesId
      WHERE v.MetadataFieldId = f.Id) AS PlanningScheduleTerms,
    (SELECT COUNT_BIG(*) FROM Planning_ResourceMetadataFieldValue l
      INNER JOIN Planning_MetadataFieldValue v ON v.Id = l.MetadataFieldValuesId
      WHERE v.MetadataFieldId = f.Id) AS PlanningResourceTerms,
    (SELECT COUNT_BIG(*) FROM Planning_ResourceAssignmentMetadataFieldValue l
      INNER JOIN Planning_MetadataFieldValue v ON v.Id = l.MetadataFieldValuesId
      WHERE v.MetadataFieldId = f.Id) AS PlanningResourceAssignmentTerms,
    (SELECT COUNT_BIG(*) FROM Reporting_ActivityMetadataFieldValue l
      INNER JOIN Planning_MetadataFieldValue v ON v.Id = l.MetadataFieldValuesId
      WHERE v.MetadataFieldId = f.Id) AS ReportingActivityTerms,
    (SELECT COUNT_BIG(*) FROM Reporting_ScheduleMetadataFieldValue l
      INNER JOIN Planning_MetadataFieldValue v ON v.Id = l.MetadataFieldValuesId
      WHERE v.MetadataFieldId = f.Id) AS ReportingScheduleTerms
FROM Planning_MetadataField AS f
WHERE f.PlanningObjectTypes NOT IN (2, 8)   -- only Schedule (2) and Activity (8) are supported
ORDER BY f.Name;

Step 2 — Delete

Run this only when step 1's output is saved, no job is running and a backup exists. Run it against the main database first, then the archive database.

Deletions run in batches, and each batch is committed on its own, so you can safely interrupt the script. If you run it again, it picks up whatever is left. It prints a line as each table finishes. It ends with two verification counts, and both must be 0.

The archive database may take longer than the main one.

SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @BatchSize int = 5000;
DECLARE @Rows int;

DROP TABLE IF EXISTS #TermIds;
DROP TABLE IF EXISTS #TermValueIds;

-- The unsupported terms (Schedule = 2 and Activity = 8 are the only supported types)
SELECT Id
INTO #TermIds
FROM Planning_MetadataField
WHERE PlanningObjectTypes NOT IN (2, 8);

-- Their values
SELECT v.Id
INTO #TermValueIds
FROM Planning_MetadataFieldValue v
INNER JOIN #TermIds t ON t.Id = v.MetadataFieldId;

-- What this run is about to remove
SELECT (SELECT COUNT_BIG(*) FROM #TermIds)      AS TermsToDelete,
       (SELECT COUNT_BIG(*) FROM #TermValueIds) AS TermValuesToDelete;

-- Phase 1: detach the values from every object. One pass takes a batch from each
-- of the six link tables; the loop ends when a whole pass removes nothing.
SET @Rows = 1;
WHILE @Rows > 0
BEGIN
    SET @Rows = 0;

    DELETE TOP (@BatchSize) link FROM Planning_ActivityMetadataFieldValue link
    INNER JOIN #TermValueIds v ON v.Id = link.MetadataFieldValuesId;
    SET @Rows = @Rows + @@ROWCOUNT;

    DELETE TOP (@BatchSize) link FROM Planning_ScheduleMetadataFieldValue link
    INNER JOIN #TermValueIds v ON v.Id = link.MetadataFieldValuesId;
    SET @Rows = @Rows + @@ROWCOUNT;

    DELETE TOP (@BatchSize) link FROM Planning_ResourceMetadataFieldValue link
    INNER JOIN #TermValueIds v ON v.Id = link.MetadataFieldValuesId;
    SET @Rows = @Rows + @@ROWCOUNT;

    DELETE TOP (@BatchSize) link FROM Planning_ResourceAssignmentMetadataFieldValue link
    INNER JOIN #TermValueIds v ON v.Id = link.MetadataFieldValuesId;
    SET @Rows = @Rows + @@ROWCOUNT;

    DELETE TOP (@BatchSize) link FROM Reporting_ActivityMetadataFieldValue link
    INNER JOIN #TermValueIds v ON v.Id = link.MetadataFieldValuesId;
    SET @Rows = @Rows + @@ROWCOUNT;

    DELETE TOP (@BatchSize) link FROM Reporting_ScheduleMetadataFieldValue link
    INNER JOIN #TermValueIds v ON v.Id = link.MetadataFieldValuesId;
    SET @Rows = @Rows + @@ROWCOUNT;
END

-- Phase 2: delete the term values, now that nothing references them
SET @Rows = 1;
WHILE @Rows > 0
BEGIN
    DELETE TOP (@BatchSize) v FROM Planning_MetadataFieldValue v
    INNER JOIN #TermIds t ON t.Id = v.MetadataFieldId;
    SET @Rows = @@ROWCOUNT;
END

-- Phase 3: delete the terms themselves
DELETE f FROM Planning_MetadataField f
INNER JOIN #TermIds t ON t.Id = f.Id;

DROP TABLE #TermIds;
DROP TABLE #TermValueIds;

-- Verification: both must be 0
SELECT 'Unsupported terms remaining' AS Check_, COUNT_BIG(*) AS Remaining
FROM Planning_MetadataField WHERE PlanningObjectTypes NOT IN (2, 8)
UNION ALL
SELECT 'Term values orphaned', COUNT_BIG(*)
FROM Planning_MetadataFieldValue v
WHERE NOT EXISTS (SELECT 1 FROM Planning_MetadataField f WHERE f.Id = v.MetadataFieldId);

If something goes wrong

• If Unsupported terms remaining is not 0, the run did not finish. Run step 2 again, and it picks up where it left off.
• If Term values orphaned is not 0, stop and investigate before you start the application.