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 (236 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:

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:

 

1.JPG

 

Or another example with column name Camp_Dev_Start:

 

2.JPG

 

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:

3.JPG

 

 

 

CI_CAMPAIGN_EXT table:

 

4.JPG

 

 

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. 

 

Contributors
Your turn
Sign In!

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


Looking for the Ask the Expert series? Find it in its new home: communities.sas.com/askexpert.