Data Archives

Some data is historical in nature, and after a certain period of time is no longer vital to operations in the application. To keep such tables lean and performing well, the system will routinely generate archive copies of these tables and move data to them.

Procedure Overview

Archiving is an automatic process that can be configured under "Admin > Tenant management" in the 'Global Tenant' record.  While all Organizational needs for Archiving are different, PCR-360 recommends setting Calls and Call Errors Archiving to 3 months (90 days).

Setting Archiving Values:

1. Navigate to: "Admin > Tenant management".
2. Open the Global Tenant record.
3. On the Settings tab, observe the "Aging/Archiving Days" section.
4. Set the desired Archiving thresholds (in days).
5. Save the form.

Archive

1. A Parent table starts the Archive process. Based on that table's criteria, an archive table suffix is generated.
2. Query statements are generated to create the archive table if it doesn't already exist, and copy data to the archive.
3. Query statements are generated to update association tables telling the linked records the live data is moving to an Archive.
4. If there are child tables (dependents), the archive process extends to them as well – regardless if there is any Child data for the Parent. This descent continues for any dependent tables that have Dependents, and so on.
5. Query statements are generated to delete the original data after it has been copied to the archive.
6. Relationships from the Archive table group are restored to other tables they reference (LISTS, for instance)

Unarchive

Note: currently the application does not unarchive any data. 

The feature has been built, but no use cases have been discovered where it is necessary.

1. A Parent table starts the Unarchive process. Based on its criteria, an archive table is checked for existence and data presence.
2. Generate query statements to copy the intended data from the archive back to the live table.
3. Generate query statements to update association tables telling the linked records the archived data has moved.
4. If the Archive table has Dependents, the Unarchive process extends to them as well (and so on if any of them have Dependents).
5. Generate query statements to drop the Archive table if it has no data after the Unarchive is complete.

Note: Some processes only delete the archived data because it is being regenerated in the live table, and eventually re-archived. The deletion/table-drop is to avoid data duplication from the old version of the data to the new.

What Gets Archived?

There are five initial implementations for Archives: 'Call Details', 'Bill Details', 'Audits', 'Imports', and 'Notifications Log'.

Call Details

As call records age, they are less and less useful, especially when some data has errors and cannot be recovered for Billing. Based on a Tenant setting ("CALL AGING"), the Call records will be archived on a routine basis by the system. No user interaction is needed for this. If the Tenant "CALL AGING" value is set to 0 or blank, then the default value of 90 days will be used.

Criteria

• Call records are archived according to these rules:
  1. Call's Created Date is older than Tenants' "CALL AGING" value. The Created Date is the date the Call was created or imported into the system. This value is not visible on the Call Details grid, so it can make it difficult to verify that the archive process is working correctly. You might see calls that have a Call Date/Time that are somewhat older than the "CALL AGING" value. This is especially true for vendor file calls, which usually get imported several weeks after the calls were actually made.
  2. Call has been Billed or is in an Error Status. If the Status is Raw (waiting to be Rated) or Rated (waiting to be Billed), then the call cannot be Archived.
• Calls are archived according to the 2-digit year and 2-digit month of the Call Date/Time:
  
  1. Table CALL_DETAILS_1505 would contain records for Call Date/Time in May 2015.
  2. If the Call Date/Time is invalid, then the Call will get Archived to the CALL_DETAILS_9999 table.

NOTE: We realize this would introduce possible issues at the turn of the next century, but we have to account for the table, index, and foreign key names fitting within specific length restrictions. We hope to have our grandchildren address this issue before the year 2099.

• Archived data can be viewed using the Call Details Grid. This is available from the Admin section, or from individual Services and AuthCodes. The interface changes for viewing archived data are discussed below.

Bill Details

Bills are essentially an archive at the time they're generated. All the data about a Bill is within the context of the Bill Date.

Criteria

• The parent BILLS record isn't archived (its archive table would only ever have 1 record), but it is a Reference to Bill Details, so it needs to know which table its Details are in.

• Bills are archived nearly at the time they're generated. An automated schedule runs to move all Bill Detail and related records into Archives based on the Bill Date:

  Table BILL_DETAILS_1905 contains Bill Details for the Bill run in May 2015.

• Test Bills add a "T" to the table suffix of their archives:

  Table BILL_DETAILS_1505T contains TEST Bill Details for the Bill run in May 2015.

• At this time, Bill Details are not unarchived, exactly. When you choose to regenerate a Bill, the system checks to see if the Details have been archived yet. If not, the original logic of deleting Detail records is used to clear out the Bill data & regenerate it. If the Details Archive table exists, it and its Dependents are simply dropped, then the Bill data is regenerated.

Viewing Archived Data

Archived data cannot be edited, but it can be reviewed from grids. Where live data can be opened in a form for editing, the archived record can also be opened in a form, but it will be read-only.

You'll find a widget in the Grid's Toolbar that lets you select tables to show in the grid. The selected tables will be stacked together, and Search, Sort, and Filters will then act on that table union. You can also use this mechanism to create "empty" perspectives, by deselecting all the tables from the list. The grid's data refresh when you click away from the list and the widget closes.

To see which archive records are coming from, click the button and you'll see the first column on the right is a hidden "Source Table" column.

You can treat the column just like any other: sort by it, filter (keep in mind, the Table Selector is the recommended way to filter by Source Table), move it to a different position, etc. You can omit it from Perspectives by hiding it, but this special column will always be available from the Preferences dialog.

Bill Details

In the case of viewing Bills, since Bills are archived shortly after they're generated when you choose the Bill Data you want to view, its data will nearly always come from the corresponding archive tables.

Go to Admin > Billing > Bill Generation, which will show you the Bills grid. When you select a bill and choose "View Bill" from the Grid Toolbar, the Bill view will open in a new tab as it always has. In the background, the system determines the Table locations of the bill data you selected and displays it as usual.