BookmarkSubscribeRSS Feed

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

Started ‎07-28-2016 by
Modified ‎08-10-2016 by
Views 1,191

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. 

 

Comments

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.

Version history
Last update:
‎08-10-2016 10:40 AM
Updated by:
Contributors

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Tags