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.
Related to this post I set up the treatment custom details and have extended CI_TREATMENT_EXT in a similar way to the campaign example above but am not seeing data populated in this table. I also have campaign custom details but these are successfully updating to CI_CAMPAIGN_EXT.
I am sorry to hear that it is not working for you. Did you verify that the column Name does match EXACTLY (lower/uppercase) the values used in the script where you created the _EXT table?
Did you create a new campaign using the new or modified Treatment? It will not work for existing already published campaigns.
If you still have issues please open a track with SAS Technical Support.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.