Rebuilding Profiling Data

The weekly profiling tables behind the Activity & Usage Analytics reports are compiled once and never revisited. This page explains when that leaves them wrong or incomplete, and how to drop and recompile some or all of them.

In this page

• Why rebuild
• How the weekly compile works
• Before you start
• Find the weeks to rebuild
• Option 1: Recompile a range of weeks
• Option 2: Rebuild everything
• Option 3: Start fresh from a chosen week
• Running a long rebuild
• Afterwards

Why rebuild

The Weekly_Update runbook adds each finished week to the profiling tables once and never goes back to it. That keeps the weekly run cheap, but it means a week keeps whatever numbers it had on the day it was compiled — even if more data arrives later, or a newer build calculates it differently.

Situation	What you see in the reports	What to do
An upgrade added new metrics, for example Copilot	The new metrics are zero for every week compiled before the upgrade	Option 1 for the weeks you want them in
The system was down, or an import fell behind, and the data arrived after the week was compiled	Weeks around the outage are low or missing	Option 1 for those weeks
You ran a Copilot audit backfill	Copilot figures are low or zero for the weeks it filled in	Option 1 for those weeks
A long outage left a gap you'd rather not report on	A hole in every trend chart	Option 3, from the first full week after the outage
A release changed how a metric is calculated	A step change in the trend at the upgrade date	Option 2
You increased WeeksToKeep	The extra, older weeks never appear	Option 1, step 2 only, with the new value

Don't rebuild if:

• Only last week is missing. That's normal. A week is compiled once it has finished and is at least four days old, so the default Sunday schedule adds it a week after it ends. If you can't wait, start Weekly_Update by hand: any run from the Friday after a week ends picks it up.
• The source data has gone. A rebuild reads the daily activity tables. If they no longer cover a week — Clean Old Data Data.sql, for example, deletes daily activity data older than a month — dropping that week loses it for good.
• The data never existed. Weeks from before a workload was imported — Copilot before the build that added it, say — are zero however often you rebuild them.

How the weekly compile works

Weekly_Update runs the stored procedure profiling.usp_CompileWeekly, passing it the WeeksToKeep Automation variable (default 52). The rules that matter when rebuilding:

• Weeks run Monday to Sunday (UTC) and are stored under their Monday's date.
• A week is compiled once it has finished and is four days old, to allow for Microsoft's usage reports arriving a few days late.
• Each run carries on after the latest week already in the tables. It compiles the finished weeks after that one and nothing before it, so a week missing from the middle stays missing.
• A week that's already in the tables is never recompiled, whatever has changed since. That's why every option below deletes weeks first.
• Of the three tables, the one furthest behind decides where a run carries on. An empty table is ignored. If all three are empty, the run starts from the beginning of the retention window.
• Retention: every run ends by deleting weeks older than WeeksToKeep weeks. On empty tables the first run compiles WeeksToKeep − 1 weeks, because it starts one week after that cut-off.
• A failed week doesn't fail the runbook. The error is written to profiling.TraceLogs and the run still reports success, so always check the trace log after a rebuild.

The compile writes three tables. Treat them as a set:

Table	Contents
profiling.ActivitiesWeekly	One row per user, week and metric. By far the largest.
profiling.ActivitiesWeeklyColumns	One row per user and week, with a column per metric
profiling.UsageWeekly	One row per user and week: the Teams devices, Office apps and Viva Engage platforms they used

See Analytics data model for the metrics.

Always delete the same weeks from all three tables. If you empty only some of them, the next run carries on after the latest week in the table you left alone, and the history you emptied is never refilled.

Before you start

