Unica Interact Event Pattern Report Tables

This section describes the database schema design for the Unica Interact Event Pattern report. To improve the Event Pattern report execution time, the report is designed to run on the aggregated data. The aggregation process is to collect the transactional ETL data and store it in separate tables from which the report is generated. The tables in which the aggregated data is stored are described here.

UARI_PATTERN_COUNTER

This table contains the aggregated data for Counter Pattern type.

Field Type Length Null? Description
PATTERNID BIGINT Pattern ID
PATTERNSTATEINSTANCEIDS BIGINT Number of pattern state instances
TYPE INT Type-2 for Counter type
AUDIENCELEVEL VARCHAR(192) Audience level
TOTALCOUNTSCORE BIGINT Total count score
EXPECTEDCOUNTSCORE BIGINT Expected count score after which pattern is said to be triggered
COUNTSCORE BIGINT Count score of the event hits
MINCOUNTSCORE BIGINT Minimum count score value
MINROW INT Row indicating the minimum count data for the pattern

UARI_PATTERN_MATCHALL

This table contains the aggregated data for MatchAll Pattern type.

Field Type Length Null? Description
PATTERNID BIGINT Pattern ID
PATTERNSTATEINSTANCEIDS BIGINT Number of pattern state instances
TYPE INT Type-1 for match All type
AUDIENCELEVEL VARCHAR(192) Audience level
NOOFEVENTS BIGINT Number of event hits for that pattern state instance
TOTALEVENTS BIGINT Total events in the pattern

UARI_PATTERN_WC

This table contains the aggregated data for Weighted Counter Pattern type.

Field Type Length Null? Description
PATTERNID BIGINT Pattern ID
PATTERNSTATEINSTANCEIDS BIGINT Number of pattern state instances
TYPE INT Type-3 for weighted counter type
AUDIENCELEVEL VARCHAR(192) Audience level
TOTALCOUNTSCORE BIGINT Total count score
EXPECTEDCOUNTSCORE BIGINT Expected count score after which pattern is said to be triggered
COUNTSCORE BIGINT Count score of the event hits

UARI_PATTERN_EVENTHITS

This table contains the aggregated data for Event Hits for the Patterns.

Field Type Length Null? Description
PATTERNID BIGINT Pattern ID
EVENTID BIGINT Event ID
TYPE INT Type may be one of the following:
  • 1-MatchAll
  • 2-Counter
  • 3-Weighted Counter
AUDIENCELEVEL VARCHAR(192) Audience level
EVENTNAME VARCHAR(192) Event Name
EVENTWEIGHT BIGINT Event weight
EVENTHITS BIGINT Event hits for the event

UARI_PROCESSED_PATTERNS

This table tracks the processing of patterns and stores the processed time for the patterns.

Field Type Length Null? Description
PATTERNID BIGINT Pattern ID
PROCESSEDTIME TIMESTAMP Processed time for the pattern ID
REGENFLAG CHAR(1) Regeneration flag:
  • Y-Yes (process for aggregation)
  • N-No (Do not process for aggregation)
RUNID BIGINT Run ID
PATTERNTYPE VARCHAR(2) Pattern Type:
  • MA-MatchAll
  • C-Counter
  • WC-Weighted Counter
PARALLEL_DEGREE INT Number of patterns to process in particular pattern type at a time
PROCESS_FLAG CHAR(1) Process flag for new/delta patterns for aggregation:
  • N-New
  • U-updated
  • D-deleted

UARI_DELTA_PATTERNS

This table is updated by ETL table with the update/delete flag for modified patterns, and is used for identifying delta patterns.

Field Type Length Null? Description
PATTERNID BIGINT Pattern ID
UPDATEFLAG CHAR(1) Update Flag:
  • U-updated
  • D-deleted
  • P-Processed
PATTERNTYPE VARCHAR(2) Pattern Type:
  • MA-MatchAll
  • C-Counter
  • WC-Weighted Counter

UARI_RUNS

This table contains the runid, which is generated for each new run.

Field Type Length Null? Description
RUNID BIGINT Run ID

UARI_DELTA_REFRESH_LOG

This table logs what has been done for each run. It shows the number of patterns processed. It also captures errors, if any occur during the runs.

Field Type Length Null? Description
RUNID BIGINT Run ID
OBJECT VARCHAR(100) Name of Procedure
MESSAGE_LINE VARCHAR(1000) Appropriate message line for the run
TOTALPATTERNS INT Total number of patterns processed
PATTERNID BIGINT Failed Pattern ID
INSERTTIME TIMESTAMP Record creation time
PARALLEL_DEGREE INT Number of patterns to process in particular pattern type at a time

