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:
- The Unica Interact ETL process transforms the audience blob data into ETL database tables.
- 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:
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:
|
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. |
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.
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.