Search This Blog

Tuesday 7 January 2014

Usage Tracking in OBIEE 11.1.1.7.0 (11g)

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)))
OR
Data Source Name: //Bose-PC:1521/ORCL 
User Name: DEV_BIPLATFORM
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
5. Now Come to BMM Layer and Create UsageTracking BMM Layer folder and Create for new logical tables in that BMM Layer like shown in the below picture

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 like shown in the table:

Physical Column
Rename
Aggregation Rule
QUERY_TEXT
Query Count
Count
ROW_COUNT
Row Count
Sum
TOTAL_TIME_SEC
Total Time Seconds
Sum
  
7. Drag the following three physical columns from UsageTracking Schema>S_NQ_ACCT to the Time logical table in the UsageTracking business model. Rename them as follows:

Physical Column
Rename
Key
START_DT
Start Date
 Yes
START_HOUR_MIN
Start Hour Minute

END_HOUR_MIN
End Hour Minute


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 as follows:
Physical Column Rename
QUERY_TEXT Logical SQL
SUBJECT_ AREA_NAME Subject Area
 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 below screen:
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
                         UsageTrackingDirectInsert
                         UsageTrackingEnabled
                         UsageTrackingPhysicalTableName
Select UsageTrackingCentrallyManaged and then select value 'true' and apply. After successful apply, click Return to previous screen
Select UsageTrackingConnectionPool and type the value "UsageTracking".UsageTracking ConnectionPool" and then apply. After successful apply, click Return to previous screen

Select UsageTrackingPhysicalTableName and type the value "UsageTracking"."UsageTracking Schema"."S_NQ_ACCT" and then apply. After successful apply, click Return to previous screen
Select UsageTrackingDirectInsert and select the value 'true' and then apply. After successful apply, click Return to previous screen
Select UsageTrackingEnabled and select the value 'true' and then click apply. After successful apply, click Return to previous screen
So with the above screen all the configuration related to Usage Tracking is applied. Now  Come to BIDomain and select 'BIDomain' under orace.biee.admin to commit the changes. Select Operation tab and select 'simplecommit'
In simplecommit screen click 'Invoke'. After successful execution of simplecommit click retun to previous screen.
Now upload the updated repository in EM and restart the BI Services.

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
select User Name from User, Logical SQL and Subject Area from Topic, Start Hour Minute from Time and Row Count from Measures table in the criteria section of creating new analysis. Now check with the results
Below Screen Shows the results for username, logical sql, query count, row count, total time seconds from the Usage Tracking subject Area.
Now If you are checking the table 'S_NQ_ACCT' in tabale, we could see the updated records.

Cheers!
Bose

8 comments:

  1. Hi Bose

    Thanks for the post. This is the only post that I found it very informative about 11.1.1.7 usage tracker.

    I am trying to configure the usage tracker for 11.1.1.7.0.. I did all the RPD changes and the EM changes but the only problem is, when we ran dashboard or any analysis, NO RECORDS ARE INSERTED into the S_NQ_ACCT Table.. Looks like I am missing a small step. Can you please guide me to identify the missing piece.

    steps we followed

    1) Created the time related tables "S_ETL_DAY" and S_ETL_TIME_DAY and populate the data in the 'bi_platform' schema
    2) Configured the RPD Usage Trackin Connections to point to the 'bi_latform' schema.
    3)Iinserted couple of sample records manually in S_NQ_ACCT and we are able report on the sample records by running analysis using "Usage Tracking" subject Area.
    4) Configured 5 usage tracker "system mbean browser" properties in EM by referring your blog.
    5) Restarted all the 5 BI Compenents and also the weblogic console and bi_server..

    THE ISSUE is after doing all the above, when we login to the application and we ran a dashboard or analysis , NO RECORDS are inserted in the S_NQ_ACCT table.

    Thanks for the help

    Regards
    Barna

    ReplyDelete
  2. Forgot to include the following.

    No errors in the NQServer.log file

    ReplyDelete
  3. very useful post

    ReplyDelete
  4. Hi,

    I have made all the changes suggested in both the RPD and em.We are on OBIEE 11.1.1.7.0. But when I run a report or dashboard, no data is inserted into S_NQ_ACCT.

    Thanking you in anticipation of your help!

    ReplyDelete
  5. Your blog has given me that thing which I never expect to get from all over the websites. Nice post guys!

    ReplyDelete
  6. Hi , i have done all theese steps and it works. But one important question, why it doesnt log reports based on data models? ( i think it is also called bi publisher reports). What should i do for this. I need to determine unusde reports at work.

    ReplyDelete
  7. Awesome and interesting article. Great things you've always shared with us. Thanks. Just continue composing this kind of post. mspy reviews

    ReplyDelete
  8. Great Information sharing .. I am very happy to read this article .. thanks for giving us go through info.Fantastic nice. I appreciate this post. gps equipment tracking

    ReplyDelete

OBIEE 12c to OAS Migration

 Hi All, I have migrated successfully few projects from BI 12c to OAS 6.4. If  anyone wants help then please reach Me! Cheers! Bose

Recent Posts