This chapter describes how to manage usage tracking for Oracle Business Intelligence. 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 inserts it directly to a database table.
Note:
The Oracle BI Summary Advisor feature works in conjunction with the usage tracking feature. Summary Advisor only works with direct insertion usage tracking.
Oracle BI Summary Advisor is only available when you are running Oracle Business Intelligence on the Oracle Exalytics Machine. See "Using Oracle BI Summary Advisor to Identify Query Candidates for Aggregation" in Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for more information about the Summary Advisor feature.
This chapter includes the following sections:
The Oracle BI Server supports the accumulation of usage tracking statistics that can be used in a variety of ways such as database optimization, aggregation strategies, or billing users or departments based on the resources that they consume. The BI Server tracks usage at the detailed query level.
When you enable usage tracking, statistics for every query are inserted into a database table or are written to a usage tracking log file. If you use direct insertion, then the BI Server directly inserts the usage tracking data into a relational database table. It is recommended that you use direct insertion to write statistics to a database table.
When the BI Server starts, it validates the column names in the metadata against the list of valid columns in the usage tracking table. The following events occur:
Column names. If there is a mismatch between the columns in the database table and the columns in the metadata, then it results in a database error on insert.
Varchar length. If the length in the metadata and the set length in the table do not match, then an error is written to the nqserver.log file and usage tracking is disabled.
Note:
A sample usage tracking repository model is provided with the Oracle Business Intelligence installation at:
ORACLE_INSTANCE\bifoundation\admin\provisioning\OracleBIServerComponent\usage_tracking.rpd.
This path applies to Oracle BI EE 11.1.1.7.10 and later versions, but does not apply to earlier versions.
To use the sample usage tracking repository, you modify the connection pool to point to your database, then merge the usage tracking repository with your existing repository.
Direct insertion is the recommended method for setting up usage tracking. This section describes how to set up direct insertion, and contains the following topics:
Before you can use direct insertion usage tracking, you must set up a database to store the usage tracking statistics. You must run the Repository Creation Utility (RCU) on the target database to create the required statistics schemas.
Typically, you use the database you installed for use with Oracle Business Intelligence as the statistics database because this database already has the RCU-created schemas. The RCU-created table names for usage tracking are S_NQ_ACCT
, S_NQ_DB_ACCT
, and S_NQ_INITBLOCK
. See Section 9.3, "Description of the Usage Tracking Data" for more information about these tables.
You also need to import the database into the Physical layer of the Oracle BI repository.
To set up the usage tracking statistics database:
Run the Repository Creation Utility on an external database of your choice. You can skip this step if you choose to use the database you installed for use with Oracle Business Intelligence for usage tracking statistics, because this database has the RCU-created tables already.
Open the Administration Tool and import the database into the Physical layer. See Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition for more information.
Save and close the repository.
Use Fusion Middleware Control to upload the repository and make it available for queries. See Section 10.2, "Using Fusion Middleware Control to Upload a Repository and Set the Oracle BI Presentation Catalog Location" for more information.
To set up direct insertion for new (non-upgraded) installations, use the System MBean Browser in Fusion Middleware Control.
To set up direct insertion usage tracking using the System MBean Browser:
Display the Fusion Middleware Control MBean Browser.
For information, see Section 2.4.2, "Displaying the Fusion Middleware Control MBean Browser."
Expand Application Defined MBeans, then expand oracle.biee.admin, then expand Domain: bifoundation_domain.
Lock the domain, as follows:
Expand BIDomain and select the BIDomain MBean where group=Service.
Display the Operations tab.
Click the lock link.
Expand BIDomain.BIInstance.ServerConfiguration, then select the BIDomain.BIInstance.ServerConfiguration MBean.
Ensure that the UsageTrackingCentrallyManaged attribute is set to true. When UsageTrackingCentrallyManaged is set to false, the following parameters are managed using the NQSConfig.INI file on each Oracle BI Server computer rather than the System MBean Browser:
SummaryAdvisorTableName
SummaryStatisticsLogging
UsageTrackingConnectionPool
UsageTrackingDirectInsert
UsageTrackingEnabled
UsageTrackingPhysicalTableName
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. For example:
"My_DB"."DEV_BIPLATFORM"."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. For example:
"My_DB"."Usage 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 supports 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.
Display the Operations tab.
Click one of the commit operations.
Go to the Oracle Business Intelligence Overview page and click Restart.
For upgrading customers, the usage tracking parameters are not centrally managed by default. You can set UsageTrackingCentrallyManaged to true as described in the previous procedure, and use the System MBean Browser to update the parameters, or you can manage the usage tracking parameters using NQSConfig.INI.
To enable direct insertion usage tracking in NQSConfig.INI when central management is disabled for these parameters, follow these steps:
On the Oracle BI Server computer, open the NQSConfig.INI file in a text editor. You can find this file at:
ORACLE_INSTANCE/config/OracleBIServerComponent/coreapplication_obisn
Make a backup copy of the file before editing.
In the [USAGE_TRACKING] section, update the following parameters:
Set ENABLE
to YES
.
Set DIRECT_INSERT
to YES
.
Set PHYSICAL_TABLE_NAME
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. For example:
PHYSICAL_TABLE_NAME = "My_DB"."DEV_BIPLATFORM"."S_NQ_ACCT";
Set CONNECTION_POOL
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. For example:
CONNECTION_POOL = "My_DB"."Usage Connection Pool";
Set INIT_BLOCK_TABLE_NAME
to the name of the fully-qualified database table for inserting records that correspond to the initialization block statistics, as it appears in the Physical layer of the Oracle BI repository. For example:
INIT_BLOCK_TABLE_NAME = "My_DB"."DEV.BIPLATFORM"."S_NQ_INITBLOCK;
Set INIT_BLOCK_CONNECTION_POOL
to the name of the fully-qualified connection pool for the table for inserting records that correspond to the initialization block statistics, as it appears in the Physical layer of the Oracle BI repository. For example:
INIT_BLOCK_CONNECTION_POOL = "My_DB"."Usage 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 supports international data.
Save and close the file.
Restart the Oracle BI Server.
If you have multiple Oracle BI Server instances, then repeat these steps in each NQSConfig.INI file for all Oracle BI Server instances.
In addition to the setup parameters described previously, you can also update the following optional parameters in the Usage Tracking section of the NQSConfig.INI file:
BUFFER_SIZE. This parameter indicates how much memory the BI Server allocates for buffering the insert statements. Such a buffer lets the BI Server submit multiple insert statements as part of a single transaction, improving Usage Tracking insert throughput. It also means that ordinary analyses do not have to wait on Usage Tracking insertions, which improves average query response time. You might want to adjust this value based on available memory and memory utilization on the server computer.
BUFFER_TIME_LIMIT_SECONDS. This parameter indicates the maximum amount of time that an insert statement remains in the buffer before the Usage Tracking subsystem attempts to issue it. This time limit ensures that the BI Server issues the insert statements quickly, even during periods of extended quiescence.
NUM_INSERT_THREADS. This parameter indicates the number of threads that remove insert statements from the buffer and issue them to the Usage Tracking database. Assuming separate connection pools for readers and inserters, the number of insert threads typically equals the Maximum Connections setting in the connection pool.
MAX_INSERTS_PER_TRANSACTION. This parameter indicates the maximum number of insert statements that the Usage Tracking subsystem attempts to issue as part of a single transaction. The larger this number, the greater potential throughput for UsageMarathon Tracking inserts. However, a larger number also increases the likelihood of transactions failing due to deadlocks. A small value for BUFFER_TIME_LIMIT_SECONDS
can limit the number of inserts per transaction.
See Appendix A, "NQSConfig.INI File Configuration Settings" for additional information about the usage tracking configuration parameters.
Table 9-1 describes each column in the S_NQ_ACCT
usage tracking table. Where appropriate, the data type and length is also included.
As you review the descriptions in Table 9-1, you might assume that certain of the time-related columns can be added or subtracted to equal exact values. For example, you might assume that TOTAL_TIME_SEC is equal to END_TS minus START_TS. The following list explains why the columns do not provide such exact values:
The various processes run in parallel and their speed depends on the load on the BI Server and on database performance. The server-based operations might be either light or intensive.
If all connections are full, then the query enters a queue and waits to be processed. The timing depends on the load and configuration of the BI Server.
Table 9-1 Usage Tracking Data in S_NQ_ACCT
Column | Description |
---|---|
CACHE_IND_FLG |
Default is N. Y indicates a cache hit for the query; N indicates a cache miss. |
COMPILE_TIME_SEC |
The time in seconds that is required to compile the query. The number for COMPILE_TIME_SEC is included in TOTAL_TIME_SEC, as described in this table. |
CUM_DB_TIME_SEC |
The total amount of time in seconds that the BI Server waited for back-end physical databases on behalf of a logical query. |
CUM_NUM_DB_ROW |
The total number of rows that are returned by the back-end databases. |
END_DT |
The date the logical query was completed. |
END_HOUR_MIN |
The hour and minute the logical query was completed. |
END_TS |
The date and time that the logical query finished. The start and end timestamps also reflect any time that the query spent waiting for resources to become available. Note: If the user submitting the query navigates away from the page before the query finishes, then the final fetch never happens, and a timeout value of 3600 is recorded. However, if the user navigates back to the page before the timeout, then the fetch completes at that time, and this is recorded as the end_ts time. |
ERROR_TEXT |
Default is Null. Varchar(250) Error message from the back-end database. This column is only applicable if the |
ID |
The unique row ID. |
NODE_ID |
Concatenates <hostname>:<instance_name>:<component_name> where <instance_name>:<component_name> can be overridden by the environment variables INSTANCE_NAME and COMPONENT_NAME. For example, examplehost:instance1:coreapplication_obis1. Default value of INSTANCE_NAME is instance1, and default value of COMPONENT_NAME is coreapplication_obis1. |
NUM_CACHE_HITS |
Indicates the number of times that the cache result returned for the query. NUM_CACHE_HITS is a 32-bit integer (or a 10-digit integer). Default is Null. |
NUM_CACHE_INSERTED |
Indicates the number of times that the query generated a cache entry. Default is Null. NUM_CACHE_INSERTED is a 32-bit integer (or a 10-digit integer). |
NUM_DB_QUERY |
The number of queries that were submitted to back-end databases to satisfy the logical query request. For successful queries (SuccessFlag = 0) this number is 1 or greater. |
PRESENTATION_NAME |
Default is Null. Varchar(128) The name of the Oracle BI Presentation Catalog. |
QUERY_BLOB |
Contains the entire logical SQL statement without any truncation. The QUERY_BLOB column is a long character string. |
QUERY_KEY |
Default is Null. Varchar(128). An MD5 hash key that is generated by Oracle Business Intelligence from the logical SQL statement. |
QUERY_SRC_CD |
The source of the request. Values that can be inserted, for example: An analysis, or any export operation inserts 'Report'. Using the 'Value' drop down in a filter dialog, or using a dashboard prompt inserts 'ValuePrompt'. Agent to seed analytics server cache inserts 'Seed'. Online Admin Tool physical table or column row count, or view data inserts 'NULL'. |
QUERY_TEXT |
Varchar(1024). The SQL statement that was submitted for the query. You can change the length of this column (using the ALTER TABLE command), but note that the text that is written into this column is always truncated to the size that is defined in the physical layer. It is the responsibility of the repository administrator not to set the length of this column to a value greater than the maximum query length that is supported by the back-end physical database. For example, Oracle Databases enable a maximum Varchar of 4000, but Oracle Databases truncate to 4000 bytes, not 4000 characters. Hence, if you use a multibyte character set, the actual maximum string size has a varying number of characters, depending on the character set and characters used. |
REPOSITORY_NAME |
The name of the repository that the query accesses. |
ROW_COUNT |
The number of rows that are returned to the query client. Note: When a large amount of data is returned from a query, this column is not populated until the user displays all of the data. |
IMPERSONATOR_USER_NAME |
Default is None. Varchar(128). The user name of the impersonated user. If the request is not run as an impersonated user, then the value is 'None'. |
SAW_DASHBOARD |
The path name of the dashboard. If the query was not submitted through a dashboard, then the value is NULL. |
SAW_DASHBOARD_PG |
Default is Null. Varchar(150) The page name in the dashboard. If the request is not a dashboard request, then the value is NULL. |
SAW_SRC_PATH |
The path name in the Oracle BI Presentation Catalog for the analysis. |
START_DT |
The date that the logical query was submitted. |
START_HOUR_MIN |
The hour and minute that the logical query was submitted. |
START_TS |
The date and time that the logical query was submitted. |
SUBJECT_AREA_NAME |
The name of the business model that is being accessed. |
SUCCESS_FLG |
The completion status of the query, as defined in the following list:
|
TOTAL_TIME_SEC |
The time in seconds that the BI Server spent working on the query while the client waited for responses to its analyses. TOTAL_TIME_SEC includes the time for COMPILE_TIME_SEC. This setting is the same as the Response time in the nqquery.log file, as described in Section 8.4.1.1, "Setting the Query Logging Level." |
USER_NAME |
The name of the user who submitted the query. |
Table 9-2 describes the S_NQ_DB_ACCT
table, which supplements the usage tracking table by providing the physical SQL information for the logical queries stored in S_NQ_ACCT
. S_NQ_DB_ACCT
has a foreign key relationship back to S_NQ_ACCT
.
Table 9-2 Usage Tracking Data in S_NQ_DB_ACCT
Column | Description |
---|---|
END_DT |
The date the physical query was completed. |
END_HOUR_MIN |
The hour and minute the physical query was completed. |
END_TS |
The date and time the physical query finished. The start and end timestamps also reflect any time that the query spent waiting for resources to become available. |
ID |
The unique row ID. |
LOGICAL_QUERY_ID |
Varchar2(50). Refers to the logical query in the S_NQ_ACCT table. |
QUERY_BLOB |
Contains the entire physical SQL statement without any truncation. The QUERY_BLOB column is a long character string. |
QUERY_TEXT |
Varchar(1024). The SQL statement that was submitted for the query. |
ROW_COUNT |
The number of rows that are returned to the query client. |
TIME_SEC |
The physical query execution time. |
START_DT |
The date that the physical query was submitted. |
START_HOUR_MIN |
The hour and minute that the physical query was submitted. |
START_TS |
The date and time that the physical query was submitted. |
Table 9-3 describes each column in the S_NQ_INITBLOCK
usage tracking table, which tracks information about initialization blocks.
Table 9-3 Usage Tracking Data in S_NQ_INITBLOCK
Column | Description |
---|---|
USER_NAME |
Varchar2(128). The name of the user who ran the initialization block. |
REPOSITORY_NAME |
Varchar2(128). The name of the repository that the query accesses. |
TENANT_ID |
Varchar2(128). The name of the tenant of the user who ran the initialization block. |
SERVICE_NAME |
Varchar2(128). The name of the service. |
ECID |
Varchar2(1024). The system-generated execution context ID. |
SESSION_ID |
Number(10). The ID of the session. |
BLOCK_NAME |
Varchar2(128). The name of the initialization block that was executed. |
START_TS |
The date and time that the initialization block started. |
END_TS |
The date and time that the initialization block finished. The start and end timestamps also reflect any time that the query spent waiting for resources to become available. |
DURATION |
Number(13,3). The length of time it took to execute the initialization block. |
NOTES |
Varchar2(1024). Notes about the initialization block and its execution. |