Revision history reports

AD
DM

Advanced reporting users can create reports to review the revision history for HCO and HCP records (SQL Query Editor).

These reports are particularly useful at the bulk data level.

Example use cases - bulk data

  • View the field level changes that occurred on records during a data import job.

  • Investigate all records that were recently updated by a specific data source.

Revision history is also useful for investigating changes to specific records;

Example use cases - individual records

  • Investigate changes in address rank.

  • Understand why a primary address changed.

Production Network instances contain all changes that have occurred to your data since the Network instance was created. Sandbox instances contain changes made to data after the 18R1 Sandbox release.

Reporting schema

Revision tables have been added to the reporting schema to help users investigate changes in their data. Use these tables to help build your SQL query.

In the schema diagram, an example custom sub-object called Publication is included.

Example

Use revision history reports to monitor the updates made to HCP records by a low-ranked data source during import jobs.

This can help you decide whether to continue using the data source if you find that low quality or inaccurate changes are being made to records.

SELECT
        job_id,
        source,
        subscription,
        record_version__v,
        entity_vid__v,
        verb,
        first_name__v_old,
        first_name__v,
        last_name__v_old,
        last_name__v,
        medical_degree_1__v_old,
        medical_degree_1__v,
        hcp_status__v_old,
        hcp_status__v,
        speaker__c_old,
        speaker__c
    FROM
        revision JOIN hcp_revision
            ON revision.revision_id = hcp_revision.revision_id
			
    WHERE
        job_id = 11354
        AND source = 'LowDataSource'

This is a report that will be run regularly, so Save the report. Each time the job is run, specify the job ID in the query.

The report results show the changes made by the specified job to HCP records. Use this information to evaluate the data source.

ParentHCO Revision table

The parenthco_revision table contains the parent_vid__v field (Parent Affiliation Vid). The value identifies all add and update actions to the parent_hco_vid__v field for each table entry. The parent_hco_vid__v field contains a value only for add actions.

Note: The parent_vid__v field is not a data model field; it exists in the parenthco_revision table only.