Stored procedures for the Unica Interact Event Pattern report

The Unica Interact Event Pattern report uses the data that is contained in staging tables, which are populated by stored procedures. The stored procedures perform a delta refresh operation.

Unica Interact Event Pattern report data is processed in two steps:

  1. The Unica Interact ETL process transforms the audience blob data into ETL database tables.
  2. The reports aggregator aggregates the data incrementally for each pattern type in preconfigured parallel execution. This is specific Unica Interact reports pack.

Both processes are integrated with the database trigger on the UACI_ETLPATTERNSTATERUN table. This trigger is fired on successful ETL execution and submits database jobs to aggregate the reports data.

The following tables provide information about the stored procedures and the tasks that they complete:

Table 1. Stored procedures for the Interact Event Pattern report

This two-columned table lists the stored procedures in the first column, and explains the tasks that the procedures complete in the second column.
Stored procedure Task
SP_GENERATE_PATTERN_MATCHALL Called internally by the SP_POPULATE_PATTERN_MATCHALL procedure. Responsible for retrieving the data for Match All patterns that were executed since the previous run of the stored procedures.
SP_GENERATE_PATTERN_COUNTER Called internally by the SP_POPULATE_PATTERN_COUNTER procedure. Responsible for retrieving the data for Counter patterns that were executed since the previous run of the stored procedures.
SP_GENERATE_PATTERN_WC Called internally by the SP_POPULATE_PATTERN_WC procedure. Responsible for retrieving the data for Weighted Counter patterns that were executed since the previous run of the stored procedures.
SP_POPULATE_PATTERN_MATCHALL Processes the Match All Pattern type data that was received since the previous run of stored procedures.
SP_POPULATE_PATTERN_COUNTER Processes the Counter Pattern type data that was received since the previous run of stored procedures.
SP_POPULATE_PATTERN_WC Processes the Weighted Counter Pattern type data that was received since the previous run of stored procedures.
SP_UPDATE_UACI_TABLES_STATS Called by the trigger to update the database statistics and the database jobs are submitted for reports data aggregation.
Updates the statistics for the following ETL tables:
  • UACI_ETLPATTERNSTATE
  • UACI_ETLPATTERNSTATEITEM
  • UACI_ETLPATTERNEVENTINFO
SP_POPULATE_PATTERN_LOCK (p_parallel_degree) Updates the UARI_PATTERN_LOCK table with the degree of parallel execution configured.

p_parallel_degree is the degree at which the aggregation processes run in parallel.
SP_AGGR_RUN_STATUS Called by the Unica Interact ETL process before the start of the aggregation process to check the lock status of the running stored procedures. Run against the UARI_PATTERN_LOCK table.
SP_REFRESH_PATTERNINFO For Oracle and DB2® only

Refreshes the UARI_PATTERNSTATE_INFO table to get the state and audience level information for the ICs and Categories.

The call to this procedure is given by a trigger before the aggregation procedures start.

Because Mviews are not supported for SQL Server, this procedure is not applicable for SQL Server.
SP_UARI_REBIND_PACKAGES For DB2® only

Rebinds the packages that are associated with the aggregation trigger and procedures. Called from the trigger after the SP_UPDATE_UACI_TABLES_STATS procedure call.
Table 2. Database sequence for Oracle and DB2®

This two-columned table lists the stored procedures in the first column, and explains the tasks that the procedures complete in the second column.
Stored procedure Task
SQ_UARI_RUN Creates a unique run identifier. The list of the run IDs is stored in the UARI_RUNS table.

For SQL Server, RunID is generated by using the IDENTITY property on the RunId column, which generates new IDs on each run.

Table 3. Database trigger

This two-columned table lists the stored procedures in the first column, and explains the tasks that the procedures complete in the second column.
Stored procedure Task
TR_AGGREGATE_DELTA_PATTERNS After the UACI_ETLPATTERNSTATERUN table is updated with the value 3, the trigger is invoked by submitting the jobs that call the stored procedures for data aggregation.

The ETL process

On the first run, ETL does not insert any values against the respective PatternID in the UARI_DELTA_PATTERNS table because all patterns are new or delta. The reports aggregation process collects all PatternID from the ETL tables and inserts them into the UARI_DELTA_PATTERNS table.

The ETL process calls the SP_AGGR_RUN_STATUS procedure. The SP_AGGR_RUN_STATUS procedure checks the UARI_PATTERN_LOCK table for running jobs based on the JobID:

JobID value Reason
Y The job is running. Scenarios are running or failed.
N Failed job.

The ETL process always checks the status of the reports aggregation by checking the status of the submitted jobs. If the ETL finds reports aggregation running, the ETL does not start its run. The ETL starts again according to the schedule.

The ETL process checks the UARI_PATTERN_LOCK table for the number of JobIDs with value Y. The ETL process starts only if no JobIDs have the value Y. If any JobIDs have the value Y, then the ETL process is skipped and runs at the next scheduled interval. For more information about the ETL process, see the Unica Interact Administrator's Guide.

From the second run onwards, the ETL process updates the UARI_DELTA_PATTERNS table with the update flag for the updated PatternID:

  • For updated data, the PatternID is marked with U.
  • For deleted data, the PatternID is marked with D.
  • For newly added data, the PatternID is identified by the reports aggregation code and is marked with P.

The aggregation process is run for only the PatternIDs that are marked with the U or D flag.