2 Oracle On Track Business Views

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:

• Overview of Business Views

• Business Views for Oracle On Track

• Security Considerations

Overview of Business Views

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.

Business Views for Oracle On Track

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:

• xcontent_rv

• xcontentobjects_rv

• xcontentsizes_rv

• xuser_rv

• xusernames_s_rv

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.

xcontent_rv

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)

Basic query for this view

The following SQL statement gets the number of each type of object available in the Oracle On Track Communication system.

SELECT COUNT(*) , objecttype
FROM xcontent_rv
GROUP BY objecttype;

xcontentobjects_rv

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)

Basic query for this view

The following SQL statement gets the number of each type of object available in the Oracle On Track Communication system.

SELECT COUNT(*) , objecttype
FROM xcontentobjects_rv
GROUP BY objecttype;

xcontentsizes_rv

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)

Basic query for this view

The following SQL statement gets the total size of the objecttype present in the Oracle On Track Communication system.

SELECT SUM(contentbloblength), objecttype
FROM xcontentsizes_rv
GROUP BY objecttype
HAVING SUM(contentbloblength)>0;

xuser_rv

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)

Basic query for this view

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.

xusernames_s_rv

This is a sensitive view that provides the names of the users in Oracle On Track Communication.

Name	Null?	Type
objectid	NOT NULL	NUMBER(38)
name		VARCHAR2(256 CHAR)

Basic query for this view

The following SQL statement gets the objectid of your admin user. The objectid of the your admin user can then be used in another SQL statement to remove your admin user from statistical reports.

SELECT objectid FROM xusernames_s_rv WHERE name='<your_admin_user>';

Security Considerations

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:

1. 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;
   

2. Create temporary tablespace.

   SQL> create temporary tablespace DEV_ONTRACK_REPORTINGVIEW_TEMP;
   

3. Create a new user and add privileges.

   1. 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>;
      

   2. 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;
      

   3. 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;
      

   4. 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;
      

   5. 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;
      

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