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 executed 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 execute 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 Oracle Analytics Developer Client Tool to manage your data model (RPD file).

    To configure usage tracking, you must add the usage tracking database details to your data model file using Oracle Analytics Developer Client Tool. If Data Modeler is your preferred modeling tool, you can't enable usage tracking. For usage tracking you must use Oracle Analytics Developer Client 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.

About the Usage Tracking Database

The system stores usage tracking details in a database that you specify. The database can be Oracle Database Classic Cloud Service or Oracle Autonomous Data Warehouse. You specify the database and connection pool details in your data model file using the Administration Tool.

See Specify the Usage Tracking Database.

About Usage Tracking Parameters

After specifying the database where you want to store usage tracking information, you must set various parameters, required for usage tracking, through the Console.

Parameters that configure usage tracking are:

  • Connection pool name
  • Physical and logical query logging table names
  • Maximum number of query rows in the usage tracking tables
Administrators must restart the system after configuring usage tracking parameters. The system:
  • Creates the physical and logical query logging tables in the database specified in the data model file. It names the tables based on the table name that you provide in the physical and logical query logging table names parameters.
  • Inserts usage tracking data in these tables.

See Set Usage Tracking Parameters.

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 data set 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

See Set Usage Tracking Parameters.

Usage Tracking Logical Query Logging Table

The following table describes each column in the database table that tracks logical queries. Where appropriate, the data type such as variable character field (varchar and varchar2) and length is specified. As you review the descriptions in this table, you might assume that certain 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 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

ID

Indicates the unique row identifier.

NODE_ID

Contains <hostname>:<component_name> where <component_name> can be overridden by the environment variable COMPONENT_NAME. For example, examplehost:obis1 (for a single instance). Default value of COMPONENT_NAME is obis1.

PRESENTATION_NAME

Indicates the name of the Catalog. Default is Null and data type is Varchar(128).

IMPERSONATOR_USER_NAME

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).

USER_NAME

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

QUERY_SRC_CD

The source of the request.

Note that the requestor can set QUERY_SRC_CD to any string value to identify itself.

Possible values include:
  • Report - If the source is an analysis or any export operation.
  • Drill - If the source is a change in dimension caused by drilling up or down.
  • ValuePrompt - If the source is the Value drop-down list in a filter dialog or a dashboard prompt.
  • VisualAnalyzer - If the source is a project to visualize data.
  • DisplayValueMap or MemberBrowserDisplayValues or MemberBrowserPath - If the source is a value related to the display of an analysis.
  • SOAP - If the source is a call from web services such as DataSetSvc.
  • Seed - If the source is an agent that seeds the cache of the analytics server.
  • Null - If the source is the Admininistration Tool physical table or column row count, or view data.

SAW_DASHBOARD

Indicates the path name of the dashboard. If the query wasn't submitted through a dashboard, then the value is NULL.

SAW_DASHBOARD_PG

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).

SAW_SRC_PATH

Specifies the path name in the Catalog for the analysis.

Query Details-related Columns

Column Description

ERROR_TEXT

Contains the error message from the back-end database. This column is applicable only if the SUCCESS_FLAG is set to a value other than 0 (zero). Multiple messages are concatenated and aren't parsed by the system. Default is Null and data type is Varchar(250).

QUERY_BLOB

Contains the entire logical SQL statement without any truncation. The QUERY_BLOB column is a character string of type Long.

QUERY_KEY

Contains an MD5 hash key generated by the system from the logical SQL statement. Default is Null and the data type is Varchar(128).

QUERY_TEXT

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 repository 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.

REPOSITORY_NAME

Specifies the name of the repository that the query accesses.

SUBJECT_AREA_NAME

Contains the name of the business model being accessed.

SUCCESS_FLG

Indicates the completion status of the query, as defined in the following list:

  • 0 - The query completed successfully with no errors.
  • 1 - The query timed out.
  • 2 - The query failed because row limits were exceeded.
  • 3 - The query failed due to some other reason.

Execution Timing-related Columns

Column Description

COMPILE_TIME_SEC

Contains the time in seconds required to compile the query. The number for COMPILE_TIME_SEC is included in TOTAL_TIME_SEC.

END_DT

Indicates the date the logical query completed.

END_HOUR_MIN

Indicates the hour and minute the logical query completed.

END_TS

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 end_ts time.

START_DT

Indicates the date that the logical query was submitted.

START_HOUR_MIN

Indicates the hour and minute that the logical query was submitted.

START_TS

Indicates the date and time that the logical query was submitted.

TOTAL_TIME_SEC

Indicates the time in seconds that the system spent working on the query while the client waited for responses to its analyses. TOTAL_TIME_SEC includes the time for COMPILE_TIME_SEC.

RESP_TIME_SEC Indicates the time taken for query response. Data type is Number(10).

Execution Details-related Columns

Column Description

CUM_DB_TIME_SEC

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, CUM_DB_TIME_SEC displays 45 seconds because the queries run in parallel.

CUM_NUM_DB_ROW

Contains the total number of rows returned by the back-end databases.

NUM_DB_QUERY

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.

ROW_COUNT

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

CACHE_IND_FLG

Holds Y to indicate a cache hit for the query; N to indicate a cache miss. Default is N.

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).

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

ID

Specifies the unique row identifier.

LOGICAL_QUERY_ID

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

QUERY_BLOB

Contains the entire physical SQL statement without any truncation. The QUERY_BLOB column is a character string of type long.

QUERY_TEXT

Contains the SQL statement submitted for the query. Data type is Varchar(1024).

Execution Timing-related Columns

Column Description

END_DT

