Description

The article describes how to rebuild Path Analyzer maps in a scaled Sitecore XP environment.

Preparing for rebuilding

1. Back up the Reporting, Shard0, and Shard1 databases (and other shards if they exist).
2. Using the following query, make sure that all collected Interactions (Shard0/ Shard1 databases) point to existing channels (Shard0, for example):

   SELECT DISTINCT [ChannelId] FROM [Shard0].[xdb_collection].[Interactions]
   WHERE [ChannelId] NOT IN (SELECT [ID] FROM [SitecoreMaster].[dbo].[Items] where [TemplateID] = '{3B4FDE65-16A8-491D-BF15-99CE83CF3506}')

   If this query outputs any values (not existing channels), these channels must be fixed, for example, by replacing them with the widely-used channel ‘{B418E4F2-1013-4B42-A053-B6D4DCA988BF}’ (Shard0, for example):

   UPDATE [Shard0].[xdb_collection].[Interactions] SET [ChannelId] = '{B418E4F2-1013-4B42-A053-B6D4DCA988BF}'
   WHERE [ChannelId] = 'VALUE-FROM-PREVIOUS-QUERY'

   Note: '00000000-0000-0000-0000-000000000000' ChannelId is also a not valid value.
3. In the application pool advanced settings of the Standalone (XP Single) or Processing (XP Scaled) role, it is better to set:
   
   • ProcessModel -> Idle Time-out (minutes) = 0
   • Recycling -> Regular Time Intervals (minutes) = 0
   to make sure that the instance does not stop working after a configured period.
4. In the Standalone/ Processing instance, temporarily perform the following change:
   
   • For on-premise SQL Server, add the parameter "Max Pool Size=1000" to the "Master" and "Reporting" connection strings.
   • For Azure SQL, consider increasing the pricing tier of the "Master" and "Reporting" databases.
   A known symptom that these databases cannot handle the needed load during a PathAnalyzer rebuilding process is the presence of the following errors in the logs:

   ERROR Exception while processing item Sitecore.Analytics.Core.ItemBatch`1[Sitecore.XConnect.Interaction] in task GUID-of-task-from-table-ProcessingTasks.
   Exception: System.InvalidOperationException
   Message: ExecuteReader requires an open and available Connection. The connection's current state is closed.
   Source: System.Data
   at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
   …
   at Sitecore.PathAnalyzer.Processing.TreeAggregator.Aggregate(ItemBatch`1 batch)
   …

5. Delete unfinished Path Analyzer rebuild tasks:

   DELETE FROM [Processing.Tasks].[xdb_processing_tasks].[ProcessingTasks]
   WHERE ([Description] = 'Build Map Agent' OR [Description] = 'Rebuild ALL Deployed Maps') AND [Status] < 2 

6. Optionally, in the \App_Config\Sitecore.config file of the Standalone/ Processing instance, temporarily change the log level to DEBUG. This change can help in better analyzing the PathAnalyzer rebuilding process using logs:

   <root>
   <priority value="DEBUG" />
   <appender-ref ref="LogFileAppender" />
   </root>

Rebuilding

For Sitecore XP Scaled, where Processing and Reporting roles are separate instances:

1. Delete data from the tables below:

   DELETE FROM [Reporting].[dbo].[Trail_Interactions]
   DELETE FROM [Reporting].[dbo].[Trail_PathAnalyzer]
   DELETE FROM [Reporting].[dbo].[TreeDefinitions]
   DELETE FROM [Reporting].[dbo].[Trees]

2. Wait for about 30 minutes. Rebuilding of the historical interactions must be started automatically.

For other topology configurations:

1. Using SQL Server Management Studio and SQL admin credentials, temporarily add role "db_owner" for the "reportinguser" user of the Reporting database.
2. Delete data from the tables below:

   DELETE FROM [Reporting].[dbo].[TreeDefinitions]
   DELETE FROM [Reporting].[dbo].[Trees]

3. In the Standalone/ Content Management instance, open the following page: /sitecore/admin/pathanalyzer.aspx
4. At the bottom of the page, click Upgrade.
5. At the top of the page, click Rebuild.

Monitoring the rebuild progress

1. The number of created trees should periodically increase when rebuilding is in progress:

   SELECT COUNT(*) FROM [Reporting].[dbo].[Trees]

2. Related rebuilding tasks can provide details:

   SELECT * FROM [Processing.Tasks].[xdb_processing_tasks].[ProcessingTasks]
   WHERE [Description] = 'Build Map Agent' OR [Description] = 'Rebuild ALL Deployed Maps'

   [Status]: Pending = 0, Processing = 1, Completed = 2, Failed = 3, Expired = 4, Canceled = 5
   [Progress]: Number of currently processed interactions
   [Total]: Total number of interactions that are expected to be processed

Post steps

1. In the Standalone/ Content Management instance, open the PathAnalyzer application and check whether it shows expected data.
2. After rebuilding the Path Analyzer maps, revert the temporary changes.

While the Path Analyzer rebuilding process creates maps backward from the current date, "live" Path Analyzer activity should create maps for new interactions forward from the current date, so no missing maps are expected.