I WebCenter Analytics Database Schema

I.1.1 Analytics Database Model Overview

The Oracle WebCenter Analytics database schema is modeled as a star-schema to optimize performance and provide fast response times.

Analytics data is stored in fact and dimension fact tables:

• Analytics Fact Tables are created when the Analytics Collector runs for the first time. Each fact table stores a specific event type, for example, page views, Space views, document uploads, blog views, wiki edits, and so on. Columns in these fact tables mostly contain integer IDs that reference descriptive data stored in dimension tables.

  Table I-1, "Analytics Facts Tables (Prefixed ASFACT_WC)" lists types of analytics events that can be collected and stored, and the associated tables.

• Analytics Dimension Tables are also created when the Analytics Collector runs for the first time. Dimension tables store redundant descriptive data associated with analytics events. When a value is not found in these tables, a new record is added. Typical analytics dimension data includes: page name, browser name, document name, Space name, and so on.

  Table I-2, "Analytics Dimension Tables (Prefixed ASDIM_WC)" lists the different types of analytics dimensions that can be collected and stored, and the associated table names.

The Oracle WebCenter Analytics database schema also contains a few application tables that store information such as system configuration parameters. Application tables are created during installation.

Figure I-1 shows the relationship between predefined fact, dimension, and application tables in the analytics database schema.

Figure I-1 Analytics Database Model

Description of "Figure I-1 Analytics Database Model"

Tables shown in Figure I-1 store the following analytics data:

• Application configuration

• Event metadata

• Report metadata

Application Configuration

Figure I-2 shows which entities are used to store system parameters for the Analytics service.

Figure I-2 Application Configuration Entities

Description of "Figure I-2 Application Configuration Entities"

Event Metadata

Figure I-3 shows which entities are used to store event data. The Users and Times entities are mainly dimensions that provide user/time oriented views of the event data.

Event metadata records which events are collected, the tables where events are stored, as well as dimensions that are saved each time an event occurs.

Figure I-3 Event Metadata Entities

Description of "Figure I-3 Event Metadata Entities"

Report Metadata

Figure I-4 shows which entities represent out-of-the-box analytics reports. Metadata in these tables are the primary source for information such as report metrics, report structure, and columns listed.

Report metadata stores out-of-the-box report configurations for Oracle WebCenter Analytics. Each report contains many columns to display in the report and which filters to apply. Some reports are composed from other reports.

Figure I-4 Report Metadata Entities

Description of "Figure I-4 Report Metadata Entities"

I.1.2 Analytics Database Table Overview

This section lists and describes database tables in the analytics schema:

• Table I-1, "Analytics Facts Tables (Prefixed ASFACT_WC)"

• Table I-2, "Analytics Dimension Tables (Prefixed ASDIM_WC)"

• Table I-3, "Analytics System Tables (Prefixed ASSYS)"

SELECT tablename "Table Name",
       displayname "Event Display Name"
FROM   assys_events
WHERE tablename like 'ASFACT_WC_DOCLIB%'
ORDER BY 1;

