Archive Data and Audit Trail
DELETE_METHOD (Audit Trail)
This topic provides a brief overview of the elementary operations of archiving data and make Audit Trail possible.
Important: Archive data is not applicable for new data engine.
This topic contains the following sections:
Archiving Data
Archived data is stored in separate tables and is never used by Bizview again. Instead of deleting old data, you can now archive it if you eventually want to restore the data in the future.
The archive feature can also be used to back up specific data. When archiving, you will have the choice to delete the archived data from dat.data_cells. If you don't delete the data, you will have a backup copy.
Audit Trail
The difference between Archive Data and an Audit Trail is that records in AT-tables are used in Bizview, while the archived data is never used. In AT-tables, many versions of the same data occurs. If a user saves a form 10 times, every cell occurs 10 times in the AT-tables.
Table Structure Changes
Three new tables must be created that contain old data. The structure of the tables are similar to dat.data_cells, dat.data_dimensions, and dat.data_attributes. The tables will have the suffix “_at” (abbreviation of audit trail). One extra column archive_date will be created in the dat.data_cells_at table that contains the time when the data were archived.
In dat.data_cells, a new column will be added, row_status that contains information about the saved data (new, deleted, to_be_archived, etc.).
Saving Data in Bizview
When data is updated in Bizview, the parameter DELETE_METHOD is checked. This parameter is set in the PARAM sheet.
If DELETE_METHOD = “0” (instant delete) the old data will be deleted instantly. This is the default behavior and how Bizview has always performed the delete.
If DELETE_METHOD=”1” (Background delete) the old data is deleted after the update is completed and the user has control over the form. The delete is executed in the background on the server.
If DELETE_METHOD = “2” (Logical delete) the old data is not deleted instantly. The new column, row_status, is set to “2” and will remain in dat.data_cells.
If DELETE_METHOD = “3” (Audit Trail) the old data is not deleted instantly. The new column, row_status, is set to “3” and will remain in dat.data_cells.
Moving/Deleting Old Data
For DELETE_METHOD 1 – 3, as described above, the data remains in the data_cells. For DELETE_METHOD = 1 (Background Delete), the data will automatically be removed by Bizview. If a form has DELETE_METHOD = 2 or 3, a scheduled job must be executed that moves the data to the Audit Trail tables (3) or delete the data permanently (2).
The job should execute the procedure dat.p_move_data_cells, which takes one parameter: @p_client_id nvarchar(50).
The Historical Database
The user must create the historical database if they want to restore a previous version of the template data. The historical database is unique for each template and contains ordered selection values, so it has to be created when it's needed. The creation of the historical database will take awhile to perform, so the historical database must only be created once.
The historical database is stored in a permanent table in the tmp schema, named “at_<uniqueidentifiter>. The table will be removed when creating a new table or when the book is closed. A procedure must be created that deletes old tables that have not been deleted successfully.
Creation of the Historical Database
To create the historical database, the procedure dat.p_data_cells_populate should be executed with a twist. The procedure, in the standard execution, reads dat.data_cells etc where row_status = 0. When creating the historical database the procedure should read dat.data_cells with row_status = 1 plus data from the AT-tables. The procedure is called dat.p_data_cells_populate_at*.
The result will contain the data_cells that belong to the template, in addition to other clutter.
To get rid of the clutter, a similar procedure as the dat.p_form_data_delete is executed that reads the AT- tables. This procedure is called dat.p_get_audit_trail_records*.
The result of this contains the data_cells that belongs to the template (for every version).
The historical database is now created by select * from the data thatdat.p_data_cells_populatehas created where data_cell_id in (selectdata_cell_idfrom the tabledat.p_form_data_deletehas created).
Get Previous Data from the Historical Database
When the user wants to get previous values for the actual template, a list should be shown by selecting distinct batch_id, upd_user_id, updated from the historical database where template_id = actual template_id. This means that the previous values only display the data that the actual template has created. Another template that updates a data_cell with the same criteria can exist, but this will not be shown when getting previous data.
When a batch_id has been chosen, Bizview can create a #plandata from the historical database, and in some way, exclude the automatic generation of #plandata and refresh the form. At the moment, this feature is not implemented.
Audit Trail
When a user wants to see all the changes that have been made to a specific cell, the template must be evaluated. The PARAM, the SEARCHVAL on the current row, and all OBJECTS, VALT, CURR, TIMEUNIT, TIMEVAL and VERSION in the current column is read.
From that search criteria, a select is done to the historical database. The result will be a set of records for all changes to the current cell. This select includes updates from every template that involves the current cell.
To create the historical database, right-click on a cell. The criteria for showing the Audit Trail menu is that the template is a FORM, the DELETE_METHOD = 3, and the cell is a CVAL Column.
The menu can display three different texts:
Audit Trail (Not Generated): The historical database has not been created for the actual sheet (and section). Upon selecting this option, the historical database will be created, but this may take awhile. Once the database is created, the Audit Trail displays (as long as the user hasn't switched sheets while the creation was executed).
Audit Trail: The historical database already exists. Upon selecting this option, an Audit Trail displays quickly.
Audit Trail (Running...): The historical database is executing and the user cannot create another until this is finished.
Below is an example of an Audit Trail:
Audit Trails display the values that were updated in chronological order. All data attributes also display at the end of each row. Note that Template ID is also included, since the data can has been entered in another template.