4 OracleAS Personalization Schemas

4.1.1 Item Table

The item table (MTR_ITEM) contains a list of all the individual items that the application deals with. When OracleAS Personalization returns a recommendation, it returns an item's ID and ITEM_TYPE. The ID and ITEM_TYPE together uniquely identify an item. Different item types may use the same ID. The item table is usually mapped to the catalog tables in the application database. The schema for MTR_ITEM has four fields; they are listed in Table 4-2 with their data types. The LABEL column can be used for any locally-defined purpose; for example, it could contain a catalog identifier used in the local database.

4.1.2 Bin Boundaries

The model-building algorithms in OracleAS Personalization employ counting techniques to calculate probabilities. The data manipulated by the algorithms must be discrete, that is, not continuous. Hence, numeric data must be divided into bins to present discrete values to OracleAS Personalization. The process of dividing data into bins is called binning.

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 the bin boundaries table for OracleAS Personalization to bin the values.

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

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

Taxonomies are implemented by using a group of tables. Taxonomies are specified by the OracleAS Personalization user at MTR setup time. These tables are:

• MTR_TAXONOMY: Lists the different taxonomies used by an application. Each taxonomy has a unique ID, name, and description. The schema for this table has three fields; they are listed in Table 4-4 with their data types:

• MTR_CATEGORY: Specifies the different categories to be used in the taxonomy. The schema for this table has three fields: the unique identifier of the category, its name, and a description. They are listed in Table 4-5 with their data types:

• 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-6 with their data types. Each row in this table describes an "edge" of the taxonomy graph, that is, a line connecting two categories. The TAXONOMY_LEVEL is the level of the parent category. PARENT_ID and CHILD_ID are values from the MTR CATEGORY table.

• MTR_TAXONOMY_CATEGORY_ITEM: Specifies which items go with a given taxonomy-category pair. In other words, it lists each item as a member of a particular category in a particular taxonomy. If an item belongs to more than one category, the item is listed once for each category. The schema for this table has four fields; they are listed in Table 4-7 with their data types:

Samples of the MTR Taxonomy Tables

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

4.1.4 Customer Table

The MTR_CUSTOMER table contains demographic information about an application's registered customers. 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.

Fifty generic attributes are available in the table that can be defined specifically for your application. The first 25 of these attributes are of type VARCHAR2, and the second 25 are of type NUMBER.

All customer attributes are locally defined and are mapped from the application database or collected through application registration procedures.

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

4.1.5 Hot Picks

Hot picks are used by some Web sites to force recommendations from a particular group of items. For example, items that are daily specials may 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 unique 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.

• MTR_HOTPICK lists the items in each hot pick group, arranged according to group ID (corresponding to the ID field in MTR_HOTPICK_GROUP). 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 in Table 4-10. 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 will likely not be any mention of this item in any customer profile. As such, the predictive models will not be able to identify any rules for it, and no recommendations can be made for that item. Hence, OracleAS Personalization uses data about a similar existing product, called a proxy. The fields in the MTR_PROXY table are listed in Table 4-11.

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 stores the navigational data corresponding to a user session. Each record contains a SESSION_ID which is linked to the CUSTOMER_ID through the session table. In case there is no SESSION_ID collected, CUSTOMER_ID is used directly. One of the two must be not null. The ID and type of the item, and the time of the activity are also stored. The MTR_CUSTOMER_NAV_DETAIL table is populated with data collected in the RE using the data collection APIs. If the application is already collecting this data, the table could be a view defined on the application's tables. The fields of the table are listed in Table 4-12.

  Table 4-12 MTR_CUSTOMER_NAV_DETAIL Fields

  Field	Data Type
  SESSION_ID	NUMBER
  TIMESTAMP	DATE
  NAVIGATIONAL_VALUE	NUMBER
  ITEM_ID	NUMBER
  ITEM_TYPE	VARCHAR2 (30)
  CUSTOMER_ID	VARCHAR2 (32)

  
  

• MTR_CUSTOMER_RATING_DETAIL stores rating data for customers. The RATING_VALUE attribute stores the value of the rating. This table is populated using the data collected in the RE through the data collection APIs. In cases where the rating data is already collected by the application, this table could be a view defined on the application's tables. The fields in this table are listed in Table 4-13.

  Table 4-13 MTR_CUSTOMER_RATING_DETAIL Fields

  Field	Data Type
  TIMESTAMP	DATE
  RATING_VALUE	NUMBER
  CUSTOMER_ID	VARCHAR2 (32)
  ITEM_ID	NUMBER
  ITEM_TYPE	VARCHAR2(30)

  
  

• MTR_PURCHASING_DETAIL stores purchasing data on a per-session basis. Typically this data is collected by the application. CUSTOMER_ID provides navigability when SESSION_ID is not collectible. The table fields are listed in Table 4-14.

  Table 4-14 MTR_PURCHASING_DETAIL Fields

  Field	Data Type
  ORDER_ID	NUMBER
  SESSION_ID	NUMBER
  TIMESTAMP	DATE
  PURCHASING_VALUE	NUMBER
  ITEM_ID	NUMBER
  ITEM_TYPE	VARCHAR2 (30)
  CUSTOMER_ID	VARCHAR2 (32)

  
  

• 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. Each record contains a SESSION_ID which is linked to the VISITOR_ID through the session table. In case there is no SESSION_ID collected, VISITOR_ID is used directly. One of the two must be not null. The ID and type of the item, and the time of the activity are also stored. This table is populated with data collected in the RE using the data collection APIs. If the application is already collecting this data, this table could be a view defined on the application's tables. The fields in the MTR_VISITOR_NAV_DETAIL table are listed in Table 4-15.

  Table 4-15 MTR_VISITOR_NAV_DETAIL Fields

  Field	Data Type
  SESSION_ID	NUMBER
  VISITOR_ID	VARCHAR2 (32)
  NAVIGATIONAL_VALUE	NUMBER
  TIMESTAMP	DATE
  ITEM_ID	NUMBER
  ITEM_TYPE	VARCHAR2 (30)

  
  

• MTR_VISITOR_RATING_DETAIL stores rating data for visitors. The RATING_VALUE attribute stores the value of the rating. This table is populated using the data collected in the RE using the data collection APIs. In cases where the rating data is already collected by the application, this table could be a view defined on those tables. The table's fields are listed in Table 4-16.

  Table 4-16 MTR_VISITOR_RATING_DETAIL Fields

  Field	Data Type
  TIMESTAMP	DATE
  RATING_VALUE	NUMBER
  VISITOR_ID	VARCHAR2 (32)
  ITEM_ID	NUMBER
  ITEM_TYPE	VARCHAR2 (30)

  
  

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. It links an attribute name to an ID number.

• MTR_CONFIGURATION and MTR_INTERNAL_CONFIGURATION stores configuration information.

  Table 4-17 MTR_CONFIGURATION Fields

  Field	Data Type
  DATA_TYPE	VARCHAR2 (32)
  NAME	VARCHAR2 (128) PK
  VALUE	VARCHAR2 (128)
  DESCRIPTION	VARCHAR2 (100)

  
  

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