Hyperproof Self-service reporting data model

The Hyperproof business objects represented in the data warehouse include:

Each business object model contains columns for each system attribute, and any applicable custom fields, visible within Hyperproof. The following metadata fields are available for each record:

A system-generated unique identifier column is available for aggregating and joining records. The name of the unique identifier column is generated based on the object name, an underscore, and "ID". For example, the unique identifier for the control object is CONTROL_ID.

Relationship objects are represented by the combination of two business object models, with an underscore. Generally, the parent object is first, and the child object is second. For example, the association between controls and labels is named CONTROL_LABEL in the data warehouse.

Relationship objects are composed of the unique identifier fields of the related objects. For example, CONTROL_LABEL has both CONTROL_ID and LABEL_ID fields that can be used in joins to the Control and Label objects in the data warehouse, respectively. The following metadata fields are available for each record:

A system-generated unique identifier column is available for aggregating records. The name of the unique identifier column is generated based on the object name, an underscore, and "ID". For example, the ID for the control to label relationship object is CONTROL_LABEL_ID.

Some Hyperproof relationships contain a third dimension, such as PROGRAM_REQUIREMENT_CONTROL. These objects follow the same structure as standard relationships, with the addition of the unique ID field for the third object.

Some Hyperproof objects contain linkages within the record and do not contain an additional relationship table. For these tables, there are columns for TARGET_OBJECT_TYPE, indicating which business object the table is associated with, and TARGET_OBJECT_ID, which is the unique identifier of the corresponding object. For example, you can query for tasks that are on requests by joining to the Request object using the following query:

select
    t.NAME as TASK,
    t.ASSIGNEE as TASK_ASSIGNEE,
    r.ID as REQUEST_ID,
    r.ASSIGNEE as REQUEST_ASSIGNEE
from TASK t
inner join REQUEST r
    on t.TARGET_OBJECT_TYPE = 'Request'
    and t.TARGET_OBJECT_ID = r.REQUEST_ID

Hyperproof captures snapshots of some data displayed in the user interface but not stored. These models can be used for time series reporting of business object attributes captured in the snapshot. Snapshot objects include:

The time series objects in the data model begin with the TS_ prefix, such as TS_CONTROL or TS_RISK. They allow you to create reports that compare values month-over-month or sets of months used to compare larger chunks of time, such as quarter-over-quarter. Snapshots occur on the first of each month.

Each snapshot contains a SNAPSHOT_DATE field indicating the date the snapshot was captured and the business object attributes captured in the snapshot.

The unique ID of the corresponding record is provided for joining. For example, when using the TS_RISK object, you can use the RISK_ID field to join to the Risk object in the data warehouse. Joining allows you to access additional risk attributes that were not captured in the snapshot.

At this time, only system fields are included in the snapshot data. Custom fields are not included.

A set of relationship object snapshots is included. See Relationship objects.

SPRS scoring

If you have added a program that uses SPRS scoring, such as NIST 800-171 or CMMC 2.0 Level 2 and 3, the associated requirement statuses are stored in the database as the Hyperproof statuses not the SPRS statuses. SPRS statuses are mapped to Hyperproof statuses in the user interface to facilitate scoring and displaying statistics on the program dashboard. Status mappings are as follows:

For more information, see SPRS statuses.

Custom fields

If you add a custom field name that is the same as a SQL reserved keyword, Hyperproof prepends an underscore _ to the name when bringing that data into the data warehouse. For a list of reserved keywords, see Reserved & limited keywords in the Snowflake documentation.