New CDM - Preview of CI360 October 2020 release (20.10)

[jimhazen]

Overview

The October 2020 (20.10) release of SAS® Customer Intelligence 360 will include a new version of the CDM. The original CDM was the data repository for contacts and responses for SAS Customer Intelligence Studio solutions (such as SAS Marketing Automation) and consisted of a data model containing information about targeted customers, campaigns, and communications. With this new version of CDM, SAS is taking elements from the original CDM and combining it with newer concepts coming from SAS Customer Intelligence 360 to generate a new model. The new model will be used to store contact and response history, along with corresponding metadata to be used in segment criteria for SAS 360 Engage: Direct. Eventually, other products within SAS Customer Intelligence 360 will use the new CDM.

How does the CDM differ from the Unified Data Model (UDM)?

In the March 2020 (20.03) release of SAS Customer Intelligence 360, the UDM model was released, enabling customers to download event data and metadata from SAS 360 Engage and SAS 360 Discover (and now Plan).

CDM will include data populated from the UDM in the cloud and any historical data that is migrated from SAS Customer Intelligence Studio. As a result, CDM will contain metadata from SAS Customer Intelligence 360 items (tasks, segments, messages, creatives, etc), objects from SAS Customer Intelligence Studio (campaigns and treatments) and event tables.

How do customers get access to the CDM tables?

1. Customers that are interested in getting access to the new CDM data need to contact SAS Tech Support to enable the CDM jobs to run for their tenant(s). 
2. Get the latest SAS download program from GitHub: https://github.com/sassoftware/ci360-download-client-sas
3. Get the sample loader program and DDLs from GitHub and modify loader program to suit your database: https://github.com/sassoftware/ci360-cdm-loader-sas

What’s in the CDM?

The CDM consists of 30 tables and primarily sourced from UDM tables. The CDM tables are:

