Support Article
Error running Pega Marketing Setup Utility with externalized IH
Summary
User has encountered an error while running Pega Marketing Setup Utility and figured that the issue was due to an (seemingly incomplete) PR_DATA_IH_DIM_CONTEXT table in the separate or standalone Interaction History (IH) schema.Error Messages
[sql] Failed to execute: CREATE MATERIALIZED VIEW <PegaDATA Schema>.MKT_CAMPAIGN_OFFER_STATS (LAST_REFRESHED, ISSUENAME, GROUPNAME, OFFER, PENDING, IMPRESSION, CLICKED, ACCEPTED) NOCACHE LOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE REFRESH COMPLETE NEXT SYSDATE + 30/1440 AS WITH all_stats AS (SELECT ifr.pxfactid AS pxfactid ,ifr.pysubjectid AS customerid ,CASE isr.pyoutcome WHEN 'Pending' THEN 1 ELSE 0 END AS pending ,CASE isr.pyoutcome WHEN 'Impression' THEN 1 ELSE 0 END AS impression ,CASE isr.pyoutcome WHEN 'Clicked' THEN 1 ELSE 0 END AS clicked ,CASE isr.pyoutcome WHEN 'Accepted' THEN 1 ELSE 0 END AS accepted ,irc.pycategory AS pycategory ,action.pyissue AS pyissue ,action.pygroup AS pygroup ,action.pyname AS offer FROM PEGAIHSTORE.pr_data_ih_fact ifr LEFT OUTER JOIN PEGAIHSTORE.pr_data_ih_dim_context irc ON (ifr.pzcontextid = irc.pzid) INNER JOIN PEGAIHSTORE.pr_data_ih_dim_outcome isr ON (ifr.pzoutcomeid = isr.pzid) LEFT OUTER JOIN PEGAIHSTORE.pr_data_ih_dim_action action ON (ifr.pzactionid = action.pzid) WHERE isr.pyoutcome IN ('Pending' ,'Impression' ,'Clicked' ,'Accepted') AND irc.pycategory LIKE 'Data-BatchOutCMPR%' WITH READ ONLY) ,offer_category_customer AS (SELECT MAX (impression) AS impression ,MAX (clicked) AS clicked ,MAX (accepted) AS accepted ,MAX (pending) AS pending ,pyissue ,pygroup ,offer ,pycategory ,customerid FROM all_stats GROUP BY pyissue ,pygroup ,offer ,pycategory ,customerid) SELECT SYSDATE AS last_refreshed ,pyissue AS issuename ,pygroup AS groupname ,offer ,SUM (pending) AS pending ,SUM (impression) AS impression ,SUM (clicked) AS clicked ,SUM (accepted) AS accepted FROM offer_category_customer GROUP BY pyissue, pygroup, offer
[sql] java.sql.SQLSyntaxErrorException: ORA-00904: "IRC"."PYCATEGORY": invalid identifier
[sql] Failed to execute: COMMENT ON MATERIALIZED VIEW <PegaDATA Schema>.MKT_CAMPAIGN_OFFER_STATS IS 'snapshot table for snapshot MKT_CAMPAIGN_OFFER_STATS'
[sql] java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not existSteps to Reproduce
1. Install Pega 7.2.1 and install Interaction History in a separate schema by running the generateIHddl.sh script available in the product media /Pega721/Additional_Products/DSM/Products/Interaction History/schema.
2. Install Pega Marketing 7.21 by importing the archives.
3. Run the Pega Marketing Setup Utility.Root Cause
User has externalized the Interaction History (IH) tables. However selecting Automatic option in “step 6” of the “Import the Application Bundles” section (page# 12) in the Pega Marketing installation guide (while importing the PegaMarketing_PreRequisites_0721.zip which contains schema changes, has taken care of the IH tables only in the PegaDATA schema and not in the externalized IH schema.Resolution
To overcome the inconsistency related to the IH tables:
- Take a backup of the PegaDATA schema as a precaution
- Drop all the tables related to Interaction History, that is, table names starting with “PR_DATA_IH_” from the PegaDATA schema with cascade option.
- Run the “IH_install_on_oracle.sql” file generated earlier, by pointing to PegaDATA schema, so that IH table structures are in sync in both the schemas, that is PegaDATA and ExternalIH.
- Follow the Pega Marketing Installation Guide to import the PegaMarketing_PreRequisites_0721.zip file and in the “step 6” of the “Import the Application Bundles” section select the Manual option to download the DDL SQL.
- Verify the downloaded DDL SQL and run it against the External IH schema to update the necessary IH related tables, it involves Alter table, Grant and Create Index SQLs for the IH tables.
- After the IH tables are in-line with the standard structure, execute the “Pega Marketing Setup Utility”.
Published August 23, 2017 - Updated December 2, 2021
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.