Appendix: Important References for Unica Open Insights

The following sections contain important references related to Unica Open Insights for the supported Unica applications.

New Folders required during Google Looker installation

When installing Google Looker, you need to create the following folders, based on the number of Unica products for which you are configuring Open Insights. The following table lists the folder names:

Unica product Location Folder name and Description
Unica \Shared folders\ Folder name: Unica

Mandatory requirement.
Unica Campaign \Shared folders\Unica\ Folder name: Campaign

Mandatory requirement if you configure Open Insights for Campaign.
Unica Deliver \Shared folders\Unica\ Folder name: Deliver

Mandatory requirement if you configure Open Insights for Deliver.

Data Model files and their Location after installing Unica

The following table lists the location where the Unica installer copies the data model files and folders, for the concerned Unica product, during installation:

Unica product Location of Data Model files Filename
Unica Campaign Campaign_Home\reports\Open Insights\projects\Campaign\ unica_campaign_project
Unica Deliver Campaign_Home\reports\Open Insights\projects\Deliver\ unica_deliver_project

Database Script locations for Unica Products

The following table lists the supported Unica products and the important folder locations for the corresponding Unica product.

Unica product Folder Locations
Unica Campaign Default location

Campaign_Home\reports\

Product SQLs

Campaign_Home\reports\Campaign-ddl\<DB-name>\
Unica Deliver Default location

Campaign_Home\reports\

Product SQLs

Campaign_Home\reports\Deliver-ddl\<DB-name>\

Unica Products and their Datasources

The following table lists the Unica products and the corresponding datasouce name or names that you must configure in Google Looker.

Unica product Datasource name
Unica Campaign campaignds

platformds
Unica Deliver deliverds

Unica Products and Open Insights SQLs

The following table lists the supported Unica products, the location of the corresponding Open Insights SQL files, and the corresponding Open Insights SQL files.
Unica product Location of Open Insights SQLs Open Insights SQLs
Unica Campaign Campaign_home\reports\Campaign-ddl\<DB-name>\open_insights\
  • Campaign_open_insights_tables.sql
  • Campaign_open_insights_scripts.sql
  • Note: Run this SQL only if you want to drop Open Insights objects.
    Campaign_open_insights_tables_drop.sql
Unica Deliver Campaign_home\reports\Deliver-ddl\<DB-name>\open_insights\
  • Deliver_open_insights_tables_1211.sql
  • Deliver_open_insights_scripts_1211.sql
  • Note: Run this SQL only if you want to drop Open Insights objects.
    Deliver_open_insights_tables_drop.sql

Configuring Database Jobs and Custom Audience

You must configure three database jobs for each default customer or custom audience. The database jobs referesh data of Contact History, Detailed Contact History, and Response History. The commands for the database jobs differ based on the database you use. The following table lists the commands for audience and customers for different Unica products and databases:

Unica product Database Commands
Unica Campaign Oracle 
exec sp_campruns;
exec sp_chrefresh ('<audience name>')
exec sp_dchrefresh ('<audience name>')
exec sp_rhrefresh ('<audience name>')

where <audience name> can be Customer.
IBM DB2 
call sp_campruns;
call sp_chrefresh ('<audience name>')
call sp_dchrefresh ('<audience name>')
call sp_rhrefresh ('<audience name>')

where <audience name> can be Customer.
Microsoft SQL Server 
exec sp_campruns;
exec sp_chrefresh '<audience name>'
exec sp_dchrefresh '<audience name>'
exec sp_rhrefresh '<audience name>'

where <audience name> can be Customer.
Unica Deliver Oracle 
exec SP_RUNID;
exec SP_Populate_Mailing_Contacts;
exec SP_Populate_Mailing_Responses;
exec SP_Populate_SMS_Contacts;
exec SP_Populate_SMS_Responses;
exec SP_Populate_WHTSAPP_Contacts;
exec SP_Populate_WHTSAPP_Responses;
exec SP_Populate_MOBILE_Responses;
IBM DB2 
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;
Microsoft SQL Server 
exec SP_RUNID;
exec SP_Populate_Mailing_Contacts;
exec SP_Populate_Mailing_Responses;
exec SP_Populate_SMS_Contacts;
exec SP_Populate_SMS_Responses;
exec SP_Populate_WHTSAPP_Contacts;
exec SP_Populate_WHTSAPP_Responses;
exec SP_Populate_MOBILE_Responses;

Additionally, for Unica Campaign, you must insert delta refresh records for all your custom audiences. For example, if the custom audience name is Account, the commands for insert are as follows:

Unica product Commands
Unica Campaign 
INSERT INTO UARC_CAMPAIGN_BATCH_LOCK VALUES (0,'Account','CH','N',NULL,NULL);
INSERT INTO UARC_CAMPAIGN_BATCH_LOCK VALUES (0,'Account','DCH','N',NULL,NULL);
INSERT INTO UARC_CAMPAIGN_BATCH_LOCK VALUES (0,'Account','RH','N',NULL,NULL);