Setting Up Usage Tracking in Oracle BI 11g

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