Orphan data in the TotalAgility Reporting Staging database
Issue
When reporting processing is working without errors, some orphan messages remain stuck in the wsa_messages table of the TotalAgility_Reporting_Staging database.
Solution
One of the elements that can contribute to a backlog of data in the TotalAgility_Reporting_Staging, or slower processing of a backlog, is orphan data in the wsa_messages table. In KTA 7.5.0.20 and earlier orphan data may prevent valid data from processing, or prevent it from processing at a normal throughput. To work around this, it may be necessary to tell the reporting service to process a larger batch of messages at a time (larger than the number of orphans). For more details see Reporting processing batch size - WSABatchSize.
Orphan records may still exist in KTA 7.5.0.21+ and KTA 7.6+, but improvements allow continued processing to occur even when orphan data is present. Additionally one cause of orphan records is resolved in 7.5.0.25, 7.6.0.11, 7.7.0.9, 7.8.0.1 (Bug 1496170 - extr_batch_ses_key isn't reported in field_accum_fact table).
As part of troubleshooting a reporting backlog issue, it may be useful to quantify any orphan data present. To do so, stop the reporting service and run the following diagnostic query on the TotalAgility_Reporting_Staging database to show the number of orphan messages, of which there are a few different types.
WITH MatchedMessages AS ( SELECT ISNULL(ba.batch_action_id,be.batch_action_id) AS batch_action_id, ExpectedTotalMsg, ISNULL(EditMsgCount,0) AS EditMsgCount, ISNULL(ActionMsgCount,0) AS ActionMsgCount FROM ( SELECT batch_action_id, COUNT(*) AS ActionMsgCount, SUM(messages_count) AS ExpectedTotalMsg FROM wsa_messages WITH(NOLOCK) WHERE function_name='KCBatchAction' GROUP BY batch_action_id ) AS ba FULL OUTER JOIN ( SELECT batch_action_id, COUNT(*) AS EditMsgCount FROM wsa_messages WITH(NOLOCK) WHERE function_name='BatchEdit' GROUP BY batch_action_id ) AS be ON be.batch_action_id=ba.batch_action_id ) SELECT MatchStatus, COUNT(*) AS MatchTypeCount, SUM(TotalMessages) AS TotalMessages FROM ( SELECT batch_action_id, EditMsgCount+ActionMsgCount AS TotalMessages, CASE WHEN ActionMsgCount>1 THEN 'NotExpected-MultipleActionSameId' WHEN ActionMsgCount=1 AND (ExpectedTotalMsg IS NULL OR ExpectedTotalMsg <2) THEN 'NotExpected-ActionNotExpectingEditMessages' WHEN ActionMsgCount=0 THEN 'Orphan-ActionMissing' WHEN ExpectedTotalMsg>EditMsgCount+ActionMsgCount THEN 'Orphan-LessEditMessagesThanExpected' WHEN ExpectedTotalMsg=EditMsgCount+ActionMsgCount THEN 'OK-ExpectedMatch' ELSE 'Unknown' END AS MatchStatus FROM MatchedMessages ) AS ms GROUP BY MatchStatus
The amount of time the query takes depends on the total number of records in the wsa_messages table, and will be slower to run if the reporting service is currently running and processing.
To use the query in a tenant database of an OPMT system, add either “reportingstaginglive.” or “reportingstagingdev.” (depending on environment) before each occurrence of the table names. The only table name used here is wsa_message.