The Analytics service enables you to display usage and performance metrics for Framework applications. This chapter describes how to integrate the Analytics service into applications built using Oracle WebCenter Portal: Framework.
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 Portal: Spaces.
For a detailed list of database tables and parameters, see Appendix I, "WebCenter Portal Analytics Database Schema."
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:
The Analytics service allows administrators and users to track and analyze traffic and usage in WebCenter Portal application. 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 Portal metrics to determine usage patterns, such as page 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 47.3.1, "Using SQL Data Controls".
Table 47-1 lists the analytics task flows available with WebCenter Portal. These task flows work similarly for Spaces and Framework applications. For detailed information about these task flows and how to use them, see "Understanding Analytics Task Flows in Spaces" in Oracle Fusion Middleware User's Guide for Oracle WebCenter Portal: Spaces.
Table 47-1 Analytics Task Flows Available in JDeveloper
Task Flows | Description |
---|---|
A summarized view for common events within the portal. |
|
Displays the number of page hits and the number of unique users that visited any page within the portal. |
|
Reports portal logins. |
|
Displays usage data for a portlet. |
|
Displays performance data for a portlet. |
|
Displays usage data for a portlet instance*. |
|
Displays performance data for a portlet instance*. |
|
Tracks portal searches. |
|
Tracks document views. |
|
Tracks most popular/least popular wikis. |
|
Tracks most popular/least popular blogs. |
|
Tracks most popular/least popular discussions. |
* If the same portlet is displayed on several different pages, each placement is known as a portlet instance.
The Analytics service requires that the analytics schema (ACTIVITIES
) is installed and up and running. In addition, Oracle WebCenter Portal's 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 Portal.
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 Portal.
To display usage and performance metrics through analytics task flows you must enable event generation for your Framework application and configure a connection to the analytics database (ACTIVITIES
). For details on how to do this in JDeveloper, see Section 47.2.2, "Setting up Connections for the Analytics Service".
At runtime, user activity in your Framework 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 Framework application is configured with the location of the collector, and all events are transmitted to that location.
In a clustered environment both the Framework 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 Framework 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 Framework 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 Framework application stops hearing the heartbeat it removes the collector from its list and stops sending events to that collector. If the Framework 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 Framework 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 Portal: Spaces.
This section describes how to include the Analytics service in your Framework applications.
This section contains the following subsections:
Section 47.2.1, "Configuration Roadmap for the Analytics Service"
Section 47.2.2, "Setting up Connections for the Analytics Service"
Section 47.2.3, "Adding Analytics Event Code to Your Application"
Section 47.2.4, "Configuring a Namespace for Analytics Customizations in MDS"
Section 47.2.5, "Adding Analytics Task Flows at Design Time"
Section 47.2.6, "Setting up Security for Analytics Task Flows and Usage Data"
See also, Section 47.1.2, "Requirements for the Analytics Service".
The flow chart depicted in Figure 47-1 and Table 47-2 in this section provide an overview of the prerequisites and tasks required to get the Analytics service working in Framework applications.
Table 47-2 Configuring the Analytics Service for Framework applications
Actor | Task | Sub-task |
---|---|---|
Administrator |
1. Install Oracle WebCenter Portal: Framework and the back-end components for the Analytics service |
|
Developer |
2. Integrate the Analytics service in your Framework application |
2.a Configure a connection to the Activities database in JDeveloper 2.b Configure a connection to the Analytics Collector in JDeveloper |
Developer/ Administrator |
3. Deploy the Framework application using one of the following tools:
|
|
Administrator |
4. (Optional) Configure the Analytics Collector using either of the following tools: |
|
Developer |
5. (Optional) Instrument events for pages and portlets using JDeveloper, then redeploy the application. |
|
Developer/ Administrator |
6. (Optional) Add/modify connection parameters using one of the following tools:
|
|
End User |
7. Test that analytics data is available in the Framework application |
6.a Log in to the Framework application 6.b Display the analytics task flow |
The Analytics service requires that the Framework application is connected to the analytics database (ACTIVITIES
). You must also configure the Framework 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 Fusion Middleware Control. For more information, see "Managing the Analytics Service" in Oracle Fusion Middleware Administrator's Guide for Oracle WebCenter Portal.
This section includes the following subsections:
Section 47.2.2.1, "How to Set Up a Connection to the Analytics Database"
Section 47.2.2.2, "How to Set Analytics Collector Properties"
Section 47.2.2.3, "How to Set Up a Connection to the Analytics Collector"
The Analytics service requires a connection to the analytics database (ACTIVITIES
) where analytics event data is stored.
To set up the analytics database connection:
Ensure that the ACTIVITIES
database is up and running.
See Section 47.1.2, "Requirements for the Analytics Service".
In the Application Navigator, expand the Applications Resources panel.
Right-click Connections, then choose New Connection, and then Database.
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)
Click Test Connection, and if it is successful, then click OK.
In the Associate to Data Source dialog, choose ActivitiesDS (Figure 47-2).
This creates a data-source to the ACTIVITIES
schema so that you can test analytics in your Framework application at design-time.
Click OK.
The connection appears as a node in the Application Resources pane, under Connections.
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 |
|
Default Port |
|
Maximum Port Number |
|
Broadcast Type |
|
Clustering Cluster Name Cluster Broadcast Frequency |
- - |
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 Portal.
The Analytics service requires a connection to the Analytics Collector that is collecting WebCenter Portal events and OpenUsage must be enabled in the Framework 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. Currently, clusters are not supported so specify only the host name here. 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:
In JDeveloper, choose Run, Active Run Configuration, and then Manage Run Configurations from the main menu.
In the Project Properties dialog, select the Default run configuration, and then click Edit.
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
Note:
In the current release the oracle.wc.openusage.unicast
value must be set to true
. The Analytics Collector cannot be clustered in this release, so multicast is not supported.
Ensure that the values you provide, shown here in bold, match your Analytics Collector installation.
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 Framework application. If the Integrated WebLogic Server is currently running you must restart it to pick up the new settings. See also, Section 70.2, "Deploying a Framework Application to the Integrated WebLogic Server".
Most events are configured out-of-the-box, so no additional coding is required in your application to send events. The only exception is page view events, which require additional code, as described in Section 47.2.3.1, "Including Event Code for Page Views."
To send page events, you must add event code to each page. Following is an example using a client JavaScript event on the page which, in turn, calls the Java Analytics API to send the actual event. To implement this example, you add the following code just below the <af:document>
tag, replacing the pageName
value with the name of the page for which you are sending events:
<af:resource type="javascript">
function initPageLoadEvent(event {)
AdfCustomEvent.queue(event.getSource(),
"generatePageEvent",
{pageName:"Page and Login Statistics"},
true);
event.cancel();
}
</af:resource>
<af:clientListener method="initPageLoadEvent" type="load"/>
<af:serverListener type="generatePageEvent"
method="#{AnalyticsInstrumentation.sendPageEvent}"/>
The example above generates a client event when the ADF document gets loaded, then the defined server listener actually sends the event. Here is the java bean that sends the event:
public void sendPageEvent (ClientEvent event) { // Checks whether Analytics events must be sent if (!AnalyticsUtil.isSendingEvents()) return; HTTPServletRequest request = (HTTPServletRequest) FacesContext.getCurrentInstance().getExternalContext().getRequest(); String requestUser = request.getRemoteUser = request.getRemoteUser(); //SEND PAGE VIEW EVENT String pageName = (String)event.getParameters().get("pageName"); if(pageName == null || pageName.isEmpty() || requestUser == null) return; AnalyticsUtil.sendPageViewEvent(FacesContext.getCurrentInstance(). getViewRoot..getViewId(), //viewID "PortalApp", //spaceDisplayName requestUser, //username 0, pageName, false, request);
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 Framework 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 47.2.5.4, "How to Allow End Users to Customize Analytics Task Flows at Runtime".
To configure a namespace for analytics metadata:
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.
Under the <metadata-namespaces>
element, add the following XML fragment:
<namespace path="/oracle/webcenter/analytics/scopedMD" metadata-store-usage="WebCenterFileMetadataStore"/>
Save the file.
See also, Chapter 23, "Performing Composer-Specific MDS Configurations".
This section describes the Analytics service task flows and how to add analytics task flows to your application. It includes the following subsections:
Section 47.2.5.2, "How to Add Analytics Task Flows to a Page"
Section 47.2.5.3, "How to Modify Analytics Task Flow Parameters"
Section 47.2.5.4, "How to Allow End Users to Customize Analytics Task Flows at Runtime"
Section 47.2.5.5, "Analytics Task Flows and Task Flow Parameters"
Table 47-3 lists the analytics task flows available with WebCenter Portal. For detailed information about these task flows and the type of information that users can see at runtime, see "Understanding Analytics Task Flows in Spaces" in Oracle Fusion Middleware User's Guide for Oracle WebCenter Portal: Spaces.
Table 47-3 Analytics Task Flows Available in JDeveloper
Analytics Task Flows | Description |
---|---|
WebCenter Portal 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. |
To add an analytics task flow to your Framework application:
Prepare your application as described in Section 47.1.2, "Requirements for the Analytics Service".
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
Open the JSF page (.jspx) on which you want to add the task flow.
In the Resource Palette, open the WebCenter Portal Services Catalog, then open the Task Flows folder (Figure 47-3).
Drag and drop an Analytics task flow onto your page inside the <af:form>
tag.
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 47-4).
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 47.2.5.5, "Analytics Task Flows and Task Flow Parameters".
Click OK.
The task flow is added to the page, and the ViewController project's libraries are configured to run the task flow.
Save your project and run the page to see the task flow running in the Framework 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.
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 47.2.5.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 Portal: Spaces.
To access the Edit Task Flow Binding dialog:
Click the Bindings tab at the bottom of the page (next to the Source tab) to go to the Bindings view.
Under Executables, you will see a list of task flows added to the page. Select the analytics task flow (Figure 47-5).
Next to the Executables heading, click the Edit selected element icon (a pencil).
In the Edit Task Flow Binding dialog (Figure 47-6), specify the binding parameter values as required
For more information, see Section 47.2.5.5, "Analytics Task Flows and Task Flow Parameters".
When you are finished, save the page and run your page to see the results.
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 47.2.5.3, "How to Modify Analytics Task Flow Parameters".
Table 47-4 lists and describes task flow binding parameters applicable to the Analytics service.
Table 47-4 Analytics Task Flow Binding Parameters
Parameter (* = required) | Task Flows | Description |
---|---|---|
Specifies the display title that appears above the analytics data, that is, you can override the default report title. See also, Figure 47-7, "analyticsReportTitle - Example". |
||
Specifies the MDS document that will be generated to store user customizations/application customizations for the task flow instance in MDS. For example: |
||
Specifies the name of the Framework application for which you want to display analytics data. For example: 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. |
||
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 47-8, "maxDataPointsPerSeries - Example". |
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.
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:
Section 47.3.2, "SQL Statements for Out-of-the-Box Analytics Reports"
Section 47.3.4, "Sample Analytics Database Queries for Specific Metrics"
For a detailed list of database tables and parameters, see Appendix I, "WebCenter Portal Analytics Database Schema."
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 detailed information about data controls, see Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.
You can also create data controls in a running Framework application if runtime resource management features are enabled. To do this in the Spaces application, see "Creating a SQL Data Control" in the Oracle Fusion Middleware User's Guide for Oracle WebCenter Portal: Spaces. To create a data control in other Framework applications, see "Managing Application Resources" in Oracle Fusion Middleware Administrator's Guide for Oracle WebCenter Portal.
Use a SQL data control to fetch data from the analytics database and display analytics data in your Framework application. Figure 47-9 shows the sample SQL statement below in a Create/Edit SQL Data Control dialog in Spaces. A database connection to the analytics database (ActivitiesDS
) is required, and you must provide a valid database password.
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
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 the Spaces application, 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 47-10.
Parameter | Default Value | Description |
---|---|---|
|
|
The corresponding application name, always |
|
|
For Spaces, determines whether or not pages created in the Home space are included in the report. |
|
|
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. |
|
|
Determines a start date for the data required. |
|
|
Determines an end date for the data required. |
Figure 47-9 shows the custom report displayed as a graph.
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 Portal Analytics Database Schema."
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
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
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
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
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_
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_
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_
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_
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_
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
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
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_
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_
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_
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 Portal Analytics Database Schema.".
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_
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
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 Portal Analytics Database Schema."
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 ...
Activity Events ---------------- Logins 25 Searches 15 ...
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_
Space Activity Events -------------------------------- Project1 PageViews 34 Project2 PageViews 42 Project1 PortletViews 98 Project2 PortletViews 74
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 ...
Activity Events --------------------- Page views 15 Portlet views 200
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
User Logins -------------- user3 245 user1 240 user2 193
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_
Search Phrase Times ------------------------ product prices 4 sales report 2011 1
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
User Views -------------- user3 245 user1 190 user2 65