Unified ICM and Unified CCE software uses
the following types of databases:
The central database that is part of the Central Controller.
The local database on each distributor Administration & Data
Server.
The Historical Data Server (HDS) database on a
distributor Administration & Data Server.
Unified
ICM and Unified CCE software uses information in the central database to determine
how to route each call. This includes information about your telephone system
configuration and routing scripts. The local database contains a copy of the
configuration data and scripts from the central database.
The local
database also contains tables of real-time information that describe activity
at the call centers. (The Central Controller keeps the real-time information in
memory but does not store it in the central database.) This information allows
you to monitor current activity within the system.
Historical
information describing past activity at the call centers and within the Unified
ICM and Unified CCE system is stored in the central database. This information is
also stored in a special HDS database on a distributor Administration &
Data Server at each site. Either the central database or an HDS database is the historical database for an Administration
& Data Server user. You
can access historical information stored in the historical database to produce
reports and screens.
General Concepts
This section gives a brief overview of relational database concepts
and details about how data is generated by the system software.
Tables Columns and
Rows
A database contains
tables of data. A table defines a series of columns or fields. The actual data
is stored as rows or records within each table. Each row contains one value for
each column of the table. For example, Figure 1 shows a table with five
columns. It contains three rows of data.
The data in tables
changes for each system, but the definition of tables and columns does not.
This manual describes the columns of each table; it does not describe the
actual data in table rows.
Table Relationships
Related tables in a database share one or more common fields or
columns. For example, both the Agent and Peripheral tables include the
PeripheralID field. This defines a relationship: each row in the Agent table is
related to the row in the Peripheral table that shares the same PeripheralID
value.
Relationships between tables can be one-to-one or
one-to-many. For example, because one peripheral can be associated with many
agents, the relationship between the Peripheral and Agent tables is
one-to-many. On the other hand, each peripheral has a single peripheral default
route and each peripheral default route belongs to only one peripheral.
Therefore, the relationship between the Peripheral and Peripheral Default Route
tables is one-to-one.
Sometimes a single row might not be
associated with any rows in a related table. For example, it is possible to
define a peripheral with no associated agents. Normally, this would only be a
temporary condition. In some cases, however, the condition might be permanent.
For example, you can define a trunk group but not define the associated
trunks.
Sometimes the natural relationship between two tables
appears to be many-to-many. For example, each agent can be a member of many
skill groups and each skill group can contain many agents. Therefore, the Agent
and Skill Group tables appear to have a many-to-many relationship. However, in
this case, a third table, called a cross-reference table, actually links the
tables so the relationship is actually one-to-many. For example, Figure 2 shows
how the Skill Group Member table acts as a cross-reference table for the Agent
and Skill Group tables.
The Skill Group Member table
contains one record for each member of each skill group. It has one-to-many
relationships with both the Agent table and the Skill Group table. This avoids
a direct many-to-many relationship between the Agent and Skill Group
tables.
Key Fields
One or more fields within a table can form a
key. Keys are the fields that you commonly use to locate specific records. Usually
the fields that make up a key are defined as NOT NULL (meaning they cannot take the
NULL value), but there are many exceptions.
Most tables have a
primary key. For example, the PeripheralID field is the primary key for the
Peripheral table.
An example of a foreign key is the
PeripheralID field in the Agent table. You can use this key to find all agents that
are associated with a specific peripheral.
The Agent table
contains two alternate keys: the EnterpriseName field, and the combination of
the PeripheralID and PeripheralNumber fields. A value for either of these keys
uniquely identifies an agent.
The combination of FirstName and LastName is
an inversion key for the Agent table. While this key value is not necessarily
unique, it is a convenient way to locate specific agents. This table lists the types
of keys and the codes that are used for them in the system database.
Key Type
Code
Description
Primary
key
PK
Consists
of one or more fields that have a unique value for each record in the table.
By default, the primary key is the clustered key for the
table.
Alternate
key
AK
A unique key, other than the primary key,
that you can use to locate a specific record.
Foreign
key
FK
A primary
key from one table that appears in a second table. A foreign key that
establishes a one-to-one relationship is always unique. A foreign key that
establishes a one-to-many relationship is not unique.
Inversion
key
IE
A key that
does not necessarily have a unique value, but can be used to locate a group of
records within the table.
Note
By default, all keys are on the PRIMARY file group for the database. Microsoft
SQL Server always creates the PRIMARY file group as the default file group.
The codes from this table are used to
identify key fields in each table. If a table has more than one key of the same
type, then numbers are attached to the codes. For example, if a table has two
alternate keys, then the fields in the first are "AK1" and the fields in the second
are "AK2."
Each field is marked as either NULL (meaning
that NULL is a valid value) or NOT NULL (meaning that NULL is not valid).
Reserved Fields
Some fields in the database are marked as reserved. This means that
system software or the database manager might use the field, but it has no
external meaning. You must not modify any field marked as
reserved.
Field Applicability
Unless specifically indicated otherwise, table fields apply to both
Unified ICM and Unified CCE.
Data Types
This table
describes the data types used for fields in the Unified ICM and Unified CCE
database.
Unified ICM and Unified CCE
Defined Data Type
MS SQL Server Data Type
Null Option Default
Description
CHANGESTAMP
int
NOT NULL
Consists of one or more
fields that have a unique value for each record in the table.
DBCHAR
char(1)
NOT NULL
Up to 1 character. The
value 1 is the storage size.
DBDATETIME
datetime
datetime
A date and time accurate to
the second. Stored as two four-byte integers (eight bytes total): days before
or since January 1, 1900 and seconds since midnight.
DBFLT4
real
NULL
A four-byte floating-point
value (7-digit precision).
DBFLT8
float
float
An eight-byte
floating-point value (15-digit precision).
DBSMALLDATE
smalldatetime
smalldatetime
A date and time accurate to
the minute. Stored as two unsigned two-byte integers (four bytes total): number
of days since January 1, 1900 and minutes since midnight.
DBINT
int
NULL
A four-byte integer value
between -2,147,483,648 and 2,147,483,647.
DBSMALLINT
smallint
NULL
A two-byte integer value
between -32,768 and 32,767.
DESCRIPTION
varchar(255)
NULL
Up to 255 characters. The
value 255 is the storage size.
DBTINYINT
tinyint
NOT NULL
A one-byte integer value
between 0 and 255.
TELNO
char (10)
NULL
Up to 10 characters. The
value 10 is the storage size.
VNAME32
varchar(32)
varchar(32)
Up to 32 characters. The
value 32 is the storage size.
VTELNO10
varchar(10)
NULL
Up to 10 characters. The
value 10 is the storage size.
VTELNO20
varchar(20)
NULL
Up to 20 characters. The
value 20 is the storage size.
char(n)
char(n)
NULL
Up to n characters. The
value n is the storage size.
varchar(n)
varchar(n)
NULL
Up to n characters. The
value n is the storage size.
image
image
NULL
Up to 2,147,483,647 bytes
of binary data. The storage size is determined by the length of the data.
datetime
datetime
NULL
A date and time accurate to
the second. Stored as two four-byte integers (eight bytes total): days before
or since January 1, 1900 and seconds since midnight.
smalldatetime
smalldatetime
NULL
A date and time accurate to
the minute. Stored as two unsigned two-byte integers (four bytes total): number
of days since January 1, 1900 and minutes since midnight.
Real-Time and
Historical Data
Unified ICM and
Unified CCE software maintains real-time and historical status information
about certain objects in the system such as service, skill groups, routes, and
scripts.
For example, the Route
Real Time table contains real-time information about each route. The Route Five
Minute and Route Half Hour tables contain historical information about each
route. The Route Real Time table contains one row for each route. (It has a
one-to-one relationship with the Route table.) The Route Half Hour table
contains many rows for each route--Unified ICM and Unified CCE software adds an
additional row for each route every half hour. (It has a one-to-many
relationship with the Route table.)
The system software
updates the real-time tables in the database every ten seconds. Real-time
information includes information about what is happening right now (for
example, CallsQNow and ExpectedDelay). It also includes summary information
about what has happened during the last five minutes (for example,
CallsIncomingTo5 and AvgTalkTimeTo5), since the last half-hour historical data
(for example, CallsRoutedHalf and CallsAbandQHalf), and since midnight (for
example, CallsOfferedToday and CallsHandledToday).
Unified ICM and
Unified CCE software generates historical information on five- and 30-minute
intervals, with the first interval beginning at midnight. For example, Unified
ICM and Unified CCE software adds a new row for each Route to the Route Five
Minute table every five minutes. Unified ICM and Unified CCE software adds a
new row for each Route to the Route Half Hour table every 30 minutes. Some of
the information for the historical tables is derived from accumulation fields
in the real-time tables. For example, at the end of each five-minute interval,
the value from the CallsOfferedTo5 field in the Route Real Time table is copied
to the CallsOfferedTo5 field of the Route Five Minute table.
Each five- and
30-minute row contains a field for the date-time. The time stored in this field
is the time at the start of the interval. For example, a Service Five Minute
row for the interval from 10:00 a.m. to 10:05 a.m. contains the time 10:00 a.m.
However, some fields within the table contain a snapshot of data from the end
of the interval. For example, the CallsQNow field of the Service Five Minute
table contains the number of calls queued at the end of the five-minute period.
Therefore, the Service Five Minute row with the time of 10:00 a.m. tells you
the number of calls queued at 10:05 a.m. To find the number of calls queued at
10:00 a.m., look at the Service Five Minute record for 9:55 a.m.
Call Detail Data
Each time Unified ICM and Unified CCE software processes a routing
request, it generates a Route Call Detail row that contains information about
the request and routing decision it made. Each row includes the day on which
the request was handled and a key value generated by Unified ICM and Unified CCE
software that is unique among all requests handled that day. These two values
together comprise a unique identifier for the call.
When Unified ICM and Unified CCE software receives information that a call is completely done
(that is, for example, it has been routed to a peripheral, handled by an agent,
and disconnected), then a row about the call is written to the Termination Call
Detail table. The Termination Call Detail row indicates the agent, skill group,
and service that handled the call. It also contains information such as how
long the caller was on hold, and whether the call was transferred to another agent
after the initial routing.
If the call was sent to a
translation route, the Termination Call Detail row contains the same day and
router key values as the Route Call Detail row for the same call. You can use
these fields to link the tables and find all the call detail information for a
single call. This process is called cradle-to-grave call
tracking.
Feedback