How to publish to the Common Data Model extension (_EXT) tables
How to publish to the Common Data Model extension _EXT tables:
1. In my example I would like to publish to the CI_CAMPAIGN_EXT table, but this works for all other _EXT tables in the same way. First you need to verify that the column names exist in your CI_CAMPAIGN_EXT table. As an example, the CI_CAMPAIGN_EXT table should contain the following column names:
CAMP_OWNER
CAMP_MANAGER
COMMS_SPECIALIST
CAMP_REP_DISPLAY
CAMP_DEV_START
CAMP_LAUNCH_PLANNED
2. You can populate the _EXT table using the DDL scripts located at: <SASHome>\SASFoundation\9.4\cicsvr\sasmisc.
Open the sas file ci_cdm_ddl_your_database_.sas which will match your database and search for:
/*=================================================================*/
/*===== B E G I N E X T E N S I O N S E C T I O N =====*/
/*=================================================================*/
3. Add all values you would like to publish to the CI_CAMPAIGN_EXT table. Just for demonstrating I highlighted the ones I would like to add in bold:
EXECUTE (CREATE TABLE &SCHEMA..CI_CAMPAIGN_EXT
(
CAMPAIGN_SK NUMERIC(10) NOT NULL,
CAMP_BRIEF_URL varchar(120) NULL,
CAMP_OWNER varchar(80) NULL,
CAMP_MANAGER varchar(80) NULL,
COMMS_SPECIALIST varchar(80) NULL,
DATA_MINER varchar(80) NULL,
CAMPAIGN_TYPE varchar(20) NULL,
CAMPAIGN_RECURRENCE varchar(20) NULL,
PRIMARY_CHANNEL varchar(40) NULL,
PRODUCTS_OFFERED varchar(40) NULL,
STRATEGIC_OBJECTIVE varchar(40) NULL,
STRATEGIC_PROGRAM varchar(40) NULL,
CAMP_REP_DISPLAY tinyint NULL,
CAMP_DEV_START datetime NULL,
CAMP_LAUNCH_PLANNED datetime NULL,
CAMP_LAUNCH_ACTUAL datetime NULL,
CAMP_END_ACTUAL datetime NULL
)) BY OLEDB;
Execute the extension part and verify that your columns exist.
4. Once the CI_CAMPAING_EXT table contains the columns, create a campaign definition using the above values as COLUMN name. See in the example below I created a custom detail with column name Camp_Owner:
Or another example with column name Camp_Dev_Start:
NOTE: The Column name in the Custom Detail properties need to match exactly the Column Names you added to the CI_CAMPAIGN_EXT table (but they are not case-sensitive).
5. When using this campaign definition for a new created campaign the values are published to the CI_CAMPAIGN_EXT table:
CI_CAMPAIGN_EXT table:
Next steps
After completing the setup and configuration tasks the User can publish campaign metadata in 2 ways:
1. Select the “Publish” icon in the Designer window of CI Studio, as illustrated here:
2. Execute a campaign. With each execution of a campaign the campaign metadata is automatically published to the SAS CDM.
