KB485386: Dossier manipulation from web is not captured by Platform Analytics in MicroStrategy 2020 and above

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.

Symptom

In Strategy 2020 and above two nodes cluster environment, Dossier/Documents manipulation from Web/Library is not captured by Platform Analytics. 
Below steps to expose the issue in the problematic environment. 

1. Choose a dossier in library that has more than 1 page. 
2. Delete all underlying report and document caches for this dossier.
3. Open the dossier in library and change pages. 
4. No action_type_id = 328 (Manipulation) in access_transactions table as shown below. 
5. Instead, only action_type_id = 300 returns post manipulation. 

Note:

• etl_rel_action_tran_type defines the action_type_id = 328 represents Manipulation. 

• To get To get a row in access_transactions for a dossier job with action type 328 (Manipulation), we must receive a log in the Mstr.PlatformAnalytics.IsDocumentStats topic with the following field values:

{
"EXECUTIONINDICATOR":2,
"EXPORTINDIC": 0
}
It is confirmed the topic Mstr.PlatformAnalytics.IsDocumentStats topic contains the correct data:
STG_IS_DOCUMENT_STATS    {"CACHECREATORJOBID":30,"CACHECREATORSESSIONID":"EED8DE46DE2DAAFD5AE001D665AD5E15","CACHEDINDICATOR":0,"CANCELINDICATOR":0,"CPUTIME":2,"DAY_ID":"2021-12-21T15:33:20.115+0000","DOCUMENTID":"CA16585FB4439D78DC0C80A669F5118B","DOCUMENTTYPE":"1879072805","EXECACTIONS":7,"EXECDURATION":16,"EXECERRORCODE":0,"EXECFLAGS":49156,"EXECUTIONINDICATOR":"2","EXPORTINDIC":0,"FINISHTIME":23,"HOUR_ID":15,"JOBID":32,"JOB_PEAK_MEMORY_IN_BYTES":2588672,"JOB_PRIORITY":999,"JOB_TYPE":0,"MESSAGEID":"B755889FE94B849B6F305A896B9163D4","MINUTE_ID":1533,"PREVIOUSJOBID":"-1","PROJECTID":"4452A9581744B4A1CBC7FC966D298224","PROMPTANSTIME":0,"PROMPTINDICATOR":0,"REPORTCOUNT":1,"REPOSITORYID":"15394FF445A2FB19A6993B8043828C53","REPOSITORY_CONNECTION_STRING":"Driver=1800;ApplicationUsingThreads=1;database=poc_metadata;host=xx.xxx.xxx.xx;uid=xxxx;port=xxxx;","REQUESTQUEUETIME":7,"REQUESTRECTIME":"2021-12-21T15:33:20.115+0000","SERVERID":"9BF2AF874240CF3908156A8CFE3FAA0F","SERVERMACHINE":"env-273751laiouse1:34952","SESSIONID":"EED8DE46DE2DAAFD5AE001D665AD5E15","STARTTIME":7,"STEPCOUNT":3,"USERID":"7FC05A65473CE2FD845CE6A1D3F13233"}

Cause

etl_rel_action_tran_type table was corrupted with incorrect values. This caused the Redis cache to be populated with incorrect values, which caused incorrect values to be inserted into access_transactions.
Use redis-cli with the below commands to see what values are stored in the cache. These are how the action_type_id is determined based on values in the kafka message. The expected value is "912:328". 
HGET PA_RelActionTranType STG_IS_DOCUMENT_STATS:2:0
HSET PA_RelActionTranType STG_IS_DOCUMENT_STATS:2:0 "912:328"
HGETALL PA_RelActionTranType

The corrupted etl_rel_action_tran_type table has multiple entries with the same action_name and method as shown below: 

Since the etl_rel_action_tran_type table is loaded into memory in natural sort order (ascending by ID), the invalid entry overwrote the valid entry in the Redis cache. This caused valid "328-Manipulation" actions to be mapped to "300-Execution"

Action

1. Stop PA Consumer
2. Take backup of platform_analytics_wh
3. Execute sql in the attached script to truncate the existing etl_rel_action_tran_type table and rebuild it (please see Attachments section)
4. Restart the consumer
5. After allowing time for the cache to be repopulated (check platform-analytics-consumer.log for "RelActionTranType data loaded."), execute the Redis commands (using redis-cli) (HGET PA_RelActionTranType STG_IS_DOCUMENT_STATS:2:0), get the result "912:328"
6. Do some manipulations against the dossier and verify that the issue is resolved (action_type_id of 328 is saved to the corresponding row in access_transactions)

Workaround

Below commands fix the redis data temporarily. 
HSET PA_RelActionTranType STG_IS_DOCUMENT_STATS:2:0 "912:328"
HGET PA_RelActionTranType STG_IS_DOCUMENT_STATS:2:0
Third Party Software Installation WARNING
The third-party product(s) discussed in this technical note is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.