Data Warehouse Tables

Your data warehouse is continually collecting object and relationship data as your information in Core is updated. This data is compiled in a series of tables, which mimics an Excel spreadsheet with multiple columns containing information about an object or relationship.

Each object type and relationship in Core is associated with a series of tables in the warehouse. If multiple object types or relationships have the same name, each subsequent object type or relationship is assigned a numbered suffix (e.g., Object Type A, Object Type A (2), Object Type A (3), etc.).

Object Data

Object data includes any changes made to an object's fields, properties, formulas, workflow states, and roles. Each object type has the following tables:

  • [Object Type Name]: Contains all current and historical data for the object (e.g., Risk).
  • [Object Type] Current: Contains all current data for the object (e.g., Risk Current).
  • [Object Type] Historical: Contains historical data for the object (e.g., Risk Historical).

For sample screenshots, see the Examples section at the end of this article.

The warehouse currently generates Master and Meta tables and data for each object type. These tables and data are for back-end use only and are not recommended for use with a BI tool.

Data in object type tables can include the following columns:

  • id: The numeric ID assigned to an object, which is displayed in the object's URL in Core (e.g., https://[environment].resolver.com/#/form/default/object/1298/edit).
  • externalRefId: The object's external reference ID in Core.
  • workflowState: A numeric ID assigned to a workflow state in Core.
  • name: The value of the object's Name property in Core.
  • description: The value of the object's Description property in Core, if any.
  • uniqueId: The ID assigned to the object in Core without the object type's monogram. For example, a Corrective Action object in Core is assigned a unique ID of CA-1 but is recorded as 1 in the uniqueId column.
  • metaId: To be hidden in a future release.
  • workflowState Label: The name assigned to a workflow state in Core.
  • Valid From/Valid Until: The date a version became current (Valid From) and the date it expired or was changed (Valid To). If the version is still valid, the Valid To column will display a date of January 1, 2099 (infinity).

Relationship Data

Relationship data captures which object is selected in a relationship and the dates that object was added or deleted. Each relationship has the following tables:

  • Rel [Source Object Type Name] [Relationship Name] Current: Contains all current data for the relationship (e.g., Rel Risk Controls Current).
  • Rel [Source Object Type Name] [Relationship Name] Master: Contains current and historical data for the relationship (e.g., Rel Risk Controls Master).

For sample screenshots, see the Examples section at the end of this article.

Relationship tables can include the following columns:

  • id: The numeric ID assigned to the link between the relationship objects, which is accessible via Swagger.
  • sourceId: The numeric ID of the originating relationship object (i.e., the object on which the relationship object is selected), which is displayed in the object's URL in Core (e.g., https://[environment].resolver.com/#/form/default/object/1298/edit).
  • destId: The numeric ID of the selected relationship object, which is displayed in the object's URL in Core (e.g., https://[environment].resolver.com/#/form/default/object/151/edit).
  • sourceTypeId: The numeric ID of the object type where the relationship is saved, which is displayed in the URL when editing the object type's settings in Core (e.g., https://[environment].resolver.com/#/admin/objectTypes/3464/edit).
  • destTypeId: The numeric ID of the destination relationship object type, which is displayed in the URL when editing the object type's settings in Core (e.g., https://[environment].resolver.com/#/admin/objectTypes/3456/edit). 
  • Valid From/Valid Until: The date a version became current (Valid From) and the date it expired or was changed (Valid To). If the version is still valid, the Valid To column will display a date of January 1, 2099 (infinity).

Examples

The screenshots in this section were generated in TablePlus, which is not a BI tool. The data displayed to end-users will vary depending on the settings applies and the BI used.

Below is a screenshot of the table for the Risk object type. The panel to the left displays the object types and relationships in the current org. 

A table for the Risk object type.

The Rel Risk Controls Current screenshot below displays the data for the Controls relationship saved on the Risk object type. The Risk object (sourceId 1) was paired with the Control object (destId 2) on August 16, 2019 (Valid From). Because this version is still current, the Valid To column shows a date of January 1, 2099 (infinity).

The Current table for the Controls relationship on Risk.