Oracle Cloud Infrastructure Documentation

Data Analysis in Google Sheets

Selecting Data Analysis opens an Oracle Autonomous Database wizard in the Google sheet.

The add-on enables you to receive a copy of data from the Autonomous Database to the Google sheet. You can query an existing Analytic View and run SQL Query using the Oracle Autonomous Database Wizard.

You can retrieve the Analytic View and manipulate the query according to your requirements to visualize the result data in the worksheet. You can search for the Analytic View and select measures, hierarchies, and levels from the query. You can also add filters and calculated measures to the query and view the result in the sheet. By default, the data is retrieved in tabular format.

You can run custom queries. The add-on enables you to apply a filter to the query results. The add-on lets you to view query results that can be customized with selected columns using a faceted filter.

To run a custom query using the add-on:
  1. On the Google Sheet, select the menu item Oracle Autonomous Database.
  2. Select Data Analysis. Selecting Data Analysis opens a Data Analysis wizard. On the Data Analysis wizard, select Query from the drop-down and the schema you want to use from the drop-down.
    Description of query-data.png follows

  3. You will view the default query in the query editor area. You can select any of the four modes to visualize the results of the SQL query report you generate:
    Description of three-modes.png follows

    • Base Query: This type of view is by default. The query written in the SQL editor is the Base Query.

    • Table: You can view the SQL results in tabular form. By selecting this view, a column drop zone appears, enabling you to drag and drop selected columns from the Table browser. Moving the selected columns in the drop zone allows you to view only those columns in the Result data generated in the worksheet. Select the cross mark beside the Column name to remove it from the drop zone.
    • Pivot: You can view the SQL query results in pivot format. By selecting this format, an X and Y drop zone appears where you can drag and drop the selected columns from the Tables browser to the drop area.
    • Chart: You can view Area Chart, Bar Chart, Line Chart, or Pie Chart when you select this option.. The mappings displayed when you select one of the options are as follows:
      • Orientation: Choose between horizontal and vertical orientation types from the drop-down list.
      • X axis label and Y axis label: Optionally enter labels for X axis and Y axis.
  4. Click the funnel icon (Faceted Filter) to add filters to the result. The wizard generates a filter for each value in the column retrieved from the query result. You can filter different columns on the faceted filter panel and view the results in the worksheet to view only the data you wish to view. For example, to view the customer reports by Gender, click the faceted filter and select Male under Gender.


    Description of filter-query.png follows

    Use the Visual facet: Use the visual indicator to graphically represent the faceted filter.


    Description of visual-facet.png follows

  5. Select Run to generate the results of the custom query in the worksheet.
    Description of custom-query-results.png follows

To query an Analytic View and explore the Data Analysis menu in the Google Sheets:
  1. Select the menu item Oracle Autonomous Database > Data Analysis on the Google Sheet. This opens a Data Analysis wizard in the Google task pane.
  2. Select AV from the AV or Query drop-down, select a schema you can access from the schema drop-down, and the AV from the available Analytic Views.
    Description of av-browser1.png follows

  3. You can select any of the three modes to visualize the results of the AV query you generate:
    • Table: You can view the AV query results in tabular form. By selecting this view, a column drop zone appears, enabling you to drag and drop selected columns from the Table browser. Moving the selected columns in the drop zone allows you to view only those columns in the Result data generated in the worksheet. Select the cross mark beside the Column name to remove it from the drop zone.
    • Pivot: You can view the SQL query results in pivot format. By selecting this format, an X and Y drop zone appears where you can drag and drop the selected columns from the Tables browser to the drop area.
    • Chart: You can view the results of the AV query in chart format. By selecting this format, an X and Y drop zone appears where you can drag and drop the chosen hierarchies and measures from the AV browser to the drop area.
      Note

      You are allowed to drop measures in the Y-axis.

  4. Click the funnel icon to view the Faceted and Visual Filter list. The wizard generates a filter for each value in the column retrieved from the query result. You can filter different columns on the faceted filter panel and view the results in the worksheet to view only the data you wish to view.

  5. Click Save.
  6. Click Run to view the results in the worksheet you select.
    Description of av-result.png follows