Table Name            Event Display Name 
-----------------------------------------
ASFACT_WC_DOCLIB__0   {HTTP://WWW.ORACLE.COM/ANALYTICS/WC}DOCLIB_DOCUMENTVIEWS
ASFACT_WC_DOCLIB__1   {HTTP://WWW.ORACLE.COM/ANALYTICS/WC}DOCLIB_DOCUMENTDOWNLOADS
ASFACT_WC_DOCLIB__2   {HTTP://WWW.ORACLE.COM/ANALYTICS/WC}DOCLIB_DOCUMENTCREATE
ASFACT_WC_DOCLIB__3   {HTTP://WWW.ORACLE.COM/ANALYTICS/WC}DOCLIB_DOCUMENTEDIT
ASFACT_WC_DOCLIB__4   {HTTP://WWW.ORACLE.COM/ANALYTICS/WC}DOCLIB_DOCUMENTTAG
ASFACT_WC_DOCLIB__5   {HTTP://WWW.ORACLE.COM/ANALYTICS/WC}DOCLIB_DOCUMENTLIKE
ASFACT_WC_DOCLIB__6   {HTTP://WWW.ORACLE.COM/ANALYTICS/WC}DOCLIB_DOCUMENTCOMMENT
ASFACT_WC_DOCLIB__7   {HTTP://WWW.ORACLE.COM/ANALYTICS/WC}DOCLIB_DOCUMENTDELETES

I.2.1 ASFACT_WC_PAGECRE_0

Fact table that stores event information captured by the Analytics service when someone creates a page.

I.2.2 ASFACT_WC_PAGEDEL_0

Fact table that stores event information captured by the Analytics service when someone deletes a page.

I.2.3 ASFACT_WC_PAGEEDI_0

Fact table that stores event information captured by the Analytics service when someone edits a page.

I.2.4 ASFACT_WC_PAGETAG_0

Fact table that stores event information captured by the Analytics service when someone tags a page.

I.2.5 ASFACT_WC_PAGEVIE_0

Fact table that stores event information captured by the Analytics service when someone views a page.

I.2.6 ASFACT_WC_DISCUSS_0

Fact table that stores event information captured by the Analytics service when someone views a discussion topic.

I.2.7 ASFACT_WC_DISCUSS_1

Fact table that stores event information captured by the Analytics service when someone views an announcement.

I.2.8 ASFACT_WC_DISCUSS_2

Fact table that stores event information captured by the Analytics service when someone creates a discussion forum.

I.2.9 ASFACT_WC_DISCUSS_3

Fact table that stores event information captured by the Analytics service when someone deletes a discussion forum.

I.2.10 ASFACT_WC_DISCUSS_4

Fact table that stores event information captured by the Analytics service when someone creates a discussion topic.

I.2.11 ASFACT_WC_DISCUSS_5

Fact table that stores event information captured by the Analytics service when someone edits a discussion topic.

I.2.12 ASFACT_WC_DISCUSS_6

Fact table that stores event information captured by the Analytics service when someone deletes a discussion topic.

I.2.13 ASFACT_WC_DISCUSS_7

Fact table that stores event information captured by the Analytics service when someone tags a discussion topic.

I.2.14 ASFACT_WC_DISCUSS_8

Fact table that stores event information captured by the Analytics service when someone replies to a discussion topic message.

I.2.15 ASFACT_WC_DISCUSS_9

Fact table that stores event information captured by the Analytics service when someone likes a discussion topic message.

I.2.16 ASFACT_WC_DISCUSS_A

Fact table that stores event information captured by the Analytics service when someone creates an announcement.

I.2.17 ASFACT_WC_DISCUSS_B

Fact table that stores event information captured by the Analytics service when someone edits an announcement.

I.2.18 ASFACT_WC_DISCUSS_C

Fact table that stores event information captured by the Analytics service when someone deletes an announcement.

I.2.19 ASFACT_WC_DOCLIB__0

Fact table that stores event information captured by the Analytics service when someone views a document.

I.2.20 ASFACT_WC_DOCLIB__1

Fact table that stores event information captured by the Analytics service when someone downloads a document.

I.2.21 ASFACT_WC_DOCLIB__2

Fact table that stores event information captured by the Analytics service when someone creates document.

I.2.22 ASFACT_WC_DOCLIB__3

Fact table that stores event information captured by the Analytics service when someone edits a document.

I.2.23 ASFACT_WC_DOCLIB__4

Fact table that stores event information captured by the Analytics service when someone tags a document.

I.2.24 ASFACT_WC_DOCLIB__5

Fact table that stores event information captured by the Analytics service when someone "likes" a document.

I.2.25 ASFACT_WC_DOCLIB__6

Fact table that stores event information captured by the Analytics service when someone comments on a document.

I.2.26 ASFACT_WC_DOCLIB__7

Fact table that stores event information captured by the Analytics service when someone deletes a document.

I.2.27 ASFACT_WC_EVENT_C_0

Fact table that stores event information captured by the Analytics service when someone creates a Space event.

I.2.28 ASFACT_WC_EVENT_D_0

Fact table that stores event information captured by the Analytics service when someone edits a Space event.

I.2.29 ASFACT_WC_EVENT_E_0

Fact table that stores event information captured by the Analytics service when an event is deleted.

I.2.30 ASFACT_WC_GROUPSP_0

Fact table that stores event information captured by the Analytics service when someone views a Space.

I.2.31 ASFACT_WC_GROUPSP_1

Fact table that stores event information captured by the Analytics service when someone creates a Space in WebCenter Spaces.

I.2.32 ASFACT_WC_GROUPSP_2

Fact table that stores event information captured by the Analytics service when someone joins a Space in WebCenter Spaces.

I.2.33 ASFACT_WC_GROUPSP_3

Fact table that stores event information captured by the Analytics service when someone deletes a Space in WebCenter Spaces.

I.2.34 ASFACT_WC_LIST_CR_0

Fact table that stores event information captured by the Analytics service when someone creates a list.

I.2.35 ASFACT_WC_LIST_DE_0

Fact table that stores event information captured by the Analytics service when someone deletes a list.

I.2.36 ASFACT_WC_LIST_ED_0

Fact table that stores event information captured by the Analytics service when someone edits a list.

I.2.37 ASFACT_WC_LOGINS_0

Fact table that stores login event information captured by the Analytics service with related data for each event.

I.2.38 ASFACT_WC_PEOPLEC_0

Fact table that stores event information captured by the Analytics service when someone adds a people connection.

I.2.39 ASFACT_WC_PEOPLEC_1

Fact table that stores event information captured by the Analytics service when someone posts on a wall.

I.2.40 ASFACT_WC_PEOPLEC_2

Fact table that stores event information captured by the Analytics service when someone edits a profile.

I.2.41 ASFACT_WC_PEOPLEC_3

Fact table that stores event information captured by the Analytics service when someone edits status information.

I.2.42 ASFACT_WC_PORTLET_0

Fact table that stores information captured by the Analytics service when portlets are accessed by end users.

I.2.43 ASFACT_WC_SEARCHE_0

Fact table that stores event information captured by the Analytics service when someone performs a search.

I.3.1 ASDIM_WC_APPLICA_0

Dimension table that stores the names of WebCenter applications accessing analytics data, for example webenter (WebCenter Spaces application).

I.3.2 ASDIM_WC_CLIENT__0

Dimension table that stores client IP address details captured by the Analytics service when an analytics event is received.

I.3.3 ASDIM_WC_DISCUSS_0

Dimension table that stores discussion topic details captured by the Analytics service when an analytics event is received.

I.3.4 ASDIM_WC_DISCUSS_1

Dimension table that stores discussion forum details captured by the Analytics service when an analytics event is received.

I.3.5 ASDIM_WC_DISCUSS_2

Dimension table that stores discussion message details captured by the Analytics service when an analytics event is received.

I.3.6 ASDIM_WC_DISCUSS_3

Dimension table that stores announcement details captured by the Analytics service when an analytics event is received.

I.3.7 ASDIM_WC_DOCUMENT_0

Dimension table that stores information captured by the Analytics service when documents are accessed by end users.

I.3.8 ASDIM_WC_EVENTS_0

Dimension table that stores information about Space events captured by the Analytics service when a user creates an event in his or her calendar.

I.3.9 ASDIM_WC_GROUPSP_0

Dimension table that stores information captured by the Analytics service when a Space is accessed by an end user (WebCenter Spaces only).

I.3.10 ASDIM_WC_LISTS_0

Dimension table that stores list details captured by the Analytics service when an analytics event is received.

I.3.11 ASDIM_WC_PAGES_0

Dimension table that stores information captured by the Analytics service when pages are accessed by end users.

I.3.12 ASDIM_WC_PORTLET_0

Dimension table that stores information captured by the Analytics service when portlets are accessed by end users.

I.3.13 ASDIM_WC_PORTLET_1

Dimension table that stores portlet instance information captured by the Analytics service when an analytics event is received.

I.3.14 ASDIM_WC_PRODUCE_0

Dimension table that stores portlet producer information captured by the Analytics service when an analytics event is received.

I.3.15 ASDIM_WC_REFERRE_0

Dimension table that stores information captured by the Analytics service related to the location (URL) to which the user was navigating when the analytics event was captured.

I.3.16 ASDIM_WC_SEARCHE_0

Dimension table that stores search phrases captured by the Analytics service when an analytics event is received.

I.3.17 ASDIM_WC_TAGS_0

Dimension table that stores tag information captured by the Analytics service when an analytics event is received.

I.3.18 ASDIM_WC_USER_AG_0

Dimension table that stores information about whichbrowsers are used to access analytics data.

I.3.19 ASDIM_TIME

Dimension table that stores times used to group analytics reports. An entry is created every hour. analytics reports use this table to group data within a specific time frame.

I.3.20 ASDIM_USERS

Dimension table that stores user details captured by the Analytics service when an analytics event is received.

I.3.21 ASDIM_USERPROPERTIES

Dimension table that stores user properties, such as Title, Department, and so on.

I.3.22 ASDIM_USERPROPERTYVALUES

Dimension table that stores values for user properties.

I.4.1 ASSYS_CONFIG

System table that stores system configuration information. Each row contains value pairs—configuration type/value.

I.4.2 ASSYS_DIMENSIONPROPS

System table that stores dimension configuration information. Each row represents property values for a dimension.

I.4.3 ASSYS_EVENTDIMENSIONS

System table that stores dimensions associated with each analytics event.

I.4.4 ASSYS_EVENTFACTS

System table that stores facts associated with each analytics event.

I.4.5 ASSYS_EVENTS

System table that stores the different analytics events representing one user action that is captured. Both out-of-the-box analytics events and custom (user-defined) analytics events are stored. Each row represents the event captured with its associated data such as active status, and table name where data is stored.

I.4.6 ASSYS_NAMESPACES

System table that stores the different namespaces in which analytics events are registered.

I.4.7 ASSYS_REPORTCOMPOSITION

System table that stores the composition for reports provided by the Analytics service.

I.4.8 ASSYS_REPORTGROUP

System table that stores the report groups included for the Analytics service. Report groups enable end users to display analytics data in a specific way.

I.4.9 ASSYS_REPORTITEMS

System table that stores item information in each report. Each row represents a specific item belonging to one report.

I.4.10 ASSYS_REPORTITEMVALUES

System table that stores the values associated to each item in a report. Each row represents a specific value (char, float or date) for one report item instance.

*Only one of CVALUE, NVALUE or DVALUE has a value.

I.4.11 ASSYS_REPORTS

System table that stores the different out-of-the-box reports to be provided by the Analytics service. Each row represents a single report and the report's configuration.

I.5.1 User Property Tables - Overview

Figure I-5 shows the database model used by the Analytics service to store information about users. The user dimension table has a single property column (USERID). In addition, there are two other tables (ASDIM_USERPROPERTIES and ASDIM_USERPROPERTYVALUES) that store user properties as key-value data. This model enables additional user properties to be added without changing the user dimension definition.

Figure I-5 User Property Dimensions for Analytics

Description of "Figure I-5 User Property Dimensions for Analytics"

The ASDIM_USERPROPERTIES table stores the names of user properties received by the Analytics service. User properties (attributes of the user including name, position, email, address, etc) are captured by the Analytics service as an event when a user logs in to Spaces, not when the properties are modified. If a user changes a property, the value of the property will be updated in analytics the next time the user logs in.

The ASDIM_PROPERTYVALUES table stores one row per property, per user, where each row corresponds to a value for a property associated with a specific user.

I.5.2 User Property Dimension Tables

User property dimension tables are described in the following topics:

• Section I.5.2.1, "ASDIM_USERPROPERTIES"

• Section I.5.2.2, "ASDIM_USERPROPERTYVALUES"

I.5.3 Out-of-the-Box User Properties for WebCenter

WebCenter applications send a specific set of user properties to the Analytics service, as shown in Table I-4.

* STREET stores the user's home address street.