4
OracleAS Personalization Schemas

Oracle Application Server Personalization (OracleAS Personalization) uses several database schemas, as follows:

• Mining Object Repository (MOR): The MOR controls the operation of OracleAS Personalization and contains mining objects (deployable packages, reports, schedule items, etc., as well as package build results such as rule tables, etc.)

• Mining Table Repository (MTR): Contains mining tables plus other important information such as bin boundaries, hot picks, taxonomy, etc.).

• RE Schema. The RE schema is where recommendations, model package information, and deployable packages are held in the Oracle Application Server. RE schemas contain tables that support the recommendation engine -- they contain deployable packages (model rules tables) used to generate real-time recommendations based on current and past session data.

All OracleAS Personalization schemas reside on the system where Oracle9i is installed.

To see a small example of the MOR, MTR, and RE schemas that are correctly populated, install the REAPI Demo. (During installation, you are prompted with a question asking whether you want to install the demo data (movie data); if you answer yes, the demo data and the REAPI Demo are installed.) If you installed the REAPI Demo, you can examine the tables there. Alternatively, you can install an unpopulated MTR when you install OracleAS Personalization. You can examine the schema of the unpopulated MTR and populate it with your own data.

Note that OracleAS Personalization uses a fixed schema for the MTR. By "fixed," we mean that the MTR must be populated with tables matching OracleAS Personalization table and column names.

Before you can obtain recommendations, you must create and deploy a package. You cannot create a package until there is some data available in the MTR. There are two ways to populate an MTR:

• Collect data using the REProxyRT method addItem or addItems (see Chapter 7).

• Populate with existing data, that is, map existing historical data that was collected by your Web application and stored in an Oracle database.

4.1 Mining Table Repository

The OracleAS Personalization MTR consists of the tables and views listed in Table 4-1, below. Certain of these tables must be populated with data specific to your Web site in accordance withthe MTR schema. Other tables, such as the tables associated with sessions and recommendations, are automataically populated by OracleAS Personalization. The third column indicates whether the table is to be populated by the user, by OracleAS Personalization, or by either.

Table 4-1  MTR Tables and Views

Table Name	Table or View	Populated by
MTR_ATTR_ID_BIN_BOUNDARY	TABLE	User
MTR_ATTR_NAME_TO_ID_MAP	TABLE	User
MTR_BIN_BOUNDARY	TABLE	User
MTR_CATEGORY (optional)	TABLE	User
MTR_CONFIGURATION	TABLE	Either
MTR_CUSTOMER	TABLE	Either
MTR_CUSTOMER_NAV_DETAIL	TABLE	Either
MTR_CUSTOMER_RATING_DETAIL	TABLE	Either
MTR_HOTPICK (optional)	TABLE	User
MTR_HOTPICK_GROUP (optional)	TABLE	User
MTR_INTERNAL_CONFIGURATION	TABLE	OP
MTR_ITEM	TABLE	User
MTR_NAVIGATION_DETAIL	VIEW	OP
MTR_PROFILE_DATA	VIEW	OP
MTR_PROXY	TABLE	User
MTR_PURCHASING_DETAIL	TABLE	User
MTR_RATING_DETAIL	VIEW	OP
MTR_RECOMMENDATION_DETAIL	TABLE	OP
MTR_SCHEMA_VERSION	VIEW	OP
MTR_SESSION	TABLE	OP
MTR_TAXONOMY (optional)	TABLE	User
MTR_TAXONOMY_CATEGORY (optional)	TABLE	User
MTR_TAXONOMY_CATEGORY_ITEM (optional)	TABLE	User
MTR_VISITOR_NAV_DETAIL	TABLE	Either
MTR_VISITOR_RATING_DETAIL	TABLE	Either

The rest of this section describes the schemas for the MTR tables; tables that you must populate are described in detail.

4.1.1 Item Table

The item table contains a list of all the individual items that a Web site deals with. When OracleAS Personalization returns a recommendation, it returns the ID and ITEM_TYPE; the item table provides more information. The item table is usually mapped to the catalog tables in the site database. The schema for MTR_ITEM has four fields; they are listed in Table 4-2, in order, with their data types.

Table 4-2  Item Table

Item	Item Type
ID	NUMBER PK
ITEM_TYPE	VARCHAR2(30) PK
LABEL	VARCHAR2(150)
DESCRIPTION	VARCHAR2(4000)

4.1.2 Bin Boundaries

