binary-1327493_1920

Unica Interact – Whitelist Table Optimization Part 2

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.

In the previous post, Unica Interact – Whitelist Table Optimization Part 1, we implemented a solution to prevent Unica from accidentally dropping the Score override table.  In this article, we’ll look into a solution to update the data faster and reduce the time the table is empty and therefore not serving any whitelist offers.

Since it could be difficult to update the Score Override table with the delta and perform a series or delete, update and insert operations, many organizations will update the whitelist table using the “Delete everything and Insert” strategy.

This operation could become very time consuming, especially when there’s many segments to insert.

To minimize the delay between the time the whitelist table is emptied and the time the table is fully loaded, we’ll implement a 2 step process using a staging table.

FIrst, we’ll insert all the data in a score override staging table, with the usual Flowchart process box.  Then we’ll use an Oracle Stored Procedure to perform the final Delete and Insert operations in the live table.  See conceptual schema below.

As for the SP_COPY_Scoreoverride stored procedure, it will  perform the following operations :

  1. Truncate table UACI_SCOREOVERRIDE 
  2. INSERT INTO UACI_SCOREOVERRIDE SELECT * FROM UACI_STG_SCOREOVERRIDE
  3. COMMIT
  4. Truncate table UACI_STG_SCOREOVERRIDE

These operations will perform fairly fast while minimizing the amount of Oracle rollback segments.

If the Database instance has enough rollback segments available, we could instead perform the following steps which will ensure the table is never left empty:

  1. Delete UACI_SCOREOVERRIDE 
  2. INSERT INTO UACI_SCOREOVERRIDE SELECT * FROM UACI_STG_SCOREOVERRIDE
  3. COMMIT
  4. DELETE UACI_STG_SCOREOVERRIDE
  5. COMMIT;

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

Comments are closed.