1. Get SQL access. You need db_owner on the analytics database, and a query tool that doesn't time out, such as SQL Server Management Studio (SSMS). See Give yourself access to query the database.
2. Check the source data covers the weeks you want. The Profiling page of the web portal shows the earliest and latest date of each source table, including Copilot interactions.
3. Make sure the profiling procedures are current. The installer's database upgrade updates them, but the *.manual.sql scripts attached to releases only apply the schema migrations. If you upgraded the database by hand, run the installer's schema upgrade once as well: see Forcing schema updates. For Copilot metrics, SELECT OBJECT_ID('profiling.usp_UpsertCopilot'); must not return NULL.
4. Pause the schedule. In the Automation account, open Schedules, select the schedule linked to Weekly_Update (by default Weekly Sunday 6pm) and set Enabled to No. A scheduled run that starts during the rebuild works on the same weeks at the same time, doubling the load and filling the trace log with errors.
5. Check the database has room. A rebuild writes every recompiled week in one go. ActivitiesWeekly holds a row for every metric for every user in each week's activity data, zeros included, so the Copilot metrics make it larger even where nobody uses Copilot. See How much extra resources are needed?
6. Warn your report users. Rebuilt weeks are missing from the Power BI reports until the rebuild finishes and the data model is refreshed.

Find the weeks to rebuild

Users per compiled week. Outages show up as dips or missing weeks:

SELECT [date] AS week_monday, COUNT(*) AS users
FROM profiling.ActivitiesWeeklyColumns
GROUP BY [date]
ORDER BY [date];

Copilot records per week, in the source data and in the compiled data:

These are coverage clues, not equal counting definitions. raw_chats counts physical audit records; turn-aware profiling can count a mapped turn once and exclude maker testing. A lower compiled figure is not by itself missing data; a matching figure does not prove the current procedure was used. After approved optional historical repair, update procedures and explicitly recompile affected weeks. Neither repair nor procedure replacement rewrites compiled results. Final Power BI refresh/operational acceptance remains separate.

WITH raw AS (
  SELECT DATEADD(WEEK, DATEDIFF(DAY, '19000101', time_stamp) / 7, '19000101') AS week_monday,
         COUNT(*) AS raw_chats
  FROM dbo.copilot_chats
  WHERE time_stamp IS NOT NULL
  GROUP BY DATEADD(WEEK, DATEDIFF(DAY, '19000101', time_stamp) / 7, '19000101')
),
compiled AS (
  SELECT [date] AS week_monday, SUM([Copilot Chats]) AS compiled_chats
  FROM profiling.ActivitiesWeeklyColumns
  WHERE [date] >= (SELECT MIN(week_monday) FROM raw)
  GROUP BY [date]
)
SELECT CONVERT(date, COALESCE(r.week_monday, c.week_monday)) AS week_monday, r.raw_chats, c.compiled_chats
FROM raw AS r
  FULL JOIN compiled AS c ON c.week_monday = r.week_monday
ORDER BY 1;

raw_chats	compiled_chats	Meaning
A number	NULL	Not compiled yet. Normal for the most recent week.
A number	0, or lower	Could be missing/old compilation or different turn/test-pane rules. Check the procedure and expected counted population before rebuilding
A number	The same number	Counts match; this alone does not prove current metric definitions or resource attribution

Weeks that failed to compile:

SELECT TOP (50) [Datetime], Message
FROM profiling.TraceLogs
WHERE Message LIKE '%Catch%'
ORDER BY Id DESC;

Fix the cause of any failure first, or the rebuild fails the same way.

Option 1: Recompile a range of weeks

Keeps the rest of the history. Works for the most recent weeks or for a range in the middle.

-- Any date in the first and in the last week to recompile
DECLARE @FromMonday date = profiling.udf_GetMonday('2026-08-03');
DECLARE @ToMonday   date = profiling.udf_GetMonday('2026-09-14');

-- 1. Delete those weeks from all three tables. ActivitiesWeekly is large, so delete it in batches.
WHILE 1 = 1
BEGIN
  DELETE TOP (100000) FROM profiling.ActivitiesWeekly
  WHERE MetricDate BETWEEN @FromMonday AND @ToMonday;
  IF @@ROWCOUNT = 0 BREAK;
