Skip Headers
Oracle® Fusion Middleware Developer's Guide for Oracle WebCenter
11g Release 1 (11.1.1.5.0)

Part Number E10148-16
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

46 Integrating the Analytics Service

The Analytics service enables you to display usage and performance metrics for WebCenter Portal applications. This chapter describes how to integrate the Analytics service into WebCenter Portal applications.

This chapter includes the following sections:

For information about using any of the analytics task flows, see Chapter "Working with the Analytics Service" in Oracle Fusion Middleware User's Guide for Oracle WebCenter Spaces.

For a detailed list of database tables and parameters, see Appendix I, "WebCenter Analytics Database Schema."

46.1 Introduction to the Analytics Service

The Analytics service offers real-time usage and activity reporting for your portal. This section provides an overview of the Analytics service, its task flows, and its usages. It includes the following subsections:

46.1.1 Understanding the Analytics Service

The Analytics service allows administrators and users to track and analyze traffic and usage in WebCenter Portal applications. The Analytics service provides the following basic functionality:

  • Usage Tracking Metrics: The Analytics service collects and reports metrics for common portal functions, including community, page, portlet, and document hits.

  • Behavior Tracking: The Analytics service can be used to analyze WebCenter Spaces metrics to determine usage patterns, such as Space visit duration and usage over time.

  • User Profile Correlation: The Analytics service can be used to correlate metric information with user profile information. Usage tracking reports can be viewed and filtered by user profile data such as country, company or title.

  • Custom Reporting: Developers and business users can build custom reports that query the analytics data. For more details, see Section 46.3.1, "Using SQL Data Controls".

Table 46-1 lists the analytics task flows available with Oracle WebCenter. For detailed information about these task flows and how to use them in WebCenter applications, see "Understanding Analytics Task Flows in WebCenter Spaces" in Oracle Fusion Middleware User's Guide for Oracle WebCenter Spaces.

Table 46-1 Analytics Task Flows Available in JDeveloper

WebCenter Analytics Task Flows Description

WebCenter Traffic

A summarized view for common events within the portal.

Page Traffic

Displays the number of page hits and the number of unique users that visited any page within the portal.

Login Metrics

Reports portal logins.

Portlet Traffic

Displays usage data for a portlet.

Portlet Response Time

Displays performance data for a portlet.

Portlet Instance Traffic

Displays usage data for a portlet instance*.

Portlet Instance Response Time

Displays performance data for a portlet instance*.

Search Metrics

Tracks portal searches.

Document Metrics

Tracks document views.

Wiki Metrics

Tracks most popular/least popular wikis.

Blog Metrics

Tracks most popular/least popular blogs.

Discussion Metrics

Tracks most popular/least popular discussions.


* If the same portlet is displayed on several different pages, each placement is known as a portlet instance.

46.1.2 Requirements for the Analytics Service

The Analytics service requires that the analytics schema (ACTIVITIES) is installed and up and running. In addition, the Oracle WebCenter Analytics Collector must be up and running on the WC_Utilities managed server. For detailed installation instructions, see Oracle Fusion Middleware Installation Guide for Oracle WebCenter.

On install, the Analytics Collector is configured to receive events out-of-the-box, using installation defaults. If the default values are not suitable for your installation or you want to deploy Analytics Collectors in a cluster, you may need to configure different values using WLST or Fusion Middleware Control. For more details, see "Configuring Analytics Collector Settings" Oracle Fusion Middleware Administrator's Guide for Oracle WebCenter.

To display usage and performance metrics through analytics task flows you must enable event generation for your WebCenter Portal application and configure a connection to the analytics database (ACTIVITIES). For details on how to do this in JDeveloper, see Section 46.2.2, "Setting up Connections for the Analytics Service".

46.1.3 What Happens at Runtime

At runtime, user activity in your WebCenter Portal application generates event data. For example, every time a user logins in, reads a discussion topic, views a document, and so on, the event is recorded. The OpenUsage API sends event metrics to the Analytics Collector using UDP (User Datagram Protocol) and the event data is stored in the analytics database (ACTIVITIES).

In a non clustered environment the WebCenter Portal application is configured with the location of the collector, and all events are transmitted to that location.

In a clustered environment both the WebCenter Portal application and Analytics Collector are configured with a cluster-specific channel name. Each collector periodically broadcasts a heartbeat with its location to the cluster-specific channel. The WebCenter Portal application listens to the channel for these collector heartbeats, and when it hears one, adds the collector to its list of known collectors. When the WebCenter Portal application sends an event it randomly selects a collector from its list and sends the event to that collector. If a collector stops (either being stopped purposefully or failing) it stops broadcasting a heartbeat. When the WebCenter Portal application stops hearing the heartbeat it removes the collector from its list and stops sending events to that collector. If the WebCenter Portal application does not hear any collector heartbeats it does not send any events.

Analytics task flows and custom analytics reports (based on SQL data controls) that are included in WebCenter Portal applications display the metrics collected for standard events by querying the analytics database at runtime. For more information about the task flows at runtime, see "Working with Analytics Task Flows" in Oracle Fusion Middleware User's Guide for Oracle WebCenter Spaces.

46.2 Basic Configuration for the Analytics Service

This section describes how to include the Analytics service in your WebCenter Portal application.

This section contains the following subsections:

See also, Section 46.1.2, "Requirements for the Analytics Service".

46.2.1 Configuration Roadmap for the Analytics Service

The flow chart depicted in Figure 46-1 and Table 46-2 in this section provide an overview of the prerequisites and tasks required to get the Analytics service working in WebCenter Portal applications.

Figure 46-1 Configuring the Analytics Service for WebCenter Portal Applications

