This tutorial shows you how to set up usage tracking and create usage reports to monitor queries in Oracle BI 11g.
Time to Complete
Approximately 30 minutes
The Oracle BI Server supports the collection of usage tracking data. When usage tracking is enabled, the Oracle BI Server collects usage tracking data for each query, and it writes statistics to a usage tracking log file or inserts them directly into a database table.
It is strongly recommended that you use direct insertion instead of writing to a log file. The Oracle BI Summary Advisor feature works in conjunction with the usage tracking feature. Summary Advisor works only with direct insertion usage tracking. Oracle BI Summary Advisor is available when you are running Oracle Business Intelligence on the Oracle Exalytics Machine.
This tutorial shows you how to set up and use usage tracking. Usage tracking is also helpful in determining which user queries are creating performance bottlenecks, based on query frequency and response time. In this tutorial, you use a database table.
Before starting this tutorial, you should:
- Complete the tutorial Creating a Repository Using the Oracle BI 11g Administration Tool.
- Have access to or have installed Oracle Database 11.2 or later
- Have access to or have installed Oracle BI EE 220.127.116.11.0 or later
- Have access to or have installed the BISAMPLE sample schema that is included with the Sample Application for Oracle Business Intelligence Suite Enterprise Edition Plus. There are two options for accessing the BISAMPLE schema:
1. If you already have installed V107 of the Sample Application, verify that you have access to the BISAMPLE schema and begin the tutorial.
2. If you want to download and install the complete Sample Application, you can access it here on the Oracle Technical Network (OTN). Install the SampleApp V107 install files. Note, however, that only the BISAMPLE schema is required to complete this tutorial.
Creating the Usage Tracking Subject Area in the Repository
In this section, you check for the existence of the S_NA_ACCT Usage Tracking table in the database Repository Creation Utility (RCU) schema. You then create the three layers in the BI Repository for Usage tracking.
Checking for Usage Tracking Table
You can also use SQL Developer to check for the S_NQ_ACCT table.
Creating the Database Object in the Physical Layer
You have now created the Physical Layer for Usage Tracking
Creating the Business Model Layer for Usage Tracking
Drag the following three physical columns from Usage Tracking Schema>S_NQ_ACCT to the Measures logical table in the 13- Usage Tracking 2 business model. For each column, right-click the logical column and select Rename, and then rename as follows:
|TOTAL_TIME_SEC||Total Time Seconds|
Similarly, set the Aggregation rule for other logical columns in the Measures logical table as follows:
|Logical Column||Aggregation Rule|
|Total Time Seconds||Sum|
Drag the following three physical columns from Usage Tracking Schema>S_NQ_ACCT to the Time logical table in the 13-Usage Tracking 2 business model. Rename them as follows:
|START_HOUR_MIN||Start Hour Minute|
|END_HOUR_MIN||End Hour Minute|
Drag the following two physical columns from Usage Tracking Schema>S_NQ_ACCT to the Topic logical table in the 13-Usage Tracking 2 business model. Rename them as follows:
|SUBJECT_ AREA_NAME||Subject Area|
You have now created the Usage Tracking business model. You will next move it to the Presentation layer.
Creating the Presentation Layer
Configuring Usage Tracking in Enterprise Manager
You now configure usage tracking through Oracle Enterprise Manager (EM) by performing the following steps:
Populating the Usage Tracking Table and Verifying Usage Tracking
You now run analyses to populate the Usage Tracking S_NQ_ACCT table, and then you run queries on the table to get details of these queries.
Creating and Running to Populate Usage Tracking Table
Note that the number of rows returned for this query is 9.
Creating and Running Analyses to Verify Usage Tracking
In this tutorial, you should have learned how to:
- Set up the three layers in the BI Administration Tool for usage tracking
- Configure usage tracking by using Enterprise Manager
- Populate the usage tracking table
- Run queries against the usage tracking table, to get user query details
To help navigate this Oracle by Example, note the following:
- Hiding Header Buttons:
- Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again.
- Topic List Button:
- A list of all the topics. Click one of the topics to navigate to that section.
- Expand/Collapse All Topics:
- To show/hide all the detail for all the sections. By default, all topics are collapsed
- Show/Hide All Images:
- To show/hide all the screenshots. By default, all images are displayed.
- To print the content. The content currently displayed or hidden will be printed.
To navigate to a particular section in this tutorial, select the topic from the list.