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

[#1]

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. 

[Suksri]

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.

[Bet_ty]

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.