Roadmap for configuring the Analytics service Install WebCenter and the back-end components for the Analytics service Configure a connection to the Activities database in JDeveloper Configure a connection to the Analytics Collector in JDeveloper Add an Analytics task flow to a page in JDeveloper JDeveloper Fusion Middleware Control WLST WLS Admin Console (Optional) Configure the Analytics Collector Use the System MBean Browser Use WLST Use JDeveloper, then redeploy the application Use Fusion Middleware Control Use WLST

Table 46-2 Configuring the Analytics Service for WebCenter Portal Applications

Actor Task Sub-task

Administrator

1. Install WebCenter and the back-end components for the Analytics service

 

Developer

2. Integrate the Analytics service in your WebCenter Portal application

2.a Configure a connection to the Activities database in JDeveloper

2.b Configure a connection to the Analytics Collector in JDeveloper

2.c Add an analytics task flow to a page in JDeveloper

Developer/

Administrator

3. Deploy the WebCenter Portal application using one of the following tools:

 

Administrator

4. (Optional) Configure the Analytics Collector using either of the following tools:

 

Developer/

Administrator

5. (Optional) Add/modify connection parameters using one of the following tools:

 

End User

6. Test that analytics data is available in the WebCenter Portal application

6.a Log in to the WebCenter Portal application

6.b Display the analytics task flow


46.2.2 Setting up Connections for the Analytics Service

The Analytics service requires that the WebCenter Portal application is connected to the analytics database (ACTIVITIES). You must also configure the WebCenter Portal application to send event information to a specific Analytics Collector.

Note:

While you can set up the connections to back-end servers at design time in JDeveloper, you can later add, delete, or modify connections in your deployed environment using Enterprise Manager Fusion Middleware Control. For more information, see "Managing the Analytics Service" in Oracle Fusion Middleware Administrator's Guide for Oracle WebCenter.

This section includes the following subsections:

46.2.2.1 How to Set Up a Connection to the Analytics Database

The Analytics service requires a connection to the analytics database (ACTIVITIES) where analytics event data is stored.

