Understanding Deliver Insight Reports and it's data flow in the tables

HCL Unica Deliver reports use the data that is contained in staging tables, which are populated by stored procedures. The stored procedures perform a delta refresh operation. The user must run the stored procedures at least once per day. Users should consider running the procedures at times of reduced system activity, such as overnight. This document will help you understand data flow from Design-time tables to Report Staging tables and how to troubleshoot in case data gets stuck during the execution of store procedure. This document will cover data flow for Email Contact and Response procedures.

How do the data flows from Design time tables to Deliver Insight Reporting tables

1. User should schedule the below-mentioned store procedure starting with SP_RUNID.

call SP_RUNID();
call SP_POPULATE_MAILING_CONTACTS();
call SP_POPULATE_MAILING_RESPONSES();
call SP_POPULATE_SMS_CONTACTS();
call SP_POPULATE_SMS_RESPONSES();
call SP_POPULATE_WHTSAPP_CONTACTS();
call SP_POPULATE_WHTSAPP_responses();
call SP_POPULATE_MOBILE_RESPONSES();

2. SP_RUNID store procedure inserts delta RUNID records in the UARE_RUNS table.

3. SP_POPULATE_MAILING_CONTACTS

A. Gets the LOCK (set ISLOCK=’Y’) in the table UARE_MAILING_BATCH_LOCK.ISLOCK if the ISLOCK=’N’ for BATCHTYPE=’EC’.

UARE_MAILING_BATCH_LOCK

B. It inserts into table UARE_DELTA_REFRESH_LOG record, which suggests that “Contact Delta refresh started”.
UARE_DELTA_REFRESH_LOG

C. SP_POPULATE_MAILING_CONTACTS internally calls SP_GET_DELTA_MAILING_CONTACTS, which inserts delta records in the table UARE_PROCESSED_CONTACTS (This table tracks batch jobs that have been run to collect contact data). The system determines if a mailing and associated links exist that should be processed as part of the delta refresh. If the yes value of REGENFLAG is set as ‘Y’ and+, the mailing and links are processed as part of the refresh. In case of failure to get the delta list of records, it inserts into table UARE_DELTA_REFRESH_LOG

→ ‘unable to generate runid’.
UARE_PROCESSED_CONTACTS

UARE_DELTA_REFRESH_LOG

D. SP_POPULATE_MAILING_CONTACTS internally calls SP_GENERATE_MAILING_CONTACTS. It inserts into UARE_MAXENV_CONTAINER ContainerID and count EnvelopeID associated with the ContainerID. Also, it starts populating the table UARE_MAILING_LINK_CONTACT (This table consolidates contact data for mailings and links in individual email messages). Once UARE_MAILING_LINK_CONTACT table is populated, the procedure updates the table UARE_PROCESSED_CONTACTS to set processedtime and REGENFLAG=’N’.
UARE_MAXENV_CONTAINER

E. After completion of SP_GENERATE_MAILING_CONTACTS, the record is inserted in the UARE_DELTA_REFRESH_LOG table to indicate whether the Email contact delta refresh is completed or not.

F. Update statement is executed on UARE_MAILING_BATCH_LOCK set ISLOCK=’N’ for Batchtype=’EC’.

G. Insert into UARE_RUN_LOG (This table provides a history of batch process runs. This table is populated from UARE_MAILING_BATCH_LOCK after each run completes).

H. If the ISLOCK=Y’ for BATCHTYPE=’EC’, then INSERT INTO UARE_DELTA_REFRESH_LOG with details ‘Email contact delta refresh is already running for runid.’

I. In case of any exception in running SP_POPULATE_MAILING_CONTACT then update UARE_MAILING_BATCH_LOCK set ISLOCK=’N’ Where Batchtype=’EC’

4. SP_POPULATE_MAILING_RESPONSES

A. Gets the LOCK (set ISLOCK=’Y’) in the table UARE_MAILING_BATCH_LOCK.ISLOCK if the ISLOCK=’N’ for BATCHTYPE=’ER’.

UARE_MAILING_BATCH_LOCK

B. It inserts into table UARE_DELTA_REFRESH_LOG record, which suggests that “Response Delta refresh started.”

UARE_DELTA_REFRESH_LOG

C. SP_POPULATE_MAILING_CONTACTS internally calls SP_GET_DELTA_MAILING_RESPONSES, which inserts delta records in the table UARE_PROCESSED_RESPONSES (This table tracks batch jobs that have been run to collect response data). The system determines if a mailing and associated links exist that should be processed as part of the delta refresh. If yes value of REGENFLAG is set as ‘Y’ and+, the mailing and links are processed as part of the refresh. In case of failure to get the delta list of records, it inserts into table UARE_DELTA_REFRESH_LOG

→’unable to generate runid’.

UARE_DELTA_REFRESH_LOG

D. SP_POPULATE_MAILING_RESPONSES internally calls SP_GENERATE_MAILING_RESPONSES. It inserts the table UARE_MAILING_LINK_RESPONSE (This table consolidates response data for mailings and for external links in individual email messages). Also, it inserts into table UARE_LP_LINK_RESPONSE(This table consolidates response data mailings and links in hosted landing pages). Once UARE_MAILING_LINK_RESPONSES and UARE_LP_LINK_RESPONSE tables are populated, the procedure updates the table UARE_PROCESSED_RESPONSES to set processedtime and REGENFLAG=’N’.

E. After completion of SP_GENERATE_MAILING_RESPONSES, the record is inserted in the UARE_DELTA_REFRESH_LOG table to indicate whether the Email response delta refresh is completed or not.

F. Update statement is executed on UARE_MAILING_BATCH_LOCK set ISLOCK=’N’ for Batchtype=’ER’.

G. Insert into UARE_RUN_LOG (This table provides a history of batch process runs. This table is populated from UARE_MAILING_BATCH_LOCK after each run completes).

H. If the ISLOCK=Y’ for BATCHTYPE=’ER’, then INSERT INTO UARE_DELTA_REFRESH_LOG with details Email response delta refresh is already running for runid’.

I. In case of any exception in running SP_POPULATE_MAILING_RESPONSES then update UARE_MAILING_BATCH_LOCK set ISLOCK=’N’ Where Batchtype=’ER’

Once data is migrated to Deliver staging table, the user should be able to see the latest Deliver contact and response details.

For example, a Detailed link report.

To learn more about how reporting works in Unica Deliver, you can reach out to us, and we will be happy to help.