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.

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;