UARI_RUN_LOG

This table logs all run history from the UARI_Pattern_Lock table.

Field Type Length Null? Description
RUNID BIGINT Run ID
PATTERNTYPE VARCHAR(2) Pattern Type
  • MA-MatchAll
  • C-Counter
  • WC-Weighted Counter
ISLOCK CHAR(1)
  • Y-aggregation is in process/failed
  • N-aggregation completed/not started
STARTTIME TIMESTAMP Start time of aggregation run
ENDTIME TIMESTAMP End time of aggregation run
PARALLEL_DEGREE INT Number of patterns to process in particular pattern type at a time
DBMS_JOBID BIGINT Database job ID

UARI_PATTERNSTATE_INFO

This materialized view is to get the state and audience level information for each pattern for the interactive channel (IC) and category. For Oracle and DB2 only. Because Microsoft SQL Server does not support a materialized view, this view is not included in the Microsoft SQL Server DDL scripts.

Field Type Length Null? Description
PATTERNID INT64 false The ID of this pattern.
PATTERNNAME VARCHAR 64 false The name of the pattern. Can be any text characters, minus standard disallowed special name characters.
CATEGORYID INT64 false The unique ID of the category that contains the pattern. Must be a globally unique positive integer within the internalIDLowerLimit and internalIDUpperLimit configuration parameter values for generated values.
CATEGORYNAME VARCHAR 64 false The name of the category. Can be any text characters, minus standard disallowed special name characters.
ICID INT64 false The unique ID of the Interact Channel to which the pattern belongs. Must be a globally unique positive integer within the internalIDLowerLimit and internalIDUpperLimit configuration parameter values for generated values.
ICNAME VARCHAR true The name of the Interact Channel to which the pattern belongs.
TYPE INT32 true The type of pattern.
STATE INT32 false The current state of the pattern indicated by PatternID, from one of the following values:
  • 1: pattern triggered
  • 0: not triggered
  • -1: expired
  • -2: disabled
AUDIENCELEVEL VARCHAR 128 false The audience level associated with this pattern state instance. Must be a globally unique positive integer within the internalIDLowerLimit and internalIDUpperLimit configuration parameter values for generated values

UARI_PATTERN_INFO

This view is to get the state and audience level information, along with the processed time of the aggregation process.

Field Type Length Null? Description
PATTERNID INT64 false The ID of this pattern.
PATTERNNAME VARCHAR 64 false The name of the pattern. Can be any text characters, minus standard disallowed special name characters.
CATEGORYID INT64 false Unique ID of the category that contains the pattern. Must be a globally unique positive integer within the internalIDLowerLimit and internalIDUpperLimit configuration parameter values for generated values.
CATEGORYNAME VARCHAR 64 false Name of the category. Can be any text characters, minus standard disallowed special name characters.
ICID INT64 false The unique ID of the Interact Channel to which the pattern belongs. Must be a globally unique positive integer within the internalIDLowerLimit and internalIDUpperLimit configuration parameter values for generated values.
ICNAME VARCHAR 64 false The name of the Interact Channel to which the pattern belongs.
TYPE INT32 true The type of pattern.
STATE INT32 false The current state of the pattern indicated by PatternID, from one of the following values:
  • 1: pattern triggered
  • 0: not triggered
  • -1: expired
  • -2: disabled
AUDIENCELEVEL VARCHAR 128 false The audience level associated with this pattern state instance. Must be a globally unique positive integer within the internalIDLowerLimit and internalIDUpperLimit configuration parameter values for generated values.
PROCESSEDTIME TIMESTAMP The time this entry was processed by the pattern state ETL.

UARI_PATTERN_LOCK

This table is to ensure that there is only one run at a time in the system, and to track the parallel degree for aggregation process.

Field Type Length Null? Description
RUNID BIGINT Run ID
PATTERNTYPE VARCHAR(2) Pattern Type:
  • MA-MatchAll
  • C-Counter
  • WC-Weighted Counter
ISLOCK CHAR(1)
  • Y-aggregation is in process/failed
  • N-aggregation completed/not started
STARTTIME TIMESTAMP Start time of the aggregation run.
ENDTIME TIMESTAMP End time of the aggregation run.
PARALLEL_DEGREE INT Number of patterns to process in particular pattern type at a time.
DBMS_JOBID BIGINT If this field contains the database job ID, it is to check the status of DB jobs.

UARI_TRIGGER_STATE

This view is used to find the current trigger state.

Field Type Length Null? Description
TRIG_CONF
  • 0-Trigger Valid
  • 1-Trigger Invalid