Real-time Data Warehouse (RTDW) - Overview
The Real-time Data Warehouse (RTDW) is a separate read-only database designed for reporting purposes, also known as the reporting database. The Real-time Data Warehouse is refreshed approximately every 15 minutes or sooner for production environments. Refresh times for Pilot and Stage portals may take longer.
The Real-time Data Warehouse cannot be accessed directly. The only way to access the Real-time Data Warehouse is through one of the reporting solutions that are built on top of it:
|Reporting API||Data Exporter|
|Overview||Read-only access to data in the Real-time Data Warehouse via RESTful APIs||Provides flat files to consume data from the Real-time Data Warehouse|
|Data Source||Real-time Data Warehouse||Real-time Data Warehouse|
|Delivery||APIs (HTTPs)||Cornerstone FTP|
|Output||JSON||Compressed (.ZIP) files with the Data (.CSV/JSON) and Metadata (JSON)|
|Data Refresh/Scheduling||On demand||Daily|
|Technique||Query Cornerstone data on demand with preferred tools/development languages||File based data exports|
|Considerations||Customer needs to have skilled, technical resources available to query Cornerstone data via APIs||Frequency is once per day - Not all data from the Real-time Data Warehouse may be available|
When custom fields are created in the application, they are automatically added to the Real-time Data Warehouse to reflect the customer's schema. This applies to almost all custom fields, though exceptions may apply (i.e., Vendor custom fields). The general rule for locating custom fields is by looking for “_cf” appended to a reporting view. Please note that custom fields are categorized by type and will not be found in a single view.
Whenever a custom field is created in the portal, it is assigned a database-generated encoded ID. Please note, this encoded ID may not be the same between environments. The ID will never change within its respective environment except for copy downs.
For drop-down, radio button, multiple checkbox, and branched hierarchy custom fields, the standard reporting views may provide you with an ID in your return. To map the ID with the description of the record, you must map the ID to one of the local lookup views (e.g., vw_rpt_custom_field_value_local). Please note that these specific views are localized, meaning you may see multiple values for a given ID. This allows Cornerstone to report on certain culture IDs or languages.
When configuring the same custom fields separately for each portal, the technical field names will most likely be different in each portal. This can make your integration project more challenging. As a best practice, configure the custom fields in your Production environment. Then copy down to Pilot or Stage. Start your integration after the copy down. The technical field names in Pilot or Stage will then be the same as Production and will not change.
Identifying New or Updated Data Records
Some reporting views support "delta" or change queries via the "_last_touched_dt_utc" field. This field represents the date and time at which the record was created or most recently updated in the Real-time Data Warehouse. Note that due to the small refresh delay, this does not represent the date and time at which the change actually occurred in the application. For this field, values are in UTC time zone, and the format corresponds to ISO 8601 specification.
Example: _last_touched_dt_utc: 2018-08-04T03:09:20.52Z
The following considerations apply:
- If records are deleted from the application, the "_last_touched_dt_utc" field is not helpful because the record no longer exists in the Real-time Data Warehouse.
- If a customer decides to use the Real-time Data Warehouse as a data source for data deletion features provided by GDPR, they should manually manage cleaning-up the deleted data in their system too.
- In rare scenarios, Cornerstone may execute a full ETL (Extract, Transform, Load). These scenarios include performing a Master Data Load or a Historical Data Load in the Cornerstone portal. If this occurs for a given view, this resets the "_last_touched_dt_utc" field for all records in the view to the date and time at which the full ETL was executed. As a result, the next time a customer pulls data from this view using this field as a delta condition, all records in the view would be returned.
The Data Exporter tool can provide delta exports for certain views. That is, it can export only data that has changed since the last data export. This is more efficient than using the "_last_touched_dt_utc" field. See Data Exporter - Overview.
The Real-Time Data Warehouse community in the Cornerstone Success Center provides the following resources:
- Information and detailed technical documentation about the Real-time Data Warehouse
- Change logs and announcements about release-related updates
- Technical documentation about the various reporting views and the field details
In addition, customers may use this community to ask questions related to Real-Time Data Warehouse.
Frequently Asked Questions
The most efficient way to find out if a specific field is made available via the Real-time Data Warehouse is to use the report designer (Custom Reports or Reporting 2.0) for custom reporting in the Cornerstone portal.
If a field appears reportable in the report designer, the data will also be available via Reporting API as the Reporting API provides access to all fields from the Real-time Data Warehouse.
- For custom or standard fields, use the Reporting 2.0 report designer in the user interface to see the technical field names for selected fields.
- For custom fields, you can contact Cornerstone Global Customer Support to request a one-time report with the field mapping.
- For standard fields, download the technical documentation from the Real-time Data Warehouse community in the Cornerstone Success Center.