Usage Tracking in OBIEE 11g

Usage Tracking in OBIEE 11.1.1.7.0 

Why Usage Tracking:-
Usage Tracking is used to monitor system and ad‐hoc query performance and to
provide input on usage trends for use in billing users and departments for resources.
Usage tracking is particularly useful in determining user queries that are creating
performance bottlenecks, based on query frequency and response time. When usage
tracking is enabled, the Oracle BI Server collects usage tracking data for each query
and writes statistics to a usage tracking log file or inserts them directly to a database
table. For the purposes of this tutorial, you will use a database table, which is the
recommended leading practice.
Here We will separate Usage Tracking Process to 4 Steps
I. Creating Uasge Tracking Table
II. Creating and Customizing rpd file
III. changing the Configuration in EM

IV. Testing the Usage Tacking in Answers

StepI: Creating Usage Tracking Table
If we latest OBIEE 11g installed in our machine, open sqlplus and connect

DEV_BIPLATFORM schema.

write the below query:

select table_name from tabs where table_name like='S%';

 The table in the name S_NQ_ACCT is related to usage tracking. so we donot have any
 manual work in the usage tracking table creation. So before setting up the usage
 tracking we couldn't have any rows in the table

StepII: Setting up the Repository File
1. Create 'New Database' by right clicking on the empty space in physical layer of the

rpd and name it as 'UsageTracking

2. Right click on the physical layer object 'UsageTracking' ‐>New Object ‐> select
'Connection Pool and enter the detail like below screen
Connection Pool Name: UsageTracking ConnectionPool
Data Source Name: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Bose‐PC)(PORT=1521)))

(CONNECT_DATA=(SERVICE_NAME=ORCL)))

Password: Admin123
Click 'OK' after you enter the password and you will be prompted to confirm the password. Type the

same password.

3. Import the 'S_NQ_ACCT' table from DEV_BIPLATFORM Schema to the 'UsageTracking'

database repository.

4. Double Click on the DEV_BIPLATFORM physical folder and change it 'UsageTracking

Schema' So that It would like below screen

6. Drag the following three physical columns from Usage Tracking

Schema>S_NQ_ACCT to the Measures logical table in the UsageTracking business

model. For each column, right‐click the logical column and select Rename, and then

apply aggregation rule

7. Drag the following three physical columns from UsageTracking

Schema>S_NQ_ACCT to the Time logical table in the UsageTracking business model.

8. Here is the example screen screen for how to create Key for Time Logical table

Under Keys Tab ‐> type 'Time_key' in the key name and select 'Start Date' in the

columns.

9. Drag the following two physical columns from UsageTracking Schema>S_NQ_ACCT

to the Topic logical table in the UsageTracking business model. Rename them

Do not forget to to apply 'Logical SQL' as a key column for this logical table.

10. Drag the USER_NAME physical column from UsageTracking Schema>S_NQ_ACCT

to the User logical table in the UsageTracking business mode. Rename it to User

Name and apply this column as a key to this table.

Below is the complete BMM Layer Model Screen for UsageTracking:

11. Right click on the UsageTracking BMM Layer folder ‐> select 'Business Model

Diagram' ‐> Whole Diagram and the create joins between the tables as shown in the

12. Just drag the UsageTracking folder to Presentation Layer of the repository.

Now Save the repository and check the consistency check. RPD works are done!!!

StepIII: Setting up the EM

1. Open EM window using the link http://localhost:7001/em by entering weblogic

username and password

Expand WebLogicDomain and the click 'bifoundation_domain' in the left pane. Now

select 'System MBean Browser' in the WebLogic Domain in the right side pane like show

in the picture

2. expand 'Application Defined MBeans' and the expand 'oracle.biee.admin' in that

3. Expand 'BIDomain' in oracle.biee.admin and click on second 'BIDomain' as show in the

picture. Now select 'operation' tab from right side pane and then click 'lock'

Click 'Invoke'. Once we get the screen like below, click 'Return'

4.. Now expand BIDomain.BIServer.ServerConfiguration under oracle.biee.admin from
the left pane. Now select all the following option from the right side pane and apply
the change using the following steps
UsageTrackingCentrallyManaged
UsageTrackingConnectionPool

StepIV: Checking results with Answers using UsageTracking Subject Area
Now Browse some analysis from the existing dashboard or create some reports and

create one report using "UsageTracking" subject for the details.

Thanks