Table Name	Description
CDM_ACTIVITY_CUSTOM_ATTR	This table contains custom properties that have been applied to an activity. These custom properties assist in analyzing activities (for example, by grouping them or otherwise distinguishing them from one another). Data for this table is obtained from the MD_ACTIVITY_CUSTOM_PROP table.
CDM_ACTIVITY_DETAIL	This table contains metadata about published activities. Data for this table is obtained from the MD_ACTIVITY table.
CDM_ACTIVITY_X_TASK	This table shows the relationship between published tasks and published activities. The data in this table can be analyzed when you want to know which tasks are part of an activity. Data for this table is obtained from the MD_ACTIVITY_X_TASK table.
CDM_BUSINESS_CONTEXT	This table contains metadata about business contexts. A business context allows you to restrict data access to only the information that is required for a specific business need. A user can have access to more than one business context. Note: This table will be available for download in the 20.10 release (October 2020), but will not initially be populated with data until November release. Data for this table will soon be obtained from the MD_BUSINESS_CONTEXT table.
CDM_CAMPAIGN_CUSTOM_ATTR	This table is in the schema but is not currently available for download. If you need more information about this table, please contact SAS Technical Support. Data for this table is obtained from the user-defined field tables that describe campaigns and campaign pages.
CDM_CAMPAIGN_DETAIL	This table is in the schema but is not currently available for download. If you need more information about this table, please contact SAS Technical Support. Data for this table is obtained from the CI_CAMPAIGN table.
CDM_CONTACT_CHANNEL	This table contains the channel of the task where the contact events occurred, as well as information about the contact. Keep these items in mind when using this table: This table contains information about the channel of the task, not the event. This table contains information only for known users (users with a subject ID). In the Properties tab, the check box for generating contact events must be selected. In addition, the check box that classifies events as contacts (available when adding an event as a metric) must be selected. Data for this table is obtained from the CONTACT_HISTORY table.
CDM_CONTACT_HISTORY	This table stores all of the SAS Customer Intelligence 360 events that are marked as contact events either in the Properties tab of a task properties or task metrics from. It might also contain events from SAS Customer Intelligence Studio for known customers. In the Properties tab, the check box for generating contact events must be selected. In addition, the check box that classifies events as contacts (available when adding an event as a metric) must be selected. Data for this table is obtained from the CONTACT_HISTORY table.
CDM_CONTACT_STATUS	This table contains data from SAS Customer Intelligence Studio. It is not populated by SAS Customer Intelligence 360.
CDM_CONTENT_CUSTOM_ATTR	This table represents the custom property metadata associated with messages, creatives, and treatments from SAS Customer Intelligence Studio. Data for this table is obtained from the following tables: MD_CREATIVE_CUSTOM_PROP and MD_MESSAGE_CUSTOM_PROP.
CDM_CONTENT_DETAIL	This table contains metadata about messages and creatives from SAS Customer Intelligence 360 and treatments from SAS Customer Intelligence Studio. Data for this table is obtained from the following tables: MD_CREATIVE and MD_MESSAGE.
CDM_DYN_CONTENT_CUSTOM_ATTR	This table contains dynamic attributes from SAS Customer Intelligence Studio, as well as their values. There is no data from SAS Customer Intelligence 360 stored in this table. This table is in the schema but is not currently available for download. If you need more information about this table, please contact SAS Technical Support.
CDM_IDENTIFIER_TYPE	This table contains a lookup of the identity and subject type, as well as a description.
CDM_IDENTITY_ATTR	This table contains information about a customer’s identity (for example, the subject ID). Data for this table is obtained from the IDENTITY_ATTRIBUTES table.
CDM_IDENTITY_MAP	This table contains a mapping between identity and the subject level. Data for this table is obtained from the IDENTITY_ATTRIBUTES table.
CDM_IDENTITY_TYPE	This table contains a lookup of subject levels and descriptions.
CDM_OCCURRENCE_DETAIL	This table contains metadata about occurrences in direct marketing, bulk email, bulk mobile push tasks, and on-premises segment maps. Occurrences that do not execute are displayed as having failed. Data for this table is obtained from the MD_OCCURRENCE table.
CDM_RESPONSE_CHANNEL	This table lists the channels where responses have occurred and can be used to create aliases for those channels. Data for this table is obtained from the RESPONSE_HISTORY table.
CDM_RESPONSE_EXTENDED_ATTR	This table lists custom attributes for responses originating from SAS Customer Intelligence Studio.
CDM_RESPONSE_HISTORY	This table contains all response events for known customers (those customers who have subject IDs). Data for this table is obtained from the RESPONSE_HISTORY table.
CDM_RESPONSE_LOOKUP	This table contains a list of response events and can be used to create aliases for those events. Data for this table is obtained from the RESPONSE_HISTORY table.
CDM_RESPONSE_TYPE	Legacy table that categorizes the type of responses (for example, “converted” or “responded to”). It is populated by SAS Customer Intelligence Studio only if the data has been migrated over.
CDM_RTC_DETAIL	This table contains metadata about Response Tracking Codes. Use this table to link response history and contact history with an RTC. Data for this table is obtained from the MD_RTC table.
CDM_RTC_X_CONTENT	Use this table to see which content (creatives, messages, or treatments from SAS Customer Intelligence Studio) is associated with a RTC. Data for this table is obtained from the MD_RTC table.
CDM_SEGMENT_CUSTOM_ATTR	This table contains custom properties associated with a published segment. Note: Custom properties are added by users. Data for this table is obtained from the MD_SEGMENT_CUSTOM_PROP table.
CDM_SEGMENT_DETAIL	This table contains metadata about published segments. Data for this table is obtained from the MD_SEGMENT table.
CDM_SEGMENT_MAP	This table contains metadata about published segment maps. Data for this table is obtained from the MD_SEGMENT_MAP table.
CDM_SEGMENT_MAP_CUSTOM_ATTR	This table contains custom properties associated with a published segment map. Note: Custom properties are added by users.  Data for this table is obtained from the MD_SEGMENT_MAP_CUSTOM_PROP table.
CDM_TASK_CUSTOM_ATTR	This table contains custom properties associated with a published task. Note: Custom properties are added by users. Data for this table is obtained from the MD_TASK_CUSTOM_PROP table.
CDM_TASK_DETAIL	This table contains metadata about published tasks. Data for this table is obtained from the MD_TASK table.

How does data end up in the CDM?

The prerequisite to having data for the CDM is having SAS Customer Intelligence 360 Engage tasks that generate contact and response events for known customers. A known user is a customer that has a subject_id in SAS Customer Intelligence 360.

