Thursday, June 20, 2013

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

1 comment:

  1. how can we get the S_NQ_Acct table updated. If we are using the same usage tracking subject area to build analysis, I wonder how the data into the S_NA_ACCT table gets updated daily. I am new to this concept and I am wondering if there are any changes required in the ETL perspective to get the udpated records?

    ReplyDelete