Stored procedures for the Interact Event Pattern report

The 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.

Interact Event Pattern report data comes in two steps. First, the Interact ETL process transforms the audiences blob data into ETL database tables. Second, the reports aggregator aggregates the data incrementally for each pattern type in pre-configured parallel execution. It is feature of Interact reports pack.

Both processes are integrated with the database trigger on table UACI_ETLPATTERNSTATERUN. This trigger is fired on successful ETL execution. This trigger 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	This stored procedure is internally called by the SP_POPULATE_PATTERN_MATCHALL procedure, and is responsible to get the data for Match All patterns that are executed since the previous run of the stored procedures.
SP_GENERATE_PATTERN_COUNTER	This stored procedure is internally called by the SP_POPULATE_PATTERN_COUNTER procedure, and is responsible to get the data for Counter patterns that are executed since the previous run of the stored procedures.
SP_GENERATE_PATTERN_WC	This stored procedure is internally called by the SP_POPULATE_PATTERN_WC procedure, and is responsible to get the data for Weighted Counter patterns that are executed since the previous run of the stored procedures.
SP_POPULATE_PATTERN_MATCHALL	This stored procedure processes the Match All Pattern type data that is received since the previous run of stored procedures.
SP_POPULATE_PATTERN_COUNTER	This stored procedure processes the Counter Pattern type data that is received since the previous run of stored procedures.
SP_POPULATE_PATTERN_WC	This stored procedure processes the Weighted Counter Pattern type data that is received since the previous run of stored procedures.
SP_UPDATE_UACI_TABLES_STATS	SP_UPDATE_UACI_TABLES_STATS is called by the trigger first to update the database statistics and then the database jobs are submitted for reports data aggregation. This stored procedure updates the statistics for the following ETL tables: UACI_ETLPATTERNSTATE UACI_ETLPATTERNSTATEITEM UACI_ETLPATTERNEVENTINFO
SP_POPULATE_PATTERN_LOCK (p_parallel_degree)	This procedure 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	This procedure is called by the Interact ETL process before the start of aggregation process to check the lock status of the running stored procedures. This procedure is run against the UARI_PATTERN_LOCK table.
SP_REFRESH_PATTERNINFO	For Oracle and DB2 only. This stored procedure 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. As Mviews are not supported for SQL Server, this procedure is not applicable for SQL Server.
SP_UARI_REBIND_PACKAGES	For DB2 only. This stored procedure rebinds the packages that are associated with the aggregation trigger and procedures. This procedure is called from the trigger after the SP_UPDATE_UACI_TABLES_STATS procedure call.

Table 2. Database sequence
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	For Oracle and DB2: This sequence creates a unique run identifier. The list of the run IDs is stored in the UARI_RUNS table.

For SQL Server: For SQL Server, RunID are generated by using the IDENTITY property on RunId column, which generates new ID 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.