We’re smarter together. Learn from this collection of community knowledge and add your expertise.

How to publish to the Common Data Model extension (_EXT) tables

by SAS Super FREQ on ‎07-28-2016 08:32 PM - edited on ‎08-10-2016 10:40 AM by Community Manager (219 Views)

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:








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:




                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     



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:










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: Publishing_metadata.png



2. Execute a campaign. With each execution of a campaign  the campaign metadata is automatically published to the SAS CDM. 


Your turn
Sign In!

Want to write an article? Sign in with your profile.