Setting Up Usage Tracking in Oracle BI 11g Part 1
I have seen several post in OTN. How to implement Usage Tracking in OBIEE 11g(11.1.1.7.0). Here am providing step by step process for Usage Tracking implementation. Please feel free your comments on this post.
Step 1:
You have check for the existence of the S_NA_ACCT Usage
Tracking table in the database Repository Creation Utility (RCU) schema. Please
verify the table in data base like below.
Step 2:
Creating
the Database Object in the Physical Layer
Open satyawrite_BI0003.rpd in offline mode in the
BI Administration Tool.
Step 3:
Right-click inside the Physical layer white
space and select New Database to
open the Database properties dialog box
Click the General tab and name the database Satya Usage Tracking and select In the Database drop-down list, select Oracle 11g.
Click the Connection Pool tab and click Add to open the Connection Pool dialog box.
Name the connection pool Usage Tacking Connection Pool and Enter the data source name, username, and password. In this example, the following are entered:
Data source name: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=REDSATYA212)(PORT=1521)))(CONNECT_DATA=(SID-orcl)))
User name: dev_biplatform
Password: admin1234
Right-click the Satya Usage Tracking database object and select New Object>Physical Schema and Name the physical schema Usage Tracking Schema and then click OK.
Right-click the Usage Tracking Connection Pool, select Import Metadata, and import the dev-biplatform.S_NQ_ACCT table into Satya Usage Tracking.
The dev_biplatform schema is added under Satya Usage Tracking.
Creating the Business Model Layer for Usage Tracking:
Right-click in the Business Model and Mapping layer white space and select New Business Model (Satya Usage BMM) then Right-click Satya Usage BMM and select New Object>Logical Table.
Name the table Measures and click OK.
Repeat and add three more tables - Time, Topic, and User to the Satya Usage BMM business model.
Drag the following three physical columns from Satya Ranki
Reddy Usage Schema>S_N_ACCT to the Measures logical table in the Satya Usage
BMM to business model. For each column, right-click the logical column and
select Rename, and then rename as follows.
Measure:
Physical Column
|
Rename
|
QUERY_TEXT
|
Query Count
|
ROW_COUNT
|
Row Count
|
TOTAL_TIME_SEC
|
Total Time Seconds
|
Time:
Physical Column
|
Rename
|
START_DT
|
Start Date
|
START_HOURS_MIN
|
Start Hour Minute
|
END_HOUR_MIN
|
End Hour Minute
|
Topic:
Physical Column
|
Rename
|
QUERY_TEXT
|
Logical SQL
|
SUBJECT_ AREA_NAME
|
Subject Area
|
User:
USER_NAME
|
Right-click the column Query Count and select Properties and Click the Aggregation tab and select Count from the Default aggregation rule drop-down list. Click OK.
Similarly,
set the Aggregation rule for other logical columns in the Measures logical
table as follows:
Logical Column
|
Aggregation Rule
|
Row Count
|
Sum
|
Total Time Seconds
|
Sum
|
Set Start Date as the logical key for the Time logical table. Right-click the Time logical table and select Properties.
Similarly you have to set Logical SQL as the logical key for the Topic logical table and Set User Name as the logical key for the User logical table.
Use the Business Model Diagram to create logical joins from Measures to Time. Right-click the Satya Usage BMM to business model and select Business Model Diagram>Whole Diagram --> Click the New Join icon from the menu and create logical joins from Measure to Time, Topic, and User as shown below.
Drag the Satya Usage BMM business model to the Presentation layer to create the Presentation layer objects. Rename the subject area to Satya Usage BMM.
Please see the next article for Oracle Enterprise Manager (EM) steps : Usage Tracking Part 2
Thanks,
Satya Ranki Reddy
