# Considerations

# DEAPI Replacement for RAPI?

Cornerstone's RAPI has been around for several years and been the de facto integration mechanism for external reporting systems. RAPI endpoints call database views that attempt to pull data on the fly from many different tables and then present a denormalized representation of that data in the response. Viewed in the context of an Extract, Transform and Load (ETL) paradigm, Cornerstone extracts and transforms the data and the end user only needs to receive and load that data (although additional user transformation may be required if Cornerstone data is combined with other external data sources). While this paradigm can be an effective way to pull certain types of data, it does not scale effectively, particularly over an API interface, and performance can suffer as the amount of data increases and more frequent reporting updates are desired.

DEAPI is the spiritual successor to RAPI. It recognizes that may customers are starting to load data from multiple sources into their own data warehouse and data lake solutions where an Extract, Load and Transform (ELT) paradigm is becoming more prevalent. DEAPI surfaces only "core" data objects built for DEAPI and the file-based Data Exporter. These core objects contain streamlined, normalized data that are more efficient and quicker to transfer via API (compared to RAPI), and are easier for end users to combine and transform in various ways on their end. Core objects provide extended metadata that inform users how to most effectively interact with those objects, for example which objects support change tracking, which fields are filterable, and/or how to join with data in other objects. Most importantly, core objects provide two different mechanisms for customers to receive changed data instead of pulling an entire data set: querying by '_last_touched_dt_utc' field, or by using built-in OData change tracking functionality. With smaller data sets containing only changed data, customers can consider solutions where their data is retrieved and updated multiple times per day instead of once- per-day common with many ETL implementations.

This document provides a mapping of RAPI views to objects in DEAPI and the file-based Data Exporter. While DEAPI doesn't cover as many reporting objects as RAPI yet (additional endpoints are added all the time), if a particular business item is covered by both APIs you should give strong consideration to DEAPI over RAPI. Over time, we will start to deprecate RAPI endpoints if the same data can be retrieved via DEAPI.

The table below lists some of the key differences between RAPI and DEAPI.

Reporting API Data Exporter API
Core objects available No Yes
Keyed Sometimes Always
Pagination by Key Time Complexity (single Object, per page) Up to O(n log n) O(log n)
Normalized Sometimes Always
Extended Metadata No Yes
Enhanced Data Types No Yes
'Last Touched Date' availability Sometimes Always
OData Delta availability No Yes
Automatic Per-Client Schema Evolution No Yes
Compression gzip broti/gzip
Throttling Limit Variable 120 calls/min
Max page size Variable up to 10,000/call

# Security

Cornerstone's APIs use the OAuth 2.0 authorization framework. Currently, Cornerstone only supports OAuth 2.0's Client Credential grant type. You can control the reporting objects/views that your API keys have access to by adjusting the endpoint scope of your OAuth 2.0 application. However, there are no available constraints to grant/deny access at a field or data level. For example, using OAuth 2.0 scopes, you can restrict access to your application to just the user and transcript views. However, there is no facility to further restrict access to data for users from specific division or region. This type of security must be managed by the client. As a result, it is very important to not share any API keys or codes to any individuals who should not have access to your data within CSOD.

You can control access to DEAPI and RAPI endpoints via the following methods:

  • Add/remove the 'Reporting API -- Read Only' permission to the user associated with an OAuth 2.0 application.
  • Adjust the specific endpoints/scopes assigned to individual OAuth 2.0 applications.
  • You can also temporarily revoke access by disabling an OAuth 2.0 application in the API Management page. You can permanently revoke access by uninstalling the OAuth 2.0 application in the API Management page. Please note that this will immediately revoke any access tokens issued by Cornerstone for that application.

# Reporting Environments

Every CSOD client typically has three environments: Pilot, Stage and Production. DEAPI and RAPI can be enabled in all three environments. Clients should ideally maintain a stage or test environment on their end to consume and test the APIs.

CSOD Reporting Environments

Best Practices:

  • Please check with your implementation team or system administrators to confirm which environment has the most relevant data for testing.
  • It is recommended to use the CSOD pilot or staging environment while testing. This will provide the team the ability to stage data for validation purposes.
  • As previously indicated, it is also important for clients to create and maintain a test environment for release planning.
  • It is important to note that the data warehouse may have certain database ID's (such as lookup IDs and form question IDs) that can be out of sync between environments. If possible, it is recommended to schedule a copy-down from production to pilot/stage for consistency with database ID fields. To schedule a copy down, please contact your Integration Consultant or log a case with Global Customer Support (GCS). Please inform all implementation teams or your Client Success Manager prior to scheduling a copy down as this could affect other work being done across environments.

WARNING

It is highly recommended not to stage any data in the production environment. This information CANNOT be deleted.

# RTDW

Every portal and environment has two sets of databases -- a transactional database and a data warehouse database (officially referred as the Real-time Data Warehouse (RTDW)). The RTDW is refreshed near real-time from the transactional database.

CSOD Reporting Environments

It is important to note that there could be up to a 15-minute delay between the syncing of the transactional database and the data warehouse. This should be noted as you build out any custom delta processes using filters. For example, if you are using a date in a $filter condition, you may want to allow for a 15-minute window as shown in the table below. This becomes less important when using OData 4.0 change tracking capabilities.

Condition Schedule Outcome
Data is modified at 1:10 AM UTC Data is pulled at 1:15 AM UTC No data is pulled
Data is modified at 1:10 AM UTC Data is pulled at 1:25 AM UTC Data updated at 1:10 AM UTC is pulled

TIP

Note: A 30 minute ETL schedule is utilized by default in Stage and Pilot environments.

# Maintenance

For information related to patches and releases, please check the Client Success Center to retrieve in-depth information on patch/release schedules as well as documentation outlining the upcoming changes. Specifically for DEAPI and RAPI, please monitor any changes published in the RTDW Documentation for Reporting API, RDW, and Data Exporter community. The release calendar available in the Client Success Center outlines the exact dates for each release and patch.

TIP

If you cannot access the Client Success Center, please advise your integration consultant or customer success manager.

During a release, changes are pushed to the stage environment (typically 3 weeks) before the push to production and pilot. It is highly recommended that you maintain a test environment before and after implementation to monitor for any breaks to existing API integrations.