R Enriched Data Export Facility

This appendix describes the structure of the RUEI database tables used for the Enriched data exchange facility. The use of this facility is described in Section 9.20, "Exporting Enriched Data". Information about the reporting of the client location is also provided.

R.1 Database Table Structures

This section explains the structure of the database tables generated by RUEI. These tables are located in the database (local or remote) used by your RUEI installation. They can be accessed through SQL querries.

The WG__BIDATA_PERIOD Table

At the highest level, the WG__BIDATA_PERIOD table, shown in Table R-1, provides an outline of the available exported data. While the export data tables do not enforce referential integrity, the PERIOD_ID (minutes since 1970 UTC) column provides a link to other related tables. The STAMP column indicates the 5-minute interval during which the data export was triggered. As data is purged from the database, according to the specified Enriched data exchange retention policy (see Section 9.6.1, "Defining Reporter Retention Policies"), rows are removed from this table. Similarily, rows are added to the table every five minutes. Note that a new row will only appear in the table if exporting of the associated 5-minute period has been completed.

Table R-1 WG__BIDATA_PERIOD Table

Column	Type
PERIOD_ID*	NUMBER
STAMP*	TIMESTAMP

The WG__BIDATA_MASTER Table

The actual export data is held in the WG__BIDATA_MASTER table, show in Table R-2. Within each PERIOD_ID, each page view receives a unique PAGEVIEW_ID, which is incremented for each page view. When a new PERIOD_ID is encountered, the PAGEVIEW_ID numbering re-starts from 1. The STAMP column specifies the page view's true timestamp, rather than a 5-minute interval. Other columns specify the page view's properties. Note that the SESSION_ID column provides a link to the viewed pages within specific sessions.

Table R-2 WG__BIDATA_MASTER Table

Column	Type
PERIOD_ID*	NUMBER
PAGEVIEW_ID*	NUMBER
APPLICATION	VARCHAR2 (4000 BYTE)
BROWSER_DETAIL	VARCHAR2 (4000 BYTE)
BROWSER_TYPE	VARCHAR2 (4000 BYTE)
CLIENT_CITY	VARCHAR2 (4000 BYTE)
CLIENT_COUNTRY	VARCHAR2 (4000 BYTE)
CLIENT_IP	VARCHAR2 (4000 BYTE)
CLIENT_NETWORK	VARCHAR2 (4000 BYTE)
CLIENT_OS_CLASS	VARCHAR2 (4000 BYTE)
CLIENT_OS_VERSION	VARCHAR2 (4000 BYTE)
CLIENT_PROVIDER	VARCHAR2 (4000 BYTE)
CLIENT_REGION	VARCHAR2 (4000 BYTE)
CONTENT	VARCHAR2 (4000 BYTE)
COOKIE	VARCHAR2 (4000 BYTE)
DYNAMIC_NETWORK_TIME	NUMBER
DYNAMIC_SERVER_TIME	NUMBER
FULL_URL	VARCHAR2 (4000 BYTE)
HITS	NUMBER
HTTP_RESULT	VARCHAR2 (4000 BYTE)
NAMED_CLIENT_GROUP	VARCHAR2 (4000 BYTE)
NAMED_CLIENT_NAME	VARCHAR2 (4000 BYTE)
NAMED_SERVER_GROUP	VARCHAR2 (4000 BYTE)
NAMED_SERVER_NAME	VARCHAR2 (4000 BYTE)
PAGE_DELIVERY_TYPE	VARCHAR2 (4000 BYTE)
PAGE_DELIVERY_VALUE	VARCHAR2 (4000 BYTE)
PAGE_GROUP	VARCHAR2 (4000 BYTE)
PAGE_LOAD_TIME	NUMBER
PAGE_NAME	VARCHAR2 (4000 BYTE)
PAGE_READ_TIME	NUMBER
REFERRER_URL	VARCHAR2 (4000 BYTE)
REPLY_HEADERS	VARCHAR2 (4000 BYTE)
REQUEST_HEADERS	VARCHAR2 (4000 BYTE)
SERVER_IP	VARCHAR2 (4000 BYTE)
SESSION_ID	VARCHAR2 (4000 BYTE)
SET_COOKIE	VARCHAR2 (4000 BYTE)
STAMP	TIMESTAMP
STATIC_NETWORK_TIME	NUMBER
STATIC_SERVER_TIME	NUMBER
SUITE_TYPE	VARCHAR2 (4000 BYTE)
URL_ARGUMENTS	VARCHAR2 (4000 BYTE)
URL_FILE	VARCHAR2 (4000 BYTE)
URL_PARAMS	VARCHAR2 (4000 BYTE)
URL_POST_ARGUMENTS	VARCHAR2 (4000 BYTE)
USER_ID	VARCHAR2 (4000 BYTE)
VHOST	VARCHAR2 (4000 BYTE)

