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:

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.