Skip Headers
Oracle® Real User Experience Insight User's Guide
12c Release 1 for Linux x86-64

Part Number E26360-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

S Enriched Data Export Facility

This appendix explains the use of the Enriched data exchange facility. This includes a description of operation, and the structure of the database tables it uses.

S.1 Exporting Enriched Data

The Enriched data exchange facility enables you to combine the data gathered by RUEI with other data sources. These could include, for instance, Customer Relationship Management (CRM) or Business Intelligence (BI) systems. Using this functionality, you can produce customized analysis of your Web environment using your own BI tooling, as well as integrate RUEI's rich set of collected data with offline data to obtain greater insight into what drives your sales and revenue.

The facility works by exporting the data collected every 1-minute period to a database. By default, the data is exported to the same database instance as used by the Reporter. However, it is strongly recommended that you configure an alternative database instance for enriched data export. Access to data in the export database is available via SQL. The procedure to do this is fully described in the Oracle Real User Experience Insight Installation Guide.

What Data is Available for Export?

The information captured during the monitoring of network traffic is available to you via reports and the Data Browser. Broadly, it contains two types of information: application-related and service-related information. For each type, detailed information across a wide variety of dimensions is available. While application-type information is available via the Enriched export facility, service-related information is not.

In addition to the user experience data gather by RUEI, KPI data can also be exported for customized analysis. This facility enables deep-dive analysis of the performance of your network environment and business-critical applications.

As described later in this section, you can customize the content of the exported data to include information not normally collected by RUEI. For example, the contents or value of visitors' shopping baskets. Because the exported data is page-based, the available data is restricted to applications and suites, and does not include service-related data.

Controlling the Availability of Exported Data

The amount of data available in the export database is controlled via the Enriched data exchange retention setting in the defined Reporter data retention policies. These are fully explained in Section 12.9.1, "Defining Reporter Retention Policies". The structure of the database tables used within the export database are described in Appendix S, "Enriched Data Export Facility". Access

Example BI Implementation Using Enriched Data Exchange

This section presents an outline of a BI solution utilizing data from the Enriched data exchange facility. In this case, it makes use of Oracle Business Intelligence foundation (part of the Oracle Fusion Middleware product family). Its schematic structure is shown in Figure S-1.

Figure S-1 Schematic Overview of Data Warehouse Staging Area

Description of Figure S-1 follows
Description of "Figure S-1 Schematic Overview of Data Warehouse Staging Area"

The framework is based on Oracle Warehouse Builder (OWB). The RUEI-captured data is exported to a database. From the export database, it is uploaded via SQL scripts to a staging database. This then populates the production database. Once in the production DWH, the RUEI data is available through a wide variety of reports and dashboards. An example of these reports is shown in Figure S-2.

Figure S-2 Example BI Dashboard

Description of Figure S-2 follows
Description of "Figure S-2 Example BI Dashboard"

Enabling and Disabling Enriched Data Exchange

To enable the Enriched data exchange facility, do the following:

  1. Select Configuration, then Applications, and then Enriched data exchange. The screen shown in Figure S-3 appears.

    Figure S-3 Enriched Data Exchange

    Description of Figure S-3 follows
    Description of "Figure S-3 Enriched Data Exchange"

  2. Use the Enriched data exchange enabled/disabled check box to specify whether the Enriched data exchange facility should be enabled. Use the KPI data exchange enabled/disabled check box to specify whether the export of KPI data should be enabled. By default, both are disabled. Note that the availability of export data is determined by your Reporter data retention polices (see Section 12.9.1, "Defining Reporter Retention Policies").

  3. Optionally, you can define additional data items to be included in the exported enriched data. Typically, these are elements in the client request or server response headers that are not normally collected by RUEI, but which you want included in the exported data. To do so, click Add new item. The dialog shown in Figure S-4 appears.

    Figure S-4 Add Enriched Data Export Item Dialog

    Description of Figure S-4 follows
    Description of "Figure S-4 Add Enriched Data Export Item Dialog"

  4. Use the Source type menu to define how the required item should be identified within the data collected by RUEI, and the scope of the search. You can specify to search within the client request header or server response header, using either a literal search or an XPath expression, or to search within a custom page-tagging implementation for a specific tag. Further information about support for custom page-tagging schemes is available in Appendix A, "Tagging Conventions".

    Use the Source value field to specify the specific argument or element from which the data item's value should be taken.

    Use the Export name field to specify the name to be assigned to the data item. This becomes the item's element name. In the case of a custom tag, this will appear in the CONTENT column in the BI__BIDATA_MASTER (see Table S-2) in the format &name=value=. Similarly, XPath expression items would be reported in the REQUEST_HEADERS or REPLY_HEADERS columns. Note that the Export name field is not available if you select a header-related option in the Source type menu. In this case, the name of the header is used in the appropriate column. When ready, click Save. The new item, if found in the monitored traffic, will start to appear in the reported data within 5-10 minutes.

    If your definition makes use of an XPath expression, click the Namespaces tab to define the namespaces it uses. The use of XPath expressions is described in Appendix F, "Working with XPath Queries".