The model-building algorithms in OracleAS Personalization require discrete data. All numerical data must be discretized (binned) before the data is used to build a model. In OracleAS Personalization, binning is performed in a transformation step before model build. The value ranges for binning (the bin boundaries) must be specified in order for OracleAS Personalization to bin the values.

In the current release of OracleAS Personalization, the bin boundaries must be specified in the bin boundaries table.

Binning is performed by joining the input data and the bin boundaries table.

Categorical data should be mapped to numbers. This can be a one-to-one mapping if the cardinality should be preserved. In the case of high cardinality values, a many-to-one mapping can be used to reduce the cardinality.

In summary, OracleAS Personalization requires all numerical data to be binned, and categorical data to be mapped.

When you create bins of numeric values, specify the bounds (upper and lower values) for each bin. When you create bins of categorical data, specify the items in each bin. To map several values to the same bin, use several records with the same bin numbering.

The table MTR_BIN_BOUNDARY has seven fields; they are listed in Table 4-3, in order, with their data types.

Table 4-3  Bin Boundary Fields

Field	Data Type
DATA_SOURCE_TYPE	NUMBER(3)
ITEM_TYPE	VARCHAR2(30)
ATTRIBUTE_NAME	VARCHAR2(30)
LOWER_VALUE	NUMBER
UPPER_VALUE	NUMBER
STRING_VALUE	VARCHAR2(60)
BIN_NUMBER	NUMBER(15)

Examples of Specifying Bin Boundaries

The following examples illustrate how to specify bin boundaries.

Consider movie rating data on a scale of 1 to 5. Suppose that you want to bin ratings as follows:

• 1 and 2 are in bin number 1

• 3 is in bin number 2

• 4 and 5 are bin number 3

You should enter the following into the bin boundaries table:

   (3, 'MOVIE', 'VALUE', 1, 2.1, NULL, 1),
   (3, 'MOVIE', 'VALUE', 3, 3.1, NULL, 2),
   (3, 'MOVIE', 'VALUE', 4, 5.1, NULL, 3).

The range of the bin includes all values that are greater than or equal to the lower value and strictly less than the upper value. The data source type for rating is 3 and string value is set to NULL for numeric data.

The following entries in a bin boundary table bin marital status, a categorical attribute:

   (1, 'NONE', 'MARITAL_STATUS', NULL, NULL, 'Single', 1),
   (1, 'NONE', 'MARITAL_STATUS', NULL, NULL, 'Divorced', 2),
   (1, 'NONE', 'MARITAL_STATUS', NULL, NULL, 'Separated', 2),
   (1, 'NONE', 'MARITAL_STATUS', NULL, NULL, 'Married', 3),
   (1, 'NONE', 'MARITAL_STATUS', NULL, NULL, 'Widowed', 4)

The data source type is 1 and the item type is NONE for demographic data. Lower and upper values are NULL for categorical data.

4.1.3 Taxonomy

In OracleAS Personalization, taxonomy refers to the structural organization of items in a company's catalog or site. Typically the catalog and/or the site has a hierarchical structure, with the most general category at the base (for example, "clothing"), and branching to increasingly specific categories (for example, from "clothing" to "shoes" to "sneakers" to "tennis shoes"). Strictly speaking, you are not required to have a taxonomy, but without one, the category structure must remain very simple. In particular, without a taxonomy, there can be only one level of category and no subcategories.

Items can belong to more than one category and to different levels of the structure (for example, "tennis shoes" can be a category in "clothing" and also a category in "sports equipment").

The structure of the OracleAS Personalization taxonomy is a DAG (directed acyclic graph), which can contain multiple top-level nodes. The different portions of the taxonomy can be disconnected too. Any node can connect to any other node but there cannot be a path that connects a node's child back to the node itself.

OracleAS Personalization also supports multiple taxonomies (different ways of organizing the items).

The taxonomy is implemented using a group of tables; they are specified by the customer at installation time:

• MTR_TAXONOMY: Lists the different taxonomies used by the site. The schema for this table has three fields; they are listed in Table 4-4, in order, with their data types:

  Table 4-4  MTR_TAXONOMY Table Fields

  Field	Data Type
  ID	NUMBER PK
  NAME	VARCHAR2 (150)
  DESCRIPTION	VARCHAR2 (4000)

• MTR_TAXONOMY_CATEGORY: Specifies which categories belong to the different taxonomies. (A category can belong to multiple taxonomies; however, for a given taxonomy, there can be only one instance of any category.) The schema for this table has four fields; they are listed in Table 4-5, in order, with their data types:

  Table 4-5  MTR_TAXONOMY_CATEGORY Table Fields

  Field	Data Type
  TAXONOMY_LEVEL	NUMBER PK
  TAXONOMY_ID	NUMBER PK
  PARENT_ID	NUMBER PK
  CHILD_ID	NUMBER PK

