Learn about the feedback schema tables and the fields or
columns they contain.
The following tables are used by the Feedback schema:
Table Name: Browsers
Table 1. Table description: This table contains the client Web browser defined internally by a User
Agent mapping.
Column Name |
Column description |
Browser_ID |
The primary key. A unique number automatically generated by the
database. |
Name |
The browser name defined internally by a User Agent mapping. |
Table Name: Calendar
Table 2. Table description: Initially, this table is empty. The Feedback component calculates date
stamp IDs, assuming a start date of 1/1/1995. The table can be populated with a new configuration
task for applications that use custom logging or reports that reference this table.
Column Name |
Column description |
Date_ID |
The primary key. A unique identifier automatically generated by the
database. |
Month |
Month expressed as integer 1-12 |
Day |
Day expressed as integer 1-31 |
Year |
Year expressed as integer 1995-2030 |
Quarter |
Quarter expressed as integer 1-4 |
Weekday |
Weekday expressed as integer 1-7 |
WeekOfYear |
The week number expressed as an integer 1-52 |
EpochDay |
The number of days since the beginning date in this table. |
EpochWeek |
The number of weeks since the beginning date in this table. |
EpochMonth |
The number of months since the beginning date in this table. |
EpochQuarter |
The number of quarters since the beginning date in this table. |
AggrWeekID |
The identifier of the first day of the week that this day belongs to. |
AggrMonthID |
The identifier of the first day of the month that this day belongs to. |
AggrQuarterID |
The identifier of the first day of the quarter that this day belongs
to. |
AggrYearID |
The identifier of the first day of the year that this day belongs to. |
Table Name: Domain
Table description: This table is RESERVED for future Feedback component use.
Column Name |
Column Description |
Domain_ID |
The primary key; a unique identifier automatically
generated by the database. |
Name |
The domain name as resolved from the TCP/IP
address. |
Table Name: Entities
Table description: This table is RESERVED for Feedback component use.
Column Name |
Column Description |
Entity_ID |
The primary key; a unique identifier automatically
generated by the database. |
Name |
The name of the entity. |
Type |
Indicates the type of entity: 0=entity 1=category
2=key/value pair |
ParentEntity_ID |
For a category entity, indicates the entity
the category is based on. |
Table Name: EntityTraversal
Table description: This table is RESERVED for HCL Feedback component use.
Column Name |
Column Description |
EntityTraversal_ID |
The primary key. A unique identifier automatically
generated by the database. |
Entity_ID |
The identifier of the entity we start from. |
Table1 |
The table this entity is stored in. |
Column1 |
ID column for this entity. |
Table2 |
The table of the parent entity. |
Column2 |
Join column between entity and parent entity
table. |
PickThis |
RESERVED for HCL Feedback component use. |
Table Name: Hit_Facts
Table description: This table contains the information processed during data collection. Each row
represents a hit to a page of your Web site--if that page contains a rule or logging bean. Not all
fields are used on each entry.
Note: The first date stamp (date ID=1) corresponds to 1/1/1995.
Time stamp IDs are calculated such that each second of the day has
a separate ID.
Column Name |
Column Description |
JS_ID |
A foreign key to the JavaScriptStatus table. |
CookiesStatus_ID |
A foreign key to the CookiesStatus table. |
Hits |
The actual number of hits. |
Status_ID |
A foreign key to the ResetStatus table. |
ReturnCode_ID |
A foreign key to the ReturnCodes table. |
HTTPVersion_ID |
A foreign key to the HTTPVersion table. |
RecordType |
Indicates what type of log file this hit came
from. This field is for Feedback component use only. |
LastUpdated |
Time stamp of when this row was last created
/ updated. |
PageViews |
The actual number of page views. If the resource
for this hit is a page, this field is set to 1. |
TimeTaken |
Time taken by the Web server to process this
hit. This column is not implemented and is RESERVED for future Feedback
component use. |
Bytes |
The number of bytes sent by the Web server to
the client browser for this hit. |
CorrelationKey |
The key used to correlate information between
the various types of logs that can be processed during data collection. |
Session_ID |
The identifier of the session this hit belongs
to. A foreign key to the Session_Facts table. |
HitTimestmp |
Time stamp of this hit expressed as a Java timeInMillis
value. |
LocalDate_ID |
The date of this hit. The first date stamp (date
ID=1) corresponds to 1/1/1995. See also, Populating CALENDAR and TIMEOFDAY
tables. |
ImportHistory_ID |
Not implemented. This field is RESERVED for
future Feedback component use. |
Hit_ID |
The primary key. A unique identifier automatically
generated by the database. |
RefProtocol_ID |
Protocol that the referrer used to request this
hit. A foreign key to the Protocols table. |
Referrer_ID |
The referrer for this hit. A foreign key to
the Referrer table. |
Protocol_ID |
The protocol the Web server used to process
this hit. A foreign key to the Protocols table. |
LocalTimeOfDay_ID |
The time of this hit. See also, Populating CALENDAR
and TIMEOFDAY tables. |
GMTDate_ID |
The GMT date of this hit. See also, Populating
CALENDAR and TIMEOFDAY tables. |
Resource_ID |
The resource requested by the client browser.
A foreign key to the Resources table. |
GMTTimeOfDay_ID |
The GMT date of this hit. See also, Populating
CALENDAR and TIMEOFDAY tables. |
Resource_ID |
The resource requested by the client browser.
A foreign key to the Resources table. |
GMTTimeOfDay_ID |
The GMT time of this hit. See also, Populating
CALENDAR and TIMEOFDAY tables. |
Table Name: HitParms
Table description: This table lists the personalization data (such as rules, campaigns, ratings),
as well as query strings associated with a given hit.
Column Name |
Column Description |
Hit_ID |
The identifier of the Hit that contains the
query string. This is a foreign key to the ID column of the Hit_facts
table. |
Parms_ID |
The identifier for a specific query string.
This is a foreign key to the ID column of the Parms table. |
Ordering |
Ordering is used to group a set of keys during
the processing of a Web page. |
ParmType |
Identifies the type of key value data. For example,
personalization rules data, query strings, or referral query status. |
Table Name: HTTPVersion
Table description: This table lists all known HTTP versions. It is pre-populated by the Feedback
component when the table is created.
Column Name |
Column Description |
HTTPVersion_ID |
1 - 'HTTP 1.0' 2 - 'HTTP 1.1' 99 - 'Unknown' |
Name |
The name of the HTTP version. |
Table Name: ImportHistory
Table description: This table is RESERVED for future Feedback component use
Column Name |
Column Description |
ImportHistory_ID |
The primary key. A unique number automatically
generated by the database. |
BeginTimeStamp |
Start time of the import, expressed as a JAVA
timeInMillis value. |
EndTimeStamp |
Time the import finished, expressed as a JAVA
timeInMillis value. |
Status |
The status of the import. |
Table Name: Key
Table description: Lists all Keys (part of Key/Value pairs) encountered during data
collection.
Column Name |
Column Description |
Key_ID |
The primary key. A unique identifier automatically
generated by the database. |
Name |
the name of the key. |
Table Name: Key_Value_combo
Table description: This table lists all Key/Value pairs that are grouped together.
Column Name |
Column Description |
Parms_ID |
A foreign key to the Parms table. |
KeyValuePair_ID |
A foreign key to the Key_Value_Pair table. |
Table Name: Key_Value_Pair
Table description: This table lists all Key/Value pairs processed during data collection.
Column Name |
Column Description |
KeyValuePair_ID |
The primary key. A unique identifier generated
automatically by the database. |
Key_ID |
A foreign key to the Key table. |
Value_ID |
A foreign key to the Value table. |
Table Name: Networks
Table description: This table contains all the TCP/IP addresses processed during data
collection.
Column Name |
Column Description |
Network_ID |
The primary key; a unique number automatically
generated by the database. |
IP_Address |
TCP/IP address as obtained from the page hit. |
Subdomain_ID |
The TCP/IP subdomain this address belongs to.
A foreign key to the ID column of the Subdomains table. |
Domain_ID |
The TCP/IP domain this address belongs to. A
foreign key to the ID column of the Domain table. |
Table Name: Parms
Table description: This table lists all the key/value pairs strings processed during data
collection.
Column Name |
Column Description |
KVCount |
Indicates how many key/value pairs are in this
query string. |
ParmsString |
This field is not used. |
WebNode_ID |
This field is not used. |
Parms_ID |
A unique identifier automatically generated
by the database. |
Table Name: Platforms
Table description: This table contains the client operating system defined internally by a User
Agent mapping.
Column Name |
Column Description |
Platform_ID |
The primary key. A unique number automatically
generated by the database. |
Name |
The Operating System name as defined via the
User Agent global settings. |
Table Name: Protocols
Table description: Lists all network protocols processed during data collection.
Column Name |
Column Description |
Protocol_ID |
The primary key. A unique identifier generated
automatically by the database. |
Name |
The name of the network protocol. |
Table Name: Referrer
Table description: This table contains all the referrers processed during data collection.
Column Name |
Column Description |
Referrer_ID |
The primary key. A unique number automatically generated by the database.
|
RefHost_ID |
A foreign key to the ID column of the REFERRERHOST table. |
ReferrerURL_ID |
A foreign key to the ID column of the REFERRERURL table. |
Table Name: ReferrerHost
Table description: This table stores all the referrer site host names processed during data
collection.
Column Name |
Column Description |
RefHost_ID |
The primary key. A unique number automatically
generated by the database. |
IsLocal |
Indicates whether this referrer is internal
or external. 0 - external referrer 1 - internal referrer |
Name |
Host name part of referrer as parsed from the
Web log record. |
Table Name: ReferrerURL
Table description: This table contains the referrer pages processed during data collection. Note
that the referrer site's host name is stored in the ReferrerHost table.
Column Name |
Column Description |
ReferrerURL_ID |
The primary key. A unique number automatically
generated by the database. |
Name |
The name of the referrer page as parsed from
the Web log record. |
Table Name: Resources
Table description: This table contains all resources (URLs) processed during data collection.
Column Name |
Column Description |
Resource_ID |
The primary key. A unique number automatically
generated by the database. IsPage Indicates whether this resource
is a page. 0 = false 1 = true |
IsImage |
Indicates whether this resource is an image.
0=false 1=true |
IsDirectory |
Indicates whether this resources is a file directory.
This column is not currently implemented and is RESERVED for future
Feedback component use. |
Name |
The resource string as parsed from the Web log
record. |
Table Name: Session_Facts
Table description:
This table contains all the visits calculated from the Web log data.
Note: The first date stamp (date ID=1) corresponds to 1/1/1995. Time
stamp IDs are calculated such that each second of the day has a separate
ID.
Column Name |
Column Description |
firstHitTimestmp |
Timestamp of the first hit in session expressed
as a JAVA timeInMillis value. |
lastHitTimestmp |
Timestamp of the last hit in the session expressed
as a JAVA timeInMillis value. |
Result_ID |
Foreign key to the ID column of the RESULT table.
For your use in classifying this session. |
User_ID |
The visitor to your site. A foreign key to the
Users table. |
Referrer_ID |
The referrer for this session. A foreign key
to the Referrer table. |
Sessions |
The actual number of sessions. This field will
always contain the value. It is used during Report Database processing. |
LastUpdated |
Timestamp of when this row was last created/updated. |
PageViews |
Number of page views that are part of this session. |
Duration |
Duration of the session up to the last hit.
The Feedback component cannot calculate the full duration of the session
because there is no explicit end-of-session signal from the Web log. |
Hits |
Number of hits that are part of this session. |
SessionIdentifier |
The session identifier string from the Web log.
This is sometimes referred to as a session cookie. The SessionIdentifier
contains information about the user's id and ip address. If this information
needs to be protected steps must be taken to enable encryption on
the database level for this column. |
SessionTimestmp |
Beginning of this session expressed as a JAVA
timeInMillis value. |
LocalDate_ID |
This is the starting date of the session in
the local time zone of the Web server that handled this session. See
also, Populating CALENDAR and TIMEOFDAY tables. |
LocalTimeOfDay_ID |
This is the start time of the session in the
local time zone of the Web server which handled this session. See
also, Populating CALENDAR and TIMEOFDAY tables. |
ImportHistory_ID |
This column is not used. |
Session_ID |
The primary key; a unique number automatically
generated by the database. |
UserAgent_ID |
The signature of the Web browser used for this
session. |
EntryResource_ID |
The first resource viewed during this session. |
Network_ID |
Pointer to the Networks table. |
GMTDate_ID |
This is the start date of the session translated
to the GMT time zone. See also, Populating CALENDAR and TIMEOFDAY
tables. |
ExitResource_ID |
The last resource viewed during this session. |
GMTTimeOfDay_ID |
This is the start time of the session translated
to the GMT time zone. See also, Populating CALENDAR and TIMEOFDAY
tables. |
Table Name: Result
Table description: For your use in classifying sessions.
Column Name |
Column Description |
Result_ID |
The primary key. We recommend that you use numbers
automatically generated by the database. In DB2/UDB, this column is
defined as IDENTITY GENERATED BY DEFAULT. On ORACLE, the sequence
RESULT_ID is created for your use with this table. |
Name |
For your use. |
Table Name: SessionParms
Table description: This table lists all the Key/Value pairs associated with the session referrer.
Column Name |
Column Description |
Parms_ID |
Foreign key to the ID column of the PARMS table. |
Session_ID |
Foreign key to the ID column of the SESSION_FACT
table. |
ParmType |
Identifies the type of Key/Value data. |
Table Name: Subdomains
Table description: This table is RESERVED for future Feedback component use.
Column Name |
Column Description |
Domain_ID |
Foreign key to the ID column of the DOMAIN table.
The TCP/IP domain this subdomain belongs to. Name Subdomain name string
from a resolved TCP/IP address. |
Subdomain_ID |
The primary key, a unique number automatically
generated by the database. |
Table Name: TimeOfDay
Table description: Initially, this table is empty. Table rows are reserved to list all seconds in
a day, when the database is populated. The table can be populated with a new configuration task for
applications that use custom logging or reports that reference this table.
Column Name |
Column Description |
Minute |
a number in the range 0..59 |
Hour |
a number in the range 0..23 |
Second |
a number in the range 0..59 |
AggrHourID |
For each database row, indicates which hour it belongs to. |
TimeOfDay_ID |
The primary key. A unique number automatically generated by the database.
|
SecondsSinceMidnig |
The number of seconds elapsed since midnight. |
TimeSpan_ID |
Foreign key to the ID column of the TIMESPAN table. |
Table Name: TimeSpan
Table description: For your use in classifying time of day values.
Column Name |
Column Description |
TimeSpan_ID |
The primary key. We recommend that you use numbers
automatically generated by the database. In DB2/UDB, this column is
defined as IDENTITY GENERATED BY DEFAULT. On ORACLE, the sequence
TIMESPAN_ID is created for your use with this table. |
Name |
For your use. |
Table Name: UserAgents
Table description: This table contains all UserAgents processed during data collection.
Column Name |
Column Description |
Browser_ID |
Foreign key to the ID column of the BROWSERS
table. |
Name |
The UserAgent string as available from the Web
page hit. |
Platform_ID |
Foreign key to the ID column of the PLATFORMS
table. |
UserAgent_ID |
The primary key. A unique number automatically
generated by the database. |
Table Name: Users
Table description: This table contains all userids processed from data collection.
Column Name |
Column Description |
FirstVisitDate_ID |
Date of first visit to the site by this userid.
See also, Populating CALENDAR and TIMEOFDAY tables. |
Name |
The userid string as parsed from the Web log
record. |
User_ID |
The primary key. A unique number automatically
generated by the database. |
Table Name: Value
Table description: Lists all values (from Key/Value pairs ) encountered during data collection.
Column Name |
Column Description |
Value_ID |
A unique identifier automatically generated
by the database. |
Value |
Value string of Key/Value pair parsed from a
Web log record. |
The following tables are included in the Feedback
schema, however, the tables are not used and are not populated.
- Aggregate_Content
- AggregateKey
- Aggregates
- AggregateStatus
- Categories
- Category_Patterns
- Category_Sets
- CategoryMap
- CookiesStatus (Note: This table may be pre-populated, however,
the table is not used.)
- JavaScriptStatus
- Linkage
- Log_file_status
- Logs
- ResetStatus
- ReturnCodes
- ServerNodes
- Web_Nodes