Support Article
When using materialized IH Summaries - latest event counts twice
SA-96251
Summary
When using materialized Interaction History (IH) Summaries in Decisioning, the current event from the Interaction History counts twice.
Error Messages
Not Applicable
Steps to Reproduce
- Create a materialized IH Summary in the Pega application.
- Create a new IH record. Monitor the output. The current event from the IH counts twice
Root Cause
The current event counts twice because Pega additionally checks for any new event that is not aggregated after the pre-aggregated IH data is loaded from Cassandra. This is performed by querying the Oracle database for events after the current event in Cassandra + one millisecond. Since the DATE type in Oracle only tracks seconds, the last event is included twice.
To address this, amend the data types for the below columns in the PR_DATA_IH_FACT table:
- PXOUTCOMETIME
- PXDECISIONTIME
Data type must be changed from DATE to TIMESTAMP to track milliseconds.
Resolution
Perform the following local-change:
Refer to Datetime Data Types and Time Zone Support, NLSPG004 (How to modify the timestamp property to the desired number of fractional seconds as well).
DateTime properties in Pega 7 platform can be mapped to both the DATE and TIMESTAMP columns in an Oracle database. It is recommended to use TIMESTAMP columns when possible, due to the increased Precision provided by millisecond data which the DATE columns do not provide.
When upgrading from a Pega Platform 7 version that is earlier, any existing DateTime properties continue to map to the DATE column.
Change the properties manually to map to the TIMESTAMP columns instead and maintain them as TIMESTAMP columns for any upgrades to future releases of the Pega application.
Execute the ALTER script on the database manually to convert an existing DATE column to TIMESTAMP. Data is unaffected by the conversion. Connect to the Pega 7 database.
Execute the below ALTER script and update the Schema_Name, Table_Name and Column_Name values.
ALTER TABLE <Schema_Name>.<Table_Name> MODIFY <Column_Name> TIMESTAMP
For example,
pc_History_Work is an internal table with the pxTimeCreated column of DATE type.
To convert the pxTimeCreated column to use TIMESTAMP instead for the column, execute the below script:
ALTER TABLE <Schema_Name>.pc_History_Work MODIFY pxTimeCreated TIMESTAMP
Tags:
Published February 19, 2020 - Updated December 2, 2021
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.