Track Usage
Usage tracking enables administrators to track user-level queries to content.
Tracking usage is helpful way to determine which user queries are creating performance bottlenecks, based on query frequency and response time. Administrators set up the criteria to track user queries and generate usage reports 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.
About Tracking Usage
You can configure usage tracking in services that offer enterprise modeling features. Usage information is tracked at the detailed user query level so you can answer questions such as:
- How are users engaging with Oracle Analytics Cloud?
- Where are they spending or not spending their time?
- How long do users spend in each session, between sessions, and between queries?
- How are queries within sessions, across sessions, and across users related to each other?
- Are users drilling up and down in analyses?
- What queries are running when issues are reported?
The usage statistics that you gather can help you to monitor system usage and performance so you can better understand and predict user behavior. You can increase your efficiency and reduce errors if you know in advance how your system is likely to be used.
When you enable usage tracking, the system collects data records for every query that is run and writes them all to database tables. Both logical and physical queries are tracked and logged in separate tables, along with various performance measures such as the time taken to run the query and number of rows searched while processing a user query.
Prerequisites for Usage Tracking
If you want to track usage, verify you meet the following prerequisites:
- You currently use Semantic Modeler or Model Administration Tool to manage your
semantic model.
To configure usage tracking, you must add the usage tracking database details to your semantic model using either Semantic Modeler or Model Administration Tool.
- You have appropriate access permissions on the database where you want to store usage information.
You must have the credentials for a user who has permissions to create the usage tracking tables on the database schema and write usage data to the tables.
- The database supports usage tracking: Oracle Database or Oracle Autonomous Data Warehouse
- You have created a data connection to your usage tracking database with
the following settings. See
Connect to Data.
- System Connection - Select the
System Connection check box.
When you select the System Connection check box, the connection becomes available in Semantic Modeler. Similarly in Model Administration Tool, the System Connection option enables you to select Use Data Connection and enter the connection's Object ID instead of manually entering the connection details in the Data Source Name field. See Specify the Usage Tracking Database.
- User Name and Password - The User Name must match the name of the schema in the database that you want to use for usage tracking. For example, if the schema you want to use is called UT_Schema the User Name must be UT_Schema.
Note:
If you use Model Administration Tool, you can also define database connections for semantic models and the usage tracking database using the Console. See Connect to Data in an Oracle Cloud Database. If you use the Console, you can select Use Console Connection and enter the connection's Name while specifying the usage tracking database in Model Administration Tool, instead of entering the connection details in the Data Source Name field.
- System Connection - Select the
System Connection check box.
- Download the Oracle Autonomous Data Warehouse wallet. See Download Client Credentials (Wallets) in Using Oracle Autonomous Database Serverless.
- Upload the Oracle Autonomous Data Warehouse wallet to Oracle Analytics Cloud. See Secure Database Connections with SSL.
- Create a self-service connection to Oracle Autonomous Data Warehouse and ensure that you select the System Connection check box. See Connect to Oracle Autonomous Data Warehouse.
About the Usage Tracking Database
The system stores usage tracking details in a database that you specify. The database can be an Oracle Database or Oracle Autonomous Data Warehouse. You specify the database and connection pool details in your semantic model using Semantic Modeler or Model Administration Tool.
About Usage Tracking Parameters
After specifying the database where you want to store usage tracking information, you must set various usage tracking parameters through the Console (System Settings page).
Parameters required to configure usage tracking:
- Enable usage tracking
- Connection pool name
- Physical and logical query logging table names
- Maximum number of query rows in the usage tracking tables
- Creates the physical and logical query logging tables in the database specified in the semantic model. The table names are based on the names that you provide in the physical and logical query logging table name parameters.
- Starts to log usage tracking data in these tables.
About Analyzing Usage Data
You can use the system to create useful usage reports from the tracking data added to the physical and logical query logging tables.
You can connect to the database, create a dataset from the tables, and create reports and visualizations to help you understand your users' queries and take appropriate action to improve performance.
Understand the Usage Tracking Tables
The system stores usage tracking data in three database tables.
The usage tracking process creates these tables with table names that you specify through settings in the Systems Settings page.
- Usage Tracking Logical Query Logging Table
- Usage Tracking Physical Query Logging Table
- Usage Tracking Initialization Block Table
Usage Tracking Logical Query Logging Table
TOTAL_TIME_SEC is equal to END_TS minus START_TS. The columns don't provide such exact values because:
- Various processes run in parallel and their speed depends on the load and on database performance. 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 the configuration.
User, Session, and ID-related Columns
| Column | Description |
|---|---|
|
|
In the Logical Query table, this column indicates the unique row identifier. In the
Physical Query table, this column is denoted by the name
|
|
|
Contains
|
|
|
Indicates the name of the Catalog. Default is Null and data type is Varchar(128). |
|
|
Specifies the user name of the impersonated user. If the request isn't run as an impersonated user, then the value is None. Default is None and the data type is Varchar(128). |
|
|
Specifies the name of the user who submitted the query. |
ECID |
Indicates the system-generated execution context ID. Data type is Varchar2(1024). |
TENANT_ID |
Specifies the name of the tenant of the user who ran the initialization block. Data type is Varchar2(128). |
SERVICE_NAME |
Specifies the name of the service. Data type is Varchar2(128). |
SESSION_ID |
Indicates the ID of the session. Data type is Number(10). |
HASH_ID |
Indicates the HASH value for the logical query. Data
type is Varchar2(128).
|
Query Origin-related Columns
| Column | Description |
|---|---|
|
|
The source of the request. Note that the requestor can set QUERY_SRC_CD to any string value to identify itself. Possible values include:
|
|
|
Indicates the path name of the dashboard. If the query wasn't submitted through a dashboard, then the value is NULL. |
|
|
Indicates the page name in the dashboard. If the request isn't a dashboard request, then the value is NULL. Default is Null and the data type is Varchar(150). |
|
|
Specifies the path name in the Catalog for the analysis. |
Query Details-related Columns
| Column | Description |
|---|---|
|
|
Contains the error message from the back-end database. This column is applicable only if the |
|
|
Contains the entire logical SQL statement without any truncation. The |
|
|
Contains an MD5 hash key generated by the system from the logical SQL statement. Default is Null and the data type is Varchar(128). |
|
|
Indicates the SQL statement that was submitted for the query. The data type is Varchar(1024). You can change the length of this column (using the ALTER TABLE command), but note that the text written into this column is always truncated to the size that is defined in the physical layer. The semantic model administrator mustn't set the length of this column to a value greater than the maximum query length that's 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. 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. |
|
|
Specifies the name of the semantic model that the query accesses. |
|
|
Contains the name of the business model being accessed. |
|
|
Indicates the completion status of the query, as defined in the following list:
|
Execution Timing-related Columns
| Column | Description |
|---|---|
|
|
Contains the time in seconds required to compile the query. The number for |
|
|
Indicates the date the logical query completed. |
|
|
Indicates the hour and minute the logical query completed. |
|
|
Indicates the date and time the logical query completed. The start and end timestamps also reflect any time that the query spent waiting for resources to become available. 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, which is recorded as the |
|
|
Indicates the date that the logical query was submitted. |
|
|
Indicates the hour and minute that the logical query was submitted. |
|
|
Indicates the date and time that the logical query was submitted. |
|
|
Indicates the time in seconds that the system spent working on the query while the client waited for responses to its analyses. |
RESP_TIME_SEC |
Indicates the time taken for query response. Data type is Number(10). |
Execution Details-related Columns
| Column | Description |
|---|---|
|
|
Contains the cumulative time of all queries sent to the database. Queries run in parallel, so the cumulative query time is equal to or greater than the total time connected to the database. For example, suppose a logical request spawns 4 physical SQL statements sent to the database, and the query time for 3 of the queries is 10 seconds, and for one query is 15 seconds, |
|
|
Contains the total number of rows returned by the back-end databases. |
|
|
Indicates the number of queries that were submitted to the back-end databases to satisfy the logical query request. For successful queries (SuccessFlag = 0), this number is 1 or greater. |
|
|
Indicates the number of rows returned to the query client. When a large amount of data is returned from a query, this column isn't populated until the user displays all the data. |
TOTAL_TEMP_KB |
Specifies the total KB received for a query. Data type is Number(10). |
Cache-related Columns
| Column | Description |
|---|---|
|
|
Holds Y to indicate a cache hit for the query; N to indicate a cache miss. Default is N. |
|
|
Indicates the number of times that the cache result returned for the query. |
|
|
Indicates the number of times that the query generated a cache entry. Default is Null. |
Usage Tracking Physical Query Logging Table
The following table describes the database table that tracks physical queries. This database table records the physical SQL information for the logical queries stored in the logical query logging table. The physical query table has a foreign key relationship to the logical query table.
User, Session, and ID-related Columns
| Column | Description |
|---|---|
|
|
Specifies the unique row identifier. |
|
|
Refers to the logical query in the logical query logging table. Data type is Varchar2(50). |
HASH_ID |
Indicates the HASH value for the logical query. Data type is Varchar2(128).
|
PHYSICAL_HASH_ID |
Indicates the HASH value for the physical query. Data type is Varchar2(128).
|
Query Details-related Columns
| Column | Description |
|---|---|
|
|
Contains the entire physical SQL statement without any truncation. The |
|
|
Contains the SQL statement submitted for the query. Data type is Varchar(1024). |
Execution Timing-related Columns
| Column | Description |
|---|---|
|
|
Indicates the date the physical query completed. |
|
|
Indicates the hour and minute the physical query completed. |
|
|
Indicates the date and time the physical query completed. The start and end timestamps also reflect any time that the query spent waiting for resources to become available. |
|
|
Indicates the physical query execution time. |
|
|
Indicates the date the physical query was submitted. |
|
|
Indicates the hour and minute the physical query was submitted. |
|
|
Indicates the date and time the physical query was submitted. |
Execution Details-related Columns
| Column | Description |
|---|---|
|
|
Contains the number of rows returned to the query client. |
Usage Tracking Initialization Block Table
Note:
Currently the initialization block usage tracking tables include only session initialization blocks and don't include the semantic model initialization blocks.User, Session, and ID-related Columns
| Column | Description |
|---|---|
USER_NAME |
The name of the user who ran the initialization block. The data type is Varchar2(128). |
TENANT_ID |
The name of the tenant of the user who ran the initialization block. The data type is Varchar2(128). |
SERVICE_NAME |
The name of the service. The data type is Varchar2(128). |
ECID |
The system-generated execution context ID. The data type is Varchar2(1024). |
SESSION_ID |
The ID of the session. The data type is Number(10). |
Query Details-related Columns
| Column | Description |
|---|---|
REPOSITORY_NAME |
The name of the semantic model that the query accesses. The data type is Varchar2(128). |
BLOCK_NAME |
The name of the initialization block that was run. The data type is Varchar2(128). |
Execution Timing-related Columns
| Column | Description |
|---|---|
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 the time that the query spent waiting for resources to become available. |
DURATION |
The length of time it took to run the initialization block. The data type is Number(13,3). |
Execution Details-related Columns
| Column | Description |
|---|---|
NOTES |
Notes about the initialization block and its running. The data type is Varchar2(1024). |
Typical Workflow for Tracking Usage
Here are the tasks to track the user-level queries to Oracle Analytics Cloud.
| Task | Description | More Information |
|---|---|---|
|
Decide where to store your usage tracking data |
Understand which database types you can use for usage tracking. |
|
|
Set up a connection to the usage tracking database |
Create a data connection (or a Console connection) to the database where you want to store usage tracking information. |
|
|
Specify the usage tracking database |
Define the usage tracking database in your semantic model. |
|
|
Specify usage tracking parameters |
Enable usage tracking for your system, and then specify connection details and table names for the usage tracking database. |
|
|
Analyze the usage tracking data |
Create usage reports from usage tracking data. |
Specify the Usage Tracking Database
Before you can track usage of reports, dashboards, and data visualization workbooks on your system, you must specify the database where you want to store the usage tracking data in your semantic model.
The database you specify must have at least one schema defined. The system creates usage tracking tables in the schema whose name matches the user name you specify in the database connection details. For example, if the name of a schema in the usage tracking database is “UT_Schema”, you must specify "UT_Schema" in the User Name field for the connection. The usage tracking-tables are created in the schema named “UT_Schema”.
You must configure the database and connection pool details in the physical layer of your semantic model. Use Semantic Modeler or the Model Administration Tool to configure the usage tracking database.
- Specify the Usage Tracking Database Using Semantic Modeler
- Specify the Usage Tracking Database Using Model Administration Tool
If you want to use Oracle Autonomous Data Warehouse as the usage tracking database, you must complete some additional Oracle Autonomous Data Warehouse-related tasks before you specify the usage tracking database. See Prerequisites for Usage Tracking.
Specify the Usage Tracking Database Using Semantic Modeler
Use Semantic Modeler to configure your usage tracking database if you currently use Semantic Modeler to develop semantic models.
Specify the Usage Tracking Database Using Model Administration Tool
Use Model Administration Tool to configure your usage tracking database if you currently use Model Administration Tool to develop semantic models.
- Specify the usage tracking database:
- In the Physical layer of the semantic model, right-click and select New Database.
- In the Database dialog, provide a name for the database of your semantic model; for example SQLDB_UsageTracking, specify the database type, for example Oracle 12c, and click OK.
- Right-click the newly created database, select New Object, and then select Connection Pool.
- In the Connection Pool dialog, enter connection pool details and specify values
for:
- Call interface: Select Default (Oracle Call Interface (OCI)).
- Require fully qualified table names: Ensure that this check box isn't selected.
- Data Source Name**: Specify
the data source to which you want this connection pool to
connect and send physical queries. For
example:
(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = <DB Host>)(PORT = <DB port>))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = <Servicename>))) - User name and Password: Enter a user name that matches the name of a schema available in the usage tracking database.
**As an alternative to providing the Data Source Name, you can refer to an existing database connection “by name” in the Connection Pool dialog.
- Data connections - To use the connection details for a database defined through the Data tab as your usage tracking database, select Use Data Connection and enter the connection's Object ID instead of manually entering the connection details in the Data Source Name field. Ensure that the data connection you want to use was created with the System Connection option selected. See Connect to a Data Source Using a Data Connection.
- Console connections - If you use Model Administration Tool, you might define database connections for semantic models using the Console. To use the connection details for a database that you defined through the Console as your usage tracking database, select the Use Console Connection check box and enter the name of the database connection in the Connection Name field. See Connect to a Data Source Using a Console Connection.
For example:
Description of the illustration model_admin_tool_connection_pool.gif
Set Usage Tracking Parameters
To start recording usage information, you must specify connection details for the database you want to use and names for the database tables used to track usage. You set these parameters through the Console (System Settings page).
Oracle Analytics creates the usage tracking tables and starts to log user queries.
Analyze Usage Tracking Data
Create usage reports to understand the user queries and take appropriate action.
Analyze Usage Tracking Data by Creating a Dataset
Create usage reports by creating datasets with data from the physical and logical query logging tables to understand the user queries.
Analyze Usage Tracking Data Using a Subject Area in the Semantic Model
Create usage reports using a subject area in the semantic model to understand the user queries.