Extended table

The extended table (t_calllog_extended) contains custom entries written to the call log files from the application. See your Nuance speech products documentation.

You can generate reports based on these fields via the t_calllog_extended table. This table is expensive to query directly, because the user-defined key/value pairs can span over multiple rows for the same utterance. You must perform self-joins and then join with other tables to access the information for the utterance to generate a custom report. This can become very expensive in terms of query performance.

Nuance recommends the following approach for generating reports from information in the t_calllog_extended table:

Create a new table to organize the user-defined key/value pairs. For example, create a table called usr.
In the usr table, create a column with the id for the utterance, the id for the call against which the utterance occurred, and a column for each user-defined key. For example, if you have two user-defined keys, empl and dept, your table might look like something this:
utt_id
call_id
empl
dept
1
2296
1001
104
2
...
...
...
By organizing the table this way, you eliminate the need to perform self-joins, which you would need to do if you queried the t_calllog_extended table directly.
To determine the maximum utterance identifier in the table, use the following query:
```
SELECT MAX(utt_id) FROM usr;
```
Note: The usr table will not have any contents if no call log data has been imported yet. In this case you do not need to perform this step.
Get the contents from the t_calllog_extended table using the query below, and insert the contents into your usr table:
```
INSERT INTO usr
```
```
SELECT E1.utterance_id, E1.log_val, E2.log_val
```
```
FROM t_calllog_extended E1, t_calllog_extended E2
```
```
WHERE
```
```
   E1.utterance_id > utt_id 
```
```
   AND E1.utterance_id = E2.utterance_id 
```
```
   AND E1.call_id = E2.call_id
```
```
   AND E1.log_key = 'empl'
```
```
   AND E2.log_key = 'dept';
```
This populates the usr table with the user-defined key/value pairs for all imported call logs. To ensure you have all the current information since the last call log import, use the maximum utt_idvalue obtained above.

utt_id	call_id	empl	dept
1	2296	1001	104
2	...	...	...

You can now use the usr table to perform queries to generate reports. This enhances performance over directly accessing the t_calllog_extended table directly.

Remember that the contents of the usr table must be refreshed each time a call log import is performed, to transfer the newly imported log information into your table. Repeat steps 3 and 4 (above) after each call log import task to ensure you have the latest contents. You may want to create a script to do this automatically.

Extended table

Related topics