• MTR_TAXONOMY_CATEGORY_ITEM: Specifies which items go with a given taxonomy-category pair. The schema for this table has four fields; they are listed in Table 4-6, in order, with their data types:

  Table 4-6  MTR_TAXONOMY_CATEGORY_ITEM Table Fields

  Field	Data Type
  CATEGORY_ID	NUMBER PK
  TAXONOMY_ID	NUMBER PK
  ITEM_ID	NUMBER PK
  ITEM_TYPE	VARCHAR2 (30) PK

• MTR_CATEGORY: Specifies the different categories used by the site. The schema for this table has three fields; they are listed in Table 4-7, in order, with their data types:

  Table 4-7  MTR_CATEGORY Table Fields

  Field	Data Type
  ID	NUMBER PK
  NAME	VARCHAR2 (150)
  DESCRIPTION	VARCHAR2 (4000)

Samples of the MTR Taxonomy Tables

The REAPI Demo includes a taxonomy; you can examine the demo MTR to see examples of all these tables.

4.1.4 Customer Table

The MTR_CUSTOMER table contains demographic information about the customer. Some customer attributes are common to all OracleAS Personalization applications and some can be tailored to your application. The common attributes are customer ID, name, creation date, gender, age, marital status, personal income, whether or not the customer is the head of household, household income, household size, and whether the customer rents or owns.

You can specify up to 50 attributes specific to your Web application. These variable attributes are all strings.

The schema of the MTR_CUSTOMER table has the following fields. They are listed in Table 4-8, in order, with their data types.

Table 4-8  MTR_CUSTOMER Table Fields and Data Types

Field	Data Type
ID	VARCHAR2 (32)
NAME	VARCHAR2 (80)
CREATION_DATE	DATE
GENDER	VARCHAR2 (10)
AGE	NUMBER (3)
MARITAL_STATUS	VARCHAR2 (20)
PERSONAL_INCOME	NUMBER
IS_HEAD_OF_HOUSEHOLD	CHAR (1)
HOUSEHOLD_INCOME	NUMBER
HOUSEHOLD_SIZE	NUMBER (2)
RENT_OWN_INDICATOR	VARCHAR2 (30)
ATTRIBUTE1	VARCHAR2 (150)
ATTRIBUTE2	VARCHAR (150)
ATTRIBUTE3	VARCHAR (150)
.  .  .
ATTRIBUTE49	VARCHAR2 (150)
ATTRIBUTE50	VARCHAR2(150)

4.1.5 Hot Picks

Hot picks are used by some Web sites; for example, the daily specials might be hot picks. Information about hot picks is stored in two MTR tables, as follows:

• MTR_HOTPICK_GROUP lists the distinct hot pick groups used by the site. There is one record for each group. Each record contains a group ID, the group name (label), and a brief description of the group. The schema for this table has three fields; they are listed in Table 4-9, in order, with their data types.

  Table 4-9  MTR_HOTPICK_GROUP Fields

  Field	Data Type
  ID	NUMBER PK
  LABEL	VARCHAR2 (150)
  DESCRIPTION	VARCHAR2 (400)

• MTR_HOTPICK lists the items in each hot pick group, arranged according to group ID. Each record consists of a group ID, an item ID, and an item type. The schema for this table has three fields; they are listed Table 4-10, in order, with their data types.

  Table 4-10 MTR_HOTPICK Fields

  Field	Data Type
  ITEM_ID	NUMBER
  ITEM_TYPE	VARCHAR2 (30)
  GROUP_ID	NUMBER

A hot pick group can also contain categories. In this case, the ITEM_TYPE is set to CATEGORY and item ID is set to the appropriate ID value in the MTR_CATEGORY table.

4.1.6 MTR_PROXY

MTR_PROXY is used to set up proxies for new items. When a new item is introduced, there may not be enough detail information about it to build packages, so OracleAS Personalization uses data about a similar existing product.

4.1.7 Detail Tables

Several tables in the MTR store the details of various activities.

• MTR_CUSTOMER_NAV_DETAIL stores the navigation data corresponding to a customer session. This table is populated with data collected in the RE.

• MTR_CUSTOMER_RATING_DETAIL stores rating data for customers. This table is populated using the data collected in the RE during data collection.

• MTR_PURCHASING_DETAIL stores purchasing data on a per-session basis. Typically this data is collected by the Web application.

