Oracle® On Track Communication Analytics Guide Release 1 (1.0) Part Number E20960-01 |
|
|
View PDF |
This chapter describes the different Business Views available for Oracle On Track Communication and an approach suggested to access these Business Views.
This chapter includes the following sections:
The Oracle On Track Business Views are a collection of Oracle Database Views which are provided within the On Track schema to facilitate statistical reporting data. A Business View is used to obtain information about how users use the application rather than about the health and status of the system that is presented in the System Reporting API chapter of this document.
Database administrators can query an Oracle On Track Business View to obtain statistical reports based on the Oracle On Track application data. These reports are analyzed to understand application use.
This section presents the details of the supported business views for Oracle On Track Communication and some basic SQL statements to retrieve data using the On Track business views.
Two types of business views are created in the On Track schema:
Regular Reporting Views (X_RV)
The names of these views end with _rv. Results of Regular Reporting Views do not contain sensitive information.
Sensitive Reporting Views (X_S_RV)
The names of these views end with _s_rv. Results of Sensitive Reporting Views include sensitive information that, for Oracle On Track Business Views, refers to user content.
Note:
The only user content that is exposed in R1.0 is user names.The following Oracle On Track Business Views are created in the On Track schema during the installation process as part of the On Track Schema component:
To have a better understanding of the information that can be retrieved using Oracle On Track Business Views, the following list presents a brief description of some columns used in the different business views:
OBJECTID: The Unique ID of the Object.
OBJECTTYPE: The Object Type in the format waggle/<type>, e.g. waggle/conversation.
TRACKID: Unique ID of the track that the object belongs to.
CONVERSATIONID: Unique ID of the conversation that the object belongs to.
PARENTID: The PARENTID of the Object, this can be used as a predicate with OBJECTID.
LASTCONNECTEDDATE: Last time the user connected to the system.
LASTDISCONNECTEDDATE: Last time the user disconnected to the system. This can sometimes be smaller than the last connecteddate if the user is currently logged in.
This view contains top-level Objects like Conversation, as well as versions of other object types. Working with this view is more complex in terms of sql queries.
The following table displays the column properties for this view:
Name | Null? | Type |
---|---|---|
objectid | NOT NULL | NUMBER(38) |
objecttype | NOT NULL | VARCHAR2(64 CHAR) |
trackid | NUMBER(38) | |
conversationid | NUMBER(38) | |
createdtimestamp | TIMESTAMP(6) | |
cratedbyid | NUMBER(38) | |
createdonbehalfofid | NUMBER(38) | |
parentobjectttype | NOT NULL | VARCHAR2(64 CHAR) |
contentmimetype | NOT NULL | VARCHAR2(255 CHAR) |
parentid | NUMBER(38) |
This view provides is a flattened view of the XCONTENT_RV and can be used for simpler queries. In the XCONTENTOBJECTS_RV the type of waggle/version is removed and is designated using ISVERSION.
The following table displays the column properties for this view:
Name | Null? | Type |
---|---|---|
objectid | NOT NULL | NUMBER(38) |
objecttype | VARCHAR2(64 CHAR) | |
createdonbehalfofid | NUMBER(38) | |
isversion | CHAR(1) | |
trackid | NUMBER(38) | |
conversationid | NUMBER(38) | |
createdstamp | TIMESTAMP(6) | |
parentobjecttype | NOT NULL | VARCHAR2(64 CHAR) |
ccontentmimetype | NOT NULL | VARCHAR2(255 CHAR) |
parentid | NUMBER(38) |
This view is similar to XCONTENT_RV but also include length information.
The following table displays the column properties for this view:
Name | Null? | Type |
---|---|---|
objectid | NOT NULL | NUMBER(38) |
objecttype | NOT NULL | VARCHAR2(64 CHAR) |
trackid | NUMBER(38) | |
conversationid | NUMBER(38) | |
createdtimestamp | TIMESTAMP(6) | |
cratedbyid | NUMBER(38) | |
createdonbehalfofid | NUMBER(38) | |
parentobjectttype | NOT NULL | VARCHAR2(64 CHAR) |
contentmimetype | NOT NULL | VARCHAR2(255 CHAR) |
contentbloblength | NUMBER | |
parentid | NUMBER(38) |
This view contains information about the users in Oracle On Track Communication as well as their log in and log out status.
The following table displays the column properties for this view:
Name | Null? | Type |
---|---|---|
objectid | NOT NULL | NUMBER(38) |
lastconnecteddate | TIMESTAMP(6) | |
lastdisconnecteddate | TIMESTAMP(6) |
The following SQL statement shows the number of users connected in the last month.
SELECT COUNT(*) FROM xuser_rv WHERE lastconnecteddate > ADD_MONTHS(sysdate,-1);
Note:
If the user is still connected the last disconnected date can actually be smaller than the last connected date.In order to provide good analytical data, Oracle On Track Business Views were created to expose Oracle On Track Communication data stored in the On Track schema to Business Intelligence Systems.
In order to meet with reporting and security requirements, the approach suggested for Oracle On Track Communication is the creation of a new Reporting View schema. This approacht involves the creation of a tablespace and a new user with the necessary privileges to access the Oracle On Track Business Views.
The following steps explain how to follow the suggested approach:
Create a new tablespace.
As a user with SYSDBA privileges, create a tablespace in the Oracle On Track Communication database matching the schema prefix from your install. This example creates a tablespace named DEV_ONTRACK_REPORTINGVIEW_TS.
SQL> create tablespace DEV_ONTRACK_REPORTINGVIEW_TS;
Create temporary tablespace.
SQL> create temporary tablespace DEV_ONTRACK_REPORTINGVIEW_TEMP;
Create a new user and add privileges.
As a user with SYSDBA privileges, create a new user and set the new tablespaces as the default tablespace for this user. This example creates a user named DEV_ONTRACK_REPORTINGVIEW.
SQL> create user DEV_ONTRACK_REPORTINGVIEW default tablespace DEV_ONTRACK_REPORTINGVIEW_TS temporary tablespace DEV_ONTRACK_REPORTINGVIEW_TEMP identified by <user_password>;
As a user with DBA privileges, grant CONNECT permission to the user created in step 3a. This example grants the CONNECT permission to a user namedDEV_ONTRACK_REPORTINGVIEW.
SQL> grant connect to DEV_ONTRACK_REPORTINGVIEW;
As a user with SYSDBA privileges, grant CREATE SYNONYM permission to the user created in step 3a. This example grants the CREATE SYNONYM permission to user named DEV_ONTRACK_REPORTINGVIEW.
SQL> grant create synonym to DEV_ONTRACK_REPORTINGVIEW;
As an On Track schema user, for example DEV_ONTRACK, grant SELECT permission on Oracle On Track Business View for the user created in step 3a. These examples grants the SELECT permission to user named DEV_ONTRACK_REPORTINGVIEW to all Oracle On Track Business Views.
Note:
Grant select permission to xusername_s_rv if you wish to allow access to user content.SQL> grant select on xcontent_rv to DEV_ONTRACK_REPORTINGVIEW; SQL> grant select on xcontentobjects_rv to DEV_ONTRACK_REPORTINGVIEW; SQL> grant select on xcontentsizes_rv to DEV_ONTRACK_REPORTINGVIEW; SQL> grant select on xuser_rv to DEV_ONTRACK_REPORTINGVIEW; SQL> grant select on xusernames_s_rv to DEV_ONTRACK_REPORTINGVIEW;
As the user created in step 3a, create synonyms for the Oracle On Track Business Views. These examples create synonyms of On Track Business Views in the DEV_ONTRACK schema.
Note:
Create synonym for <prefixOnTrack_ schema>. xusername_s_rv if you wish to allow access to user content..SQL> create synonym xcontent_rv for DEV_ONTRACK.xcontent_rv; SQL> create synonym xcontentobjects_rv for DEV_ONTRACK.xcontentobjects_rv ; SQL> create synonym xcontentsizes_rv for DEV_ONTRACK.xcontentsizes_rv ; SQL> create synonym xuser_rv for DEV_ONTRACK.xuser_rv ; SQL> create synonym xusernames_s_rv for DEV_ONTRACK.xusernames_s_rv;
As SYSDBA, revoke the create synonym privilege from the user created in step 2.a. In this example the privelege is revoked from DEV_ONTRACK_REPORTINGVIEW user.
SQL> revoke create synonym from DEV_ONTRACK_REPORTINGVIEW;
In General, data mining queries can be expensive and running these queries in the Oracle On Track Communication database can cause performance issues.
There are a wealth of options available in controlling the amount of resources used in querying the views including the use of Oracle's Database Resource Manager.
One approach would be to create Materialized Views that are synced once a week during a off peak time. The following figure is a graphical representation of the use of Materialized Views in a separate Reporting database working with the Reporting View schema, for further details on this approach refer to Appendix A.