The Oracle BI Server supports the collection of usage tracking data.
When usage tracking is enabled, the Oracle BI Server collects usage
tracking data for each query, and it writes statistics to a usage
tracking log file or inserts them directly into a database table.
After upgrading 10 g to 11g , need to manually setup Usage tracking in new 11g environment. There are two ways
i)Still we can use old existing tables to write Usage information
ii)New schema DEV_BIPLATFORM can be used(Usage Tracking tables will be installed during the RCU installation)
This blog covers first case where Old existing tables can be used for
Usage tracking. Below are the steps needed to be performed to enable
Usage Tracking on existing 10g tables.
1. Check table structure of S_NQ_ACCT table in both 10g
warehouse(existing) and 11g DEV_BIPLATFORM schema.(Here we can find some
difference)
2. Alter old table S_NQ_ACCT structure to in synch with 11g table.
3. Add new columns in RPD S_NQ_ACCT table
4.Eanable Usage Tracking in Enterprise Manager
1. Checking S_NQ_ACCT table structure
In order to reuse the existing Usage Tracking tables , we have to
make sure tables structure should be same in both 10g and 11g schemas.
Login to 10 g warehouse and describe S_NQ_ACCT
DESC S_NQ_ACCT
| Name | Null | Type |
| —————— | ——– | ————– |
| USER_NAME | VARCHAR2(128) | |
| REPOSITORY_NAME | VARCHAR2(128) | |
| SUBJECT_AREA_NAME | VARCHAR2(128) | |
| NODE_ID | VARCHAR2(15) | |
| START_TS | DATE | |
| START_DT | DATE | |
| START_HOUR_MIN | CHAR(5) | |
| END_TS | DATE | |
| END_DT | DATE | |
| END_HOUR_MIN | CHAR(5) | |
| QUERY_TEXT | VARCHAR2(1024) | |
| SUCCESS_FLG | NUMBER(10) | |
| ROW_COUNT | NUMBER(10) | |
| TOTAL_TIME_SEC | NUMBER(10) | |
| COMPILE_TIME_SEC | NUMBER(10) | |
| NUM_DB_QUERY | NUMBER(10) | |
| CUM_DB_TIME_SEC | NUMBER(10) | |
| CUM_NUM_DB_ROW | NUMBER(10) | |
| CACHE_IND_FLG | NOT NULL | CHAR(1) |
| QUERY_SRC_CD | VARCHAR2(30) | |
| SAW_SRC_PATH | VARCHAR2(250) | |
| SAW_DASHBOARD | VARCHAR2(150) | |
| SAW_DASHBOARD_PG | VARCHAR2(150) | |
| PRESENTATION_NAME | VARCHAR2(128) | |
| ERROR_TEXT | VARCHAR2(250) | |
| RUNAS_USER_NAME | VARCHAR2(128) | |
| NUM_CACHE_INSERTED | NUMBER(10) | |
| NUM_CACHE_HITS | NUMBER(10) |
Login to DEV_BIPLATFORM schema in 11g and do describe on S_NQ_ACCT.
DESC S_NQ_ACCT
| Name | Null | Type |
| ———————- | ——– | ————– |
| USER_NAME | VARCHAR2(128) | |
| REPOSITORY_NAME | VARCHAR2(128) | |
| SUBJECT_AREA_NAME | VARCHAR2(128) | |
| NODE_ID | VARCHAR2(50) | |
| START_TS | DATE | |
| START_DT | DATE | |
| START_HOUR_MIN | CHAR(5) | |
| END_TS | DATE | |
| END_DT | DATE | |
| END_HOUR_MIN | CHAR(5) | |
| QUERY_TEXT | VARCHAR2(1024) | |
| QUERY_BLOB | CLOB | |
| QUERY_KEY | VARCHAR2(128) | |
| SUCCESS_FLG | NUMBER(10) | |
| ROW_COUNT | NUMBER(20) | |
| TOTAL_TIME_SEC | NUMBER(10) | |
| COMPILE_TIME_SEC | NUMBER(10) | |
| NUM_DB_QUERY | NUMBER(10) | |
| CUM_DB_TIME_SEC | NUMBER(10) | |
| CUM_NUM_DB_ROW | NUMBER(20) | |
| CACHE_IND_FLG | NOT NULL | CHAR(1) |
| QUERY_SRC_CD | VARCHAR2(30) | |
| SAW_SRC_PATH | VARCHAR2(250) | |
| SAW_DASHBOARD | VARCHAR2(150) | |
| SAW_DASHBOARD_PG | VARCHAR2(150) | |
| PRESENTATION_NAME | VARCHAR2(128) | |
| ERROR_TEXT | VARCHAR2(250) | |
| IMPERSONATOR_USER_NAME | VARCHAR2(128) | |
| NUM_CACHE_INSERTED | NUMBER(10) | |
| NUM_CACHE_HITS | NUMBER(10) | |
| ID | NOT NULL | VARCHAR2(50) |
Here columns in maroon color are existed in 10 g warehouse tables, but need to be altered in terms of length and column rename.
Columns which are in Green color completely new in 11g,So these columns need to be added into existing old tables.
2. Alter old table S_NQ_ACCT structure to in synch with 11g table.
To synch 10 g usage tracking tables with 11g tables, need to perform below alterations on the 10g usage tracking tables.
ALTER TABLE S_NQ_ACCT MODIFY NODE_ID VARCHAR2(50);
ALTER TABLE S_NQ_ACCT MODIFY ROW_COUNT NUMBER(20);
ALTER TABLE S_NQ_ACCT MODIFY CUM_NUM_DB_ROW NUMBER(20);
ALTER TABLE S_NQ_ACCT RENAME COLUMN RUNAS_USER_NAME TO IMPERSONATOR_USER_NAME;
ALTER TABLE S_NQ_ACCT ADD (ID VARCHAR2(50) NOT NULL,QUERY_BLOB CLOB,QUERY_KEY VARCHAR2(128));
COMMIT
3. Add new columns in RPD S_NQ_ACCT table
Open RPD in Offline ,Navigate to Physicala Database Oracle Analytics
Usage->Catalog->dbo Add the
IMPERSONATOR_USER_NAME,ID,QUERY_BLOB,QUERY_KEY columns to the S_NQ_ACCT
table as shown below.
And Make sure Not Null checked for required columns.
4.Enable Usage Tracking in Enterprise Manager
To Enable Usage Tracking in EM ,perform below steps.
- Log into EM.
- Select WebLogic Domain>bifoundation_domain from the left navigation pane.
3.Click the WebLogic Domain drop-down list in the right pane.
4.Select System MBean Browser from the list
5.Expand Application Defined MBeans>oracle.biee.admin.
6.Expand bifoundation_domain and select BIDomain
7.Lock the domain to update it. Expand BIDomain and select the BIDomain MBean where group=Service. Note: Expand Show MBean Information in the right pane.
8.Click the Operation tab.
9.Click the Lock link. On the Operation:lock page, click the Invoke button
10.Click the Return button.
11.Expand BIDomain.BIInstance.ServerConfiguration, and then select the BIDomain.BIInstance.ServerConfiguration MBean.
12.Click the Attributes tab. Scroll down and click UsageTrackingCentrallyManaged. Set Value to true in the drop-down list.
13.Click the Apply button. When you see the confirmation message for the update, click the Return button.
14.Similarly, set the UsageTrackingEnabled attribute to true to enable usage tracking
15.Set the UsageTrackingPhysicalTableName attribute to the name of
the fully qualified database table for collecting query statistic
information, as it appears in the Physical layer of the Oracle BI
repository. In this example, it is set as follows:“ Oracle Analytics Usage “.”Catalog”.”dbo”.”S_NQ_ACCT”
16.Set the UsageTrackingConnectionPool attribute to the name of the
fully qualified connection pool for the query statistics database, as it
appears in the Physical layer of the Oracle BI repository. In this
example, it is set as follows: “ Oracle Analytics Usage “.”Usage Tracking Writer Connection Pool”
17.
Note: For usage tracking insertions to succeed, the
connection pool must be configured with a user ID that has write access
to the back-end database. Also, it is recommended that the connectivity
type support international data.After applying your changes, release the
lock on the domain, as follows:
- Return 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.
18.Go to the Oracle Business Intelligence Instance page and click Restart All on the Availability>Processes tab to Restart all the services.
No comments:
Post a Comment