Oracle Analytics Cloud supports the accumulation of Usage Tracking (UT) statistics that helps in monitoring system usage and performance.
These statistics can be used in a variety of ways such as in system or database optimization, aggregation strategies, or internal billing of users/departments based on the resources that they consume. Usage tracking is particularly useful in determining user queries that are creating performance bottlenecks, based on query frequency and response time.
Oracle BI Server tracks usage at a detailed query level. When Usage Tracking is enabled, BI Server collects data records for every query that is executed and writes it all to database tables. Both logical and physical SAL queries are tracked and logged in separate tables, along with various performance measures :
The following video gives a high level overview of how to set up Usage Tracking on your OAC environment. This blog content below gives more details about this capability.
1) Defining a Database connection in RPD: using Admintool client (editor for RPD repository), define a database connection in the physical layer of your RPD. This connection should point to a database where Usage Tracking tables will be created and maintained. This database can be anywhere on the Cloud as long as it is accessible for write accss by OAC.
2) System Settings in OAC : Configure Usage Tracking parameters like connection pool name, physical query table name and logical query table name.
Under this database, create a new Connection Pool with an appropriate name (Eg: UTConnectionPool). Provide the connection details to the database where Usage Tracking is to be configured and login credentials to the schema.
Note:- This database user needs to have create table privileges on the schema.
Next, create a physical schema in the RPD with the same name as the Database schema to be used by UT (Eg:- UT_demo).
Once these definitions are completed, the physical layer should look like this.
Save the RPD and upload it onto OAC using the Replace Data Model option within the Service Console.
Scroll down and modify the following properties.
1) Toggle on the Enable Usage Tracking button
2) Usage Tracking Connection Pool:
Enter the connection pool name in the format : <Database>.<ConnectionPool>.
Eg:- UsageTracking.UTConnectionPool
3) Usage Tracking Physical Query Logging Table:
This the table where details about the physical queries executed by BI Server against the underlying database are tracked.
Enter the name in the format : <Database>.<Schema>.<Table>
Eg:- UsageTracking.UT_demo.PhysicalQueries
4) Usage Tracking Logical Query Logging Table:
This is the table where details about the logical SQLs executed by BI Server are tracked. Enter the name in the format : <Database>.<Schema>.<Table>
Eg:- UsageTracking.UT_demo.LogicalQueries
5) Usage Tracking Max Rows : Specifies the number of rows after which the UT tables will be purged. The default value is 0 which implies there is no purging. You can set it to a any desired value and the records would be purged once the threshold is reached.
After entering these details, restart Oracle BI Server. Once the server is restarted, Usage Tracking is enabled and the two UT tables are created in the database.
To generate some usage tracking data, login to OAC and click around some DV projects: both XSA as well as subject area based. Also open the BI classic home and open few dashboards to generate some queries. Now observe that the tables for logical and physical queries are populated with usage tracking information.
Following is a sample analysis built on the UT tables that shows # of sessions, # of queries, most frequently used subject areas, most frequently used dashboards etc.
These statistics can be used in a variety of ways such as in system or database optimization, aggregation strategies, or internal billing of users/departments based on the resources that they consume. Usage tracking is particularly useful in determining user queries that are creating performance bottlenecks, based on query frequency and response time.
Oracle BI Server tracks usage at a detailed query level. When Usage Tracking is enabled, BI Server collects data records for every query that is executed and writes it all to database tables. Both logical and physical SAL queries are tracked and logged in separate tables, along with various performance measures :
The following video gives a high level overview of how to set up Usage Tracking on your OAC environment. This blog content below gives more details about this capability.
Prerequisites to enable Usage Tracking
Following conditions need to be met in order to enable Usage Tracking on your environment:
1) Usage Tracking requires a full RPD (Metadata Repository) to be enabled. It will track usage on any queries, even outside the RPD, but it only requires an RPD for its configuration. For example, UT cannot be enabled when only a TCM (Thin Client Modeler, ie web based admin-tool) model is active on your OAC.
2) UT requires an access to a Database to write data back into tables. So a user login with Create table privileges on the database schema will be needed during the configuration.
Steps to configure Usage Tracking
UT configuration broadly involves the following two steps:1) Defining a Database connection in RPD: using Admintool client (editor for RPD repository), define a database connection in the physical layer of your RPD. This connection should point to a database where Usage Tracking tables will be created and maintained. This database can be anywhere on the Cloud as long as it is accessible for write accss by OAC.
2) System Settings in OAC : Configure Usage Tracking parameters like connection pool name, physical query table name and logical query table name.
Step 1: Define a database connection in rpd
Open the RPD which is uploaded in OAC and create a new database in the physical layer. Provide an appropriate name (eg: UsageTracking) and choose the database type as Oracle 12c.Under this database, create a new Connection Pool with an appropriate name (Eg: UTConnectionPool). Provide the connection details to the database where Usage Tracking is to be configured and login credentials to the schema.
Note:- This database user needs to have create table privileges on the schema.
Next, create a physical schema in the RPD with the same name as the Database schema to be used by UT (Eg:- UT_demo).
Step 2:System Configuration
On OAC, open the System settings screen from the Console Tab.
Scroll down and modify the following properties.
1) Toggle on the Enable Usage Tracking button
2) Usage Tracking Connection Pool:
Enter the connection pool name in the format : <Database>.<ConnectionPool>.
Eg:- UsageTracking.UTConnectionPool
3) Usage Tracking Physical Query Logging Table:
This the table where details about the physical queries executed by BI Server against the underlying database are tracked.
Enter the name in the format : <Database>.<Schema>.<Table>
Eg:- UsageTracking.UT_demo.PhysicalQueries
4) Usage Tracking Logical Query Logging Table:
This is the table where details about the logical SQLs executed by BI Server are tracked. Enter the name in the format : <Database>.<Schema>.<Table>
Eg:- UsageTracking.UT_demo.LogicalQueries
5) Usage Tracking Max Rows : Specifies the number of rows after which the UT tables will be purged. The default value is 0 which implies there is no purging. You can set it to a any desired value and the records would be purged once the threshold is reached.
After entering these details, restart Oracle BI Server. Once the server is restarted, Usage Tracking is enabled and the two UT tables are created in the database.
UT Tables Explained
Open SQL Developer, login to the UT database and observe that the 2 tables are created.To generate some usage tracking data, login to OAC and click around some DV projects: both XSA as well as subject area based. Also open the BI classic home and open few dashboards to generate some queries. Now observe that the tables for logical and physical queries are populated with usage tracking information.
Important Columns in Logical Queries table
| END_TS | End time of query execution |
| ERROR_TEXT | Error message if the query has errored out |
| ID | Primary key |
| QUERY_TEXT | Actual Query Text |
| RESP_TIME_SEC | Total response time of query in seconds |
| ROW_COUNT | No of rows returned by the query |
| SAW_DASHBOARD | Dashboard name where query is getting generated |
| SESSION_ID | Session ID from the user firing the query |
| START_TS | Start time of the query execution |
| SUBJECT_AREA_NAME | Subject area used for the query |
| USER_NAME | User ID who has executed the query |
Important Columns in Physical Queries table
| ID | Primary Key |
| LOGICAL_QUERY_ID | Foreign Key from the logical queries table |
| QUERY_TEXT | Query Text |
| TIME_SEC | Time taken by query to complete |
| ROW_COUNT | No of rows returned by Query |
| START_TS | Start time of the query |
| END_TS | End time of query |
The join between the 2 tables can be performed using the join condition
LogicalQueries.ID = PhysicalQueries.Logical_Query_ID.
Note that not all logical queries would generate a corresponding physical query. Some of the logical queries may hit cache and return results from cache and not generate a physical query.
LogicalQueries.ID = PhysicalQueries.Logical_Query_ID.
Note that not all logical queries would generate a corresponding physical query. Some of the logical queries may hit cache and return results from cache and not generate a physical query.
Analyzing UT Data
Once UT is enabled, the system usage can be analyzed from DV. In order to do this, create a DV connection to the UT database, create datasets for the PhysicalQueries and LogicalQueries table and analyze them within a DV project.Following is a sample analysis built on the UT tables that shows # of sessions, # of queries, most frequently used subject areas, most frequently used dashboards etc.
Conclusion
Usage Tracking provides a mechanism for administrators to keep track of the usage of the OAC system. These statistics can be leveraged to take decisions to scale up, scale down, restrict access during certain time periods, pause/resume the system and so on.
Are you an Oracle Analytics customer
or user?
We want to hear your story!
Please voice your experience and provide feedback
with a quick product review for Oracle Analytics Cloud!