Existing data items can be modified by right-clicking them within Figure S-3, and selecting Edit. You can also select Remove to delete it, or select Remove all to delete all currently defined items.

Best Practices

Be aware that the SQL queries used to access exported data can place a significant performance overhead on the export database. For this reason, it is recommended that you pay particular attention to the following points:

S.2 Enriched Data Exchange Database Table Structures

This section explains the structure of the database tables generated by RUEI for Enriched data exchange. The table used for KPI data export is explained in Section S.3, "KPI Data Exchange Database Table Structures". These tables are located in the database (local or remote) used by your RUEI installation. They can be accessed through SQL queries.

The WG__BIDATA_PERIOD Table

At the highest level, the WG__BIDATA_PERIOD table (Table S-1), provides an outline of the available exported data. While the export data tables do not enforce referential integrity, the combination of the PERIOD_ID (minutes since 1970 UTC) and PROCESSOR_ID columns provides a link to other related tables.

The STAMP column indicates the 1-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 12.9.1, "Defining Reporter Retention Policies"), rows are removed from this table. Similarly, rows are added to the table every one minute. Note that a new row will only appear in the table if exporting of the associated 1-minute period has been completed. The availability of export data is determined by the Enriched data exchange retention setting described in Section 12.9.1, "Defining Reporter Retention Policies".

Due to the scalability of RUEI, most tables contains a PROCESSOR_ID column. It indicates that data for a given interval was successfully exported from the specified Processing Engine system. This column does not appear in the KPI-related tables because KPI data is exported from the Reporter, and not from Processing Engine systems. Note that if your RUEI installation does not include any configured Processing Engines, only one row is created for each interval, with PROCESSOR_ID reported as 0.

When designing your SQL queries, it is recommended that you start by examining the relevant period table (such as WG__BIDATA_PERIOD or WG__BIDATA_USERFLOW_PERIOD) to see whether data for a required period has been exported. If available, you can then use the combination of PROCESSOR_ID, PERIOD_ID, and PAGEVIEW_ID to access the other tables. In the case of KPI-related data, only PERIOD_ID is required.

Table S-1 WG__BIDATA_PERIOD Table

Column Type

PROCESSOR_ID*

NUMBER

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 S-2. Within each PERIOD_ID and PROCESSOR_ID combination, each page view receives a unique ID, which is incremented for each page view. When a new PERIOD_ID and PROCESSOR_ID combination is encountered, the PAGEVIEW_ID numbering re-starts from 1. The STAMP column specifies the page view's true timestamp, rather than a 1-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 S-2 WG__BIDATA_MASTER Table

Column Type

PROCESSOR_ID*

NUMBER

PERIOD_ID*

NUMBER

PAGEVIEW_ID*

NUMBER

APP_VIOLATIONS

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)

SESSION_SEQ_NUMBER

NUMBER

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)

USER_VIOLATIONS

NUMBER

VHOST

VARCHAR2 (4000 BYTE)


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

The WG__BIDATA_PROPERTIES Table

