Oracle by Example brandingAnalyze Usage Tracking Data in Oracle Analytics

section 0Before You Begin

This 15-minute tutorial shows you how to analyze the usage tracking data stored in your usage tracking database.

Background

When you create or view existing content such as an analysis, a data visualization project, or a report from your dashboard, Oracle Analytics populates the query in the usage tracking tables in the usage tracking database. In this tutorial, you connect to your usage tracking database and create usage reports to understand the user queries in Oracle Analytics.

This is the second tutorial in Track Usage in Oracle Analytics. Read the tutorials in the order listed.

What Do You Need?

  • Access to Oracle Analytics
  • Access to Oracle Database Cloud Service instance and these connection details:
    • Host name
    • Port number
    • Database service name
    • User name and password that match the name of a schema available in the database
  • Download the sample_revenue_denorm.xlsx to your computer

section 1Create a Project

Create a project using a dataset to enable Oracle Analytics to start storing usage queries in the usage tracking database.

  1. Sign in to Oracle Analytics with your administrator credentials.
  2. On the Home page, click Create, and then click Data Set. In Create Data Set, click Drop data file here or click to browse, select the sample_revenue_denorm.xlsx file, and then click Open.
  3. In Add Data Set, enter Sample_Revenue in Name, and then click Add. Wait for the loading data to appear in the Prepare page.
  4. Click back Back icon to return to the Home page.
  5. On the Home page, click Create, and then click Project.
  6. In Add Data Set, select Sample_Revenue, and then click Add to Project.
  7. Add the following columns to the project by dragging and dropping them from the Data panel onto the canvas:
    • Company
    • Department
    • PROD_NAME
    • PROD_TYPE
    • PROD_NUMBER
    • ORDER_NUMBER
    • ORDER_STATUS
    • REVENUE
  8. Click Save to display the Save Project dialog.
  9. In Name, enter UT Analysis, and then click Save.

section 2Create a Connection to Your Usage Tracking Database

Create a connection to the database that you specified for storing the usage tracking data.

  1. On the Home page, click Create, then click Connection.
  2. In Create Connection, click Oracle Database.
  3. Enter the database connection details and then click Save:
    • Name: UT Database
    • Host: Address of your database server. For example, demo.example.com
    • Port: Port number on which the database server is listening for incoming connections.
    • User Name: User name that you specified in the connection pool.
    • Password: Password for the user name specified in the connection pool.
    • Service Name: Network service name of your database.
    • Description of createdbconnection.png follows
      Description of the illustration createdbconnection.png
  4. Click Save.

section 3Create a Usage Report

Use the usage tracking data stored in the usage tracking database to create usage reports. In this tutorial, create a report displaying the number of queries required for the logical query request, the SQL statement that was submitted for the query, and the name of the user who submitted the query.

When you create the data sets for usage tracking, search for the physical and logical query table names you specified in system settings. You can then use these tables to create usage visualizations and reports.

  1. On the Home page, click Create, and then click Data Set.
  2. In Create Data Set, click UT Database.
    Description of createds.png follows
    Description of the illustration createds.png
  3. In Add Data Set, in UT Database, select the schema that you 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.

    Description of selectschema.png follows
    Description of the illustration selectschema.png

  4. In Add Data Set, in Search, enter PhysicalQueries. After the table data is displayed, click Add All, click Get Preview Data, enter UT Physical Queries in Name, and then click Add.
  5. Click back Back icon to return to the Home page, click Create, and click Data Set.
  6. In Create Data Set, click UT Database, and select your schema.
  7. In Add Data Set, in Search, enter LogicalQueries. After the table data is displayed, click Add All, click Get Preview Data, enter UT Logical Queries in Name, and then click Add.
  8. On the UT Logical Queries page, click Create Project.
  9. On the Untitled project page, click Add Add icon, and select Add Data Set.
  10. In Add Data Set, select UT Physical Queries, and then click Add to Project.
    Description of addphysicalqueriesds.png follows
    Description of the illustration addphysicalqueriesds.png
  11. In Visualize, drag the following data:
    • USER_NAME and NUM_DB_QUERY from the UT Logical Queries data set
    • QUERY_TEXT from the UT Physical Queries data set
  12. In the visualization, click Change Visualization Type Change Visualization Type icon, select Bar, and then click Save.
  13. In Save Project, enter My UT Analysis in Name, select My Folders, and then click Save.
    Description of utdataanalysis.png follows
    Description of the illustration utdataanalysis.png

more informationWant to Learn More?