CLEARGOALS began as a Unica focused consultancy. It has been impressive to see how far the platform has come in such a short-time. As with all MarTech platforms, there are challenges for the beginner when it comes to simplifying and maximizing the interface. One of these opportunities is Whitelist Table Optimization in Unica Interact.
With the current design of the Scoreoveride process box in the Unica Campaign flowcharts, it brings many challenges implementing Interact whitelist tables. In the following use case, we’ll be using Unica Campaign version 10.1 with an Oracle database for storing the system tables.
The first challenge is the option to replace the table data. As we can see from the flowchart log files, it will empty the table using a sql DROP TABLE statement. Althrought, this statement is very efficient on Oracle, it will also remove any custom privileges or indexes on the scoreoveride table.
To overcome this potential problem, we need to always pick the “append” option instead of replace. However, this means, we’ll need to manually delete the data from the table before the new scores are inserted.
This will bring another potential issue since the table will be empty for some time while the new rows get processed which will impact the presented offers.
For our client, we developed the following solution which will minimize the time the whitelist table is empty and provide DB consistency by disabling any DDL modifications to the tables.
The solution is using 2 database schemas :
- The Unica INTPROFILE schema is created as per Unica DDL
- The INTPROFILE_RW schema is created to provide connection and access to INTPROFILE using Oracle grants between the schemas
As part of this solution, we’ve also developed a store procedure for the purpose of truncating the score override table which will be faster than a DELETE and avoid having the Interact process box dropping the table.
The flowchart implementation will need to add a Select process box in order to execute the truncate store procedure as below
After implementing this solution, it will be impossible for flowchart users to accidentally drop the table by choosing the “Replace option”. In such a case, the user will receive a DB error for missing privileges. The INTPROFILE_RW user cannot be granted a DROP command on the INTPROFILE schema.
In part 2, we will discuss a solution to minimize the time where the whitelist table is empty between the truncate and the Insert operations.
If you have questions about how to make Unica Interact work better for your company, contact [email protected].
Author: François Veillette, CLEARGOALS Principal Solution Architect