The WG__BIDATA_PROPERTIES table (Table S-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 S-3 indicates whether the item refers to a defined Enriched data exchange item, or to a custom dimension (as described in Section 3.11, "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 S-3 WG__BIDATA_PROPERTIES Table

Column Type

PROCESSOR_ID*

NUMBER

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 retrieved from the WG__BIDATA_PROPERTIES table using a SQL query based on the appropriate PAGEVIEW_ID.

WG__BIDATA_USERFLOW_PERIOD

At the highest level, the WG__USERFLOW_PERIOD table, shown in Table S-4, provides an outline of the available exported data. While the export data tables do not enforce referential integrity, the combination of PERIOD_ID (minutes since 1970 UTC) and PROCESSOR_ID columns provides a link to other related tables.

The STAMP column indicates the 1-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 12.9.1, "Defining Reporter Retention Policies"), rows are removed from this table. Similarly, rows are added to the table every one minute. Note that a new row will only appear in the table if exporting of the associated 1-minute period has been completed. The availability of export data is determined by the Enriched data exchange retention setting described in Section 12.9.1, "Defining Reporter Retention Policies".

Table S-4 WG__BIDATA_USERFLOW_PERIOD

Column Type

PROCESSOR_ID*

NUMBER

PERIOD_ID*

NUMBER

STAMP*

TIMESTAMP


WG__BIDATA_USERFLOWS

The actual export data is held in the WG__BIDATA_USERFLOWS table, show in Table S-5. Within each PERIOD_ID and PROCESSOR_ID combination, each user flow receives an identifier, which is a timestamp.

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. Other columns specify the user flow's properties.

Table S-5 WG__BIDATA_USERFLOWS Table

Column Type Description

PERIOD_ID*

NUMBER

Timestamp.

PROCESSOR_ID*

NUMBER

Processing Engine from which the data is derived.

PAGEVIEW_ID

NUMBER

Internal page view ID (unique per PERIOD_ID).

USERFLOW_PERIOD_ID

NUMBER

Timestamp.

CATEGORY

VARCHAR2 (4000 BYTE)

User-defined user flow category name.

NAME

VARCHAR2 (4000 BYTE)

User-defined user flow name.

STEP

VARCHAR2 (4000 BYTE)

User-defined step name.

STEPNR

NUMBER

The number of the step within the user flow.

STATUS

NUMBER

Status of user flow. The possible values are explained in Table S-6.

MONETARY_VALUE

NUMBER

The monetary value assigned to the user flow.


The Status column indicates the current position within the user flow. Its possible values are shown in Table S-6.

Table S-6 User Flow Status Values

Status Description

started

The first user flow step has been completed.

move-forward

The page view matched the next step in the user flow (that is, not the first), possibly skipping optional steps.

move-back

With this page view, the user returned to a previous step in the user flow.

active

The current page view matches the current step. Therefore, while the user has not moved forwards or backwards, there is activity within the user flow.

outside

The current page view is the first of a possible series of page views not in the user flow. Note that only the first of this series is recorded in the table.

abort

The current page view matches a defined abort condition.

back-in

Follows a previous outside status, and indicates that this page view is the first again within the user flow. It is also reported immediately before a ses-timeout or complete status, so that every outside status is matched by a row with the back-in status.

idle-out

Indicates that there was no activity on an uncompleted user flow for more than the user configured step idle time. Note that in this case, the reported page view is more or less random, and is the last known page view and may not necessarily be part of the user flow.

idle-in

Follows a previous idle-out status, and indicates that this page view is the first again within the user flow. It is also reported immediately before a ses-timeout or complete status, so that every idle-out status is always matched by a row with the idle-in status.

ses-timeout

Same as idle-out, but indicates that the entire session has timed out.

complete

The page view completes the last step of the user flow.


The WG__BIDATA_SUITES Table

The WG__BIDATA_SUITES table, shown in Table S-7, 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 S-7 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 S-8.

Table S-8 WG__BIDATA_SUITE_EBS Table

Column Type

PROCESSOR_ID*

NUMBER

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)


S.2.1 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 S-9.

Table S-9 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 S-9, the region code is reported as 00. For example, A1-00.

S.3 KPI Data Exchange Database Table Structures

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

WG__BIDATAKPI_PERIOD

At the highest level, the WG__BIDATAKPI_PERIOD table, shown in Table S-10, provides an outline of the available exported KPI data. While the export data tables do not enforce referential integrity, the PERIOD_ID (minutes since 1970 UTC) column provides a link to the other KPI-related table, WG__BIDATAKPI_MASTER.

The STAMP column indicates the 1-minute period interval during which the data export was triggered. As data is purged from the database, according to the specified Enriched data exchange retention for KPIs policy (see Section 12.9.1, "Defining Reporter Retention Policies"), rows are removed from this table. Similarly, rows are added to the table every minute. Note that a new row will only appear in the table if exporting of the associated 1-minute period has been completed. The availability of KPI data is determined by the KPI data exchange retention setting described in Section 12.9.1, "Defining Reporter Retention Policies".

Table S-10 WG__BIDATAKPI_PERIOD Table

Column Type

PERIOD_ID*

NUMBER

STAMP*

TIMESTAMP


WI__BIDATAKPI_MASTER

The actual KPI data is held in the WG__BIDATAKPI_MASTER table, shown in Table S-11. Within each PERIOD_ID, each KPI receives a unique KPI_ID.

Table S-11 WG__BIDATAKPI_MASTER Table

Column Type Description

PERIOD ID*

NUMBER

Timestamp.

KPI_ID

NUMBER

Internal unique KPI ID.

NAME

VARCHAR (255 CHAR)

User-defined KPI name.

CATEGORY

VARCHAR (255 CHAR)

User-user KPI category name.

DESCRIPTION

VARCHAR (255 CHAR)

User-defined KPI description.

TARGET_TYPE

NUMBER

(0=none, 1=fixed, 2=automatic).

TARGET_MIN

NUMBER

KPI's minimum target value.

TARGET_MAX

NUMBER

KPI's maximum target value.

NUMERATOR

BINARY_DOUBLE

Numerator value for period.

DENOMINATOR

BINARY_DOUBLE

Denominator value for period.

VALUE

NUMBER

Current KPI value calculated over the SPAN period.

STATUS

NUMBER

Status of KPI (-1=unknown, 0=fail, 1=okay).

SPAN

NUMBER

Periods (in minutes) over which the KPI value is calculated.

DATA_TYPE

VARCHAR (255 BYTE)

Data access definition for KPI (null=generic, app=application-generic, suite=suite-specific, service=service-specific)Foot 1 .

SUITE_TYPE

VARCHAR (255 BYTE)

Data access suite type definition (for example, EBS and Siebel).

FILTERS

CLOB

Dimension-level filters definitions.

REQUIREMENTS

CLOB

Metric-level requirement definitions.


Footnote 1 The precise values are based on those used in the GUI, and can change.