Before You Begin
This 15-minute tutorial shows you how to analyze the usage tracking data stored in your usage tracking database.
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.
- Set Up Usage Tracking in Oracle Analytics
- Analyze Usage Tracking Data in Oracle Analytics
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
Create a Project
Create a project using a dataset to enable Oracle Analytics to start storing usage queries in the usage tracking database.
- Sign in to Oracle Analytics with your administrator credentials.
- 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.
- In Add Data Set, enter
Sample_Revenuein Name, and then click Add. Wait for the loading data to appear in the Prepare page.
- Click back to return to the Home page.
- On the Home page, click Create, and then click Project.
- In Add Data Set, select Sample_Revenue, and then click Add to Project.
- Add the following columns to the project by dragging and dropping them from the Data panel onto the canvas:
- Click Save to display the Save Project dialog.
- In Name, enter
UT Analysis, and then click Save.
Create a Connection to Your Usage Tracking Database
Create a connection to the database that you specified for storing the usage tracking data.
- On the Home page, click Create, then click Connection.
- In Create Connection, click Oracle Database.
- Enter the database connection details and then click Save:
- 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.
Create 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.
- On the Home page, click Create, and then click Data Set.
- In Create Data Set, click UT Database.
- 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.
- In Add Data Set, in Search, enter
PhysicalQueries. After the table data is displayed, click Add All, click Get Preview Data, enter
UT Physical Queriesin Name, and then click Add.
- Click back to return to the Home page, click Create, and click Data Set.
- In Create Data Set, click UT Database, and select your schema.
- In Add Data Set, in Search, enter
LogicalQueries. After the table data is displayed, click Add All, click Get Preview Data, enter
UT Logical Queriesin Name, and then click Add.
- On the UT Logical Queries page, click Create Project.
- On the Untitled project page, click Add , and select Add Data Set.
- In Add Data Set, select UT Physical Queries, and then click Add to Project.
- 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
- In the visualization, click Change Visualization Type , select Bar, and then click Save.
- In Save Project, enter
My UT Analysisin Name, select My Folders, and then click Save.