To set up the analytics database connection:

  1. Ensure that the ACTIVITIES database is up and running.

    See Section 46.1.2, "Requirements for the Analytics Service".

  2. In the Application Navigator, expand the Applications Resources panel.

  3. Right-click Connections, then choose New Connection, and then Database.

  4. In the Create Database Connection dialog, enter the following information for the database connection:

    • Connection Name: connectionName (for example, myAnalyticsDatabaseConnection

    • Connection Type: Oracle (JDBC)

    • Username: username (a user with access to the database)

    • Password: password (the specified user's password)

    • Host Name: host (where the analytics database is installed, for example localhost)

    • JDBC Port: port (for example, 1521)

    • SID: sid (system identifier for the database)

  5. Click Test Connection, and if it is successful, then click OK.

  6. In the Associate to Data Source dialog, choose ActivitiesDS (Figure 46-2).

    Figure 46-2 Connecting to the Analytics Database

    ActivitiesDS to connect to Analytics Database

    This creates a data-source to the ACTIVITIES schema so that you can test analytics in your WebCenter Portal application at design-time.

  7. Click OK.

    The connection appears as a node in the Application Resources pane, under Connections.

46.2.2.2 How to Set Analytics Collector Properties

Out-of-the-box, the Analytics Collector is installed on the WC_Utilities managed server and is configured to receive events using the following default values:

Analytics Collector Configuration Default Value
Collector Host Name localhost
Default Port 31314
Maximum Port Number 31314
Broadcast Type Multicast
Clustering

Cluster Name

Cluster Broadcast Frequency

Disabled

- null

- 10 seconds


If these defaults are not suitable for your installation, your administrator can configure suitable values using WLST or the Fusion Middleware Control. For more information, see "Configuring Analytics Collector Settings" in Oracle Fusion Middleware Administrator's Guide for Oracle WebCenter.

46.2.2.3 How to Set Up a Connection to the Analytics Collector

The Analytics service requires a connection to the Analytics Collector that is collecting WebCenter events and OpenUsage must be enabled in the WebCenter Portal application.

Use the following OpenUsage JVM properties to set properties for the Analytics service:

  • oracle.wc.openusage.enabled - Specifies whether to send analytics events raised using OpenUsage APIs to the Analytics Collector. Valid values are true and false. The default value is false.

  • oracle.wc.openusage.unicast - Specifies whether events are sent to a clustered Analytics Collector in multicast mode or whether a single Analytics Collector using unicast communication is required. Valid values are true and false. The default value is true (unicast).

  • oracle.wc.openusage.clustername - Name of the collector cluster or the host name of an Analytics Collector (if unicast is enabled). The default value is localhost.

  • oracle.wc.openusage.collectorport - Port on which the Analytics Collector listens for events. The default value is 31314.

  • oracle.wc.openusage.timeout Period of time (in seconds) used to determine availability of the collector service in multicast mode. The default value is 30 seconds.

To set Analytics Collector JVM properties:

  1. In JDeveloper, choose Run, Active Run Configuration, and then Manage Run Configurations from the main menu.

  2. In the Project Properties dialog, select the Default run configuration, and then click Edit.

  3. In the Launch Settings section, add the following OpenUsage options to the Java Options field as follows:

    -Doracle.wc.openusage.enabled=true -Doracle.wc.openusage.clustername=localhost
    
    -Doracle.wc.openusage.collectorport=31314 
    
    -Doracle.wc.openusage.unicast=true 
    
    -Doracle.wc.openusage.timeout=30
    
    

    Ensure that the values you provide, shown here in bold, match your Analytics Collector installation.

  4. Click OK to save the default run configuration, and then click OK again.

    These steps enable OpenUsage in the Integrated WebLogic Server for the current WebCenter Portal application. If the Integrated WebLogic Server is currently running you must restart it to pick up the new settings. See also, Section 68.2, "Deploying a WebCenter Portal Application to the Integrated WebLogic Server".

46.2.3 Configuring a Namespace for Analytics Customizations in MDS

Analytics task flows use MDS to store customizations made by the user and these customizations are stored in an MDS namespace specific to analytics. If you want to enable user customizations for analytics task flows in your WebCenter Portal application, you must configure a namespace for the analytics metadata in adf-config.xml.

Note:

In addition, each task flow must specify a unique MDS document in which to its store user customizations details. See Section 46.2.4.4, "How to Allow End Users to Customize Analytics Task Flows at Runtime".

To configure a namespace for analytics metadata:

  1. Open adf-config.xml.

    Use the Application Resources panel to navigate to this file. The file is located in the Descriptors\ADF META-INF folder.

  2. Under the <metadata-namespaces> element, add the following XML fragment:

    <namespace path="/oracle/webcenter/analytics/scopedMD" metadata-store-usage="WebCenterFileMetadataStore"/>

  3. Save the file.

See also, Chapter 22, "Performing Oracle Composer-Specific MDS Configurations".

46.2.4 Adding Analytics Task Flows at Design Time

This section describes the Analytics service task flows and how to add analytics task flows to your application. It includes the following subsections:

46.2.4.1 Analytics Task Flows

Table 46-3 lists the analytics task flows available with Oracle WebCenter. For detailed information about these task flows and the type of information that users can see at runtime, see "Understanding Analytics Task Flows in WebCenter Spaces" in Oracle Fusion Middleware User's Guide for Oracle WebCenter Spaces.

Table 46-3 Analytics Task Flows Available in JDeveloper

WebCenter Analytics Task Flows Description

WebCenter Traffic

A summarized view for common events within the portal.

Page Traffic

Displays the number of page hits and the number of unique users that visited any page within the portal.

Login Metrics

Reports portal logins.

Portlet Traffic

Displays usage data for a portlet.

Portlet Response Time

Displays performance data for a portlet.

Portlet Instance Traffic

Displays usage data for a portlet instance*.

Portlet Instance Response Time

Displays performance data for a portlet instance*.

Search Metrics

Tracks portal searches.

Document Metrics

Tracks document views.

Wiki Metrics

Tracks most popular/least popular wikis.

Blog Metrics

Tracks most popular/least popular blogs.

Discussion Metrics

Tracks most popular/least popular discussions.


46.2.4.2 How to Add Analytics Task Flows to a Page

To add an analytics task flow to your WebCenter Portal application:

  1. Prepare your application as described in Section 46.1.2, "Requirements for the Analytics Service".

  2. Ensure that your application includes the services for which analytics event data is available, that is, portlets, discussions, documents, and search.

    Note:

    If no usage data exists when you run the application, analytics task flows displays the message: No data to display
  3. Open the JSF page (.jspx) on which you want to add the task flow.

  4. In the Resource Palette, open the WebCenter Services Catalog, then open the Task Flows folder (Figure 46-3).

    Figure 46-3 Analytics Task Flows Available Through the Resource Palette

    Analytics Task Flows Available
  5. Drag and drop an Analytics task flow onto your page inside the <af:form> tag.

  6. Choose Region from the Create context menu.

    You may be prompted to add the ADF library for the Analytics service (analytics-reporting-service-view.jar) to the project. Confirm by clicking Add Library (Figure 46-4).

    Figure 46-4 Adding the ADF Library for the Analytics Service

    Adding the ADF library for the Analytics Service
  7. In the Edit Task Flow Binding dialog, enter values for analytics task flow parameters:

    • applicationName - Required

    • analyticsResourceId - Required

    • analyticsReportTitle - Optional

    • maxDataPointsPerSeries - Optional

    For more information, see Section 46.2.4.5, "Analytics Task Flows and Task Flow Parameters".

  8. Click OK.

    The task flow is added to the page, and the ViewController project's libraries are configured to run the task flow.

  9. Save your project and run the page to see the task flow running in the WebCenter Portal application.

    If no usage data exists when you run the application, analytics task flows display the message: No data to display

    You may create some data for the analytics task flow to display by performing actions that relate to the task flow you are testing. For example, to test discussion metrics, create and view discussions, to test page metrics, create and view pages, and so on.

46.2.4.3 How to Modify Analytics Task Flow Parameters

Each analytics task flow has a set of required and optional task flow binding parameters. Required parameters are not mandatory but enable you to capture information that is essential to the task flow's successful function. For example, if you want user customizations for a particular task flow instance to be stored in MDS you must specify the MDS document required.

In addition, you can use task flow binding parameters to customize the appearance and behavior of a task flow instance. For example, you can use parameter values to specify a display title above your analytics data.

You can provide task flow binding parameter values when you drag and drop a task flow onto an application page. Doing so opens the Task Flow Bindings dialog (for more information, see Section 46.2.4.2, "How to Add Analytics Task Flows to a Page").

You can also adjust task flow binding parameter values after you have placed a task flow on a page at run time. For details, see "Setting Analytics Task Flow Properties" in the Oracle Fusion Middleware User's Guide for Oracle WebCenter Spaces.

To access the Edit Task Flow Binding dialog:

  1. Click the Bindings tab at the bottom of the page (next to the Source tab) to go to the Bindings view.

  2. Under Executables, you will see a list of task flows added to the page. Select the analytics task flow (Figure 46-5).

    Figure 46-5 Accessing Input Parameters for Analytics Task Flows

    Input Parameters for Analytics Task Flows
  3. Next to the Executables heading, click the Edit selected element icon (a pencil).

  4. In the Edit Task Flow Binding dialog (Figure 46-6), specify the binding parameter values as required.

    For more information, see Section 46.2.4.5, "Analytics Task Flows and Task Flow Parameters".

    Figure 46-6 Adding Binding Parameters for Analytics Task Flows

    Adding Binding Parameters for Analytics Task Flows
  5. When you are finished, save the page and run your page to see the results.

46.2.4.4 How to Allow End Users to Customize Analytics Task Flows at Runtime

If you want to enable user customizations for a particular task flow instance you must specify the MDS document where customizations are stored using the Analytics Resource Id parameter.

The ID must be unique, so consider using a consistent naming pattern such as <app_name>_<page_name>_<task_flow>_<sequence>. For example, set the analyticsResourceId parameter for a Page Traffic task flow to myapp_analyticspage_pagetraffic_1.

For details, see Section 46.2.4.3, "How to Modify Analytics Task Flow Parameters".

46.2.4.5 Analytics Task Flows and Task Flow Parameters

Table 46-4 lists and describes task flow binding parameters applicable to the Analytics service.

Table 46-4 Analytics Task Flow Binding Parameters

Parameter (* = required) Task Flows Description

analyticsReportTitle

All

Specifies the display title that appears above the analytics data, that is, you can override the default report title. See also, Figure 46-7, "analyticsReportTitle - Example".

analyticsResourceId*

All

Specifies the MDS document that will be generated to store user customizations/application customizations for the task flow instance in MDS. For example: mymainloginmetrics

applicationName*

All

Specifies the name of the WebCenter Portal application for which you want to display analytics data. For example: MyPortalApplication

The analytics database can be used to store event data from multiple applications so this parameter is required to identify which application data to display.

If omitted, the task flow display analytics data for all applications.

maxDataPointsPerSeries

All

Indicates the maximum number of data points to be displayed in a bar or line chart. Enter a value between 1 and 1000.

The default value is 25.

Increasing the number of data points can increase the time it takes to display the chart. See also, Figure 46-8, "maxDataPointsPerSeries - Example".


Figure 46-7 analyticsReportTitle - Example

analyticsReportTitle - Example

Figure 46-8 maxDataPointsPerSeries - Example

maxDataPointsPerSeries - Example

46.2.5 Setting up Security for Analytics Task Flows and Usage Data

Analytics task flows are intended to make usage metrics visible to a limited set of administrative users who perform particular business functions, such as capacity planning, quality of service (QoS) analysis, return on investment (ROI) analysis, "best bet" customization for Search, and so on.

Analytics usage data is valuable for portal analysis but might be regarded as private or sensitive to portal users. For example, the Search, Document, and Portlet reports can be configured to display activity metrics for a particular user, based on user properties such as E-mail Address, First Name, or Last Name.

To protect security and privacy interests associated with analytics task flows and custom reports:

  • Manage administrative access to the analytics task flows, custom reports, and any page that displays sensitive usage data.

    To ensure that only a limited number of administrative users can add analytics task flows to pages, create reports based on custom analytics data controls, or view pages set up to display sensitive usage metrics, create a new administrative group and manage group membership accordingly.

  • Manage user access to analytics task flows.

    Ensure analytics task flows and custom reports do not contain private or sensitive data unless such a view is particularly intended. If the metrics in the report do contain private or sensitive data, configure security so that only appropriate, specified users have access to the task flow or the page.

    For example, at design-time, developers can expose the analytics task flows to non-admin users by granting them appropriate privileges to the page. In addition, developers can customize the reports pre-deployment, to hide or show and predefine certain report options (such as time frame, chart type, user property filter, group by option, and so on). Administrators will be able to perform the same tasks at runtime, that is, grant page access and customize the information that displays. Non-admin users can still personalize the reports they are allowed to see, but they cannot change customizations made by the administrator.

  • Ensure that unauthenticated users are never allowed to add analytics task flows to pages or see sensitive data.

46.3 Building Analytics Reports

Out-of-the-box analytics task flows present common analytics event metrics in a specific display format. If you want to present analytics data in a different way or display custom event data, you can build a custom analytics report using SQL.

This section contains the following subsections:

For a detailed list of database tables and parameters, see Appendix I, "WebCenter Analytics Database Schema."

46.3.1 Using SQL Data Controls

Use SQL data controls to define, in an SQL query statement, the information you want to retrieve from the analytics database. When you expose data controls on a page you can choose whether the analytics data presents in a graph, table or a form, and you can also configure the bind parameters and other display options.

You can use JDeveloper to build SQL data controls at design-time. For details, detailed information about data controls, see Oracle Application Development Framework Developer's Guide.

You can also create data controls in a running WebCenter application if runtime resource management features are enabled. To do this in WebCenter Spaces, see "Creating a SQL Data Control for WebCenter Spaces" in the Oracle Fusion Middleware User's Guide for Oracle WebCenter Spaces. To create a data control in other WebCenter Portal applications, see "Managing Application Resources" in Oracle Fusion Middleware Administrator's Guide for Oracle WebCenter.

Use a SQL data control to fetch data from the analytics database and display analytics data in your WebCenter application. Figure 46-9 shows the sample SQL statement below in an the Create/Edit SQL Data Control dialog in WebCenter Spaces. A database connection to the analytics database (ActivitiesDS) is required, and you must provide a valid database password.

Sample SQL: Page Hits by Day

SELECT space.name_, page.name_, space.id, page.id, count(1), fact.page_, page.resourceid_
  FROM  asfact_wc_pagevie_0 fact, asdim_wc_groupsp_0 space, asdim_wc_pages_0 page, asdim_wc_applica_0 app
  WHERE space.id = fact.groupspace_ 
    and page.id = fact.page_ 
    and app.id = fact.application_
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname 
    and page.personal_ = :ispersonal
    and space.name_ is not null 
    and page.name_ is not null 
    and space.id is not null 
    and page.id is not null 
    and fact.page_ is not null 
    and page.resourceid_ is not null
GROUP BY space.name_, page.name_, space.id, page.id, fact.page_, page.resourceid_
ORDER BY count(1) desc

Figure 46-9 Creating SQL Data Control Dialog in WebCenter Spaces

Create SQL Data Control Dialog in WebCenter Spaces

You can add as many bind parameters as required. When you add parameters, you can restrict the data retrieved from the data source based on the parameter values you specify. As this example is for WebCenter Spaces, the name of the application is set to webcenter and personal pages (pages in the Home Space) are included in the report shown in Figure 46-10.

Figure 46-10 Setting Report Parameters

Create SQL Data Control Dialog in WebCenter Spaces

Table 46-5 Report Parameters

Parameter Default Value Description

appname

webcenter

The corresponding application name, always webcenter for WebCenter Spaces. Other WebCenter Portal applications will have a different name.

ispersonal

0 or 1

For WebCenter Spaces, determines whether or not pages created in the Home Spaces are included in the report.

dateformat

mm/dd/yyyy hh24

Any valid pattern for dates in an Oracle database. Every component of the pattern is optional since it depends on the data you want to retrieve.

startdate

01/01/2010

Determines a start date for the data required.

enddate

01/01/2011

Determines an end date for the data required.


Figure 46-9 shows the custom report displayed as a graph.

Figure 46-11 Sample Analytics Report - Page HIts by Day

Create SQL Data Control Dialog in WebCenter Spaces

46.3.2 SQL Statements for Out-of-the-Box Analytics Reports

This section provides SQL statements that can be used as a starting point for custom reports. You can customize these statements in many ways by providing additional filters, groupings, and so on.

For a detailed list of database tables and parameters, see Appendix I, "WebCenter Analytics Database Schema."

46.3.2.1 Analytics SQL: WebCenter Traffic

SELECT 'Spaces' Name, count(1) Hits
  FROM asfact_wc_groupsp_0 fact, asdim_wc_groupsp_0 space, asdim_wc_applica_0 app
  WHERE space.id = fact.groupspace_ 
    and app.id = fact.application_ 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname and space.personal_ = :ispersonal
UNION ALL
  SELECT 'Pages' Name, count(1) Hits
  FROM asfact_wc_pagevie_0 fact , asdim_wc_pages_0 page , asdim_wc_applica_0 app
  WHERE page.id = fact.page_ 
    and app.id = fact.application_ 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname 
    and page.personal_ = :ispersonal
UNION ALL
  SELECT 'Portlets' Name, count(1) Hits
  FROM asfact_wc_portlet_0 fact, asdim_wc_applica_0 app
  WHERE app.id = fact.application_ 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname
UNION ALL
  SELECT 'Logins' Name, count(1) Hits
  FROM asfact_wc_logins_0 fact , asdim_wc_applica_0 app
  WHERE app.id = fact.application_ 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname
UNION ALL
  SELECT 'Searches' Name, count(1) Hits
  FROM asfact_wc_searche_0 fact , asdim_wc_applica_0 app
  WHERE app.id = fact.application_ 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname
UNION ALL
  SELECT 'Wikis' Name, count(1) Hits
  FROM asfact_wc_doclib__0 fact, asdim_wc_documen_0 doc, asdim_wc_applica_0 app
  WHERE app.id = fact.application_ 
    and fact.document_ = doc.id 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) and app.name_ = :appname 
    and doc.objecttype_ like '%WIKI%'
UNION ALL
  SELECT 'Blogs' Name, count(1) Hits
  FROM asfact_wc_doclib__0 fact, asdim_wc_documen_0 doc, asdim_wc_applica_0 app
  WHERE app.id = fact.application_ 
    and fact.document_ = doc.id 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname 

    and doc.objecttype_ like '%BLOG%'
UNION ALL
  SELECT 'Documents' Name, count(1) Hits
  FROM asfact_wc_doclib__0 fact, asdim_wc_documen_0 doc, asdim_wc_applica_0 app
  WHERE app.id = fact.application_ 
    and fact.document_ = doc.id 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname 
    and doc.objecttype_ like '%DOCUMENT%'
UNION ALL
  SELECT 'Discussions' Name, count(1) Hits
  FROM asfact_wc_discuss_1 fact, asdim_wc_applica_0 app
  WHERE app.id = fact.application_ 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname

46.3.2.2 Analytics SQL: Page Traffic

SELECT  space.name_, page.name_, space.id, page.id, count(1), fact.page_, page.resourceid_
  FROM asfact_wc_pagevie_0 fact, asdim_wc_groupsp_0 space, asdim_wc_pages_0 page, asdim_wc_applica_0 app
  WHERE space.id = fact.groupspace_ 
    and page.id = fact.page_ 
    and app.id = fact.application_ 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname 
    and page.personal_ = :ispersonal 
    and space.name_ is not null 
    and page.name_ is not null 
    and space.id is not null 
    and page.id is not null 
    and fact.page_ is not null 
    and page.resourceid_ is not null
GROUP BY space.name_, page.name_, space.id, page.id, fact.page_, page.resourceid_
ORDER BY count(1) desc

46.3.2.3 Analytics SQL: Login Metrics

SELECT  count(1)
 FROM asfact_wc_logins_0 fact, asdim_wc_applica_0 app
 WHERE app.id = fact.application_ 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname

46.3.2.4 Analytics SQL: Space Traffic

SELECT  space.name_, space.id, count(1), fact.groupspace_, space.resourceid_
  FROM asfact_wc_groupsp_0 fact, asdim_wc_groupsp_0 space, asdim_wc_applica_0 app
  WHERE space.id = fact.groupspace_ 
    and app.id = fact.application_ 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname 
    and space.personal_ = :ispersonal 
    and space.name_ is not null 
    and space.id is not null 
    and fact.groupspace_ is not null 
    and space.resourceid_ is not null
GROUP BY space.name_, space.id, fact.groupspace_, space.resourceid_
ORDER BY count(1) desc

46.3.2.5 Analytics SQL: Space Response Time

SELECT  space.name_, space.id, avg(fact.response_time_), fact.groupspace_, space.resourceid_
  FROM asfact_wc_groupsp_0 fact, asdim_wc_groupsp_0 space, asdim_wc_applica_0 app
  WHERE space.id = fact.groupspace_ 
    and app.id = fact.application_ 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname 
    and space.personal_ = :ispersonal 
    and space.name_ is not null 
    and space.id is not null 
    and fact.groupspace_ is not null 
    and space.resourceid_ is not null
GROUP BY space.name_, space.id, fact.groupspace_, space.resourceid_

46.3.2.6 Analytics SQL: Portlet Traffic

SELECT  portlet.default_title_, portlet.id, count(1), fact.portlet_, portlet.resourceid_
  FROM asfact_wc_portlet_0 fact, asdim_wc_portlet_0 portlet, asdim_wc_applica_0 app
  WHERE app.id = fact.application_ 
    and portlet.id = fact.portlet_ 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname 
    and portlet.default_title_ is not null 
    and portlet.id is not null 
    and fact.portlet_ is not null 
    and portlet.resourceid_ is not null
GROUP BY portlet.default_title_, portlet.id, fact.portlet_, portlet.resourceid_

46.3.2.7 Analytics SQL: Portlet Instance Traffic

SELECT  space.name_, space.id, page.name_, page.id, portletinst.title_, portletinst.id, count(1), fact.portlet_instance_, portletinst.resourceid_
  FROM asfact_wc_portlet_0 fact, asdim_wc_groupsp_0 space, asdim_wc_pages_0 page, asdim_wc_portlet_1 portletinst, asdim_wc_applica_0 app
  WHERE space.id = fact.groupspace_ 
    and page.id = fact.page_ 
    and app.id = fact.application_ 
    and portletinst.id = fact.portlet_instance_ 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname 
    and space.name_ is not null 
    and space.id is not null 
    and page.name_ is not null 
    and page.id is not null 
    and portletinst.title_ is not null 
    and portletinst.id is not null 
    and fact.portlet_instance_ is not null 
    and portletinst.resourceid_ is not null
GROUP BY space.name_, space.id, page.name_, page.id, portletinst.title_, portletinst.id, fact.portlet_instance_, portletinst.resourceid_

46.3.2.8 Analytics SQL: Portlet Response Time

SELECT  portlet.default_title_, portlet.id, avg(fact.response_time_), fact.portlet_, portlet.resourceid_
  FROM asfact_wc_portlet_0 fact, asdim_wc_portlet_0 portlet, asdim_wc_applica_0 app
  WHERE app.id = fact.application_ 
    and portlet.id = fact.portlet_ 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname 
    and portlet.default_title_ is not null 
    and portlet.id is not null 
    and fact.portlet_ is not null 
    and portlet.resourceid_ is not null
GROUP BY portlet.default_title_, portlet.id, fact.portlet_, portlet.resourceid_

46.3.2.9 Analytics SQL: Portlet Instance Response Time

SELECT  space.name_, space.id, page.name_, page.id, portletinst.title_, portletinst.id, avg(fact.response_time_), fact.portlet_instance_, portletinst.resourceid_
  FROM asfact_wc_portlet_0 fact, asdim_wc_groupsp_0 space, asdim_wc_pages_0 page, asdim_wc_portlet_1 portletinst, asdim_wc_applica_0 app
  WHERE space.id = fact.groupspace_ 
    and page.id = fact.page_ 
    and app.id = fact.application_ 
    and portletinst.id = fact.portlet_instance_ 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname 
    and space.name_ is not null 
    and space.id is not null 
    and page.name_ is not null 
    and page.id is not null 
    and portletinst.title_ is not null 
    and portletinst.id is not null 
    and fact.portlet_instance_ is not null 
    and portletinst.resourceid_ is not null
GROUP BY space.name_, space.id, page.name_, page.id, portletinst.title_, portletinst.id, fact.portlet_instance_, portletinst.resourceid_

46.3.2.10 Analytics SQL: Search Metrics

SELECT  search.phrase_, search.id, count(1)
  FROM asfact_wc_searche_0 fact, asdim_wc_applica_0 app, asdim_wc_searche_0 search
  WHERE app.id = fact.application_ 
    and search.id = fact.searched_phrase_ 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname 
    and search.phrase_ is not null 
    and search.id is not null
GROUP BY search.phrase_, search.id

46.3.2.11 Analytics SQL: Document Metrics

SELECT  doc.name_, doc.id, count(1), fact.document_, doc.resourceid_
  FROM asfact_wc_doclib__0 fact, asdim_wc_documen_0 doc, asdim_wc_applica_0 app
  WHERE app.id = fact.application_ 
    and fact.document_ = doc.id 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname 
    and doc.objecttype_ like '%DOCUMENT%' 
    and doc.name_ is not null 
    and doc.id is not null 
    and fact.document_ is not null 
    and doc.resourceid_ is not null
GROUP BY doc.name_, doc.id, fact.document_, doc.resourceid

46.3.2.12 Analytics SQL: Wiki Metrics

SELECT  doc.name_, doc.id, count(1), fact.document_, doc.resourceid_
  FROM asfact_wc_doclib__0 fact, asdim_wc_documen_0 doc, asdim_wc_applica_0 app
  WHERE app.id = fact.application_ 
    and fact.document_ = doc.id 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname 
    and doc.objecttype_ like '%WIKI%' 
    and doc.name_ is not null 
    and doc.id is not null 
    and fact.document_ is not null 
    and doc.resourceid_ is not null
GROUP BY doc.name_, doc.id, fact.document_, doc.resourceid_

46.3.2.13 Analytics SQL: Blog Metrics

SELECT  doc.name_, doc.id, count(1), fact.document_, doc.resourceid_
  FROM asfact_wc_doclib__0 fact, asdim_wc_documen_0 doc, asdim_wc_applica_0 app
  WHERE app.id = fact.application_ 
    and fact.document_ = doc.id 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname 
    and doc.objecttype_ like '%BLOG%' 
    and doc.name_ is not null 
    and doc.id is not null 
    and fact.document_ is not null 
    and doc.resourceid_ is not null
GROUP BY doc.name_, doc.id, fact.document_, doc.resourceid_

46.3.2.14 Analytics SQL: Discussion Metrics

SELECT  forum.name_, forum.id, count(1), fact.topic_, forum.resourceid_
  FROM asfact_wc_discuss_0 fact, asdim_wc_discuss_0 forum, asdim_wc_applica_0 app
  WHERE app.id = fact.application_ 
    and forum.id = fact.topic_ 
    and fact.occurred between to_date(:startdate, :dateformat) 
    and to_date(:enddate, :dateformat) 
    and app.name_ = :appname 
    and forum.name_ is not null 
    and forum.id is not null 
    and fact.topic_ is not null 
    and forum.resourceid_ is not null
GROUP BY forum.name_, forum.id, fact.topic_, forum.resourceid_

46.3.3 Sample Queries for User Metrics

This section provides example queries for user-specific metrics. It includes the following samples:

For a detailed list of database tables and parameters and information on user properties, seeSection I.5, "Analytics User Properties" in Appendix I, "WebCenter Analytics Database Schema.".

46.3.3.1 Sample SQL: Filter by User Property

The following query returns the names of the pages accessed by users in the Sales department, and the number of views for each of those pages.

SELECT p.name_ as page, count(*) as views
  FROM asfact_wc_pagevie_0 f
    JOIN asdim_wc_pages_0 p on f.page_ = p.id
    JOIN asdim_userpropertyvalues pv on f.userid = pv.userid
    JOIN asdim_userproperties pr on pv.propertyid = pr.id
  WHERE pr.name = 'DEPARTMENT'

    and pv.value = 'Sales'
GROUP BY p.name_

46.3.3.2 Sample SQL: Group by User Property

The following query returns the total number of page views broken down into departments.

SELECT pv.value as department, count(*) as views
  FROM asfact_wc_pagevie_0 f
    JOIN asdim_userpropertyvalues pv on f.userid = pv.userid
    JOIN asdim_userproperties pr on pv.propertyid = pr.id
  WHERE pr.name = 'DEPARTMENT'
GROUP BY pv.value

46.3.4 Sample Analytics Database Queries for Specific Metrics

This section provides example queries for specific metrics. It includes the following samples:

For a detailed list of database tables and parameters, see Appendix I, "WebCenter Analytics Database Schema."

46.3.4.1 Sample SQL: User Activities

The following query returns a list of all the activities executed by a specific user during a specific time period.

SELECT 'Logins' AS Activity, count(*) AS Events
  FROM asfact_wc_logins_0 f
    JOIN asdim_time t ON f.timeid = t.id
    JOIN asdim_users u ON f.userid = u.id
  WHERE u.userid = 'user1'
    AND f.timeid >= (SELECT id
                     FROM asdim_time
                     WHERE year = 2011
                       AND monthofyear = 0
                       AND dayofmonth = 1
                       AND hourofday = 0)
    AND f.timeid <  (SELECT id
                     FROM asdim_time
                     WHERE year = 2011
                       AND monthofyear = 1
                       AND dayofmonth = 1
                       AND hourofday = 0)
UNION ALL
  SELECT 'Searches' AS Activity, count(*) AS Events
  FROM asfact_wc_searche_0 f
    JOIN asdim_time t ON f.timeid = t.id
    JOIN asdim_users u ON f.userid = u.id
  WHERE u.userid = 'user1'
    AND f.timeid >= (select id
                     from asdim_time
                     where year = 2011
                       and monthofyear = 0
                       and dayofmonth = 1
                       and hourofday = 0)
    AND f.timeid <  (select id
                     from asdim_time
                     where year = 2011
                       and monthofyear = 1
                       and dayofmonth = 1
                       and hourofday = 0)
UNION ALL
   ...

Sample Report Output

Activity   Events
----------------
Logins        25
Searches      15
...

46.3.4.2 Sample SQL: Space Activities

The following query returns a list of all activities executed in a specific Space.

SELECT g.name_ AS Space, 'Page views' AS Activity,
    count(*) AS Events
  FROM asfact_wc_pagevie_0 f
    JOIN asdim_time t ON f.timeid = t.id
    JOIN asdim_wc_groupsp_0 g ON f.groupspace_ = g.id
  WHERE g.name_ IN ('Project1', 'Project2')
    AND f.timeid >= (select id
                     from asdim_time
                     where year = 2011
                       and monthofyear = 0
                       and dayofmonth = 1
                       and hourofday = 0)
    AND f.timeid <  (select id
                     from asdim_time
                     where year = 2011
                       and monthofyear = 1
                       and dayofmonth = 1
                       and hourofday = 0)
  GROUP BY g.name_
UNION ALL
  SELECT g.name_ AS Space,
    'Portlet views' AS Activity, count(*) AS Events
  FROM asfact_wc_portlet_0 f
    JOIN asdim_time t ON f.timeid = t.id
    JOIN asdim_wc_groupsp_0 g ON f.groupspace_ = g.id
  WHERE g.name_ in ('Project1', 'Project2')
    AND f.timeid >= (select id
                     from asdim_time
                     where year = 2011
                       and monthofyear = 0
                       and dayofmonth = 1
                       and hourofday = 0)
    AND f.timeid <  (select id
                     from asdim_time
                     where year = 2011
                       and monthofyear = 1
                       and dayofmonth = 1
                       and hourofday = 0)
  GROUP BY g.name_

Sample Report Output

Space       Activity      Events
--------------------------------
Project1    PageViews         34
Project2    PageViews         42
Project1    PortletViews      98
Project2    PortletViews      74

46.3.4.3 Sample SQL: Space Activities for a Specific Time Period

The following query returns a list of all activities executed in a specific Space during a specific time period.

SELECT 'Page views' AS Activity, count(*) AS Events
  FROM asfact_wc_pagevie_0 f
    JOIN asdim_time t ON f.timeid = t.id
    JOIN asdim_wc_groupsp_0 g ON f.groupspace_ = g.id
  WHERE g.name_ = 'Project1'
    AND f.timeid >= (select id
                     from asdim_time                     where year = 2011
                       and monthofyear = 0
                       and dayofmonth = 1
                       and hourofday = 0)
    AND f.timeid <  (select id
                     from asdim_time
                     where year = 2011
                       and monthofyear = 1
                       and dayofmonth = 1
                       and hourofday = 0)
UNION ALL
  SELECT 'Portlet views' AS Activity, count(*) AS Events
  FROM asfact_wc_portlet_0 f
    JOIN asdim_time t ON f.timeid = t.id
    JOIN asdim_wc_groupsp_0 g ON f.groupspace_ = g.id
  WHERE g.name_ = 'Project1'
    AND f.timeid >= (select id
                     from asdim_time
                     where year = 2011
                       and monthofyear = 0
                       and dayofmonth = 1
                       and hourofday = 0)
    AND f.timeid <  (select id
                     from asdim_time
                     where year = 2011
                       and monthofyear = 1
                       and dayofmonth = 1
                       and hourofday = 0)
UNION ALL
   ...

Sample Report Output

Activity       Events
---------------------
Page views         15
Portlet views     200

46.3.4.4 Sample SQL: Activity for a Service During a Specific Time Period

The following query returns activity for a specific service (in this example, the login service) during a specific time period and groups results by user.

SELECT u.userid AS "User", count(*) AS "Logins"
FROM asfact_wc_logins_0 f
  JOIN asdim_time t ON f.timeid = t.id
  JOIN asdim_users u ON f.userid = u.id
WHERE f.timeid >= (select id
                   from asdim_time
                   where year = 2011
                     and monthofyear = 0
                     and dayofmonth = 1
                     and hourofday = 0)
    AND f.timeid <  (select id
                     from asdim_time
                     where year = 2011
                       and monthofyear = 1
                       and dayofmonth = 1
                       and hourofday = 0)
GROUP BY u.userid
ORDER BY "Logins" DESC

Sample Report Output

User      Logins
--------------
user3     245
user1     240
user2     193

46.3.4.5 Sample SQL: Search Phrases

The following query returns the phrases searched during a specific time and lists how many times each search was executed.

SELECT s.phrase_ AS "Search Phrase", count(*) AS "Times"
FROM asfact_wc_searche_0 f
  JOIN asdim_time t ON f.timeid = t.id
  JOIN asdim_users u ON f.userid = u.id
  JOIN asdim_wc_searche_0 s ON f.searched_phrase_ = s.id
WHERE u.userid = 'user1'
  AND f.timeid >= (select id
                     from asdim_time
                     where year = 2011
                       and monthofyear = 0
                       and dayofmonth = 1
                       and hourofday = 0)
    AND f.timeid <  (select id
                     from asdim_time
                     where year = 2011
                       and monthofyear = 1
                       and dayofmonth = 1
                       and hourofday = 0)
GROUP BY u.userid, s.phrase_
ORDER BY count(*) DESC, s.phrase_

Sample Report Output

Search Phrase      Times
------------------------
product prices         4
sales report 2011      1

46.3.4.6 Sample SQL: Page Views

The following query returns the pages viewed by a specific user during a specific time period.

SELECT u.userid AS "User", count(*) AS "Views"
FROM asfact_wc_pagevie_0 f
  JOIN asdim_time t ON f.timeid = t.id
  JOIN asdim_users u ON f.userid = u.id
  JOIN asdim_wc_pages_0 p ON f.page_ = p.id
  JOIN asdim_wc_groupsp_0 g ON f.groupspace_ = g.id
WHERE g.name_ = 'Space 1'
  AND p.name_ = 'Page 1'
  AND f.timeid >= (select id
                   from asdim_time
                   where year = 2011
                     and monthofyear = 0
                     and dayofmonth = 1
                     and hourofday = 0)
  AND f.timeid <  (select id
                   from asdim_time
                   where year = 2011
                     and monthofyear = 1
                     and dayofmonth = 1
                     and hourofday = 0)
GROUP BY u.userid
ORDER BY count(*) DESC

Sample Report Output

User    Views
--------------
user3     245
user1     190
user2      65