Question
IKOR
AU
Last activity: 16 Jan 2017 17:04 EST
Need to retrieve the archived cases from the DB using Search functionality
Hi,
I am looking for some guidance regarding search of archived cases from the archive DB . Once the archiving exercise is completed, then the cases will not be no longer available in the Transactional DB. But, if user wants to open the case which is in archive db, then what changes are required in the pega application which can retrieve the case information from archive DB ?
Any thoughts !
-Naren
***Updated by moderator: Lochan to update Categories***
Accepted Solution
Pegasystems Inc.
IN
Hi Naren,
I hope you have viewed my second comment for this post as well.
If you use a stored procedure (or even direct queries to the database) to delete the entries from the main table because the data has been moved to the archive, Pega platform doesn't realise this and thus Elastic Search would not know (so the index will contain the old data unless a full re-index is triggered from the search landing page for that class or set of classes).
As of now, that is the only option because we cannot track changes to tables which are not going through our Obj-Save / Obj-Delete methods for the index to be updated.
With Elastic Search in 7.1.7 onward, the queue table and agent activity have changed calling different APIs now, so in that sense any custom changes that you do will need to be modified again upon upgrade if you are in lower version.
Indexing external content is still an open area for the product along with how to search and index archived content.
You could potentially write triggers on these table which upon delete could populate the pr_sys_queue_ftsindexer table with the pzInsKeys of the records deleted. But this will cause duplicate entries during normal deletes (obj-delete).
Pegasystems Inc.
IN
Pattern to consider - create a standalone archive system. Migrate cases from active to archive. Create archive-user access groups and roles for searching and displaying old cases.
Use case --
* cases can not be re-opened beyond a certain date
* new cases or customer interactions have no dependency on old / archived cases
* there is need to search and display old cases for compliance or legal
Archive search and case display can be performed three ways
1- swivel-chair
2- web services and/or cross system database queries
3- federation -- archive search and archive case display embedded in a tab inside the active system
Regards,
Sudhish OP
Pegasystems Inc.
IN
Further to above comment
At one customer site they have setup an archive solution with a way to search and restore a case.
They have another database schema (can be also in another database server) named archive with the same tables structure for live data. They have created a stored procedure to daily move (insert and then delete) cases with all related rows (links, attachments, history, party indexes, ...) from prpc database to archive database.
In the sysadmin database table rule, they define the archive database as the alternate database for report. So, for the search archive, they are able to search using report on the archive database using the alternate link. The report search just gives a limited set of columns. If the user wants to see the details, he need to restore it. So when he click on the review button, it run another stored procedure which move back the individual case from archive to prpc database. Once done, they are able to review the case from the prpc database.
The restored case will automatically be archived again through the daily archiving stored procedure which is based on the resolution timestamp.
They have also setup a permanent delete stored procedure to definitively remove very old cases from the archive database.
Regards,
Sudhish OP
IKOR
AU
Thanks buddy for the reply. With the information shared, i have a starting point. I will do thorough analysis and will come back, if there are any queries.
-Naren
Updated: 5 Jan 2017 1:27 EST
IKOR
AU
Hi Sudhish,
I have another query regarding archive search. How to deal with Elastic search items ?. In the case manager portal, if user searches for the cases which are in archive DB, then how can we retrieve the information and showcase to the user ?
Any Suggestions !
-Naren
Accepted Solution
Pegasystems Inc.
IN
Hi Naren,
I hope you have viewed my second comment for this post as well.
If you use a stored procedure (or even direct queries to the database) to delete the entries from the main table because the data has been moved to the archive, Pega platform doesn't realise this and thus Elastic Search would not know (so the index will contain the old data unless a full re-index is triggered from the search landing page for that class or set of classes).
As of now, that is the only option because we cannot track changes to tables which are not going through our Obj-Save / Obj-Delete methods for the index to be updated.
With Elastic Search in 7.1.7 onward, the queue table and agent activity have changed calling different APIs now, so in that sense any custom changes that you do will need to be modified again upon upgrade if you are in lower version.
Indexing external content is still an open area for the product along with how to search and index archived content.
You could potentially write triggers on these table which upon delete could populate the pr_sys_queue_ftsindexer table with the pzInsKeys of the records deleted. But this will cause duplicate entries during normal deletes (obj-delete).