Skip to main content

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

  1. Create a materialized IH Summary in the Pega application.
  2. 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

Suggest Edit

Published February 19, 2020 - Updated December 2, 2021

Did you find this content helpful? Yes No

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Ready to crush complexity?

Experience the benefits of Pega Community when you log in.

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us