Data Warehouse Table Types

Your data warehouse is continually collecting Object, Relationship, Role Membership, and Geolocation 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, Relationship, or Role Membership.

Each object type and relationship in Core is associated with a series of tables in the warehouse. One Role Membership Table handles all Object Role allocations, and one Geolocation Table handles Object geolocation data. If multiple object types or relationships have the same name, each subsequent object type or relationship is assigned a numbered suffix. For example, Object Type A, Object Type A (2), Object Type A (3).

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: Linking Object Type Table 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.

Object type tables' data can include these columns (as well as any Field and Formula columns contained in the object type):

  • id: The numeric ID assigned to an object, 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).

Rich Text Fields store a preview of the first 500 characters added to the field.

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: Linking Object Type Table article.

Relationship tables can include these 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).

Role Membership Data

Role Membership data captures the User or User Group data that is selected in an Objects Role Field. Role membership tables can include these columns:

  • id: The numeric ID assigned to the link between the object and the user or user group. For internal use only.

    If the user is assigned to an object with a role, is then removed, then added back again, the value of this column will change.
  • objectId: The numeric ID of the object the user has been assigned to.

  • roleId: The numeric ID of the selected role, which is displayed in the role's URL in Core (e.g., https://[environment].resolver.com/#/admin/roles/66770/edit).

  • userGroupId: The numeric ID of either the user group or the user assigned to the role, depending on the Type field. For example, if the type is 1 the id will be from the Users edit page (e.g., https://[environment].resolver.com/#/admin/users/37979/edit). If the type is 2, the id will be from the User Groups edit page (e.g., https://[environment].resolver.com/#/admin/userGroups/37979/edit).

  • Type: The numeric value that represents whether the role is linked to a user or user group for the related object. 1 for user, 2 for user group.

  • 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).

  • metaId: For internal use only.

  • roleName: The name of the user or user group it has been assigned to.

  • userOrUserGroupName: The name of the user or user group directly linked to the object.

  • TypeLabel: 1 = User, 2 = User Group.

Geolocation Data

Geolocation Data is information that can be used to identify an electronic device's physical location. Geolocation Data tables can include these columns:

  • id: The numeric ID assigned to a Geolocation. The Geolocation's table id column is used to join the geolocationId column on any object type.
  • country: The Country of the Geolocation.
  • state: The State, if relevant, of the Geolocation.
  • city: The City of the Geolocation.
  • zipcode: The Zipcode, or equivalent, of the Geolocation.
  • street: The Street of the Geolocation.
  • houseNumber: The House Number of the Geolocation.
  • notes: Not currently used.
  • label: Full Geolocation address as presented in Core UI.
  • created: Date the Geolocation was created.
  • latitude: Latitude of the Geolocation.
  • longitude: Longitude of the Geolocation.