• MTR_RECOMMENDATION_DETAIL stores the results of recommendation requests. The data stored in this table is used to generate reports on the performance of OracleAS Personalization.

• MTR_VISITOR_NAV_DETAIL stores the navigation data corresponding to a visitor session. This table is populated with data collected in the RE.

• MTR_VISITOR_RATING_DETAIL stores rating data for visitors. This table is populated using the data collected in the RE during data collection.

If the application is not collecting data through the RE, these tables can be mapped to other tables in the application schema where such information is maintained.

4.1.8 Miscellaneous MTR Tables

The following tables are used internally by OracleAS Personalization:

• MTR_ATTR_NAME_TO_ID_MAP is used to speed up package building.

• MTR_CONFIGURATION and MTR_INTERNAL_CONFIGURATION stores configuration information.

• The MTR_SESSION table stores information about the session that OracleAS Personalization creates internally on behalf of the application.

• MTR_ATTR_ID_BIN_BOUNDARY is a materialized view of the join of the MTR_BIN_BOUNDARY table and the MTR_ATTR_NAME_TO_ID_MAP table. It is used when transforming data during package builds.

4.2 Recommendation Engine

The RE schema stores current session data, package information, navigational information, as well as information about the taxonomy structure. RE data is sychronized back to the MTR automatically. The RE includes the following tables (partial list):

• Tables specific to the model rules tables that are currently deployed to the RE:

  ATTR_ID_BIN_BOUNDARY
  HOTPICK
  HOTPICK_GROUP
  I_I_ANTECEDENT
  I_I_RULE
  P_I_CATEGORY_RULES
  P_I_ITEM_RULES
  TAXONOMY_CATEGORY
  TAXONOMY_CATEGORY_ITEM
  TAXONOMY_TRANS_CLOSURE

• Internal tables:

  RE_DEPLOYABLE_PAPCKAGE
  RE_DEPLOYABLE_PKG_CONTENTS
  RE_ERROR_TABLE
  RE_INTERNAL_CONFIGURATION
  RE_LOG
  RE_PROFILE_DATA
  RE_SCHEMA_ACCESS
  RE_ACTIVE_USER

• Data tables:

  RE_CURRENT_SESSION_DATA
  RE_RECOMMENDATION_DETAIL

• Diagnostic table:

  RE_MESSAGE_LOG

• Configuration table; may need to be tweaked:

  RE_CONFIGURATION

HOT_PICKGROUP and HOTPICK are copies of the corresponding tables in the MTR.

RE_CURRENT_SESSION_DATA holds all the data collected using the data collection methods. This data is written back to the MTR using data synchronization.

RE_PROFILE_DATA stores the historical profiles of active users. When a user is detected, the profile of that user is loaded from the MTR to this table.

RE_RECOMMENDATION_DETAIL is the source of data for the corresponding table in the MTR. The data is synchronized back to the MTR.

ATTR_ID_BIN_BOUNDARY is a copy of the corresponding table in the MTR.

RE_CONFIGURATION and RE_INTERNAL_CONFIGURATION store the configuration parameters for the RE.

RE_DEPLOYABLE_PACKAGE keeps track of the deployable package that is currently deployed in the RE.

RE_LOG records events occurring in the RE.

RE_ACTIVE_USER stores information about all users who are currently active in the system. Data from this table is used to populate the session table in the MTR.

All other tables are used internally by the RE.

4.3 Mining Object Repository

Much of the administrative work done by OracleAS Personalization uses MOR tables and views. The MOR includes the following tables (partial list). These tables cannot be configured or modified by the user.

MOR_VISITOR_TO_BROWSER_REPORT
MOR_CONFIGURATION
MOR_CROSS_SOLD_ITEMS_REPORT
MOR_DEPLOYABLE_PACKAGE
MOR_EFFECTIVENESS_REPORT
MOR_EMAIL_ADDRESS
MOR_ERROR_TABLE
MOR_INTERNAL_CONFIGURATION
MOR_MESSAGE_LOG
MOR_MINING_MODEL
MOR_MINING_RESULT
MOR_MTR_CONNECTION
MOR_RECOMMENDATION_ENGINE
MOR_RECOMMENDATION_REPORT
MOR_RECOMMENDATION_STRATEGY
MOR_RE_FARM
MOR_SCHEDULE_EVENT
MOR_SCHEDULE_ITEM
MOR_SCHEMA_ACCESS
MOR_TAXONOMY_TRANS_CLOSURE
MOR_TRAN_SUPERVISED_RESULT