Reporting Guide for Cisco Unified Customer Voice Portal Release 4.1(1) Updated: December 2008 Americas Headquarters Cisco Systems, Inc. 170 West Tasman Drive San Jose, CA 95134-1706 USA http://www.cisco.
THE SPECIFICATIONS AND INFORMATION REGARDING THE PRODUCTS IN THIS MANUAL ARE SUBJECT TO CHANGE WITHOUT NOTICE. ALL STATEMENTS, INFORMATION, AND RECOMMENDATIONS IN THIS MANUAL ARE BELIEVED TO BE ACCURATE BUT ARE PRESENTED WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED. USERS MUST TAKE FULL RESPONSIBILITY FOR THEIR APPLICATION OF ANY PRODUCTS.
Table of Contents Preface ...........................................................................................................................................................1 Purpose .....................................................................................................................................................1 Audience ....................................................................................................................................................
Adding a VXML Server to the Operations Console..................................................................................43 Adding a VXML Server........................................................................................................................43 Editing a VXML Server in the Operations Console...................................................................................45 Editing a VXML Server....................................................................................
Unified CVP and Unified ICME Report................................................................................................77 Running Crystal Reports Templates....................................................................................................78 7. Reporting Best Practices..........................................................................................................................81 CPU Intensive Reports.....................................................................
List of Figures Figure 1: CVP Architecture...............................................................................................................................................6 Figure 2: Call Flow..........................................................................................................................................................54 Figure 3: ER Diagram.................................................................................................................................
Preface Purpose This document provides information to help you configure and manage the Reporting Server. Audience This guide is intended for Call Center managers, Unified Customer Voice Portal (CVP) system managers, Cisco Unified Intelligent Contact Management Enterprise (ICME) and Cisco Network Application Manager (NAM) system managers, VoIP technical experts, and IVR application developers. Readers of this guide should already have a general understanding of Unified CVP software.
Preface Related Documentation Chapter Description Chapter 5, "Introduction to the Database Schema" (page 53) Provides information about the database schema. Chapter 6, "Reporting Against the Database" (page 75) Discusses generating reports. Chapter 7, "Reporting Best Practices" (page 81) Provides a list of best practices.
Preface Conventions For additional information about Unified ICME, see the Cisco web site (http://www.cisco.com/ en/US/products/sw/custcosw/ps1001/tsd_products_support_series_home.html) listing Unified ICME documentation. Conventions This manual uses the following conventions: Convention Description boldface font Boldface font is used to indicate commands, such as user entries, keys, buttons, and folder and submenu names. For example: • Choose Edit > Find. • Click Finish.
Preface Obtaining Documentation, Obtaining Support, and Security Guidelines Obtaining Documentation, Obtaining Support, and Security Guidelines For information on obtaining documentation, obtaining support, providing documentation feedback, security guidelines, and also recommended aliases and general Cisco documents, see the monthly What's New in Cisco Product Documentation, which also lists all new and revised Cisco technical documentation, at: http://www.cisco.com/en/US/docs/general/whatsnew/whatsnew.
Chapter 1 Introduction to the Reporting Server This section contains the following topics: • Overview, page 5 • How the Reporting Server Functions, page 5 Overview The Reporting Server houses the Reporting Service, and hosts an IBM Informix Dynamic Server (IDS) database management system. The Reporting Service provides historical reporting to a distributed self-service deployment in a call center environment.
Chapter 1: - Introduction to the Reporting Server How the Reporting Server Functions Figure 1: CVP Architecture Note: • The Reporting Service and the Database are separated merely for functional clarity. Together they comprise the Reporting Server. • The connection of the Operations Console to the Call Server, through an OAMP Resource Manager (ORM) is simply indicative. An ORM is co-located with each managed Unified CVP component, and the Operations Console is connected to each component.
Chapter 1: Introduction to the Reporting Server How the Reporting Server Functions A third-party reporting engine, such as Crystal Reports, can be used to generate various reports against the predefined Unified CVP database schema. Unified CVP provides a few sample Crystal report templates to demonstrate how to use Unified CVP data stored in Unified CVP database tables. See Chapter 6, "Reporting Against the Database" (page 75) for a description of the templates.
Chapter 1: - Introduction to the Reporting Server How the Reporting Server Functions Reporting Guide for Cisco Unified Customer Voice Portal Release 4.
Chapter 2 Managing the Database The Reporting Service does not itself perform database administrative and maintenance activities such as backups or purges. However, Unified CVP provides access to such maintenance tasks through the Operations Console. Caution: The management of database and reporting users must be done using the Operations Console. This ensures that all dependencies are synchronized.
Chapter 2: - Managing the Database Data Retention • • • • • • Data Categories, page 12 Database Backup, page 12 Retries: Backup and Purge, page 13 Database Recovery, page 14 Database Users, page 15 Failure and Restoration, page 16 Data Retention Via the Operations Console, users are able to select the time of day to run database purge, and to set the number of days of data to be retained by data category. During schema creation, default data retention values are specified for each data category.
Chapter 2: Managing the Database This page is intentionally left blank. Reporting Guide for Cisco Unified Customer Voice Portal Release 4.
Chapter 2: - Managing the Database Data Categories Data Categories The following data categories exist for Unified CVP. Note that a high level category, such as Call, cannot have a lower retention time than a dependent category, such as CallEvent. For each category, the default data retention times, in days, is given within parentheses.
Chapter 2: Managing the Database Retries: Backup and Purge Unified CVP uses the Informix backup utility ontape (for both backup and restore). Unified CVP names backup files as follows. When a new backup launches—either scheduled, or on demand from the Operations Console—the new file is named cvp_backup_data. The Unified CVP backup script has been written to first copy the last backup file cvp_backup_data to cvp_backup_data.old.
Chapter 2: - Managing the Database Database Recovery Database Recovery Unified CVP database recovery returns the database to the state of the most recent complete backup. For example, if the user schedules a backup at 01:00 and restores the database at 23:00, the same day, the restored database is in the state it was in at 01:00.
Chapter 2: Managing the Database Database Users b. If it returns 'IBM Informix Dynamic Server Version 10.00.xxx -- Quiescent…', change the database to multiuser mode by typing: onmode -m then wait 2 minutes, and recheck the status by typing: onstat – c. If it returns 'IBM Informix Dynamic Server Version 10.00.xxx -- On-Line', the database is ready to support the Unified CVP application Database Users Unified CVP defines four categories of database users.
Chapter 2: - Managing the Database Failure and Restoration Failure and Restoration • If the Reporting Server fails, messages destined for the Reporting Server are buffered by the Call Server, in memory, up to 200,000 messages. After that limit is reached, all new messages are dropped. • If the database connection fails, the Reporting Server sends out an SNMP alert and starts persisting messages to a file, up to a user specified limit. During this time the Reporting Server stays In Service.
Chapter 3 Configuring the Reporting Server You can configure and manage the Reporting Server using the Unified CVP Operations Console, a web-based interface from which you can configure the Unified CVP components in the Unified CVP solution. From the Device Management menu, Reporting Server option, you can configure one or more Reporting Servers. Reporting provides historical reporting to a distributed self-service deployment in a call center.
Chapter 3: - Configuring the Reporting Server Adding a Reporting Server to the Operations Console SIP, H.323, and VoiceXML calls handled by the Call Servers is stored in the Reporting Database. You can also add the server to one or more logical groups of devices, called device pools. Adding a Reporting Server Create a new Reporting Server either by using an existing Reporting Server configuration as a template or by filling in its values from scratch.
Chapter 3: Configuring the Reporting Server Adding a Reporting Server to the Operations Console Step 7 Optionally, select the Infrastructure tab and configure log file and syslog settings. Step 8 When you finish configuring the Reporting Server: a. click Save to save the settings in the Operations Server database b.
Chapter 3: - Configuring the Reporting Server Adding a Reporting Server to the Operations Console Field Description Default Range You must configure secure communications before you enable this option. See Chapter 6 in the Configuration and Administration Guide for Unified Customer Voice Portal. Associate Call Servers A given Call Server can only be associated with one Reporting Server. Select one or more Call Servers to associate None with the Reporting Server.
Chapter 3: Configuring the Reporting Server Adding a Reporting Server to the Operations Console Configuring Reporting Server Infrastructure Settings The Reporting Server publishes statistics on the total number of reporting events received from the VXML Server, the SIP Service, and the IVR Service. It also publishes the total number of times the Reporting Server writes data to the Reporting database.
Chapter 3: - Configuring the Reporting Server Editing a Reporting Server in the Operations Console Field Description Default Range numbers are integers between 1 and 65535. Backup Server Name of a backup server to which the Reporting None Server writes log messages. Not applicable Backup Server Port Number Port number of the backup Syslog server. Any available port number. Valid port numbers are integers between 1 and 65535.
Chapter 3: Configuring the Reporting Server Editing a Reporting Server in the Operations Console Step 1 Choose Device Management > CVP Reporting Server. The Find, Add, Delete, Edit Reporting Servers window opens. Step 2 Select a Reporting Server by clicking on the link in its name field or by clicking the radio button preceding it and then clicking Edit. The Edit Reporting Server Configuration window opens.
Chapter 3: - Configuring the Reporting Server Editing a Reporting Server in the Operations Console Changing a Reporting Database User Password The Unified CVP installation procedure creates the following three user accounts and sets an initial password for each account. You can change passwords from the Reporting Server screen in edit mode, but you can only change one user password at a time. • Informix User (Instance Owner) - Starts and stops the Reporting database, using Informix tools.
Chapter 3: Configuring the Reporting Server Editing a Reporting Server in the Operations Console See Also Running Reporting Database Backups (page 27) Configuring Reporting Database Purge (page 29) Managing Reporting Users (page 25) Guidelines for Choosing Secure Passwords (page 32) Viewing Reporting Database Details (page 31) Getting Reporting Server Statistics (page 39) Managing Reporting Users The Unified CVP Database Administrator should create reporting users to run reports against the Reporting data
Chapter 3: - Configuring the Reporting Server Editing a Reporting Server in the Operations Console Step 9 Click Add to add the user. See Also Changing Reporting User Passwords (page 26) Removing Reporting Users (page 27) Changing Reporting Database User Passwords (page 23) Finding a Reporting Server (page 40) Changing a Reporting User's Password To change a reporting user's password: Step 1 Choose Device Management > CVP Reporting Server. The Find, Add, Delete, Edit Reporting Servers window opens.
Chapter 3: Configuring the Reporting Server Editing a Reporting Server in the Operations Console Changing Reporting User Passwords (page 26) Removing Reporting Users (page 27) Adding New Reporting Users (page 25) Changing Reporting Database User Passwords (page 23) Finding a Reporting Server (page 40) Removing Reporting Users To remove a reporting user from the Reporting Server: Step 1 Choose Device Management > Reporting Server. The Find, Add, Delete, Edit Reporting Server window opens.
Chapter 3: - Configuring the Reporting Server Editing a Reporting Server in the Operations Console to the Reporting Server's local file system. You are responsible for managing backed-up files. Scheduled backups occur once each day. You can configure the time of day at which backups occur. A maximum of two backups and a minimum of one backup will be available at any time on the local machine. Procedure To run a reporting database backup: Step 1 Choose Device Management > CVP Reporting Server.
Chapter 3: Configuring the Reporting Server Editing a Reporting Server in the Operations Console Step 1 Choose Device Management > CVP Reporting Server. The Find, Add, Delete, Edit Reporting Servers window opens. Step 2 Select a Reporting Server by clicking on the link in its name field or by clicking the radio button preceding it and then clicking Edit. The Edit Reporting Server Configuration window opens with the current settings displayed.
Chapter 3: - Configuring the Reporting Server Editing a Reporting Server in the Operations Console Step 3 Select the Database Administration menu in the toolbar, then select Data Purge. The Reporting Server - Database Purge Settings page opens, displaying the IP address and host name for the currently selected Reporting Server. Step 4 In the Data Purge section of the page, you can change the data retention time for each category of data. Step 5 Select the hours and minutes to run the purge each day.
Chapter 3: Configuring the Reporting Server Editing a Reporting Server in the Operations Console Data Category Description Default VoiceXML Element A VoiceXML element is a distinct component of a voice application call 15 flow whose actions affect the experience of the caller. A VoiceXML element contains detailed script activity to the element level, such as, Call Identifiers, activity time stamp, VoiceXML script name, name and type of the VoiceXML element, and event type.
Chapter 3: - Configuring the Reporting Server Editing a Reporting Server in the Operations Console Reporting Database Details: • Database Name - Name of the database • Total Size (MB) - Total data size • Free size (MB) - Amount of space that has not been taken by extents • Used Size (MB) - Data space used • Extent size (MB) - Space reserved for tables. This size may be greater than the total size. • % Free Size - The percent of space that has not been extended (reserved). This might be greater than 100%.
Chapter 3: Configuring the Reporting Server Editing a Reporting Server in the Operations Console – Digits (0123456789) – The following special characters: !"#$%&'()*+,-./ :;<=>?@ [\]^_` {|}~ • No character in the password can be repeated more than three (3) times consecutively. • Password must not repeat or reverse username. Password is not "cisco", "ocsic", or any variant obtained by changing the capitalization of letters therein.
Chapter 3: - Configuring the Reporting Server Editing a Reporting Server in the Operations Console See Also Downloading a Log Messages XML File (page 33) Editing the Log Messsages XML File (page 35) Bulk Administration File Transfer (page 50) Downloading a Log Messages XML File You can download a Log Messages XML file, CVPLogMessages.xml, to your local machine from any Unified CVP server. After downloading the file, you can edit it to configure the way Unified CVP event notifications are handled.
Chapter 3: Configuring the Reporting Server Editing a Reporting Server in the Operations Console Editing the Log Messages XML File The log messages XML file, CVPLogMessages.xml, defines the severity, destination (SNMP management station or Syslog server) and possible resolution for Unified CVP log messages. This file also identifies an event type identifier and message text identifier for each event. The identifiers are stored in the resource properties file, CVPLogMessages.properties.
Chapter 3: - Configuring the Reporting Server Applying a License to a Reporting Server Using the Operations Console Level Severity Purpose EMERGENCY 0 System or service is unusable ALERT 1 Action must be taken immediately CRITICAL 2 Critical condition, similar to ALERT, but not necessarily requiring an immediate action ERROR 3 An error condition that does not necessarily impact the ability of the service to continue to function WARN 4 A warning about a bad condition, which is not necessari
Chapter 3: Configuring the Reporting Server Deleting a Reporting Server From the Operations Console Step 4 Step 5 If the license file is not listed in the Select From Available License Files box: a. Click Select a License File from Your Local PC b.
Chapter 3: - Configuring the Reporting Server Viewing Reporting Statistics Viewing Reporting Statistics The Reporting Server publishes statistics that include the number of events received from the IVR, SIP, and VoiceXML services during an interval and the total events published since the Reporting Server started. Reporting Server Statistics Reporting Server statistics include the total number of events received from the IVR, SIP, and VoiceXML services.
Chapter 3: Configuring the Reporting Server Viewing Reporting Statistics Statistic Description Duration Elapsed The amount of time that has elapsed since the service start time. VXML Events Received The total number of reporting events received from the VoiceXML Service since the service started. For each reporting event received from the VoiceXML Service, this metric will be increased by one.
Chapter 3: - Configuring the Reporting Server Finding a Reporting Server in the Operations Console Managing Reporting Users (page 25) Viewing Reporting Database Details (page 31) Finding a Reporting Server in the Operations Console The Operations Console provides a basic and advanced search feature for locating a specific Reporting Server. Finding a Reporting Server The Operations Console lets you locate a Reporting Server on the basis of specific criteria.
Chapter 3: Configuring the Reporting Server Adding and Removing a Device from a Device Pool Search criteria: • begins with • contains • ends with • is exactly • is empty Step 6 Specify the appropriate search text, if applicable, and click Find. Adding and Removing a Device from a Device Pool Device Pools are logical groupings of devices, for example, SanJose-Gateways.
Chapter 3: - Configuring the Reporting Server Adding and Removing a Device from a Device Pool If: Then: You want to remove a device from Select the device pool from the Selected pane, and then click the left arrow to move the device pool to the a device pool Available pane. Step 5 Click Save. Reporting Guide for Cisco Unified Customer Voice Portal Release 4.
Chapter 4 Configuring the VXML Server for Reporting The VXML Server is a J2EE-compliant application server that provides a complete solution for rapidly creating and deploying dynamic VoiceXML applications. If you installed a VXML Server, you must configure it before using it to deploy VoiceXML applications or licenses.
Chapter 4: - Configuring the VXML Server for Reporting Adding a VXML Server to the Operations Console Do the following before adding a VXML Server to the Operations Console: • Collect the host name or IP address of the VXML Server during the installation of Unified CVP software. • Install and configure at least one Call Server before configuring the VXML Server.
Chapter 4: Configuring the VXML Server for Reporting Editing a VXML Server in the Operations Console Step 9 Shut down and then restart the VXML Server and the primary and backup Call Servers. Editing a VXML Server in the Operations Console You can change the properties of a VXML Server that has been added to the Operations Console. In edit mode, the Statistics, File Transfer, and System Id menus are also available. From the Statistics menu, you can view call statistics published by the VXML Server.
Chapter 4: - Configuring the VXML Server for Reporting Editing a VXML Server in the Operations Console Step 9 Shut down and then restart the VXML Server and—if they have changed—the primary and backup Call Servers. VXML Server General Properties You can configure settings that identify the VXML Server and choose a primary, and optionally, a backup Call Server to communicate with the Reporting Server. You can also enable secure communications between the Operations Console and the VXML Server.
Chapter 4: Configuring the VXML Server for Reporting Editing a VXML Server in the Operations Console VXML Server Configuration Properties From the VXML Server Configuration tab, you can enable reporting of VXML Server script and call activities to the Reporting Server. When enabled, the VXML Server reports on call and application session summary data. Call summary data includes call identifier, start and end timestamp of calls, ANI, and DNIS.
Chapter 4: - Configuring the VXML Server for Reporting Editing a VXML Server in the Operations Console Field Description Default Range Restart/Reboot Needed Note: For more information about filter syntax and rules, see Inclusive and Exclusive VoiceXML Filters for Reporting (page 48). Exclusive Filters List of applications, element types, None element names, and element fields, and ECC variables to exclude from reporting data. A semicolon-separated list Yes - Restart of text strings.
Chapter 4: Configuring the VXML Server for Reporting Editing a VXML Server in the Operations Console or AppName.*.*.SESSION:Varname Note: This syntax is used to indicate session variables. • A semicolon (;) should be used to separate each item in a filter. For example, ElementA ; ElementB is valid. • A single wildcard (*) can be specified anywhere within the application name, element type, element name, or field name.
Chapter 4: - Configuring the VXML Server for Reporting Transferring a File to Multiple Devices Inclusive Filter Exclusive Filter Data the VXML Server Feeds To the Reporting Server Application1.*.*.* *.*.Element1.*; All Application1 data, except Element1, Element2, and Field1 *.*.Element2.*; *.*.*.Field1 *.Voice.*.* which matches Element3 and Element4 All Application1 data, except Element3 and Element4 Application1.*.*.* No data for Application1 *.Voice.*.* which matches *.*.Element3.
Chapter 4: Configuring the VXML Server for Reporting Transferring a File to Multiple Devices one type of device at a time. If you try to transfer a type of file to a device that does not support that file type, an error message is displayed, and the previous settings are cleared. Procedure To transfer a script file: Step 1 Choose Bulk Administration > File Transfer. The File Transfer window opens to the Files tab.
Chapter 4: - Configuring the VXML Server for Reporting Transferring a File to Multiple Devices Step 5 From the Select Device Type drop-down menu, select the type of device to which you want to transfer the selected file. Only devices of the selected type are displayed in the Available Devices box. Step 6 Select a device from the Available Devices box and click the right arrow to move the device to the Selected Devices box.
Chapter 5 Introduction to the Database Schema This section contains the following topics: • About the Database Schema, page 53 • Entity-Relationship Diagram, page 54 • Table Definitions, page 54 About the Database Schema The Reporting Server hosts an Informix Dynamic Server (IDS) database, where it stores reporting data in a defined database schema. The schema is fully published so that customers may develop custom reports based on it, using a customer-provided report presentation product.
Chapter 5: - Introduction to the Database Schema Entity-Relationship Diagram Figure 2: Call Flow Entity-Relationship Diagram The following ER diagram shows the Unified CVP database schema. Figure 3: ER Diagram Table Definitions This section lists all the tables, in alphabetical order. Reporting Guide for Cisco Unified Customer Voice Portal Release 4.
Chapter 5: Introduction to the Database Schema Table Definitions ActionTypeRef Table This is the table to map an ActionTypeID to the type of action for an element that changes data. Field Type Null Index Description ActionTypeID int No Yes (Primary Key) The unique id of an action type Name nvarchar(96) No No The name of the action type Description Table Values (ID, Name): 1, "Initialize" 2, "Update" 3, "Return" Call Table This table contains one record per call.
Chapter 5: - Introduction to the Database Schema Table Definitions Field Type Null Index Description EndDateTime datetime Yes No The date and time a call ended with hang-up or disconnect (but see the Note below) UUI varchar(100) Yes No The UUI of the originating caller sent by telephony provider Iidigits varchar(100) Yes No The IIDIGITS of the originating caller sent by telephony provider UID varchar(50) Yes No The external UID of the caller if the call is associated with a user Nu
Chapter 5: Introduction to the Database Schema Table Definitions CallEvent Table Field Type Null Index Description CallGUID char(35) No Yes (Composite index The global unique id of CallGUID, a call CallStartDate) CallStartDate date No Yes (Composite index Date of the call, for CallGUID, data purging purposes CallStartDate) CallLegID varchar(43) Yes No A call id assigned by a Service MessageBusName varchar(42) No No The name of the Call Server (its message adapter name) with which the
Chapter 5: - Introduction to the Database Schema Table Definitions Field Type Null Index Description RouterCallKey int No Yes ICM Router CallKey RouterCallKeyDay int No Yes ICM RouterCallKeyDay RouterCallKeySequenceNumber int Yes Yes ICM RouterCallKeySequenceNumber EventDateTime datetime No Yes Date and time of the event MessageBusName varchar(42) No No The name of the message bus that delivers the VXML datafeed message DBDateTime datetime No No The date and time of the dat
Chapter 5: Introduction to the Database Schema Table Definitions 13, "Called Party Disconnected" 14, "Max Ports" 15, "Suspended" 16, "Time Out" 17, "Invalidated" 18, "Error" 1001, "Hang Up" 1002, " Network" 1003, "System" 1004, "Script Type" 1005, "Unknown UApp" 1006, "Script Name" 1007, "Config Param" 1008, "Misconfig Ecc" 1009, "Media File" 1010, "Semantic" 1011, "VXML Format" 1012, "VXML Element" 1013, "Variable Data" 1014, "No Var Data" 1015, "Format" 1016, "Entry Invalid" 1017, "No Entry" 1020, "Data
Chapter 5: - Introduction to the Database Schema Table Definitions 1023, "No Answer" 1024, "Busy" 1025, "Gen Transfer" 1026, "Invalid Extn" 1027, "Hang Up Forced" 1028, "After Trans Estab" 1030, "Unsupported Language" 1031, "Media Resource ASR" 1032, "Media Resource TTS" 1033, "General ASR TTS" 1040, "CVP System Unavailable" 1041, "CVP App Error" 1042, "CVP App Hang Up" 1043, "Error CVP App Suspended" 1044, "Error CVP No Session Error" 1045, "Error CVP Bad Fetch" CVPDataRetention Table Field Type Null
Chapter 5: Introduction to the Database Schema Table Definitions Field Type Null Index Description ExtentSpaceUsed float Yes No The extent space used TotalSpaceUsed float Yes No The data plus index space size used in megabytes DataSpaceUsed float Yes No The data space size used in megabytes DailyPurgeLastRunDate datetime Yes No The date and time of last daily run EmergencyPurgeLastRunDate datetime Yes No The date and time of last emergency purge CVPDateTrap Table Field Type N
Chapter 5: - Introduction to the Database Schema Table Definitions Field Type Null Index Description MinorVersion varchar(20) No Yes (Composite index Minor version MajorVersion, MinorVersion) UpdateDateTime datetime No No The date and time when the record is updated Field Type Null Index Description MessageDateTime datetime Yes No Date and time of message Message varchar(255) Yes No Message details MessageClass char(8) Yes Yes Type of message: Add, Aged Off, Alter, Err Info
Chapter 5: Introduction to the Database Schema Table Definitions Field Type Null Index Description DaysAhead int No No Number of days ahead to create partitions; minimum is 1 ContainerSizeLarge int No No Starting size in KB for large sized tables ContainerSizeMedium int No No Starting size in KB for medium sized tables ContainerSizeSmall int No No Starting size in KB for small sized tables Field Type Null Index Description Action char(1) Yes No Action to take: A=Add, D=Drop
Chapter 5: - Introduction to the Database Schema Table Definitions 4, "Decision" 5, "Action" 6, "Custom" 7, "HotLink" 8, "HotEvent" 9, "ElementFlag" 10, "Voice" 11, "VXMLInsert" 12, "ReqICMLabel" 13, "General" EventTypeRef Table This is the table to map an EventID to its name (event type).
Chapter 5: Introduction to the Database Schema Table Definitions 10, "Call Transfer" 11, "Run Script" OutgoingECCVariable Table Field Type Null Index Description CallGUID char(35) No Yes (Composite index The global unique id of CallGUID, a call CallStartDate) CallStartDate date No Yes (second field in Composite indexes) SessionID int8 No Yes (Composite index The identifier of the session in which the SessionID, ECC variable changes CallStartDate) ElementID int8 No Yes (Composite index
Chapter 5: - Introduction to the Database Schema Table Definitions 2, "Invalidated" 3, "HotEvent" 4, "HotLink" 5, "Hang Up" 6, "Error" 7, "Transfer" SubSystemTypeRef Table This is the table to map a SubSystemTypeID to a Unified CVP Service type.
Chapter 5: Introduction to the Database Schema Table Definitions Field Type Null Index Description UserInputModeID int No Yes (Primary Key) The unique id of a user input mode Name nvarchar(96) No No The name of the user input mode Table Values (ID, Name): 1, "DTMF" 2, "Voice" 3, "DTMF Voice" VarDataTypeRef Table This is the table to map a VarDataTypeID to the data type of a variable.
Chapter 5: - Introduction to the Database Schema Table Definitions 2, "No Input" 3, "Audio Group" 4, "Input Mode" 5, "Utterance" 6, "Interpretation" 7, "Confidence" VXMLCustomContent Table This table contains one record for each VoiceXML custom event. This event will occur if a custom component programmatically calls the addToLog method of the Session API. The event will also occur when an element whose configuration contains entries in the Add To Log table in the General tab is run.
Chapter 5: Introduction to the Database Schema Table Definitions Field Type Null Index Description ElementID int8 No Yes (Primary Key, The unique id of a Composite ElementID, visited element CallStartDate) CallStartDate date No Yes (second field in Primary Key and Composite indexes) SessionID int8 No Yes (Composite index The unique id of a VXML application SessionID, session CallStartDate) CallGUID char(35) No Yes The global unique id of a call ElementName nvarchar(51) No No The n
Chapter 5: - Introduction to the Database Schema Table Definitions Field Type Null Index Description CallStartDate date No Yes (Composite index Date of the call, for ElementID, data purging purposes CallStartDate) VarName nvarchar(51) No No The name of the element variable VarValue nvarchar(255) Yes No The String value of the element variable VarDataTypeID int No No The data type of the element variable, such as String, Integer, Boolean ActionTypeID int No No The type of action
Chapter 5: Introduction to the Database Schema Table Definitions VXMLError Table Field Type Null Index Description ElementID int8 No Yes (Composite index The unique id for the element in which the ElementID, error occurs CallStartDate) CallStartDate date No Yes (Composite index Date of the call, for ElementID, data purging purposes CallStartDate) ErrorName varchar(12) No No Name of an error EventDateTime datetime No Yes The date and time when the error occurred Description nvarchar(
Chapter 5: - Introduction to the Database Schema Table Definitions VXMLHotLink Table Field Type Null Index Description ElementID int8 No Yes (Composite index The unique id for the element in which the ElementID, hotlink activated CallStartDate) CallStartDate date No Yes (Composite index Date of the call, for ElementID, data purging purposes CallStartDate) EventDateTime datetime No Yes The date and time when the hot link activated Name nvarchar(51) No No The name of the hot link Messa
Chapter 5: Introduction to the Database Schema Table Definitions Field Type Null Index Description EventTypeID int Yes No The mechanism used to end the application visit CauseID int Yes No The reason that the application visit ended EndDateTime datetime Yes No The end date and time of the session SourceAppName nvarchar(51) Yes No The name of the application that transferred to this one LocalTimeZone int Yes No The local time zone identifier Duration int Yes No The duration
Chapter 5: - Introduction to the Database Schema Table Definitions Field Type Null Index Description ActionTypeID int No No The type of action for a session variable that changes data EventDateTime datetime No Yes Date and time when the session variable changed VarDataTypeID int No No The data type of the session variable, such as Integer, String, Boolean MessageBusName varchar(42) No No The name of the message bus that delivers the VXML datafeed message DBDateTime datetime No N
Chapter 6 Reporting Against the Database Unified CVP does not provide a native reporting engine. A third-party reporting engine, such as Crystal Reports, can be used to generate various reports against the predefined Unified CVP database schema. Unified CVP does provide, as examples, a few sample report templates. This chapter discusses the various sample report templates.
Chapter 6: - Reporting Against the Database Using the Cisco-Provided Sample Report Templates Unified CVP Call Summary Report The Reporting Service captures and presents detailed data on every call made to the Call Server.
Chapter 6: Reporting Against the Database Using the Cisco-Provided Sample Report Templates Call Num CallGUID Start Date Time 9 2E2D8EB6- 11/30/2006 0:40:54 7F6C11DB900F00146944B962 Call ANI Duration 50 DNIS 191191191 3110 Num Num Time Out Errors Num Apps Visited Total Transfers 0 1 0 0 Unified CVP Application Summary Report To measure the overall effectiveness of various self-service applications, the application summary report displays the core application data.
Chapter 6: - Reporting Against the Database Using the Cisco-Provided Sample Report Templates The report discussed here is designed to only return data from one call at a time. The user is prompted to enter the RouterCallKey and the RouterCallKeyDay when launching the report. All of the Unified ICME data originates from the Termination_Call_Detail table.
Chapter 6: Reporting Against the Database Using the Cisco-Provided Sample Report Templates – Create New Connection, select ODBC – Select the Unified CVP connection that you configured in Step 3, and click Next.
Chapter 6: - Reporting Against the Database Using the Cisco-Provided Sample Report Templates Reporting Guide for Cisco Unified Customer Voice Portal Release 4.
Chapter 7 Reporting Best Practices This section contains the following topics: • • • • • • • • • • • • • • • • CPU Intensive Reports, page 81 Filtering Data to be Stored in the Database, page 82 ECC Variable Security, page 82 Writing Efficient SQL when Creating Reports, page 83 Database Sizing Issues, page 83 Database Backup and Recovery, page 84 Only Use Reporting Users when Querying the Database, page 84 Informix, Operating System Time, and Local Time, page 84 Assuring Accurate Time Stamps for Reporting
Chapter 7: - Reporting Best Practices Filtering Data to be Stored in the Database Filtering Data to be Stored in the Database Users can reduce the data generated by means of data filters (for VXML Server application detail data filtering, see Chapter 4, "Configuring the VXML Server for Reporting" (page 43)). Either adding more exclusive filters, or using fewer inclusive filters, would cut down on the amount of data stored.
Chapter 7: Reporting Best Practices Writing Efficient SQL when Creating Reports Writing Efficient SQL when Creating Reports The following guidelines should be kept in mind. • When writing SQL, developers must organize their WHERE clauses and put the most important join first. The most important join is the one that will reduce the size of the dataset to the least amount of rows. • Reports must be written so that every field in the WHERE and ORDER BY clauses uses an indexed field.
Chapter 7: - Reporting Best Practices Database Backup and Recovery Database Backup and Recovery Issues to keep in mind are: • Managing your backup strategy • Turning off Reporting Server when doing database recovery These are discussed in "Database Backup" (page 12) and "Database Recovery" (page 14). Only Use Reporting Users when Querying the Database In the interests of security, allow only reporting users to generate reports.
Chapter 7: Reporting Best Practices Joining Data with an ICM HDS Database For each aspect of the password, the Reporting password must meet the requirement of the more restrictive policy. Joining Data with an ICM HDS Database Joining Unified CVP and Unified ICME data can be a very expensive operation on the Unified ICME system.
Chapter 7: - Reporting Best Practices Zero Duration Calls and Writing Reports Zero Duration Calls and Writing Reports On occasion, messages are dropped—even for an otherwise successful call. In such cases, EndDateTime is set to the same value as StartDateTime. Thus, if a call appears to be of 0 duration, report writers will know to exclude such a call from consideration in cases where it would otherwise skew metrics. Reporting Guide for Cisco Unified Customer Voice Portal Release 4.
Index Reporting Server reporting properties....20 Index VXML Server....47 VXML Server properties....46 ActionTypeRef table....55 CPU intensive reports....81 analytics engine....53 Crystal Reports....7, 53, 75, 78 application summary report....77 cvp_dbadmin....15 application user....15 cvp_dbuser....15 backups....5, 9, 12, 84, 85 CVPDataRetention table....61 cancelling for Reporting Server....28 CVPDateTrap table....61 retries....13 CVPDBSpaceUsed table....61 running for Reporting Server..
Index default number of days....30 destination log messages....35 device pools adding or removing a device....41 device statistics Reporting Server....39 ECC variable....65, 82 join report....77, 85 license files transferring multiple files....51 log messages CVPLogMessages.xml....35 Operations Console....9 OutgoingECCVariable table....65 passwords....9 ElementTypeRef table....63 changing reporting database user....24 emergency purge (see purging)....10 policies....84 Entity-Relationship diagram....
Index failure....16 of events....35 finding....40 SIP Service....6 general configuration settings....19 sizing....83, 85 infrastructure settings....21 SQL prerequisites for adding....18 prerequisites for deleting....37 removing reporting users....27 writing....83 statistics Reporting Server....38 running backups....27 SubSystemTypeRef table....66 running purge....29 templates....75 statistics....38 time uploading Log Messages XMLfile....33 local....84 viewing database details....
Index VXMLCustomContent table....68 VXML element by call report....77 VXMLElementDetail table....69 VXMLElementFlag table....70 VXMLElement table....68 VXMLError table....71 VXML filters example wilcards....49 rules....48 VXML Filters example inclusive and exclusive....49 VXMLHotEvent table....71 VXMLHotLink table....72 VXML Server....6 adding....44 configuration properties....47 configuring for reporting....43 creating reporting filters for....47 downloading Log Messages XML file....34 editing....
