How to Enable Usage Tracking in Oracle BI 11g ( Version : 11.1.1.7.150120 )

Step 1:- In this step,need to check ‘S_NA_ACCT’ table avaiable in the dev_biplatform Schema user in DW repositary.

Step 2:- Configuring the Physical layer of the RPD file.
Right Click on the physical layer and selec the New database to create a database.

In the properties window go to genaral tab and give below information.
Name :- Usage Tracking
Database Type:- Oracle 11g (In my repository developed using oracle 11g database)

Go to the connection pool tab and create new connection pool for connect to dev_biplatform user.

Name the connection pool Usage Tacking Connection Pool.
Enter the Dat source name, User name and password as followed.
           Data source name: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.202.89)(PORT=1521)))(CONNECT_DATA=(SID=obiee)))
           User name: dev_biplatform
            Password: <password for dev_biplatform which has given when installing the obi server>
Click OK. Enter same password to confim.

Right click on teh usage tracking database and select New Object –> Phsical Schema

In the general tab give the Physical schema name as ‘Usage Tracking Schema’.

Right-click the Usage Tracking Connection Pool, select Import Metadata, and import the dev-biplatform.S_NQ_ACCT table into Usage Tracking.

Click Next in Step 2, Import Metadata - Select Metadata Types dialog box.

select the S_NQ_ACCT table under dev_biplatform and move it to the Repository View pane. Click Finish.

select the S_NQ_ACCT table under dev_biplatform and move it to the Repository View pane. Click Finish.

The dev_biplatform schema is added under Usage Tracking.
Right-click S_NQ_ACCT from dev_biplatform and then cut and paste it under Usage Tracking Schema.

S_NQ_ACCT table stucture is as below.

Step 3:- Configuring the Business Model and mapping layer of the RPD file.

Right-click in the Business Model and Mapping layer white space and select New Business Model.

Name the business model as ‘Usage Tracking’ in the general Tab.

Right Click on the Usage Tracking model and select New Object>Logical Table.

Name the table Measures and click OK.

Again create another three logical table BMM layer as below.

Logical Table Name

Measures(created from above step)

Time

Topic

User

Drag the following three physical columns from Usage Tracking Schema>S_N_ACCT to above logical tables(Measures logical table ) and rename as below.

Physical Column	Updated Column Name
QUERY_TEXT	Query Count
ROW_COUNT	ROW_COUNT
TOTAL_TIME_SEC	Total Time Seconds

Right-click the column Query Count and select Properties.

Click the Aggregation tab and select Count from the Default aggregation rule drop-down list. Click OK.

likewise, 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

Drag the following three physical columns from Usage Tracking Schema>S_N_ACCT to TIME logical table and rename as below.

Physical Column	Updated Column Name
START_DT	Start Date
START_HOURS_MIN	Start Hour Minute
END_HOUR_MIN	End Hour Minute

Set Start Date as the logical key for the Time logical table. Right-click the Time logical table and select Properties.

Click the Keys tab and enter Time_key in the Key Name column.

Select Start Date from the Columns drop-down list. Click OK.

Drag the following three physical columns from Usage Tracking Schema>S_N_ACCT to TOPIC logical table and rename as below.

Physical Column	Updated Column Name
QUERY_TEXT	Logical SQL
SUBJECT_ AREA_NAME	Subject Area

Set Logical SQL as the logical key for the Topic logical table.

Drag the USER_NAME physical column from Usage Tracking Schema>S_NQ_ACCT to the User logical table in the Usage Tracking business mode. Rename it to User Name.

Update User Name as the logical key for the User logical table.

Business model layer.

Right-click the Usage Tracking 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.

Step 4:- Configure the Presentation Layer

Drag the Usage Tracking business model to the Presentation layer to create the Presentation layer objects.

Save and you want to check consistency of teh RPD file.

Step 5:- Configure the Usage Tracking in EM

Logn to EM as Weblogic user.

URL:- :7001/em">http://<localhost>:7001/em

Go to the Weblogic Domain and select bifoundation_domain.

Click on the Weblogic domain and select the System MBeans Browser.

Then go to Confiuration MBeans –> MBeanServer Delegate and click on the MBeanServer Delegate bean.

Then go to the Application Defined MBeans and expand the oracle.biee.admin menu

Expand the BIDomain.

There are two BIDomain and select BIDomain where Group = service

Go to Operation tab and lock the bi domain.click the Invoke button.

Click the Return button.

Expand BIDomain.BIInstance.ServerConfiguration, and then select the BIDomain.BIInstance.ServerConfiguration MBean.

Click the Attributes tab. Scroll down and click UsageTrackingCentrallyManaged. Set Value to true in the drop-down list.
Click the Apply button. When you see the confirmation message for the update, click the Return button.

Similarly, set the UsageTrackingEnabled attribute to true to enable usage tracking.
Set the UsageTrackingDirectInsert attribute to true to enable direct insertion.
Set the UsageTrackingPhysicalTableName as "Usage Tracking"."Usage Tracking Schema"."S_NQ_ACCT
Set the UsageTrackingConnectionPool as "Usage Tracking"."Usage Tracking Connection Pool"
refer below screenshot for above configuration

Go to the BIDomain MBean where group=Service under oracle.biee.admin, Domain:bifoundation_domain, BIDomain. Click the Operations tab.
Click one of the first commit operations where description is ‘Same as commit("ERROR"); raises an exception if any errors occur’