Pega Archive DB
Planning to move all the resolved cases which are older than 1 year to different database instance called as ArchiveDB after that ,still case workers/CSR should able to review the archived cases in the same manager portal...
Can we have a parallel classes for Work-, History-, Data-Work-Attachments/Notes & Link-Attachment , point these classes to archive DB , just give a flag on search view to look up for the archive data if case is not found in transactional database? Do you see any challenges with this approach except maintaining/tracking the database changes (exposing columns etc) in archiveDB?
System will not write the data to any table in ArchiveDB ,all cases in ArchiveDB are resolved and not reopened again ,we will move the data to ArchiveDB on incremental basis like one time job in a year .
I had gone through the similar questions/articles but didn't find the correct the approach for this .
Updated: 15 Jul 2024 8:58 EDT
Pegasystems Inc.
GB
Your approach of having parallel classes for Work-, History-, Data-Work-Attachments/Notes & Link-Attachment, and pointing these classes to the archive DB is feasible. However, there are a few challenges and considerations:
- Indexing: Pega uses Elastic Search for indexing data. If data is deleted from the main table because it has been moved to the archive, Pega platform doesn't realize this and thus Elastic Search would not know. Therefore, 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.
- Data Consistency: You need to ensure data consistency between the transactional database and the archive database. This includes maintaining/tracking the database changes (exposing columns etc) in archiveDB.
- Search Functionality: You need to modify the search functionality to look up for the archive data if the case is not found in the transactional database.
- Performance: Moving large amounts of data to the archive database could impact performance. You need to ensure that the archival process is optimally efficient and meets the needs of your organization's database size.
- Regulatory Compliance: Depending on the industry and region, there may be specific regulations regarding data retention and archiving that you need to comply with.
Remember to test your approach thoroughly before implementing it in a production environment.
Your approach of having parallel classes for Work-, History-, Data-Work-Attachments/Notes & Link-Attachment, and pointing these classes to the archive DB is feasible. However, there are a few challenges and considerations:
- Indexing: Pega uses Elastic Search for indexing data. If data is deleted from the main table because it has been moved to the archive, Pega platform doesn't realize this and thus Elastic Search would not know. Therefore, 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.
- Data Consistency: You need to ensure data consistency between the transactional database and the archive database. This includes maintaining/tracking the database changes (exposing columns etc) in archiveDB.
- Search Functionality: You need to modify the search functionality to look up for the archive data if the case is not found in the transactional database.
- Performance: Moving large amounts of data to the archive database could impact performance. You need to ensure that the archival process is optimally efficient and meets the needs of your organization's database size.
- Regulatory Compliance: Depending on the industry and region, there may be specific regulations regarding data retention and archiving that you need to comply with.
Remember to test your approach thoroughly before implementing it in a production environment.
⚠ This is a GenAI-powered tool. All generated answers require validation against the provided references.
Need to retrieve the archived cases from the DB using Search functionality
Understanding and complying with Case Management best practices > Archiving Case data
Purge/Archive Work objects into another database table
Planning your Case archiving process
Please see the below support document for the latest OOTB behaviour:
@Chetan.Chaudhari would you be able to provide some input here?
Pegasystems Inc.
CA
I can think of the following impacts with Archived DB
- Archived DB size grows (if you're using file storage, the storage cost is comparatively cheaper than Archived DB)
- Build and validate the tool to manage the data (to move the data to Archived DB, purge the data from transactional database)
- Yearly once Archival and Purge seems to be a long time (OOTB archives and purges as when the case meets the criteria).
- It impacts performance because you have to search against Archived and then search Transaction databases.
I recommend leveraging the out-of-the-box Purge and Archive functionality offered by Pega if you're in Pega Cloud. Pega Cloud clients have the option to archive resolved case data to Pega Cloud File storage and then remove the data from the Pega database. Archiving and purging this data can enhance system performance and other functionalities. Additionally, you can establish a data retention policy for data stored in Pega Cloud File storage to permanently expunge the archived data. Periodically purging entries from your database offers the following advantages:
- Enhances application performance
- Improves search and reporting capabilities
- Boosts performance for database queries
- Shortens database maintenance cycles
- Reduces primary database costs
I can think of the following impacts with Archived DB
- Archived DB size grows (if you're using file storage, the storage cost is comparatively cheaper than Archived DB)
- Build and validate the tool to manage the data (to move the data to Archived DB, purge the data from transactional database)
- Yearly once Archival and Purge seems to be a long time (OOTB archives and purges as when the case meets the criteria).
- It impacts performance because you have to search against Archived and then search Transaction databases.
I recommend leveraging the out-of-the-box Purge and Archive functionality offered by Pega if you're in Pega Cloud. Pega Cloud clients have the option to archive resolved case data to Pega Cloud File storage and then remove the data from the Pega database. Archiving and purging this data can enhance system performance and other functionalities. Additionally, you can establish a data retention policy for data stored in Pega Cloud File storage to permanently expunge the archived data. Periodically purging entries from your database offers the following advantages:
- Enhances application performance
- Improves search and reporting capabilities
- Boosts performance for database queries
- Shortens database maintenance cycles
- Reduces primary database costs
- Helps ensure compliance with government regulations such as the General Data Protection Regulation (GDPR) and the Health Insurance Portability and Accountability Act (HIPAA).
Updated: 18 Jul 2024 7:54 EDT
Pegasystems Inc.
US
@BRAHMESH@ Has the client considered using the OOTB Pega archival approach? It uses a job scheduler to archive eligible cases to a secondary file repository where the archived cases are also indexed and can be searched from the case manager portal.
One reason for doing the archive is to save the database storage, and we may consider consulting clients to use the OOTB approach to stay within best practices. OOTB solution is maintained by the Pega engineering whereas any custom solution is responsible by client. The OOTB solution also archives associated artifacts such as attachments, history, pulse, etc.
I recently documented one article about our implementations using the OOTB Pega archiving solution that may give you some more ideas. https://support.pega.com/discussion/archive-case-and-its-associated-artifacts
Skandiabanken
SE
@Will Cho @RameshSangili we are not using any cloud instance for storing the data and system is running on-premises and we don't have any file repository system for archiving the cases .
we are not using OOTB search function in case worker portal as our custom search function is depending on many attributes ( LoanNumber, SSN, Case Status ,etc ) to return the results.
Do you see any issue if we keep two different database ( ArchiveDB and Transactional DB ) .. We will migrate the data from transactional DB to ArchiveDB on regular intervals ( once in a year) but still case officer should able to search for archived cases in case worker portal ?
Can Pega instace be configured with two databases ?,one database(ArchiveDB) just for reading the instance on-demand ?.
Is there anyway to solve the issue?
Pegasystems Inc.
US
@BRAHMESH@ Technically, it may work for a few number of classes, but i see a bigger challenge of maintaining and scaling the design with more classes in future. I can't think of alternatives at this point. Perhaps you may consider using BIX to extract the Resolved cases (that are very old) and build a report around them, and schedule a job to purge them from the active DB. Recently resolved cases may stay in the active database for search. Client will need to determine the age of resolved cases for extraction.
Pegasystems Inc.
CA
This would lead to maintenance issues when your application is growing. For example, if you add a property and optimize the column, you have to manage the same change in another database before you push the cases to the Archived database.
Another example: If you're creating a new data table or case work table, you must ensure the schema is up to date with the Archived database.
This would be a big maintenance headache, and troubleshooting would be a nightmare if both databases were out of sync.