x This Article is for Internal Use Only x
The Temporal Data Warehouse (Data Warehouse or DWH for short) is a separate database in Resolver that stores versions of objects (temporal instances) each time an object is changed. This historical data allows customers to connect the warehouse to their BI tools to do traditional and historical reporting (e.g., risk over time).
All Orgs created after 2.7 automatically come with their own data warehouses, but they're not accessible to users until the warehouse details (domain URL, database name, username, and password) are enabled in Swagger at an additional cost. Because the warehouse is active from the date of the org's creation, it's possible for users to create reports using data collected before the DWH details were enabled. For example, if an org was created on July 30, 2019, but the warehouse wasn't enabled in Swagger until August 30, 2019, the customer could still run reports in their BI tool from July 30, 2019, onward.
All object changes made in Core are pushed to the DWH with a timestamp which allows users to build reports based on historical changes. If changes are made multiple times within a few seconds, those changes are aggregated, and only the most recent change is logged in the DWH. Note that the aggregation logic is subject to change in future releases.
In the example below, this Tableau report uses temporal data to show how Residual Impact scores have changed over time for each risk. The Risk Oversight entry shows that it was originally set to Significant (4) at 3:28 pm on May 21, 2019, but was later changed to Low (1) at 3:34 pm on the same date. For more detailed information on the data collected in the warehouse, see the Data Warehouse Tables article.
A sample report built in Tableau.
 |
The data displayed in the reports will vary depending on the settings applied and the BI tool used to generate them. |
Important Notes & Limitations
- The data warehouse is active on all new orgs created after 2.7 but is not accessible to end-users until it's enabled in Swagger.
- Up to 200 fields, formula values, and roles per object type are stored in the warehouse. Data is updated every 2 to 5 minutes, depending on the data load.
- This feature does not:
- Store attachments (image or file) or text formatting (Markdown or rich text).
- Transfer role permissions. This means that any user with access to the BI tool will be able to view object data their role permissions would otherwise restrict.
- Automatically transfer field, formula, or object type name changes into the warehouse. A manual update in the tool is required to reflect these changes, but note that updates of this nature may negatively affect BI reports.
- Following initial activation, it may take 30 minutes or more before the DWH is fully functional. Additionally, significant changes to an org (e.g., a data import) can take 30 minutes or more to transfer successfully.
- Recently imported orgs take a minimum of 30 minutes before transferring data into the DWH.
- Data warehouse passwords are not stored in Core. If you need to log into the BI tool, but you've misplaced the original password, another password must be generated.
- If the user account that generated the password entered into the BI tool is deleted, disabled, or loses its admin privileges, the current warehouse session will be terminated. To once again gain access, a new password must be generated from another admin account and entered into the tool.
- To view relationship data, users must have advanced knowledge of SQL and running join queries. Below is an example of running a query to get the risks and controls in a relationship:
SELECT * FROM "Risk" r JOIN "Risk to Control Master" rtc ON r.id = rtc.sourceId AND r.during @> upper(rtc.during)