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:

• Custom Reports, Reporting 2.0 (User interface)
• Reporting API
• Data Exporter

Data Consumption

	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

Custom Fields

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.

Real-Time Data Warehouse Community

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.

Link: Real-time Data Warehouse Community

Frequently Asked Questions