END
DELETE FROM profiling.ActivitiesWeeklyColumns WHERE [date] BETWEEN @FromMonday AND @ToMonday;
DELETE FROM profiling.UsageWeekly             WHERE [date] BETWEEN @FromMonday AND @ToMonday;

-- 2. Compile every missing week in the retention window.
--    Pass the same value as the WeeksToKeep Automation variable.
EXEC profiling.usp_CompileWeekly @WeeksToKeep = 52, @All = 1;

@All = 1 makes the run go through every week in the retention window instead of carrying on from the latest one: it compiles the weeks that are missing and skips the rest. That's what refills a range in the middle, and what adds the older weeks after you increase WeeksToKeep.

Use the same WeeksToKeep as the Automation variable. Every run deletes the weeks older than the window it's given, so a smaller number deletes history.

Option 2: Rebuild everything

Drops all the weekly data and recompiles the whole retention window from the source data. With the default WeeksToKeep of 52, that's the last 51 finished weeks.

TRUNCATE TABLE profiling.ActivitiesWeekly;
TRUNCATE TABLE profiling.ActivitiesWeeklyColumns;
TRUNCATE TABLE profiling.UsageWeekly;
TRUNCATE TABLE profiling.TraceLogs;   -- optional: the log then shows only the rebuild

-- Pass the same value as the WeeksToKeep Automation variable
EXEC profiling.usp_CompileWeekly @WeeksToKeep = 52;

Any period the source data doesn't cover, such as an outage or the time before a workload was imported, comes back missing or low. For a long gap, Option 3 gives a cleaner series.

Option 3: Start fresh from a chosen week

Drops all the weekly data and compiles from a Monday you choose, such as the first full week after an outage. The scheduled runs carry on from there, so the history grows back to WeeksToKeep weeks over time. Weeks before your start are never compiled.

TRUNCATE TABLE profiling.ActivitiesWeekly;
TRUNCATE TABLE profiling.ActivitiesWeeklyColumns;
TRUNCATE TABLE profiling.UsageWeekly;
TRUNCATE TABLE profiling.TraceLogs;   -- optional

-- Any date in the first week to keep
DECLARE @StartMonday date = profiling.udf_GetMonday('2026-08-05');
DECLARE @ThisWeeksMonday date = profiling.udf_GetMonday(DATEADD(DAY, -4, GETDATE()));
DECLARE @WeeksToKeep int = DATEDIFF(DAY, @StartMonday, @ThisWeeksMonday) / 7 + 1;
EXEC profiling.usp_CompileWeekly @WeeksToKeep = @WeeksToKeep;

The @WeeksToKeep here is only for this one run: it's the window that makes the run start at @StartMonday. Leave the Automation variable at its usual value.

Only run this straight after the TRUNCATE statements. On tables that still hold data, the short window deletes nearly all the history before @StartMonday.

Keep the schedule paused until this has written its first week. A scheduled run against empty tables rebuilds the whole retention window instead.

Running a long rebuild

A rebuild does a normal weekly run's work for every week it compiles, so Option 2 with the default settings is 51 weeks' worth.

• Run it in SSMS rather than through the runbook. SSMS doesn't time out by default; Weekly_Update stops after about three hours.

• If it stops, run it again without the TRUNCATE or DELETE statements. Finished weeks are kept, and it carries on after them.

• Watch progress from a second query window:

  SELECT TOP (20) [Datetime], Message FROM profiling.TraceLogs ORDER BY Id DESC;

  Each week logs Week from <Monday> to <Sunday>. A message containing Catch: means part of that week failed.

Afterwards

1. Re-enable the schedule you paused.
2. Check the result. On the Profiling page, or with the Aggregation_Status runbook, each profiling table's earliest and latest dates should match what you rebuilt, and the trace log should have no Catch: messages. Re-run the queries in Find the weeks to rebuild.
3. Rebuild the indexes by running the Database_Maintenance runbook with MAINTENANCETYPE set to weekly.
4. Refresh the Power BI data model (Analytics_DataModel) so the reports show the rebuilt weeks.