Feedback schema tables

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 IBM 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 IBM 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.
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