Data that populates CDM is sourced from the UDM in the SAS Customer Intelligence 360 cloud, as well as from any historical data that is migrated from SAS Customer Intelligence Studio. As a result, the model consists:

• Metadata from SAS Customer Intelligence 360 objects (tasks, segments, messages, creatives, and so on)
• Migrated data from SAS Customer Intelligence Studio (campaigns and treatments)
• Event tables (contacts and responses)

An hourly job runs in the cloud to produce CDM tables in the right format to be loaded straight into the on-premises CDM with minimal transformation occurring on-premises. Here is an overview of that process:

First, contact and response events need to be generated. Contact events are generated by SAS Customer Intelligence 360 either:

• when a user selects to generate a contact in the Properties tab of a task, or
• from classifying a primary or secondary metric as a contact in the Insights tab of a task.

Figure 1 Properties tab with contact event selected

Figure 2 Metric classified as contact event

Responses are generated by the user selecting to classify the primary or secondary metric in the Insights tab as a response.

Figure 3 Metric classified as response event

When the hourly jobs in the cloud run, the contact and response events are filtered by using the IDENTITY_ATTRIBUTES table in the UDM to filter out anonymous contacts or responses. The filter ensures that the contacts and responses have a subject_id associated with them.

After the jobs have run in the cloud, the data is placed in partitioned and nonpartitioned files in the cloud. The event data is partitioned based into hourly buckets. The nonpartitioned data consisting of the metadata are full snapshots of the data each time.

Downloading CDM Data from SAS Customer Intelligence 360

SAS makes the CDM data available directly through the Download API, as well as through a SAS program which also uses the download API. With the release of CDM, SAS added a new category to the Download API called ‘cdm’. Use this category along with schemaVersion=6 to download tables for the CDM from the cloud. Note: Only those events marked as contact or response will be included in the CDM category.

Here is an example of an API call that download non-partitioned data:

https://extapigwservice-<tenant> /marketingGateway/discoverService/dataDownload/eventData/detail/nonPartitionedData?schemaVersion=6&category=cdm

Here is an example of an API call that download partitioned data:

https://extapigwservice-<tenant> /marketingGateway/discoverService/dataDownload/eventData/detail/partitionedData?schemaVersion=6&dataRangeStartTimeStamp=2020-11-01T00:00:00.000Z&dataRangeEndTimeStamp=2020-11-06T23:59:59.999Z

The recommended method to download these tables is to use the SAS program. The SAS program is a program that can be utilized to download data, unzip the files, and combine the files into staging tables on-premise that are ready to be loaded into the database. The program will reside in GitHub so that users of the program can subscribe to updates that happen over time. You can find the download program here: https://github.com/sassoftware/ci360-download-client-sas

In the download program, make sure you set the following variables in the dsc_download.sas macro:

• %let mart_nm=cdm;
• %let DSC_SCHEMA_VERSION=6;
• %let CATEGORY=cdm;

The download program performs the following functions:

• Downloads the CDM tables from the cloud based on a time period specified in macro
• Unzips all the files
• Combines files into SAS datasets that are used by the loader program

Modifications that you can make to macro:

• Start/End Time
• Update the tenantId/secret key in order to generate a new JWT

Prerequisites for the SAS download program:

• BASE SAS9.4(M4) (Unicode Support)
• Enable the “SAS to Allow XCMD System Option” on the Workspace and Pooled Workspace Servers
• Python3 Python is used to generate token for API authentication. Make sure following python libraries are installed getopt, http.client, urllib, re, base64, jwt
• gzip utility program Download gzip.exe. Add gzip utility program location to PATH environment variable. This is required for SAS program to read .gz files without un-compressing the file.
• From SAS Customer Intelligence 360, navigate to General Settings -> External -> Access and create new access point if it's not already created. If it is created, get the tenantId and client secret to use in the download program.

Loading Data into On-Premises Database

Once the data has been downloaded from the cloud, it is now ready to be loaded into the on-premises database. SAS is making a sample loader program available in GitHub: https://github.com/sassoftware/ci360-cdm-loader-sas. You can use it as a reference for building a program that can take the downloaded data and load into the CDM. The sample program was built with Oracle in mind as the target database. To use a different target database, you can write your own loader program or modify the sample program. The sample program includes some database-agnostic code that can be utilized.