Information about the reported data items in Table R-2 is available from Section D, "Summary of Data Items".

The WG__BIDATA_PROPERTIES Table

The WG__BIDATA_PROPERTIES table, shown in Table R-3, contains additional page view properties. Note that while each row in the WG__BIDATA_MASTER table refers to one page view, multiple rows in the WG__BIDATA_PROPERTIES table can refer to the same page view.

The TYPE column shown in Table R-3 indicates whether the item refers to a defined Enriched data exchange item (as described in Section 9.20, "Exporting Enriched Data"), or to a custom dimension (as described in Section 3.9, "Working With Custom Dimensions"). The NAME column specifies the name of the page property. This is either a custom export item, or a custom dimension.

Table R-3 WG__BIDATA_PROPERTIES Table

Column	Type
PERIOD_ID*	NUMBER
PAGEVIEW_ID*	NUMBER
TYPE*	VARCHAR2 (64 BYTE)
NAME*	VARCHAR2 (255 BYTE)
VALUE*	VARCHAR2 (4000 BYTE)

Information about the custom items within a page view reported in the WG__BIDATA_MASTER table can be retrived from the WG__BIDATA_PROPERTIES table using a SQL query based on the appropriate PAGEVIEW_ID.

The WG__BIDATA_SUITES Table

The WG__BIDATA_SUITES table, shown in Table R-4, specifies the suite types for which export information is available. A suite type only appears in this table if a suite instance has been defined for the suite type.

Table R-4 WG__BIDATA_SUITES Table

Column	Type
SUITE_TYPE*	VARCHAR2 (255 BYTE)

Suite-Specific Tables

The individual suite tables are essentially extensions of the WG_BIDATA_MASTER table, and provide the suite-specific information associated with each page view. An example, the WG__BIDATA_SUITE_EBS table, is shown in Table R-5.

Table R-5 WG__BIDATA_SUITE_EBS Table

Column	Type
PERIOD_ID*	NUMBER
PAGEVIEW_ID*	NUMBER
EBS_FORMNAME_ID	VARCHAR2 (4000 BYTE)
EBS_FORMNAME_NAME	VARCHAR2 (4000 BYTE)
EBS_FWK_NAME	VARCHAR2 (4000 BYTE)
EBS_JSP_FILENAME	VARCHAR2 (4000 BYTE)
EBS_MODULE_ID	VARCHAR2 (4000 BYTE)
EBS_MODULE_NAME	VARCHAR2 (4000 BYTE)
EBS_REGION_ID	VARCHAR2 (4000 BYTE)
EBS_REGION_NAME	VARCHAR2 (4000 BYTE)
EBS_RESP_KEY	VARCHAR2 (4000 BYTE)
EBS_RESP_NAME	VARCHAR2 (4000 BYTE)

R.2 Country And Region Reporting

The CLIENT_COUNTRY reported within the exported data is based on the ISO 3166-1 standard. This uses a 2-character abbreviation (for example, "AU" for Australia) to indicate the end-user's country location. However, in cases were it is not possible to determine the end-user's location, a number of special codes are reported. These are shown in Table R-6.

Table R-6 Exceptions to ISO 3166-1 Country Code Reporting

Code	Description
--	A local (rather than top level) domain name is used for a home network.
A1	An anonymous proxy is being used as an intermediary for requests from the client.
A2	Client access to the Internet is via an ISP satellite.
EU	A corporate proxy located in Europe is being used.
AP	A corporate proxy located in Asia or the Pacific region is being used.

For the USA and Canada, the reported CLIENT_REGION is based on the ISO 3166-2 standard. This uses a combination of country code and region. For example, the Texas region of the USA would be reported as "US-TX". For locations in the rest of the world, the relevant FIPS 10-4 region codes are reported. For the special country codes shown in Table R-6, the region code is reported as "00". For example, "A1-00".