Facets™ Data Archival: Do You Have a Strategy in Place?

Facets™ Data Archival: Do You Have a Strategy in Place?
– July 20, 2017

Implementing archiving within Facets™ offers a number of benefits, both within the Facets application and for the underlying infrastructure. Limiting the size of the Facets database can improve system performance, make production more portable to non-production environments, and reduce online storage space. A Facets archival strategy should be on the roadmap for consideration and potential implementation for any health plan running Facets but not currently archiving, regardless of how long the system has been live.

Read on to for a deeper dive into the key considerations of data archival in Facets, including:

  • Why Data Archival Matters

  • Benefits of Archiving

  • Strategy and Approach

  • Pre-requisites and Key Business Decisions

  • Three-step Archival Process

Why Data Archival Matters

Since the initial release of the Facets™ system to health plans there has been an uncertainty and ambiguity regarding the need for, and use of, the built-in archival capabilities. The larger the Facets database gets the more likely it is to have performance related issues. To keep the system performance optimized, the IT department must constantly manage the underlying infrastructure to keep up with the technology improvements and to meet the software vendor’s upgrade pre-requisites. However, it is easy to be blindsided by data volume growth as the health plan matures and Facets gets more use. This article outlines the necessity of implementing an archival strategy for the Facets core system data, as well as insight into some of the more critical decisions that must be made prior to launching the archiving capability.

Given the transactional nature of the core system, the volume of data stored increases significantly over the course of time. Additionally, much of this data isn’t required to be kept permanently in the live Facets database and it provides no immediate value to a health plan’s business. This leads to a significant increase in the database size which adds to database management challenges and can cause overall system performance problems including missed batch windows and a reduction of end-user productivity. Archival of historical data not only reduces the burden on the core system to carry that volume, but also provides the ability to stage that data outside of the core system and provides a method to recover that data when required.

Industry standards suggest archival processes and procedures should be put in place after the first three to four years of usage. Once the procedure is established, health plans are able to identify historical data based on a specific date and can archive this data to remove it from the core system database, thereby reducing the size of the database.

Sub-optimized health plans tend to utilize a tactical, and somewhat reactionary approach by dealing with performance issues as they arise. For example, last year one health plan acquired another and migrated the acquired plan’s membership data to Facets. Since the acquisition, claims volume has doubled and batch performance issues have begun to surface. Previously performance was managed via the addition of computing power or faster storage solutions. Unfortunately, this is time consuming, expensive, and does not make the best use of the plan’s IT investments.

Keeping database size in check is clearly a best practice that should be utilized whenever possible. With Facets, specific benefits of archiving include:

  • Shorter maintenance windows – smaller database size eases the load on system resources during maintenance

  • Faster Facets database upgrades – the less data in the Facets database during an upgrade the more quickly the upgrade scripts will be able to run and the less likely they are to have issues

  • Quicker backup and recovery – less data allows for a shorter back-up window and restoring from a back-up takes less time

  • Reduced disk space requirements – reduces the requirement for what is typically expensive storage space

  • Shorter disaster recovery time – moving and restoring databases in case of a disaster is much more efficient if the databases are as small as possible


When planning an archival strategy, take the following items into account:

  • Plan an initial archive strategy –the initial archival process will take longer than subsequent ones and may need to be distributed across multiple batch windows

  • Identify business requirements – discuss with the business to ensure archiving meets organizational needs without impacting business operations

  • Test the initial archive strategy in a development environment (including any restoration processes that will be used)

  • Develop an implementation plan for moving the archival process to Production

  • Develop an ongoing archive strategy – how often will archive jobs run? How will restore requests be handled?

Prior to implementing Facets Archiving, the following areas will need to be addressed:

  • Creation of a separate archiving database which follows a specific naming convention as outlined in the Facets documentation

  • To archive audit-related data, the Audit Data Archive functions must be activated and the appropriate options selected during the installation/upgrade

  • Collection of business requirements and key decisions that must be made

Key Business Decisions
It is important to explore all the options before the archive project begins. Following is a sample of questions that should be answered before the project kicks off:

  • How much data is required (or desired) to keep immediately available? Is it possible to archive anything more than two-years-old or does the business require a longer period?

  • How will data be restored or accessed after it is archived?

  • What kind of storage will be used?

  • What non-Production environments will need a copy of the archive database?

  • How will archiving impact ancillary applications like replication to a data warehouse?

General 3-Step Process
In general, Facets data archiving is completed in a three-step process as outlined below. Not all transactional data within Facets can be archived or purged, as only certain data models within Facets allows for data archival and purging. To aid the archive processes, batch jobs are provided as part of the application server builds to facilitate the 3-step approach of the archival, purging, and recovery of historical data. Supporting the overall archive process is an archive table, otherwise called reference table, which resides in the main database designated for each data-model (module) that can be archived and a separate archive data store (database or schema). The archive table maintains the references to the data rows that are identified for archival by the selection process and the data rows that are already archived and purged from the main database.

Step 1: “Selection” of data to be archived from source database

Step 2: “Archival” of the data from source to target database and removal of the data from the source

Step 3: “Restoration” of the data from target back to the source database when needed

Facets Archive Process Flow

Types of Facets Archiving Supported

  • Claims – utilizes three Facets batch runbooks to select claims for archival, perform the archival process on the selected claims, and to restore previously archived claims to the OLTP Facets database

  • Billing – also uses three batch runbooks for selection, archival, and restoration of bills/billing entities based on user defined parameters

  • Capitation – archives capitation data based on how retroactive adjustments are configured in relation to user provided parameters

  • Customer Service – archives selected customer service tasks (CSTK)

  • Process Item (PITM) – utilizes a single Facets batch runbook to archive, or restore, PITM records based on status

  • Broker Statistics (BRKS) – also uses a single Facets batch runbook, with runtime parameters, to archive or restore BRKS data

  • Audit Tables – a single Facets runbook for archiving, purging, or restoring audit history rows

  • System Instance – archives or restores SYIN related data

There is also a selection of system tables that are considered “archive safe” and you can use the System Instance table to archive the data from them. There are also some tables that should also be purged on a regular basis as this data is unrelated to historical data but impacts current performance (for example data that is needed temporarily for processing but does not need to be stored permanently).

Phased Approach
It is highly recommended, especially if a plan has been live on Facets for more than three years without archiving, to limit the number of archival batches running in the same batch window. This will alleviate any negative effects on the batch server’s performance and keep the database logging process from overloading the DBMS and causing performance issues. Given the complex nature of the Facets data model, it is best practice to approach the archival process in a phased manner. Within each phase, if the data volume happens to be too large to complete within a given window, the historical time frames can be broken into manageable chunks to mitigate this issue. Suggested phases are:

Phase I: System tables, Audit
Phase II: Customer Service, Capitation, and Billing
Phase III: Claims

This approach gets archiving up and running with a limited impact on the system and end users.

Taking time to plan a thoughtful and deliberate approach to Facets data archival can yield many benefits, both within the Facets application and for the underlying infrastructure. Archival helps limit the size of the Facets database which can improve system performance, make production more portable to non-production environments, and reduce online storage space. Any health plan running Facets but not currently archiving – regardless of how long the system has been live – should be on the roadmap for consideration and potential implementation.