Indicates the date the physical query completed.

END_HOUR_MIN

Indicates the hour and minute the physical query completed.

END_TS

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.

TIME_SEC

Indicates the physical query execution time.

START_DT

Indicates the date the physical query was submitted.

START_HOUR_MIN

Indicates the hour and minute the physical query was submitted.

START_TS

Indicates the date and time the physical query was submitted.

Execution Details-related Columns

Column Description

ROW_COUNT

Contains the number of rows returned to the query client.

Usage Tracking Initialization Block Table

The following table describes the database table that tracks information about the 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 repository that the query accesses. The data type is Varchar2(128).
BLOCK_NAME The name of the initialization block that was executed. 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 execute the initialization block. The data type is Number(13,3).

Execution Details-related Columns

Column Description
NOTES Notes about the initialization block and its execution. 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

Specify the usage tracking statistics database

Specify a database to store the usage tracking statistics.

Specify the Usage Tracking Database

Specify the usage tracking parameters

Specify the connection details and table names of the usage tracking statistics database.

Set Usage Tracking Parameters

Analyze the usage tracking data

Create usage reports using the usage tracking data.

Analyze Usage Tracking Data

Specify the Usage Tracking Database

Before you can track usage of reports, dashboards, and data visualization projects on your system, you must specify the database where you want to store the usage tracking statistics.

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 connection pool details. For example, if the name of a schema in the usage tracking database is “ANALYTICS_USAGE”, you must specify "ANALYTICS_USAGE" in the User Name field for the connection pool. The usage tracking-tables are created in the schema named “ANALYTICS_USAGE”.

You must configure the database and connection pool details in the physical layer of your data model file (RPD). Use the Oracle Analytics Developer Client Tool to edit the data model file.

  1. In Oracle Analytics Developer Client Tool, open the data model file in the cloud.
    From the File menu, select Open, and then In the Cloud. Enter connection information for your instance.
  2. Specify the usage tracking database:
    1. In the Physical layer of the data model file, right-click and select New Database.
    2. In the Database dialog, provide a name for your database, specify the database type, for example Oracle 12c, and click OK.
    3. Right-click the newly created database, select New Object, and then select Connection Pool.
    4. In the Connection Pool dialog, enter a name for the connection pool 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>)))

        As an alternative to providing the data source name, select the Externalize connection check box and enter the name of the database connection that you defined for the data model. You can define database connections for data models using the Console. If you want to use the same database connections in the Oracle Analytics Developer Client Tool, then you don’t have to re-enter the connection details. Instead, refer to the database connections “by name” in the Connection Pool dialog. See Connect to Data in an Oracle Cloud Database and Connect to a Data Source with an External Connection.

      • User name and Password: Enter a user name that matches the name of a schema available in the usage tracking database.
  3. Validate your changes by clicking Tools, Show Consistency Checker, and then Check All Objects.
  4. Optionally, save changes locally by clicking File, and then Save.
  5. Upload the data model file that you edited, to your instance by clicking File, Cloud, and then Publish.

Set Usage Tracking Parameters

To start recording usage information, you 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.

  1. Sign in to your service.
  2. Click Console.
  3. Click System Settings.
  4. Click Usage Tracking.
  5. Set the following properties:
    • Usage Tracking Connection Pool

      Name of the connection pool that you created for your usage tracking database in the format, <database name>.<connection pool name>. For example, UsageTracking.UTConnectionPool.

    • Usage Tracking Initialization Block Table

      Name of the database table you want to use to store information about initialization blocks in the format, <database name>.<catalog name>.<schema name>.<table name> or <database name>.<schema name>.<table name>. For example, UsageTracking.Analytics_Usage.InitBlockInfo.

    • Usage Tracking Physical Query Logging Table

      Name of the database table you want to use to store physical query details in the format, <database name>.<catalog name>.<schema name>.<table name> or <database name>.<schema name>.<table name>. For example, UsageTracking.Analytics_Usage.PhysicalQueries.

    • Usage Tracking Logical Query Logging Table

      Name of the database table you want to use to store logical query details in the format, <database name>.<catalog name>.<schema name>.<table name> or <database name>.<schema name>.<table name>. For example, UsageTracking.Analytics_Usage.LogicalQueries.

    • Usage Tracking Max Rows

      Maximum number of rows that you want in the usage tracking tables.

  6. Click Restart.

Oracle 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.

  1. On the Home page, click the Page Menu and select Open Classic Home. Create and run an analysis.
    The system populates the query in the usage tracking tables in the usage tracking database.
  2. Create a connection to the usage tracking database.
  3. On the Home page, click Create, and click Data Set.
  4. In Create Data Set, click the connection to the usage tracking statistics database, and select the schema specified in the Physical Query and Logical Query Logging table names in System Settings. For example, schema name provided in <database name>.<schema name >.<table name> for the Physical Query and Logical Query Logging table names.
  5. In Add Data Set, search for the usage tracking physical query logging table, add all the columns, name the data set (for example, Physical Queries), and then click Add. Similarly, search for the usage tracking logical query logging table, add all the columns, name the data set (for example, Logical Queries), and then click Add.
  6. On the data set Results page, click Create Project. Add both the data sets to the project: for example, the Physical Queries and Logical Queries data sets. Name the project (for example, Usage Tracking).
  7. In the Prepare tab of the project, click Data Diagram, and create joins between the data sets using a column such as the ID column.
  8. In Visualize, drag data to create visualizations based on your requirement.
    Refer to the usage tracking table descriptions in "Understand Usage Tracking Tables" to select applicable columns. For example, you can create a visualization to show how many queries took how much time.