About Common Data Model (CDM) Tables
Common Data Model (CDM) data is structured to require minimal transformation before loading the data into your on-premises database. CDM tables contain data from SAS Customer Intelligence 360. In addition, CDM tables can contain campaign and treatment data that was migrated from SAS Customer Intelligence Studio.
Use the following information when downloading the CDM tables:
- Use version 6 or later of the database schema.
- In your API call, use the detail/partitionedData endpoint to download event data that is partitioned hourly. Use the detail/nonPartitionedData endpoint to download metadata tables.
- Download the entity relationship diagrams to review the tables' organization and links.
- The downloaded data might contain
more columns than are documented in the Help Center or in the schema files.
These columns might have been introduced for internal consumption already
and will be added to a future version of the schema.
The same scenario can happen when you are not using the most recent schema. The data that you download always uses the columns from the latest schema, but you might have requested a schema version that has fewer columns.
- A schema might add columns to
tables, but the columns are not populated with data until a later release of
SAS Customer Intelligence
360. For example, a table in the current schema might have nine
columns, but data records populate only seven columns. When columns are
added but not yet populated with data, you can ignore these columns when you
process the data.
When SAS Customer Intelligence 360 is updated to populate these columns, you might need to wait 2–3 days before the data is populated in the downloaded tables.
To mitigate any issues, configure your ETL processes to allow for the physical files to have more columns than what is described in the JSON schema and the documentation.
There are currently two CDM processes:
- The partitioned tables are sourced from the contact and response history tables in the Unified Data Model (UDM). The job for the partitioned tables is submitted only when new data is detected for the Detail tables.
- The non-partitioned tables are primarily
sourced from the metadata tables in the UDM that have a name similar to
*_all. These tables are updated hourly, and the non-partitioned table job is also run hourly. All records are loaded into the on-premises database. If there are existing records with the same keys, those records are updated with new values.
CDM tables are refreshed every hour and you can download the tables with the SAS Customer Intelligence 360 download API. Any information that is available is downloaded when the download utility runs. For more information, see Downloading Data Tables with the REST API in SAS Customer Intelligence 360: Administration Guide. After the tables are downloaded, the CDM loader program on GitHub enables you to import the downloaded data into an on-premises database.
If you use the CDM loader program, the cdm_load_datastep.sas macro load utility populates the source_system_cd column, updated_by_nm column, and updated_dttm column as they are loaded into the database. These columns are null in the downloaded data set but are updated during the loading process.
If you do not use the CDM loader program and you have site-specific code to load into the on-premises database, you must populate the source_system_cd column and updated_dttm column (and optionally the updated_by_nm column) when you load the data. The updated_dttm column is required and is populated by the cloud process for partitioned tables only. However, this column remains null for non-partitioned tables.
The CDM_CAMPAIGN_CUSTOM_ATTR and CDM_CAMPAIGN_DETAIL tables are not supported by cloud processing and are neither created nor downloaded, although they could be populated by a different on-premises process. The CDM data model could be used as a reference for which columns are present in both of these tables and could also be used for joins.