The loader program performs the following functions:

• Loads the downloaded SAS datasets to the data model
• Keeps track of successes and failures

The sample loader program can be edited in the following ways:

• Specify the location of the downloaded CDM data sets
• Designate the location of the loader macros
• Change the database credentials
• Change the level of log messaging

Which tables will not be populated in the initial release?

• The CDM_BUSINESS_CONTEXT table will not be populated initially but will be populated with the November release.
• The CDM_IDENTITY_TYPE, CDM_IDENTITY_MAP and CDM_IDENTIFIER_TYPE will either be empty or very sparsely populated until SAS Customer Intelligence 360 supports different identity levels (account, household, individual, etc). Once this feature is in place, the tables will be populated.
• CDM_CAMPAIGN_DETAIL, CDM_CAMPAIGN_CUSTOM_ATTR, CDM_RESPONSE_EXTENDED_ATTR, CDM_CONTACT_STATUS and CDM_RESPONSE_TYPE will not be populated from SAS Customer Intelligence 360. Instead will be populated from data migrated from SAS Customer Intelligence Studio.

Which fields aren’t being populated?

• CDM_RESPONSE_HISTORY
  • content_version_id – the unique identifier for specific content tied to the response
  • contact_id – the unique identifier of a unique contact row (associating contact and response)
• CDM_IDENTITY_ATTR
  • valid_from_dttm
  • valid_to_dttm

What target databases are supported with DDLs?

DDLs can be found in GitHub: https://github.com/sassoftware/ci360-cdm-loader-sas/tree/main/ddl

• SAP HANA
• Snowflake
• Google BigQuery
• Postgres
• IBM DB2
• Teradata
• EMC Greenplum
• Microsoft SQL Server
• Amazon Redshift
• Oracle

Statement of Support

The SAS product functionality described above is provided for informational purposes only based on SAS’ current product roadmap. It does not represent nor alter official SAS product documentation nor is it a guarantee as to the timing of the availability of any product functionality or that such functionality will be made available.  

[nktech]

Thanks Jim for sharing the detail. Couple of question:

Regarding the UDM model. I believe, currently the contact & response history being saved in the UDM data model.  Do we know that where this UDM data reside, is that on SAS private cloud? & what is the way to access the information from UDM data model?

Secondly, as you mentioned that CDM20.10 ( New release) will maintain the customer Intelligence data ( Marketing automation) along with the SAS-CI 360 engage direct, so below are my questions:

-- Where this new CDM going to reside, if understood correctly, this is on SAS-Cloud and customer can use API/SAS program to download the data etc, but please let me know if this is correct assumption

-- If customer is using the hybrid mode i.e. Marketing automation 6.6 with on-prem database and also SAS-CI 360 then how the on-prem data will sycn with CDM ( which is on the cloud)

Is there any way that we can only use the on-prem oracle database for both Marketing automation 6.6. campagins as well as campaigns/journeys buid on SAS-CI 360 engage direct instead of saving the contact & response history over the SAS-private cloud.

Thanks.

[jimhazen]

The contact and response history for the UDM is stored in the cloud. You can access that through the Download API, it was part of schemaVersion=4, you need to use one of the Engage categories and you have to have an Engage license to get to it. A note on the CONTACT_HISTORY and RESPONSE_HISTORY tables in the UDM....those tables are 'unfiltered' meaning they contain anonymous and known customers in the data. The CDM tables for contact and response have been filtered by customers that have a subject_id stored in the cloud. 

Both the UDM and the CDM tables are stored in the cloud and are accessible by using the Download API.

Your second group of questions:

1) While the CDM tables are stores in the cloud the actual model is on-premises. Your assumption is correct, the tables are in the cloud and can be downloaded to be loaded into the on-premises database

2) I mentioned the old CDM data, there is a script available through the Professional Services group that can be utilized to migrate data from old CDM to new CDM.

[nktech]

Thanks Jim. One quick question:

Can we not sore contact/response history directly in our database either on-prem or Cloud instead of saving on SAS cloud?

[jimhazen]

Technically, you could write contacts directly into the CDM but that would all be custom code. The drawback to doing so would be that you wouldn't have the identity_id on the contacts in the CONTACT_HISTORY as that happens in the cloud.