Summarized call log data
The Management Station has a built-in data summarization feature that periodically transforms detailed call log data into hourly sums and stores the results in new tables. (See How the system optimizes reports with summarization.) Reports generated with summarized data produce the same results as with detailed data. The feature, which is transparent to the user, has several advantages:
- Report generation is very fast. The amount of time to generate a report is proportional to the amount of data to be queried. A report that might take an hour with detailed records takes just a few seconds with summarized data.
- Summarized data takes up far less disk space that detailed data and can be kept in the database. Because the same reports are generated with this data as with detailed data, you can archive the detailed data offline and purge it from the Management Station database.
- CPU and disk consumption are minimal.
Schema overview
This figure shows the tables that store the contents of summarized data. Each table showed in this schema (except the t_calendar table) is prefixed with the string t_calllog_. For example, the table shown here as calls_hour is fully titled t_calllog_calls_hour.
t_calllog_calls_hour
The t_calllog_calls_hour table stores values and hourly sums required for a Call Volume report.
|
Field name |
Description |
Type |
Constraints |
|---|---|---|---|
|
app_id (Primary keys: app_id, local_url, time_key, host_name, tenant_id, group_id) |
Reference to the application (listed in t_calllog_apps) that handled the call. The company information is implied by app_id. |
int(11) |
NOT NULL |
|
local_url (Primary keys: app_id, local_url, time_key, host_name, tenant_id, group_id) |
Number dialed by the caller to initiate this call. |
varchar(255) |
NOT NULL |
|
time_key (Primary keys: app_id, local_url, time_key, host_name, tenant_id, group_id) |
Time of each hourly interval. References a t_calendar record. For example, 2010071516 corresponds to July 15, 2010, 16:00. |
int(11) |
NOT NULL |
|
volume |
Hourly call volume, as indicated by time_key. |
int(11) |
NOT NULL |
|
duration |
Hourly call duration, as indicated by time_key. The average is calculated by dividing this value by the hourly call volume. |
double |
NOT NULL |
|
host_name (Primary keys: app_id, local_url, time_key, host_name, tenant_id, group_id) |
Identifies the host where log files are collected and imported from. |
varchar(64) |
NOT NULL |
|
tenant_id (Primary keys: app_id, local_url, time_key, host_name, tenant_id, group_id) |
Name of the tenant as present in the t_calllog_calls table. |
varchar(15) |
NOT NULL |
|
group_id (Primary keys: app_id, local_url, time_key, host_name, tenant_id, group_id) |
Required for log merge task. |
int(11) |
NOT NULL |
t_calllog_hits_hour
The t_calllog_hits_hour table stores values and hourly sums required for a Hotspot or Voice Site Hits report.
Note: Because of a MySQL restriction, the sum of the primary keys in a table cannot exceed a certain number of bytes. For this reason, grammar_label is truncated at 150 bytes.
|
Field name |
Description |
Type |
Constraints |
|---|---|---|---|
|
app_id (Primary keys: |
Reference to the application (listed in t_calllog_apps) that handled the call. The company information is implied by app_id. |
int(11) |
NOT NULL |
|
local_url |
Number dialed by the caller to initiate this call. |
varchar(128) |
NOT NULL |
|
grammar_label (Primary keys: |
Included for just-in-time (JIT) grammars. The value of the Nuance-Grammar-Label field in the JIT header. If this field is logged, recognition is performed and a status is available. See your Nuance speech products documentation. |
varchar(150) |
NOT NULL |
|
status (Primary keys: |
Status of the recognition operation, returned in the recognition result. This field is available only when grammar_label is logged. |
varchar(64) |
NOT NULL |
|
time_key (Primary keys: |
Time of each hourly interval. References a t_calendar record. For example, 2012071516 corresponds to July 15, 2012, 16:00. |
int(11) |
NOT NULL |
|
total |
Number of occurrences of each status per hour (as indicated by time_key) per dialog state (as indicated by grammar_label). For example, the row: 1,GetPizzaSize,RECOGNITION,2012071500,23 means that for ID 1 in the application, there were 23 occurrences of the RECOGNITION status at the GetPizzaSize dialog state on July 15, 2012, between midnight and 1 a.m. |
int(11) |
|
|
host_name (Primary keys: |
Identifies the host where log files are collected and imported from. |
varchar(64) |
NOT NULL |
|
tenant_id (Primary keys: |
Name of the tenant as present in the t_calllog_calls table. |
varchar(15) |
NOT NULL |
|
group_id (Primary keys: |
Required for log merge task. |
int(11) |
NOT NULL |
t_calllog_tasks_hour
The t_calllog_tasks_hour table stores values and hourly sums required for the Task Completion and Task Duration reports.
|
Field name |
Description |
Type |
Constraints |
|---|---|---|---|
|
app_id (Primary keys: app_id, name, time_key, host_name, tenant_id, group_id) |
Reference to the application (listed in t_calllog_apps) that handled the call. The company information is implied by app_id. |
int(11) |
NOT NULL |
|
name (Primary keys: app_id, name, time_key, host_name, tenant_id, group_id) |
Name assigned to the task. |
varchar(64) |
NOT NULL |
|
time_key (Primary keys: app_id, name, time_key, host_name, tenant_id, group_id) |
The time of each hourly interval. References a t_calendar record. For example, 2012071516 corresponds to July 15, 2012, 16:00. |
int(11) |
NOT NULL |
|
attempted |
Total number of tasks started per hour (even if task end is not reached). |
int(11) |
|
|
success |
Not currently used in Task Completion report. Successes are stored in the result field of t_calllog_tasks_reason. |
int(11) |
|
|
failed |
Not currently used in Task Completion report. Failures are stored in the result field of t_calllog_tasks_reason. |
int(11) |
|
|
host_name (Primary keys: app_id, name, time_key, host_name, tenant_id, group_id) |
Identifies the host where log files are collected and imported from. |
varchar(64) |
NOT NULL |
|
tenant_id (Primary keys: app_id, name, time_key, host_name, tenant_id, group_id) |
Name of the tenant as present in the t_calllog_calls table. |
varchar(15) |
NOT NULL |
|
completed |
Total number of completed tasks per hour. Completed tasks reach task end. |
int(11) |
|
|
duration |
Average amount of time it took for tasks to complete, grouped by hour. |
int(11) |
|
|
max_duration |
Maximum amount of time it took for tasks to complete, grouped per hour. |
int(11) |
|
|
group_id (Primary keys: app_id, name, time_key, host_name, tenant_id, group_id) |
Required for log merge task. |
int(11) |
NOT NULL |
t_calllog_tasks_reason_hour
The t_calllog_tasks_reason_hour table stores the results for completed tasks in a Task Completion report. The results can be success, failure, unknown, or a detailed reason if specified in the application.
|
Field name |
Description |
Type |
Constraints |
|---|---|---|---|
|
app_id (Primary keys: app_id, name, reason, time_key, host_name, status, tenant_id, result, group_id) |
Reference to the application (listed in t_calllog_apps) that handled the call. ‘ The company information is implied by app_id. |
int(11) |
NOT NULL |
|
name (Primary keys: app_id, name, reason, time_key, host_name, status, tenant_id, result, group_id) |
Name assigned to the task. |
varchar(64) |
NOT NULL |
|
reason (Primary keys: app_id, name, reason, time_key, host_name, status, tenant_id, result, group_id) |
Details, if provided in the application, for the specific task reason. |
varchar(180) |
NOT NULL |
|
time_key (Primary keys: app_id, name, reason, time_key, host_name, status, tenant_id, result, group_id) |
The time of each hourly interval. References a t_calendar record. For example, 2012071516 corresponds to July 15, 2012, 16:00. |
int(11) |
NOT NULL |
|
total (Primary keys: app_id, name, reason, time_key, host_name, status, tenant_id, result, group_id) |
The total number of reasons per hour. |
int(11) |
|
|
host_name (Primary keys: app_id, name, reason, time_key, host_name, status, tenant_id, result, group_id) |
Identifies the host where log files are collected and imported from. |
varchar(64) |
NOT NULL |
|
tenant_id (Primary keys: app_id, name, reason, time_key, host_name, status, tenant_id, result, group_id) |
Name of the tenant as present in the t_calllog_calls table. |
varchar(15) |
NOT NULL |
|
status( (Primary keys: app_id, name, reason, time_key, host_name, status, tenant_id, result, group_id) |
Reason for a successful or failed task. |
varchar(45) |
NOT NULL |
|
result (Primary keys: app_id, name, reason, time_key, host_name, status, tenant_id, result, group_id) |
The result of the transaction: SUCC, FAIL and UNKN. Any other result other than the above three is treated as UNKN. |
varchar(10) |
NOT NULL |
|
group_id (Primary keys: app_id, name, reason, time_key, host_name, tenant_id, status, result, group_id) |
Required for log merge task. |
int(11) |
NOT NULL |
Sample queries
This section includes several queries that show how to use the summarized database tables just described.
Call volume report
This query shows how to generate a call volume report:
SELECT t_calendar.year, t_calendar.month,
t_calendar.week_of_year, t_calendar.day, t_calendar.hour,
SUM(t_calllog_calls_hour.volume),
SUM(t_calllog_calls_hour.duration) /
SUM(t_calllog_calls_hour.volume
FROM t_calllog_calls
RIGHT OUTER JOIN t_calendar ON t_calendar.id =
t_calllog_calls_hour.time_key, t_calllog_apps
INNER JOIN t_calllog_users ON t_calllog_apps.user_id =
t_calllog_users.id
WHERE t_calllog_calls_hour.app_id = t_calllog_apps.id
AND t_calllog_apps.name = 'PizzaTalk'
AND t_calllog_users.name = 'Nuance'
AND t_calendar.hour between 0 and 23
AND t_calendar.id between 2012071500 and 2012071599
GROUP BY t_calendar.year, t_calendar.month, t_calendar.day,
t_calendar.hour;
Hotspot report
These queries show how to generate a hotspot report:
/* Produce a list of grammar labels for user to select */
SELECT DISTINCT t_calllog_hits_hour.grammar_label
FROM t_calllog_hits_hour, t_calllog_apps, t_calllog_users
WHERE t_calllog_apps.id=t_calllog_hits_hour.app_id
AND t_calllog_apps.user_id=t_calllog_users.id
AND t_calendar.id=t_calllog_hits_hour.time_key
AND t_calllog_apps.name='PizzaTalk'
AND t_calllog_users.name='Nuance'
AND t_calendar.hour between 0 and 23
AND t_calendar.id between 2012071500 and 2012071599;
/* Use the following query for each grammar label */
SELECT t_calllog_hits_hour.status, SUM(t_calllog_hits_hour.total)
FROM t_calllog_calls_hits_hour
INNER JOIN t_calllog_apps
ON t_calllog_calls.app_id=t_calllog_hits_hour.app_id
INNER JOIN t_calllog_users
ON t_calllog_users.id=t_calllog_apps.user_id
INNER JOIN t_calendar
ON t_calendar.id=t_calllog_hits_hour.time_key
WHERE t_calllog_apps.name='PizzaTalk'
AND t_calllog_users.name='Nuance'
AND t_calllog_hits_hour.grammar_label = 'GetPizzaSize'
AND t_calendar.hour between 0 and 23
AND t_calendar.id between 2012071500 and 2012071599
GROUP BY t_calendar.year, t_calendar.month, t_calendar.day,
t_calendar.hour;
