- To monitor system and ad hoc query performance and to provide usage details.
- Which report is queried? Who queried? How many times the report/dashboard or ad hoc analysis is performed by users. Who logged in?
- Daily and weekly peak usage patterns and how much load variance on OBIEE.
There are three main components:
- Changes/configuration in the RPD
- Configuration in Enterprise Manager
- SQL to run to setup Usage Tracking schema.
How to ENABLE?
There are two methods of enabling Usage tracking.
- Direct Insertion - Oracle BI Server inserts statistics for every query are inserted into a database table.
- Log File - Oracle BI Server inserts statistics for every query into log file
It is recommended that you use direct insertion to write statistics to a database table.
The sample RPD can be found at:
{OBIEE_11G_Instance}/bifoundation/OracleBIServerComponent/coreapplication_obis1/sample/usagetracking.
Note: By default Usage Tracking is recorded in the xxx_BIPLATFORM schema that gets created by the Repository Creation Utility[RCU] at the start of the OBIEE installation. You can create your own table to hold usage tracking data within ORACLE or other database. You should generally use th predefined table to hold this data.
Scripts are available from Directory:
{OBIEE_11G_Instance}\bifoundation\OracleBIServerComponent\coreapplication_obis1\sample\usagetracking\SQL_Server_Time
Create the following sql scripts:
- Oracle_create_nQ_Calendar—Create table scripts
- Oracle_create_nQ_Clock—Create Table Scripts
- Oracle_nQ_Calendar—Insert Scripts
- Oracle_nQ_Clock—Insert Scripts
- Also create S_NQ_ACCT table
- We need to even create NQ_LOGIN_GROUP view also using the following syntax:
Scripts are available from Directory:
{OBIEE_11G_Instance}\bifoundation\OracleBIServerComponent\coreapplication_obis1\schema
Compile - SAACCT.Oracle
- SQLServer_create_nQ_UserGroup - Use This Script for Oracle Database
CREATE OR REPLACE VIEW nq_login_group AS SELECT DISTINCT user_name AS login, user_name AS resp FROM s_nq_acct;
Grant select on nq_login_group to public;
Commit;
These script create the following tables
- S_ETL_DAY
- S_ETL_TIME_DAY
- S_NQ_ACCT
We now need to add extra fields to the S_NQ_ACCT table in the physical layer. This is because Oracle have added extra fields to the actual table and altered the usage tracking functionality in OBIEE to use them. If they are not in the RPD, OBIEE will fail to update the table with usage details.
- Expand the S_NQ_ACCT table in the physical layer and check that all of the following fields exist with the correct data type.
- Rename RUNAS_USER_NAME to IMPERSONATOR_USER_NAME
- Add the following missing fields (right click on S_NQ_ACCT, select New Object -> Physical Column…)
- ID
- Type: VARCHAR
- Length: 50
- Nullable: Unchecked
- QUERY_BLOB
- Type: LONGVARCHAR
- Length: 4000
- Nullable: Checked
- QUERY_KEY
- Type: VARCHAR
- Length: 128
- Nullable: Checked
- Below is a list of all of the required fields, data type, length, and nullable flag:
| Field Name | Data Type | Length | Nullable? |
| CACHE_IND_FLG | CHAR | 1 | No |
| COMPILE_TIME_SEC | DOUBLE | Yes | |
| CUM_DB_TIME_SEC | DOUBLE | Yes | |
| CUM_NUM_DB_ROW | DOUBLE | Yes | |
| END_DT | DATETIME | Yes | |
| END_HOUR_MIN | CHAR | 5 | Yes |
| END_TS | DATETIME | Yes | |
| ERROR_TEXT | VARCHAR | 250 | Yes |
| ID | VARCHAR | 50 | No |
| IMPERSONATOR_USER_NAME | VARCHAR | 128 | Yes |
| NODE_ID | VARCHAR | 15 | Yes |
| NUM_CACHE_HITS | DOUBLE | Yes | |
| NUM_CACHE_INSERTED | DOUBLE | Yes | |
| NUM_DB_QUERY | DOUBLE | Yes | |
| PRESENTATION_NAME | VARCHAR | 128 | Yes |
| QUERY_BLOB | LONGVARCHAR | 4000 | Yes |
| QUERY_KEY | VARCHAR | 128 | Yes |
| QUERY_SRC_CD | VARCHAR | 30 | Yes |
| QUERY_TEXT | VARCHAR | 1024 | Yes |
| REPOSITORY_NAME | VARCHAR | 128 | Yes |
| ROW_COUNT | DOUBLE | Yes | |
| SAW_DASHBOARD | VARCHAR | 150 | Yes |
| SAW_DASHBOARD_PG | VARCHAR | 150 | Yes |
| SAW_SRC_PATH | VARCHAR | 250 | Yes |
| START_DT | DATETIME | Yes | |
| START_HOUR_MIN | CHAR | 5 | Yes |
| START_TS | DATETIME | Yes | |
| SUBJECT_AREA_NAME | VARCHAR | 128 | Yes |
| SUCCESS_FLG | DOUBLE | Yes | |
| TOTAL_TIME_SEC | DOUBLE | Yes | |
| USER_NAME | VARCHAR | 128 | Yes |
- The join between S_NQ_ACCT and NQ_LOGIN_GROUP is broken. Select both tables, right click and choose Phyiscal Diagram -> Selcted Object(s) Only.
- Select the red arrow, and click Delete to remove the broken join.
- Now click on the new join icon
- Draw a new join from S_NQ_ACCT to NQ_LOGIN_GROUP and change the join condition to:
"ABC Usage Tracking"."Catalog"."dbo"."NQ_LOGIN_GROUP"."LOGIN" = "OBI Usage Tracking"."Catalog"."dbo"."S_NQ_ACCT"."USER_NAME"
Now right click on the table S_NQ_ACCT and select properties.
Select the Keys tab.
If there is no key defined, enter ID in Key name and select the ID field from the drop down in the Columns field.
Click Ok.
Don't forget to setup proper Connection Pool information for both connection pools:
- Usage Tracking Connection Pool
- Usage Tracking Writer Connection Pool.
4. Ammend NQSCONFIG.ini ( File Location has changed from 10g to Below)
C:\Middleware\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1\
nqsconfig.INI
5. Set the Physical Table Parameter.
- Set the 'PHYSICAL_TABLE_NAME' parameter to specify the table into which the insert the records corresponding to query statistics
- The physical Table name is fully qualified name in Physical layer of Admin Tool.
The general structure of this parameter depends on the type of database being used. For Oracle, the structure is
PHYSICAL_TABLE_NAME = "<Database>"."<Schema>"."<Table>".
Set the CONNECTION_POOL parameter to specify the connection pool to use for inserting records into the usage tracking table. The setting is CONNECTION_POOL = "ABC Usage Tracking"."Usage Tracking Connection Pool". Leave the default settings for BUFFER_SIZE, BUFFER_TIME_LIMIT_SECONDS, NUM_INSERT_THREADS, and MAX_INSERTS_PER_TRANSACTION. Your configuration file should look like this:
The connection pool name is the fully qualified name as it appears in the Physical layer of the repository.
Save and close the NQSConfig.ini configuration file.
Creating the Business Model Layer for Usage Tracking
Right-click in the Business Model and Mapping layer white space and select New Business Model.
Name the business model 13-Usage Tracking 2, and then click OK. Note: there is a similar business model name 10 - Usage Tracking. This is set up by Sample Applications.
Right-click 13-Usage Tracking 2 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 13-Usage Tracking 2 business model.
Drag the following three physical columns from Usage Tracking Schema>S_N_ACCT to the Measures logical table in the 13- Usage Tracking 2 business model. For each column, right-click the logical column and select Rename, and then rename as follows:
| Physical Column | Rename |
| 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.
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 |
Drag the following three physical columns from Usage Tracking Schema>S_NA_ACCT to the Time logical table in the 13-Usage Tracking 2 business model. Rename them as follows:
| Physical Column | Rename |
| START_DT | Start Date |
| START_HOURS_MIN | Start Hour Minute |
| END_HOUR_MIN | End Hour Minute |
The Time logical table should look like this:
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 two physical columns from Usage Tracking Schema>S_NQ_ACCT to the Topic logical table in the 13-Usage Tracking 2 business model. Rename them as follows:
| Physical Column | Rename |
| 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 13-Usage Tracking 2 business mode. Rename it to User Name.
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 13-Usage Tracking 2 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.
You have now created the Usage Tracking business model. You will next move it to the Presentation layer.
Configuring Usage Tracking in Enterprise Manager
You now configure usage tracking through Oracle Enterprise Manager (EM) by performing the following steps:
Log into EM. In this example, the user name is weblogic and the password is welcome1.
Select WebLogic Domain>bifoundation_domain from the left navigation pane.
Click the WebLogic Domain drop-down list in the right pane.
Select System MBean Browser from the list.
Expand Application Defined MBeans>oracle.biee.admin.
Expand bifoundation_domain.
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.
Click the Operation tab.
Click the Lock link. On the Operation:lock page, 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 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:
"12-Usage Tracking"."Usage Tracking Schema"."S_NQ_ACCT"
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:
"12-Usage Tracking"."UsageTracking Connection Pool"
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.
Go to the Oracle Business Intelligence Instance page and click Restart All on the Availability>Processes tab.
No comments:
Post a Comment