The Data Analysis Tool

The Data Analysis tool enables you to create Analytic Views with multidimensional metadata.

You create Analytic Views on top of a fact table with several dimensions and hierarchies. Analytic views refer to tables in the database and allow users to create hierarchies for dimensions. You can also create Analyses and reports using information from the Analytic Views. The Data Analysis homepage enables you to search for Analyses, view and perform tasks such as edit, delete, view or rename Analyses. You can also analyze, find errors, export, edit, compile and delete Analytic Views. You can analyze tables and generate SQL reports from them.

Select the Data Analysis card from the Data Studio suite to access this tool. You can also access it by clicking the Selector icon and selecting Data Analysis from the Data Tools menu in the navigation pane.

Note:

If you do not see the Data Analysis card then your database user is missing the required DWROLE role.

The Data Analysis home page consists of three parts: Analyses, Analytic Views and Tables.
Description of dataanalysis-homepage.png follows
Description of the illustration dataanalysis-homepage.png

Analyses

Description of adp-analyses.png follows
Description of the illustration adp-analyses.png

The top section of the homepage comprises of a list of Analyses. Use the search field to search for Analyses you create. The top section of the homepage comprises of a list of Analyses. Use the search field to search for Analyses you create.

Analyses are analysis of multiple Analytic Views. The Analyses card displays the name of the analysis. Click Actions (three vertical dots) to open the context menu.

The actions available are:
  • View: Opens the Analysis View page in a new window where you can view the analysis.
  • Edit: Opens the selected Analysis page where you can edit the reports present in the analysis.
  • Rename: Allows you to rename the Analysis. Click save to modify the new name.
  • Delete: Opens the delete Analysis dialog where you can delete the analysis.

Analytic Views

Description of adp-analytic-views.png follows
Description of the illustration adp-analytic-views.png
The bottom section of the homepage displays list of existing Analytic Views. Each Analytic View card displays the name of the Analytic View. The Actions icon enables you to manage the Analytic View. Click Actions (three vertical dots) to open the context menu. The actions available are:
  • Analyze: Opens the Analytic View browser and the Analysis View page in a new window where you can view the Analysis.
  • Data Quality: Opens the Data Quality page where the tool validates the selected Analytic View for errors and lists them out.
  • Export: Allows you to export the Analytic View to Tableau and PowerBI.
  • Edit Analytic View: Opens the Edit Analytic View dialog box where you can edit the properties of the selected Analytic View.
  • Compile Analytic View: This option compiles the Analytic View and returns compilation errors if there are any.
  • Show DDL: Displays the DDL statements for the Analytic View.
  • Delete Analytic View: Deletes the selected Analytic View.

The +Create button enables you to create Analysis and create Analytic View from the home page.

You can select both hierarchies and measures from Analytic Views. Hierarchies are DB objects that allow users to define relationships between various levels or generations of dimension members. As the name implies, hierarchies organize data using hierarchical relationships. With this tool you can analyze and visualize data in different Points of View (POV). You can export the metadata and visualize it with sophisticated tools like Oracle Analytics Cloud (OAC) and Tableau.

Advantages of Data Analysis tool

With Data Analysis tool you can:
  • Visualize, analyze and inspect your data clearly and efficiently with pivot tables
  • Calculate total number of errors present in the Analytic View you create and provide solutions to minimize the errors
  • Automatically display meaningful insights to help you make better decisions
  • Analyze your data across dimensions with support for hierarchical aggregation and drill-down
  • Share your Analytic Views with the tool of your choice over various options of raw data consumption to draw meaningful insights and make them accessible to any user

By identifying relationships among tables and columns, Analytic Views enable your system to optimize queries. They also open new avenues for analyzing data. These avenues include data insights, improved hierarchy navigation, and the addition of hierarchy-aware calculations.

This tool runs complex and hierarchical SQL queries along with SQL extensions in the background, which simplifies real-time calculations. It makes complex data more accessible and easier to understand.

The Data Analysis Page

The following section describes searching and obtaining information about Analytic Views, creating Analytic Views, inspecting your data, discovering insights and visualizing data using tools like Oracle Analytics Cloud (OAC), Tableau, and Microsoft Power BI.

Note:

  • OAC has in-built tools to search and utilize Analytic Views.
  • We have no direct support for Microsoft Power BI, yet its users can map their tool to the AV transparency views to avail some of the benefits of Analytic Views.

Searching and obtaining information about Analytic Views

When you first open the Data Analysis page, it displays the list of schemas and Analytic Views. With Select Schema, you can select a preferred Schema from a list of schemas available in the drop-down.

The Select Analytic Views drop-down enables you to select an available Analytic View associated with the schema. When you create an Analytic View, it appears in the drop-down option with your schema. The Refresh AV icon refreshes the contents of the selected Analytic View.

The Action icon next to the Refresh AV button enables you to manage Analytic Views. You can Create Analytic View, Edit Analytic View, Compile Analytic View, Show the Data Definition Language (DDL) that generates the Analytic View or Delete Analytic View from the menu.

Obtain information about Analytic Views

By default, Analytic Views are filtered by the current user's schema, as indicated by the schema list below the menu-bar. You can remove the selected schema filter by selecting another user's schema. To search for Analytic Views in other schemas, select one of the schemas from the drop-down.

If there is no Analytic View associated with the schema selected, the tool prompts you to create an Analytic View.



Creating Analytic Views

You can create Analytic Views and view information about them. You can also edit and perform other actions on them.



When you create an Analytic View, you identify a fact table that contains the data to inspect. The Generate Hierarchies and Measures button looks at the contents of that table, identifies any hierarchies in the fact table, and searches for other tables that may contain related hierarchies.

While creating an Analytic View, you can enable or disable the following advanced options:
  • Autonomous Aggregate Cache, which uses the dimensional metadata of the Analytic View to manage a cache and that improves query response time.
  • Analytic View Transparency Views, which presents Analytic Views as regular database views and enables you to use your analytic tools of choice while gaining the benefits of Analytic Views.
  • Analytic View Base Table Query Transformation, which enables you to use your existing reports and tools without requiring changes to them.

Create Analytic View

To create Analytic View, click Create from the Data Analysis home page and select Create Analytic View to begin the process.

Click Cancel to cancel the creation of the Analytic View at any time.

Specify Attributes of the Analytic View

On the General tab of the Create Analytic View pane, specify the following:
  • The name for the Analytic View
  • The fact table for the view
  • Advanced options

You can also preview the data of the fact table and see statistics about that data.

In the Name field, specify a name of your choice.

The Schema field has the current user's schema. You can only create an Analytic View in that schema.

In the Fact Table field, expand the drop-down list and click More Sources. The Select Sources dialog box has a list of the available tables and views. Select a table or view from the list.

To filter the list, begin typing characters in the Filter field. As you type, the list changes to show the tables or views that contain the characters. Clear the field to show the complete list again. After you select a table or view, click OK.

To enable or disable the advanced options, on the Create Analytic View pane, click the Show Advanced Options icon at the bottom left. Select or deselect options as desired.

To view the data in the fact table and statistics about the data, click the Preview Data button. In the Preview and Statistics pane, the Preview tab shows the columns of the table and the data in the columns.

The Statistics tab shows the size of the table and the number of rows and columns. The statistics may take a few moments to appear, during which time the message, "No statistics available..." may appear. The statistics include the names of the columns, their data types, the number of distinct values and null values, the maximum and minimum values, and other information. The bar graph displays the top unique column values and the number of their occurrences for the selected column. Point to a bar in the graph to see the number of occurrences of the unique value.

Click Close to close the Preview and Statistics pane and return to the Create Analytic View pane.

Click on Generate Hierarchies and Measures icon.

The Generating Hierarchies and Measures dialog box displays the progress of searching for dimension tables, analyzing the dimension tables and identifying and creating the data sources, joins, hierarchies, and measures to use. When the process completes, click Close.



The Search for Dimension Tables check box when selected, enables you to search for dimension tables while generating hierarchies and measures.

After the hierarchies and measures are generated, they are displayed under their respective tabs. Review the hierarchies and measures created for you.

Specify the Name, Fact Table and select Advanced Options in the General tab of Create Analytic View pane. Click Create to generate an Analytic View.

View Data Sources

The Data Sources tab displays the sources of the data and the relationships among them. It has a graphical display of the fact table and the related dimension tables. For example, a fact table of health insurance data might have columns for geography identifiers, income codes, and gender codes. The Data Sources tab would display items for the fact table and for the geography, income, and gender dimension tables.

You can add hierarchies from data sources even after generating hierarchies from the existing fact table. You can add one or more hierarchies to your new or existing analytic view. Multiple hierarchies can be defined and used in an analytical view, however only one will be used by default.

Right-click the Data Sources tab and select Add Hierarchy Sources or select Add Hierarchy Sources.

Selecting Add Hierarchy Sources launches an Add Hierarchy Source dialog box.



You can view all the fact tables and views associated with the analytic view.

In the filter field, you can either manually look for the source or start typing to search for the fact table or views from the list of available fact tables and views. After typing the full name of the source, the tool automatically matches the fact table or view.

Select Generate and Add hierarchy from Source to generate analysis and hierarchies associated with the source data you select.

Select Find and Add Joins to link all the data sources with the fact table. You can add multiple join entries for a single hierarchy.

Click OK to select the source.

The Generating Hierarchies and Measures dialog box displays the progress of analyzing the dimension tables and creating the hierarchies. When the process completes, click Close.

Note:

When you add a hierarchy from the data source, you see the new hierarchy in the list of hierarchies in the Hierarchies tab. You can navigate between the Data Sources tab, the Hierarchies tab, the Measures tab, the Calculations tab. You can add a hierarchy from a source that is not connected by navigating back to the Data Sources tab.

Select Remove Hierarchy Source to remove the hierarchies you create from the data sources. You cannot remove hierarchies generated from the fact table you select from this option.



Expand Joins to view the Hierarchy Source, Hierarchy Column and the Fact column mapped with the Analytic View. The Joins is visible only when the hierarchy table differs from the fact table. You can add multiple join entries for a single hierarchy.

Expand Sources to view the fact table associated with the Analytic View. The data model expands to include the data from the source that you added.

Pointing to an item displays the name, application, type, path and the schema of the table. Click the Actions (three vertical dots) icon at the right of the item to display a menu to expand or collapse the view of the table.



An expanded item displays the columns of the table. Pointing to a column displays the name, application, type, path, and schema of the column.



The lines that connect the dimension tables to the fact table indicate the join paths between them. Pointing to a line displays information about the join paths of the links between the tables. If the line connects a table that is collapsed, then the line is dotted. If the line connects two expanded tables, then the line is solid and connects the column in the dimension table to the column in the fact table.

View and Manage Hierarchies

The Hierarchies tab displays the hierarchies generated by the Analytic View creation tool. The display includes the name of the hierarchy and the source table.



An analytic view must include at least one hierarchy.

To add a Hierarchy, click Add Hierarchy. This results in a display as a list of column in that table. Select a column that operates as the detailed level of the hierarchy and be the join-key to the fact table.



To remove the hierarchy, select the hierarchy you want to remove from the list and click Remove Hierarchy

Select Move Up or Move Down to position the order of the Hierarchy in the resulting view.

Click Switch Hierarchy to Measure to change the hierarchy you select to a measure in the Measures list.

You can also Add Hierarchy and Add Hierarchy From Table by right-clicking the Hierarchy tab.

If you click on a hierarchy name, a dialog box displays the Hierarchy Name and Source.

To change the source, select a different source from the drop-down list.

Select Add Level to add a level to the hierarchy. Click Remove Level to remove the selected level from the hierarchy.



To view the data in the fact table and statistics about the data, click the Preview Data button. In the Preview and Statistics pane, the Preview tab shows the columns of the table and the data in the columns. The Statistics tab shows the size of the table and the number of rows and columns.

If you click on a particular level in the Hierarchy tab, a dialog box displays it's respective Level Name, Level Key, Alternate Level Key, Member Name, Member Caption, Member Description, source, and Sort By drop-down. To change any of the field values, enter the value in the appropriate field.

Note:

You can enter multiple level keys Member Name, Member Caption, Member Description and Sort By.


Member Captions and Member Descriptions generally represent detailed labels for objects. These are typically end-user-friendly names. For example, you can caption a hierarchy representing geography areas named GEOGRAPHY_HIERARCHY as "Geography" and specify its description as "Geographic areas such as cities, states, and countries."

To see the measures for the Analytic View, click Measures tab. To immediately create the Analytic View, click Create. To cancel the creation, click Cancel.

View and Manage Measures

The Measures tab displays the measures suggested for the Analytic View. It displays the Measure Name, Column, and operator Expression for each measure.

The measures specify fact data and the calculations or other operations to perform on the data.

To add measures, click Add Measure. You can view a new measure at the bottom of the measures list. To remove the measure, select the measure you want to remove from the list and click Remove Measure.



To alternatively add a measure from the data source, right- click the Measures tab. This pops up a list of columns that can be used as measures. Select one measure from the list.



You can exclude a column from the measures on right-clicking the Measures tab and selecting Remove Measure.

Click Switch Measure to Hierarchy to change the measure you select to hierarchy in the Hierarchies list.

You must specify a measure as the default measure for the analytic view; otherwise, the first measure in the definition is the default. Select Default Measure from the drop-down.

To add a measure, right-click the Measures tab and select Add Measure. To remove a measure, select the particular measure you want to remove, right-click on it and select Remove Measure.

You can select a different column for a measure from the Column drop-down list. You can select a different operator from the Expression drop-down list.

In creating an analytic view, you must specify one or more hierarchies and a fact table that has at least one measure column and a column to join to each of the dimension tables outside of the fact table.

Create new calculated measures

You can add measure calculations to a query of an analytic view.

The measures and hierarchies associated with the analytic views enable us to create new calculated measures.

Calculated measures return values from data stored in one or more measures. You compute these measures at run time.

Note:

You can create the measures without increasing the size of the database since the calculated measures do not store the data. However, they may slow performance. You need to decide which measures to calculate on demand.

The Analytic Views provides easy-to-use templates for creating calculated measures.

Once you create a calculated measure, it appears in the list of measures of the Analytic View .You can create a calculated measure at any time which is available for querying in SQL.

The Data Analysis tool provides easy-to-use templates for creating calculated measures.



Click Add Calculated Measure to add calculations to the measures. You can view the new calculation with system generated name in the Calculations tab.

Click the newly created calculated measure.



In the Measure Name field, enter the name of the calculated measure.

You can select preferred category of calculation from a list of options such as Prior and Future Period, Cumulative Aggregates, Period To Date, Parallel Period, Moving Aggregates, Share, Qualified Data Reference, and Ranking using the Calculation Category drop-down.

Your choice of category of calculation dynamically changes the Calculation Template.

For more details on how to use Calculation templates, see Using Calculation Templates.

Select the Measure and Hierarchy on which you want to base the calculated measures.

Select Offset value by clicking the up or the down arrow. The number specifies the number of members to move either forward or backward from the current member. The ordering of members within a level is dependent on the definition of the attribute dimension used by the hierarchy. The default value is 0 which represents POSITION FROM BEGINNING.

The Expression field lists the expressions which the calculated measure uses.

On the creation of the Analytic view, the calculated measure appears in the navigation tree in the Calculated Measures folder.

Click Create. A confirmation dialog box appears that asks for your confirmation. Select Yes to proceed with the creation of Analytic View.

After creating the Analytic View, you will view a success message informing you of its creation.

On editing the Analytic View you create, you can view the calculated measure in the navigation tree in the Calculations folder.

Click the Tour icon for a guided tour of the worksheet highlighting salient features and providing information if you are new to the interface.

Click the help icon to open the contextual or online help for the page you are viewing.

Click Show DDL to generate Data Definition Language statements for the analytic view.

Edit Analytic View

You might want to edit an Analytic View to make changes to the data sources, the hierarchies, or the measures.

To edit an Analytic View, click the Action icon on the Analytic View item, then click Edit Analytic View. On the Edit Analytic View screen, select a tab and make changes as desired.

When you have completed the changes, click Update.

Working with Analyses

Analyses are a collection of multiple reports on a single page, which provides quick access to multiple data analyses collected from different Analytic Views.

Analyses enable you to monitor performance, create reports and set estimates and targets for future work. It provides you a visual representation of performance with charts and graphs.

You can access the Analyses page by clicking the Analyses tile on the Data Analysis home page.

Viewing Analyses

Analyses provide you an insight into the performance of your data.

You can use the Analysis and the Analyze pane to search or browse Analytic Views, view their analysis, or reports you have access to. Clicking on the Analyses takes you to a page where you can view the Analyze pane. Here you can view default hierarchy level and measures selected. You can drag and drop any levels and measures from the Analytic View browser to rows/columns and Values in the drop area respectively. This defines your analysis criteria. Once the values are dropped, the Data Analysis tool generates a query internally. The tool displays the results of the analysis in the form of reports in the Analyses that matches your analysis criteria. You can add multiple reports to the Analysis. You can also examine and analyze the reports and save them as a new analysis. You can just save the Analysis and not a single report. Once you save all the reports, it will be part of that single Analysis. Reports are unnamed.

Workflow to build Analyses

Here is the workflow to build an analyses.

Following are few common tasks to start building Analyses:
  • Create a useful analysis: Before creating your first analysis, you can construct a useful analysis over a single Analytic View. This way you can generate analyses on which you can create reports that you display on the Analyses.
  • Create Analysis: Create an analysis to display data from analysis.
  • Create Reports: An Analysis can have multiple reports that are independent of one another. This can be used to compare and analyze data generated from different Analytic Views.
  • Save Analysis: Create customized Analyses that enable you to view reports and their analyses in the current state and save it for future reference.

Creating Analyses

Use the Data Analysis tool to create and edit your Analyses. The analysis provides you customized view of Analytic View data. An analysis consists of one or more reports that displays the results of analysis.

You can create a Basepay analysis and add content to track your team's pay. You can view the analysis in a pivot view or tabular view or in the form of charts. You can create an analysis that displays these three views.

In this example, you create a new analysis called New_Analysis.

  1. On the Data Analysis home page, click the Create Analysis button.
  2. You can make changes to existing Analyses by adding different values from hierarchies and measures.
  3. The AV name you view on the report represents the AV used to create the report. With a different Analytic View you can create a different report.

    Note:

    You must have at least one report to build an analysis.
  4. To edit the existing analysis, In the Analytic View browser, select the objects to analyze in the navigation pane and drag and drop them to the drop area in Columns, Rows or Values and Filters of the Analyze tab.
  5. The report updates based on the artifacts (levels and measures) you select.

The new analysis which contains the updated report will now be visible in the Data Analysis home page for further editing.

Saving Analyses

You can save the personalized settings you made for the Analysis and use them on any other Analyses for future reference.

You won't have to make these decisions manually every time you open the Analyses page if you save these preferences.
  1. Open the analysis for editing from the Data Analysis home page. Select Edit from the Analysis tile you wish to edit.
  2. Click on the Save As icon. Enter a descriptive name for your Analysis.
  3. Click Save.

Creating Reports

A single report you generate analyzes an AV based on the Levels and measures you select.

You can add multiple reports to the newly created analysis. One report is independent of another report. To add a report in the Analysis.
  1. Open the Analysis for editing from the Data Analysis home page. Select Edit from the Analysis tile you wish to edit.
  2. Click on the + Report icon to add one or more reports to the Analysis. You can use a report to add configured Analyses to the Analyses page.
  3. Click on the report to select it. The resize arrow in the report resizes the report window.
  4. Click the cross icon on the selected report to delete the report from the Analysis.
  5. The header displays the name of the Analytic View you select.
  6. You can expand or collapse the report with their respective arrows.
When you add a report to the Analysis, the report provides the following actions:
  • Edit SQL
  • Performance
  • Rename Report: Click Rename Report to rename it. Click Save Report to save the current report.
  • Delete Report: Click Delete Report to delete the report.
Edit SQL

You can view the SQL output when you click Edit SQL. The lower right pane in SQL displays the output of the operation executed in the SQL editor. The following figure shows the output pane on the SQL page.
Description of sql-output.png follows
Description of the illustration sql-output.png

The output pane has the following tabs:
  • Query Result: Displays the results of the most recent Run Statement operation in a display table.

  • Explain Plan: Displays the plan for your query using the Explain Plan command. The default view is the diagram view. For more information, see the description of Explain Plan Diagram in subsequent sections.
Performance menu

The Performance menu displays the PL/SQL procedures in the worksheet area which describes the reports associated with the Analytic Views.

The top part of the performance output consists of the worksheet editor for running SQL statements and an output pane to view the results in different forms. You can view the results in a Diagram View, Chart View, Clear Output from the SQL editor, Show info about the SQL statements and Open the performance menu in a new tab.

The following figure shows the output pane of the performance menu:
Description of performance-output.png follows
Description of the illustration performance-output.png

The output pane has the following tabs:
  • Diagram View: Displays the plan of your query in the diagram view.
  • Chart View: Displays the plan of the query in a chart view.
  • Clear Output: Clears the PL/SLQ statements from the worksheet.
  • Show info: Displays the SQL statement for which the output is displayed.
  • Open in new tab: Opens the explain plan in a new window. An Explain Plan displays the plan for your query.
Explain Plan Diagram

The Explain Plain diagram view is a graphical representation of the contents of the insert row statements in the SQL Query. The plan depicts the hierarchical nature of the steps in the execution plan.

By default, three levels of steps are visible in the diagram. You can use the +/- signs at the

bottom of each step (available when the step has children) to expand or collapse. Use Expand All in the toolbar to view all steps in the diagram.
The diagram also provides the following details:
  • Cardinality (number on the arrow to the parent step), which is the number of rows processed
  • Operation and options applied in that step
  • Execution order, which is the sequential number in the order of execution
  • Access predicates CPU cost in percentage (orange bar)
  • Total CPU cost for the step in percentage (blue circle)
  • Estimated I/O Cost, Bytes processed, and Cost metrics

You can see a brief description pop-up when you hover over any of these statistics in a step.

The icons in the toolbar are:

  • Advanced View: This is the default view of the query when you click Performance. Displays data from SQL Query in mixed tabular/tree view. There is a Diagram View icon that you can use to switch back to the diagram view.
  • Chart View: Displays data from the SQL query in the form of charts.
  • Print Diagram: Prints the diagram.
  • Save to SVG: Saves the diagram to file
  • Zoom In, Zoom Out: If a step is selected in the diagram, clicking the Zoom In icon ensures that it remains at the center of the screen.
  • Fit Screen: Fits the entire diagram in the visible area.
  • Actual Size: Sets the zoom factor to 1.
  • Expand All: Displays all steps in the diagram.
  • Reset Diagram: Resets the diagram to the initial status, that is, only three levels of steps are displayed.
  • Show Info: Shows the SELECT statement used by the Explain Plan functionality.
  • Open in New Tab: Opens the diagram view in a new tab for better viewing and navigation. The diagram is limited to the initial SELECT statement.
  • Min Visible Total CPU Cost(%): Defines the threshold to filter steps with total CPU cost less than the provided value. Enter a value between 0 and 100. There is no filtering for 0.
  • Plan Notes: Displays the Explain Plan notes.

Properties in Explain Plan Diagram

Double-click or press Enter on a selected step to open the Properties slider, which provides more information about that step. See PLAN_TABLE in Oracle Database Reference for a description of each property. The Properties slider shows:
  • Displays information for that step extracted from PLAN_TABLE in a tabular format. Nulls are excluded. You can select JSON to view the properties in JSON format.
  • Information from OTHER_XML column of PLAN_TABLE. The information is displayed in JSON format.

Working with Reports

Reports help you in analyzing Analytic Views and Queries.

The reports are based on the levels and measures you select for the Analytic View and the columns you select for a query.

Click Analyze in the Analytic View and click the Table you want to analyze to view the Analyses page.

The Analyses page comprises the following components:
  1. Analytic View Browser: Select Analytic View from the drop-down if you choose to create reports on Analytic Views to view an Analytic View browser. The Analytic View browser displays the Hierarchies, Levels, and Measures associated with the selected Analytic View.

    Table Browser: Select Query from the drop-down if you choose to create a report on SQL query to view a Table browser. If you select SQL Query, the Table browser displays the available tables and their corresponding columns. You can drill down tables to view their corresponding columns.
    Description of table-browser.png follows
    Description of the illustration table-browser.png

  2. SQL Worksheet editor with the Run icon: You can view this component only when you generate a report on a SQL Query and not an Analytic View.

    The SQL editor area enables you to run SQL statements and PL/SQL scripts from the tables you want to query displayed on the Table browser. By default, the SQL editor displays the Select * statement to display all the columns from the first table. Click Run to run the statements in the editor.
    Description of run.png follows
    Description of the illustration run.png

  3. Output pane: The output pane, when you view the results of a SQL Query, consists of the following tabs:
    • Query Result: Displays the results of the most recent Run Statement operation in a display table.
    • Explain Plan: Displays the plan for your query using the Explain Plan command. For more information, refer to the Explain Plan Diagram in Creating Reports section.
    • Autotrace: Displays the session statistics and execution plan from v$sql_plan when running an SQL statement using the Autotrace feature.

  4. Modes of visualization in the Query Result tab: You can select any of the four modes to visualize the results of the SQL query report you generate.

    The four modes of visualization, when you view the reports generated on a SQL query, are:
    • Base Query: This type of view is by default. 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 which enables you to drag and drop selected columns from the Table browser. By dropping the selected columns in the drop zone, you can view only those columns in the Query Result tab. Select the cross mark beside the Column name to remove it from the drop zone.
    • Pivot: You can view the results of the SQL query in pivot format. By selecting this format, a Columns, Rows, and Values drop zone appears where you can drag and drop the selected columns, rows or values from the Tables browser.

      Note:

      Values must be a NUMBER type.
    • Chart: You can view the SQL results in the form of a chart. By selecting this view an X-axis and Y-axis drop zone appears. Drag and drop selected columns from the Table browser to the drop zone. You must ensure that only the columns with NUMERIC data type can be dropped in the Y axis. Otherwise, the display result would fail with a Must be a NUMBER type error. You can add multiple values to the Y-axis. To view the results in the chart view of only a particular y axis, select the Y axis value from the drop-down.
  5. Modes of visualization of reports generated on an Analytic View: You can select any of the three modes to visualize the results of the report you generate on an Analytic View.

    The three modes of visualization when you view the reports generated on an Analytic View are:
    • Table: You can view the SQL results in tabular form. By selecting this view, a Rows and Filters drop zone appears which enables you to drag and drop selected Hierarchies and Measures from the Analytic View browser. This way you can view the report results that consist of the selected hierarchies and measures.
    • Pivot: You can view the results of the Analytic View report in the pivot format. By selecting this format, a Columns, Rows,Values and Filters drop zone appears where you can drag and drop the selected hierarchies and measures from the Analytic View browser.Note: Values must be a NUMBER type.
    • Chart: You can view the report you generate on an Analytic View in the form of a chart. By selecting this view an X-axis, Y-axis, and Filters drop zone appears. Drag and drop selected columns from the Table browser to the drop zone. You must ensure that only the columns with NUMERIC data type can be dropped in the Y axis. Otherwise, the display result would fail with a Must be a NUMBER type error. You can add multiple values to the Y-axis. You can select Horizontal and Vertical from the drop-down to view Horizontal and Vertical Charts respectively. You also have the option to select Area Chart, Bar Chart, Line Chart, and Pie Chart from the drop-down.
  6. Faceted search panel: For the reports you generate on a SQL query and an Analytic View, you can view a Faceted search column. For a SQL report, this panel allows you to add filters to the report. The tool generates a filter for each value in the column that is retrieved from the query result. You can filter different columns on the faceted search panel and view the results in the Query result to get only the data you wish to view. You can view the data retrieved from the SQL query in either text or visual format. For reports you generate on an Analytic View, select Faceted from the radio button. This filter behaves differently than the Faceted search you generate on an SQL Query. See Adding filters to a report you generate on an Analytic View.

The Analyses page when you create a report on an SQL query looks like this:
Description of analyses-report.png follows
Description of the illustration analyses-report.png

The Analyses page when you create a report on an Analytic View looks like this:
Description of analyses-av.png follows
Description of the illustration analyses-av.png

The following topics describes how to create a report and access the Analyses page:

Creating Reports

  1. You can create Reports using either of the following ways:
  2. Adding filters to a report you generate on an Analytic View

Creating Reports on a Query

This section describes the steps to create reports on an SQL query.

  1. From the Analysis home page, select any of the Tables you want to create a report on. You will view the Analyses page with a default query displayed on the SQL editor.

    Note:

    By default, you will view “Select * from the <Tablename> you select.
  2. Click Run to run the SQL statement.

    The Query Results tab displays the result in whichever mode you select. The default view is Base Query.

  3. Add a filter to the report by displaying the Sales Records of only Asia region. Select Asia from the faceted search panel.
    Description of filter.png follows
    Description of the illustration filter.png

  4. You will view a funnel icon in the Query Result tab which displays a filter with the REGION column as Asia. The Query Result will display only the records with REGION as Asia.

Creating Reports on an Analytic View

This section describes the steps to create reports on an Analytic View:

  1. From the Analysis home page, under the Analytic View section, select any of the Analytic Views you want to create a report on. You will view the Analyses page with a default report displayed as the output.
  2. Drag and drop hierarchies and measures from the Analytic View browser to edit the results you view in the output. For more information, refer to Working with Reports.
  3. Click Expand Report to expand the view of the report and click Collapse Report to minimize the view of the report. The default view of the report you generate on an Analytic View is Pivot.

Adding filters to a report you generate on an Analytic View

Let us add a filter to the report you generated on an Analytic View. Let's say you wish to view the salary of an employee named SCOTT.
  1. From the Analyses page which displays the report you generated on an Employee Analytic View, select “Scott from the faceted search panel. You will view the report displaying the result of employees named Scott.
  2. You can select more filters by selecting the values from the faceted search panel, or by clicking the funnel.
  3. Clicking the funnel icon displays all the values of the Employee name column. Select Jones to filter the report results further displaying the salary of employees named Scott and Jones. You can view the values in a list view or a multi select view.

  4. Select Deselect All to remove all filters.

You can now view the original report result that does not consist of any filters.

Using Calculation Templates

The Data Analysis tool provides templates for all of the calculations typically in demand for business intelligence applications.

The following topics describe the types of calculations available as calculation templates in the tool.

Cumulative Aggregates

Cumulative calculations start with the first time period and calculate up to the current member, or start with the last time period and calculate back to the current member.

The tool provides several aggregation methods for cumulative calculations:

  • Cumulative Average: Calculates a running average across time periods.

  • Cumulative Maximum: Calculates the maximum value across time periods.

  • Cumulative Minimum: Calculates the minimum value across time periods.

  • Cumulative Total: Calculates a running total across time periods.

You can choose the measure, the time dimension, and the hierarchy. For selecting the time range see "Choosing a Range of Time Periods" in Oracle OLAP User’s Guide.

Cumulative Calculation Example

This template defines a calculated measure using Cumulative Minimum.

Cumulative minimum of SALES in the TIME dimension and TIME.CALENDAR hierarchy within ancestor at level TIME.CALENDAR_YEAR. Total from beginning to current member.

These are the results of a query against the calculated measure, which displays values for the descendants of calendar year 2021. The minimum value for quarters begins with Q1-21 and ends with Q4-21, and for months begins with Jan-21 and ends with Dec-21.

TIME     TIME_LEVEL                SALES  MIN_SALES
-------- -------------------- ---------- ----------
Q1.21    CALENDAR_QUARTER       32977874   32977874
Q2.21    CALENDAR_QUARTER       35797921   32977874
Q3.21    CALENDAR_QUARTER       33526203   32977874
Q4.21    CALENDAR_QUARTER       41988687   32977874
JAN-21   MONTH                  11477898   11477898
FEB-21   MONTH                  10982016   10982016
MAR-21   MONTH                  10517960   10517960
APR-21   MONTH                  11032057   10517960
MAY-21   MONTH                  11432616   10517960
JUN-21   MONTH                  13333248   10517960
JUL-21   MONTH                  12070352   10517960
AUG-21   MONTH                  11108893   10517960
SEP-21   MONTH                  10346958   10346958
OCT-21   MONTH                  14358605   10346958
NOV-21   MONTH                  12757560   10346958
DEC-21   MONTH                  14872522   10346958

Prior and Future Period

The Data Analysis tool provides several calculations for prior or future time periods.

Here are the calculations used for for prior or future time periods:

  • Prior Period: Returns the value of a measure at an earlier time period.

  • Difference From Prior Period: Calculates the difference between values for the current time period and an earlier period.

  • Percent Difference From Prior Period: Calculates the percent difference between the values for the current time period and an earlier period.

  • Future Period: Returns the value of a measure at a later time period.

  • Difference From Future Period: Calculates the difference between the values for the current time period and a later period.

  • Percent Difference From Future Period: Calculates the percent difference between the values for the current time period and a later period.

When creating a calculation for prior or future time periods, you choose the measure, the time dimension, the hierarchy, and the number of periods from the current period.

Prior Period Example

This template defines a calculated measure using Prior Period:

Prior period for measure SALES in TIME dimension and TIME.CALENDAR hierarchy 1 period ago.

These are the results of a query against the calculated measure. The PRIOR_PERIOD column shows the value of Sales for the preceding period at the same level in the Calendar hierarchy.

TIME     TIME_LEVEL           SALES PRIOR_PERIOD
-------- -------------------- ---------- ------------
2020     CALENDAR_YEAR         136986572    144290686
2021     CALENDAR_YEAR         140138317    136986572
Q1.20    CALENDAR_QUARTER       31381338     41988687
Q2.20    CALENDAR_QUARTER       37642741     31381338
Q3.20    CALENDAR_QUARTER       32617249     37642741
Q4.20    CALENDAR_QUARTER       35345244     32617249
Q1.21    CALENDAR_QUARTER       36154815     35345244
Q2.21    CALENDAR_QUARTER       36815657     36154815
Q3.21    CALENDAR_QUARTER       32318935     36815657
Q4.21    CALENDAR_QUARTER       34848911     32318935

Period to Date

Period-to-date functions perform a calculation over time periods with the same parent up to the current period.

These functions calculate period-to-date:

  • Period to Date: Calculates the values up to the current time period.

  • Period to Date Period Ago: Calculates the data values up to a prior time period.

  • Difference From Period to Date Period Ago: Calculates the difference in data values up to the current time period compared to the same calculation up to a prior period.

  • Percent Difference From Period To Date Period Ago: Calculates the percent difference in data values up to the current time period compared to the same calculation up to a prior period.

When creating a period-to-date calculation, you can choose from these aggregation methods:

  • Sum

  • Average

  • Maximum

  • Minimum

You also choose the measure, the time dimension, and the hierarchy.

Period to Date Example

This template defines a calculated measure using Period to Date.

Gregorian Year to date for SALES in the TIME dimension and TIME.CALENDAR hierarchy. Aggregate using MINIMUM from the beginning of the period.

These are the results of a query against the calculated measure. The MIN_TO_DATE column displays the current minimum SALES value within the current level and year.

TIME     TIME_LEVEL                SALES MIN_TO_DATE
-------- -------------------- ---------- -----------
Q1.21    CALENDAR_QUARTER       36154815    36154815
Q2.21    CALENDAR_QUARTER       36815657    36154815
Q3.21    CALENDAR_QUARTER       32318935    32318935
Q4.21    CALENDAR_QUARTER       34848911    32318935
JAN-21   MONTH                  13119235    13119235
FEB-21   MONTH                  11441738    11441738
MAR-21   MONTH                  11593842    11441738
APR-21   MONTH                  11356940    11356940
MAY-21   MONTH                  13820218    11356940
JUN-21   MONTH                  11638499    11356940
JUL-21   MONTH                   9417316     9417316
AUG-21   MONTH                  11596052     9417316
SEP-21   MONTH                  11305567     9417316
OCT-21   MONTH                  11780401     9417316
NOV-21   MONTH                  10653184     9417316
DEC-21   MONTH                  12415325     9417316

Parallel Period

Parallel periods are at the same level as the current time period, but have different parents in an earlier period. For example, you may want to compare current sales with sales for the prior year at the quarter and month levels.

The Data Analysis tool provides several functions for parallel periods:

  • Parallel Period: Calculates the value of the parallel period.

  • Difference From Parallel Period: Calculates the difference in values between the current period and the parallel period.

  • Percent Difference From Parallel Period: Calculates the percent difference in values between the current period and the parallel period.

To identify the parallel period, you specify a level and the number of periods before the current period. You can also decide what happens when two periods do not exactly match, such as comparing daily sales for February (28 days) with January (31 days).

You also choose the measure, the time dimension, and the hierarchy.

Parallel Period Example

This template defines a calculated measure using Parallel Period.

Parallel period for SALES in the TIME dimension and TIME.CALENDAR hierarchy 1 TIME.CALENDAR.QUARTER ago based on position from beginning to ending of period.

These are the results of a query against the calculated measure, which lists the months for two calendar quarters. The parallel month has the same position within the previous quarter. The prior period for JUL-21 is APR-21, for AUG-21 is MAY-21, and for SEP-21 is JUN-21.

TIME    PARENT     SALES   LAST_QTR
-------- ---------- ---------- ----------
APR-21   CY2006.Q2    11356940   13119235
MAY-21   CY2006.Q2    13820218   11441738
JUN-21   CY2006.Q2    11638499   11593842
JUL-21   CY2006.Q3     9417316   11356940
AUG-21   CY2006.Q3    11596052   13820218
SEP-21   CY2006.Q3    11305567   11638499

Moving Aggregates

Moving aggregates are performed over the time periods surrounding the current period.

The Data Analysis tool provides several aggregation methods for moving calculations:

  • Moving Average: Calculates the average value for a measure over a fixed number of time periods.

  • Moving Maximum: Calculates the maximum value for a measure over a fixed number of time periods.

  • Moving Minimum: Calculates the minimum value for a measure over a fixed number of time periods.

  • Moving Total: Returns the total value for a measure over a fixed number of time periods.

You can choose the measure, the time dimension, and the hierarchy. You can also select the range, as described in "Choosing a range of time periods" in Oracle OLAP User’s Guide , and the number of time periods before and after the current period to include in the calculation.

Moving Aggregates Example

This template defines a calculated measure using Moving Minimum.

Moving minimum of SALES in the TIME dimension and TIME.CALENDAR hierarchy. Include 1 preceding and 1 following members within level.

These are the results of a query against the calculated measure, which displays values for the descendants of calendar year 2021. Each value of Minimum Sales is the smallest among the current value and the values immediately before and after it. The calculation is performed over all members of a level in the cube.

TIME     TIME_LEVEL           SALES  MIN_SALES
-------- -------------------- ---------- ----------
Q1.21    CALENDAR_QUARTER       32977874   32977874
Q2.21    CALENDAR_QUARTER       35797921   32977874
Q3.21    CALENDAR_QUARTER       33526203   33526203
Q4.21    CALENDAR_QUARTER       41988687   31381338
JAN-21   MONTH                  11477898   10982016
FEB-21   MONTH                  10982016   10517960
MAR-21   MONTH                  10517960   10517960
APR-21   MONTH                  11032057   10517960
MAY-21   MONTH                  11432616   11032057
JUN-21   MONTH                  13333248   11432616
JUL-21   MONTH                  12070352   11108893
AUG-21   MONTH                  11108893   10346958
SEP-21   MONTH                  10346958   10346958
OCT-21   MONTH                  14358605   10346958
NOV-21   MONTH                  12757560   12757560
DEC-21   MONTH                  14872522   12093518

Share

Share calculates the ratio of a measure's value for the current dimension member to the value for a related member of the same dimension.

You can choose whether the related member is:

  • Top of hierarchy: Calculates the ratio of each member to the total.

  • Member's parent: Calculates the ratio of each member to its parent.

  • Member's ancestor at level: Calculates the ratio of each member to its ancestor, that is, a member at a specified level higher in the hierarchy.

When creating a share calculation, you can choose the measure, dimension, and hierarchy. You also have the option of multiplying the results by 100 to get percentages instead of fractions.

Share Example

This template defines a calculated measure using SHARE:

Share of measure SALES in PRODUCT.PRIMARY hierarchy of the PRODUCT dimension as a ratio of top of hierarchy.

These are the results of a query against the calculated measure. The TOTAL_SHARE column displays the percent share of the total for the selected products.

PRODUCT           PROD_LEVEL         SALES TOTAL_SHARE
-------------------- --------------- ---------- -----------
Total Product        TOTAL            144290686         100
Hardware             CLASS            130145388          90
Desktop PCs          FAMILY            78770152          55
Portable PCs         FAMILY            19066575          13
CD/DVD               FAMILY            16559860          11
Software/Other       CLASS             14145298          10
Accessories          FAMILY             6475353           4
Operating Systems    FAMILY             5738775           4
Memory               FAMILY             5430466           4
Modems/Fax           FAMILY             5844185           4
Monitors             FAMILY             4474150           3
Documentation        FAMILY             1931170           1

Rank

Rank orders the values of a dimension based on the values of the selected measure. When defining a rank calculation, you choose the dimension, a hierarchy, and the measure.

You can choose a method for handling identical values:

  • Rank: Assigns the same rank to identical values, so there may be fewer ranks than there are members. For example, it may return 1, 2, 3, 3, 4 for a series of five dimension members.

  • Dense Rank: Assigns the same minimum rank to identical values. For example, it may return 1, 2, 3, 3, 5 for a series of five dimension members.

  • Average Rank: Assigns the same average rank to identical values. For example, it may return 1, 2, 3.5, 3.5, 5 for a series of five dimension members.

You can also choose the group in which the dimension members are ranked:

  • Member's level: Ranks members at the same level.

  • Member's parent: Ranks members with the same parent.

  • Member's ancestor at level: Ranks members with the same ancestor at a specified level higher in the hierarchy.

Rank Example

This template defines a calculated measure using Rank:

Rank members of the PRODUCT dimension and PRODUCT.PRIMARY hierarchy based on measure SALES. Calculate rank using RANK method with member's parent in order lowest to highest. Rank NA (null) values nulls last.

These are the results of a query against the calculated measure in which the products are ordered by RANK:

PRODUCT             SALES       RANK
-------------------- ---------- ----------
Monitors                4474150          1
Memory                  5430466          2
Modems/Fax              5844185          3
CD/DVD                 16559860          4
Portable PCs           19066575          5
Desktop PCs            78770152          6

Oracle Autonomous Database Add-in for Excel

The Oracle Autonomous Database Add-in for Excel integrates Microsoft Excel spreadsheets with the Autonomous Database to retrieve and analyze data from Analytic Views in the database. You can run custom SQL queries and view their results in the worksheet.

Install the Oracle Autonomous Database add-in on Mac

The Oracle Autonomous Database add-in for Excel is supported on Mac OS running Microsoft Office 365.

Before you can install the Autonomous Database Add-in for Excel, download the oracleplugin.zip file and extract it to get the installer script file from your Database Actions instance.

  • Open the Database Actions Launchpad.

  • On the Downloads section of the Database Actions page, click the Download Microsoft Excel/Google Sheets Add-in card. To use Oracle Autonomous Database add-in for Excel Sheet, click the Microsoft Excel tab and select Download. Selecting the Download button downloads the zipped oracleplugin folder to a new oracleplugin folder on your desktop.
  • You can now view a zipped folder in the Downloads folder of your system.
  • Create a new folder named Add-in in your Downloads folder.
  • Extract the zipped folder in the Add-in folder.

Follow these steps to install the add-in.

  1. Quit Excel before you run the installer.
  2. Double-click the install.sh file that you downloaded previously.
  3. Right click on install.sh and select the following options as shown : Open With -> Other… -> Enable: All Applications ->Utilities->Terminal
  4. Start Excel and open a new or existing workbook.
  5. From the Insert menu in the Excel ribbon, select drop-down menu of My Add-ins.

    A new Oracle Autonomous Database entry appears under Developer Add-Ins dialog box.

  6. Select Oracle Autonomous Database.

A new Autonomous Database ribbon tab appears in MS Excel.



Install the Oracle Autonomous Database add-in on Windows

The Oracle Autonomous Database Add-in for Excel is supported on Windows 10 operating systems running Microsoft Excel 365.

Before you can install the Autonomous Database Add-in for Excel, download the oracleplugin.zip file and extract it to get the install.cmd script file from your Database Actions instance.

  • Open the Database Actions Launchpad.

  • Under the Downloads section, select the DOWNLOAD MICROSOFT EXCEL/GOOGLE SHEETS ADD-IN Card.

  • Click the Download Add-in icon in the Microsoft Excel tab to download the Oracle Autonomous Database Add-in for Excel.

  • Extract the oracleplugin.zip folder to a new folder in the Downloads of your system. The extracted folder consists of an installer (install.cmd file), a manifest.xml file and a readme.txt file.

Follow these steps to install the add-in.

  1. Quit Excel before you run the installer.
  2. Right-click the install.cmd file that you downloaded.

    Note:

    After running the installer on Windows, the add-in automatically creates a network share and adds the shared location as a trusted catalog location for Office add-ins. A catalog is used to store the manifest for the Excel Add-in. It enables publishing and management of the Excel add-in as well as other add-ins that are available in the Office Store and licensed for corporate use. You can acquire the Excel add-in by specifying the shared manifest folder as a trusted catalog.

  3. Select Run as administrator.

    Note:

    You must have Administrator privileges to successfully install the Excel add-in for Oracle Autonomous Database.
  4. Start Excel and open a new or existing workbook.
  5. After you install the add-in, a new Autonomous Database ribbon tab appears in MS Excel.

Note:

You can change the functionality of the installer after initial installation. Re-run the installer and choose the option of your preference. You can either choose to repair your existing installation by deleting it and selecting the installed trusted catalog or adding another manifest to the working installation.

Uninstall the Oracle Autonomous Database add-in

The following section describes the steps to uninstall the Oracle Autonomous Database add-in.

To uninstall the Oracle Autonomous Database Add-in for Excel for Windows:

No add-ins will now be available from the Shared folder of the Office Add-ins window.

To uninstall the Oracle Autonomous Database Add-in for Excel for Mac:

  • Enter the following command in the terminal to remove the manifest.xml file.
    rm ~/Library/Containers/com.microsoft.Excel/Data/Documents/wef/manifest.xml

The Oracle Autonomous Database Add-in is uninstalled from Excel for Mac.

Note:

After uninstalling the Add-in, if you re-install it from different Autonomous Database (ADB) then the add-in attempts to load the old ADB. You need to then check if the location (share path) of the shared manifest folder is pointing to the right location. Refer to Configuring the Excel Trusted Add-in Catalog in FAQs for Troubleshooting errors with Excel Add-in chapter for more details.

Using Oracle Autonomous Database Add-in for Excel

After you install the add-in, a new ribbon tab, Autonomous Database appears in MS Excel.

You can connect to multiple Autonomous Databases, work with Analytic Views, tables, and Views, and view the data in the worksheet.

This ribbon provides buttons that lets you connect to the Autonomous Database.

Click Connections to connect to an Autonomous Database. You must Refer to the Connection management for more details.

Click Connections to view the log settings of the Excel Add-in.

Click About to view the Add-in and the supported excel versions. The About window also displays whether the spreadsheet is connected to the database or not. It also displays version information for the database and Oracle Rest Data Services.

Click Native SQL to write and run custom SQL queries.

Click Query Wizard to select the Analytic View you want to query. You can review and edit the query, add or edit filters and calculations to it and choose the output format from tabular and pivot formats.

Click Analyses and Reports to view the Analyses and reports the Excel Add-in generates from the selected Analytic View.

Selecting Native SQL icon or Query Wizard icon from the ribbon launches the Oracle Autonomous Database wizard in the Excel task pane.



Click Move in the drop-down of the wizard pane to move the wizard to your preferred location.

The Resize option in the drop-down resizes the query window. As you select this option, you can resize the wizard window by moving the double-headed arrow sideways. The wizard expands when you move the arrow to the left and it contracts when you drag it to the right.

Click Move out of Tab to move the add-in from the taskpane.

Click Close to close the wizard.

Connection management

Each time you start the add-in for excel, you must start a connection.

With the connections feature, you can connect to multiple autonomous databases with a single add-in. Multiple connections can be created, however, only one connection can remain active. Ensure that the autonomous database from where you download the plug-in is available for connecting to other autonomous databases.

The connection panel lets you connect to the autonomous database through a connection where you provide the login credentials and access the autonomous database.

With the Connections icon, you can:
  • Create or delete multiple connections using a single add-in.
  • Share connection information by exporting and importing connection information to a file.
  • View existing connections.
  • Refresh connections to retrieve updated data from the autonomous database and view the connection status of the Excel Add-in.

Selecting Connections opens the Connections wizard. You must reinstall the add-in if you had already installed the add-in once before.

Note:

This is implicit type of connection. Refer to Authenticate using Implicit connection to understand more about implicit connection.

The Connections wizard consists of the following four buttons at the header:
  • Refresh: You can refresh the connection with this icon. The green icon besides the connection name indicates the connection is active. A red icon besides the connection name indicates that you are not connected to the database.
  • Add: Select Add to Add a Connection. Refer to Add a Connection section for more details.
  • Export: Select Export to export connections. Refer to the Share a Connection section for more details.
  • Import: Select Import to launch the import wizard to choose a connection file. These files are in JSON format. Refer to the Share a Connection section for more details.

Add a connection

You can add a connection to an autonomous database. Adding a connection enables you to specify the connection credentials to the database in which you will connect to the schema of your autonomous database.

This connection will allow you to use the database from Excel.
  1. Click on the Add button on the header of the Connections pane to add a connection. This opens an Add new connection dialog box.Description of add-new-connection.png follows
    Description of the illustration add-new-connection.png
  2. Specify the following fields on the Add new connection dialog box:
    • Alias: Enter the Alias name for the Autonomous Database URL. For readability purpose, Oracle recommends to use a different name than the actual URL.
    • Autonomous Database URL: Enter the URL of the Autonomous Database you wish to connect to. Copy the entire url from the web UI of the autonomous database. For example, enter or copy the following link "https://<hostname>-<databasename>.adb.<region>.oraclecloudapps.com/" to connect to the database. This will be provided to you by the administrator.
    • Schema: Enter the schema you use for this connection.
    • Client ID: Enter the Client ID for this connection. Refer to the Generate the client ID for a connection section to generate the client ID of this implicit connection and paste it on this field.

    Click the Copy implicit connection query template button to get the PL/SQL code to generate a client ID required in the Client ID field. For details refer to the "Generate Client ID for a connection" section below.

  3. Click Save to save the connection.

You should be able to view the new connection now.

Import a Connection

You can import a connection file that is in a JSON format without specifying connection credentials to the database.

Before importing the connection file from the Database Actions instance, you need to download the connection file.
  1. From the Downloads page of the Database Actions instance, click the Download Connection File icon to download the connection file in your system.

  2. The file is downloaded in your system.
To import a connection:
  1. Select Connections from the Autonomous Database menu in the Excel sheet. This opens the Connections wizard.
  2. Click Import to import the connection file you downloaded earlier from the Database Actions instance.

  3. Click and drop the connection file from your system to the drop area of the wizard. After the connection file loads, select the check box beside the connection file you downloaded to import the file in the wizard.

  4. Click ok to proceed.
  5. Click the three vertical dots beside the connection file and select Connect.

    Note:

    If you view a red icon beside the connection file, this means that the connection is inactive or incorrect. Click the three vertical dots beside the connection file and select Edit to update the connection file. Ensure the Autonomous Database URL is correct and click Save. An example of a correct URL is “https://<hostname>-<databasename>.adb.<region>.oraclecloudapps.com/"

  6. Specify the schema name in the username field and the corresponding password in the credentials screen you view. Click Sign in to login to the autonomous database.

You will view “Active Connection” beside the connection name.

Generate Client ID for a connection

The OAuth Client key is generated using SQL.

The Copy implicit connection query template button copies the connection query template. The template consists of a PL/SQL code that generates OAuth Client ID. You can copy and run this PL/SQL code in the worksheet editor to create the Client ID.

This section describes how to generate a client ID.

  1. On the Development section of the Database Actions Launchpad, select SQL card. This opens the SQL page.Description of sql-card.png follows
    Description of the illustration sql-card.png
  2. Paste the implicit connection query template you copied as explained in the previous section. Here is a sample of the implicit connection query template.
    -- Implicit client creation template, replace the content in the square brackets.
    set serveroutput on;
    DECLARE
       name_of_client   VARCHAR2(256) := '[PROVIDE_A_UNIQUE_CLIENT_NAME]';
       name_of_schema   VARCHAR2(30)  := '[PROVIDE_THE_SCHEMA_NAME]';
       v_client           user_ords_clients.client_id%TYPE;
    BEGIN
      OAUTH.create_client(
        p_name            => name_of_client,
        p_grant_type      => 'implicit',
        p_owner           => name_of_schema,
        p_description     => 'An Oauth client for Excel addin',
        p_redirect_uri    => 'https://phoenix96088.dev3sub2phx.databasede3phx.oraclevcn.com/sheet-query/src/v2/oauth/get-token.html',
        p_support_email   => 'youremail@yourorg.com',
        p_support_uri     => 'https://support.oracle.com/',
        p_privilege_names => NULL
      );
     
      COMMIT;
      select client_id into v_client from user_ords_clients where name = name_of_client;
      dbms_output.put_line('Client id for ' || name_of_client || ':');
      dbms_output.put_line(v_client);
    END;
    /
    -- To list all the clients, run this query:
    select name, client_id from user_ords_clients;
    
  3. On the worksheet editor, replace the "[PROVIDE_A_UNIQUE_CLIENT_NAME]" text in the variable name field with the client name of your choice. The name has to be unique. For example, no other OAuth client can have the same name as the name you provide in this field.
  4. On the worksheet editor, replace the"[PROVIDE_THE_SCHEMA_NAME]" text in the variable name field with the schema name you use.
  5. You could replace the support URI in the Create Client PL/SQL procedure with the email you used to create the OAuth Client. For example, “youremail@yourorg.com".

    Note:

    Do not change the template else you might view errors which will cause unsuccessful creation of Client ID. The p_redirect_uri field is auto generated and is different for each autonomous database.

  6. Click the Run Script icon on the worksheet toolbar to run the PL/SQL code.Description of worksheet-editor.png follows
    Description of the illustration worksheet-editor.png
  7. The following is the sample output you will view in the Script Output tab after you run the PL/SQL code.
    Client id for Example client for confluence docs:
    2MUsYWgbkvSgKMDwF2BxbQ..
     
     
    PL/SQL procedure successfully completed.
     
    Elapsed: 00:00:00.113
     
    ----------------------------------------
     
    NAME                               CLIENT_ID               
    ---------------------------------- ------------------------
    Client                         OohrmcjhzmXh3skoeEusXA.. 
    [PROVIDE_A_UNIQUE_CLIENT_NAME] hFEVl8GSn1yR3cSAP3KL2w..  
     
    Elapsed: 00:00:00.170
    2 rows selected.
    
  8. Copy the client ID from the first line of the script output. You can also copy the client ID equivalent to the client name you provided on the implicit connection query template. Here is the client ID, in the above example,
    OohrmcjhzmXh3skoeEusXA..
  9. Paste the Client ID on the Client ID value field of Add new connection dialog box. Refer to the Add a connection for more details on this.
Once you have created a new connection you will be able to view the connection you have added in the Connections panel. A connection in the panel lists the following:
  • An alias at the top. For example, test is the alias.
  • The bottom part of the connection panel displays the URL of the autonomous database with the schema you connect to.
  • A connection status indicator where the indicator identifies if the connection is connected or not. A red cross mark indicates there is no connection whereas a check mark indicates the connection is successful.
  • An actions icon right most to the connection panel.
Description of adp-connection-panel.png follows
Description of the illustration adp-connection-panel.png
Click the Actions icon on the connection. You can perform the following actions on the selected connection:
  • Connect: Click Connect to connect the add-in with the autonomous database. This opens the login page of Oracle Database Actions the autonomous database you wish to connect to.

    Enter the schema name in the username field and the corresponding password.

    Note:

    • The add-in for Excel asks for your permission the first time you log into the database. Select Approve to proceed with the login.

    After you connect to Oracle Database Actions, the Database Actions home page appears.

  • Activate:

    When you connect to multiple different autonomous databases, there can be only one active connection. Click Activate to make the selected connection active. The Active connection is displayed at the top of the panel. You can expand or collapse the active connection. Expand the active connection to view its detailed status such as the Autonomous database URL, schema and the status of connection.

    You can collapse in case of spacing issues. You can view the alias and the status of the connection in its collapsed form.

  • Edit: This button enables you to edit the existing connection. Click Edit to review, view or edit any of the connection-based information. Selecting Edit opens the same dialog you view when you add a connection. Edit any of the information details, such as, Alias, Autonomous Database URL, Schema or the Client ID of the existing connection.
  • Duplicate: Select Duplicate to clone the connection from the list of connections displayed in the Connections panel. This creates a copy of the connection without having to enter the details again.
  • Disconnect: Select Disconnect to disconnect from the connection. Once the connection disconnects, you will see a red cross mark besides the connection name. This indicates that the connection is terminated.
  • Remove: Select Remove to remove the connection from the list of connections displayed in the Connections panel.

Share a connection

You can import or export a connection using the Import and Export buttons on the Connections panel.

  • Import: Click Import and select a connection file from your local device. Once you import the connection file, you can view the connection in the Connection panel with a check box besides it. Select the check box and click OK to add the connection to the list of connections in the panel. The add-in copies the connection information which you can use as a new connection. With the import feature, you do not have to enter the connection information to add a new connection.
  • Export: The export button exports an existing connection which you can import later. Clicking Export opens a check box beside each connections present in the connections list. You can select the connection you wish to export. Multiple selections are allowed. After you select the connection, click OK. Once the connection file is exported, you can view that the add-in for Excel downloads the connection file (*.json file) in your local device. The exported connection file is named spreadsheet_addin_connections.json.

Run native SQL queries in an Excel worksheet

The Oracle Autonomous Database add-in for Excel lets you run native SQL queries to work with your data in an Excel worksheet.

With the add-in, you can create a table and insert, update and delete rows from the existing tables or views. You can view the results in the existing worksheet or in a different worksheet.

The following image shows your data retrieved from the Autonomous Database and displayed in the worksheet. The Query Info section comprising the Timestamp, User name and SQL Query are shown in Excel. You can edit custom queries, run them, query Analytic Views, and add or edit filters to the Analytic View. The worksheet displays the results of queries in tabular format from the retrieved data.

The add-in maintains a live connection with the database. However, the data retrieved is local to Excel. In case of inactivity, the connection times out, and you will need to log in again. You can change the active connection from connections panel. The image shows the results from a single query, but you can insert many queries in a single workbook.
Description of result-native-sql.png follows
Description of the illustration result-native-sql.png

To run a query using the add-in, run Excel and create a blank workbook using the standard Excel workbook file format.

  1. In the Excel ribbon, select the Autonomous Database.
  2. Click the Native SQL icon from the ribbon. This opens an Oracle Autonomous Database dialog box in the Excel Task Pane with Tables and Views icons and a search field beside them.

  3. Select Table to view all the existing tables in the schema. Click Views to view the existing views in the schema.
  4. You can right click on the table whose data you want to query and choose Select to view all the columns of the table. The column names will be displayed in the Write a Query section. You can click on the table and view individual columns as well. Click the Run button to run the commands in the query editor. The query results will be displayed in the worksheet you select.

    Note:

    You will view an error message if you click the Run icon while the query editor is empty.
  5. You can click + sign besides the Select worksheet drop-down to display the results in a new worksheet.
  6. The worksheet also displays information such as the timestamp, the user who creates and runs the query and the autonomous database URL.

To run another query follow these steps:

  1. Click the eraser icon to clear the previous query from the SQL editor and write a new query.
  2. In the Select worksheet drop-down, select a new sheet, Sheet 2 in this case. The Add-in adds a sheet for the user. If you choose to work on the same sheet, the Add-in refreshes the data in the existing worksheet.
  3. Click the Run icon to display the query results.

The worksheet displays the result of the query at a go. While this behavior works for most scenarios, sometimes, for large data sets, the query result might exceed 10K rows. Although you can view the 10K rows, a confirmation window appears which asks if you would want to view the rest of the result.

Description of adp-entire-result-set.jpg follows
Description of the illustration adp-entire-result-set.jpg

Select Yes to view the entire result set. Loading the entire data may take a while. You must fetch the entire query result before working with Pivot tables, or else it will lead to incorrect results from aggregation in Pivot tables.

Close the Query Wizard panel to cancel the operation of fetching the result.

Note:

Close the Query Wizard panel to cancel the operation of fetching the result.

Query an Analytic View in an Excel worksheet

The Query Wizard menu enables you to query an Analytic View and retrieve the results in an Excel Worksheet. Once the wizard retrieves the data, it becomes local to Excel. You can further edit the data in Excel but cannot write back to the Autonomous Database.

You can retrieve the Analytic View to visualize the result data in the worksheet. You can search for the Analytic View, select measures, hierarchies, and levels from the query. You also have options to add filters and calculated measures to the query and view the query result in the spreadsheet.

By default, the data is retrieved in tabular format. You also have the option to create an Excel pivot from this data.

The Query Wizard has three panels:

  1. Analytic View panel:The Analytic View panel contains a list of analytic views from which you build queries. You edit the query by selecting measures, hierarchies and levels and progress to the next panel.Description of av-new.png follows
    Description of the illustration av-new.png
  2. Filter panel:The Filter panel displays to the right of the Analytic View panel. You can create filter conditions and add calculations to the query in this panel.Description of filter-new.png follows
    Description of the illustration filter-new.png
  3. Query Result panel:The Query Result panel displays to the right of the Filter panel. Once you select the filter criteria and determine what calculated measures to add to your query, you run the query. You can view and revise the SQL query. After the SQL query runs, you view the query results in the worksheet. You can select the output format of the result here. You can view the results in tabular format or the form of a Pivot table.Description of query-result-new.png follows
    Description of the illustration query-result-new.png

To query an analytic view and explore the Query Wizard menu in the MS Excel ribbon:

  1. On the ribbon, select the Query Wizard icon.
  2. Selecting the Query Wizard opens an Oracle Autonomous Database dialog box in the Excel Task Pane.
  3. Select an existing Analytic View from the drop-down in the Analytic View pane. As you select the Analytic View, it appears on the Analytic View field.
  4. Select your choice of measures, hierarchies, and levels from list of available measures, hierarchies and levels associated with the Analytic View. Click Next.
  5. The wizard window progresses to the Filter pane where you can add or edit filters to query.
  6. Under Add or Edit filter conditions, do the following.
    • Select the column name and the attribute name from the drop-down. The values of the attribute change dynamically with the change in column names.
    • Select an operator in the Operator field to apply to the values that you specify in the Value field.
    • Specify a value or values from the list that contains members of the column that you select. You need to enter the value into the Values field manually. For example, you can select > in the Operator field to use only values greater than the value that you select in the Value list. If you select 100,000 from the Value list, then the filter uses values from the column that are greater than 100,000. You can use this information in an analysis to focus on products that are performing well.
    • Click Add Filter to add another filter condition.
  7. Under Add or Edit Calculations, do the following.
    • Specify the column whose values you want to include in the group or calculated item.
    • On the Calc expression field, enter a custom calculated expression you want to perform on the column value. You can add functions or conditional expressions.
  8. Click Next to progress to the Query Result.
  9. You can view, edit, and review the query you have generated from the Query Review editor.
  10. Select Remove empty columns to remove empty columns from the result.
  11. Select Column per level to retrieve all levels of a hierarchy in a single column.
  12. Select the worksheet from the drop-down where you want to view the result.
  13. Click Execute to run the query.
  14. You can view the result of the query in the worksheet you select.
  15. You can always modify the query in the Oracle Autonomous Database dialog box editor even after results are generated.
  16. Select Table in the Query Result pane to view the results in the worksheet in a tabular format.
  17. Select Pivotin the Query Result pane to view the results in a new worksheet in Pivot format.

View the results in Pivot tables

Pivot table view is an interactive view that allows you to transpose rows and columns. A pivot table can summarize, sort, reorganize, count total and perform average of the result data. They are navigable and drillable.

Apart from tabular mode, to view the query results in pivot table mode, select the Pivot Table option in the Autonomous Database wizard. Click Execute to view the query results in Pivot table.

Clicking Execute opens the query results in a new sheet with a PivotTable Fields wizard. Click anywhere outside the table in the spreadsheet to switch the Pivot Table wizard to Native SQL query wizard. Select any cell in the table to continue editing the Pivot Table fields.

You can view the Grand Total of the entire pivot table in the last row of the table.



Work with the PivotTable Fields wizard

This topic describes what you need to know to work with the PivotTable Fields wizard.

You can edit the pivot tables in different ways to display data the way you want. You use drop targets to modify the way data is arranged in a data view by dragging and dropping columns to different targets within the view.

For example, you can edit a pivot table in an analysis by dragging the month field to the rows area, the sales field to the Values area and the year name to the Columns area to display its corresponding sales for each month and year.

You can add filters in the Filters area to limit the data in your worksheet.

If you want to remove a field from the pivot table analysis, click on the field you want to remove and select Remove Field.

To select the type of calculation you want to summarize the data, select the appropriate option from the Values field. The different types of calculations you can perform on the Values field are sum, count, average, maximum, and minimum.

You can customize the appearance of Fields section and Areas section in the Pivot Table Fields wizard. To change the way you want to view the Fields section and the Areas section, select the Tools icon in the Pivot Table Fields wizard.



See https://support.microsoft.com/en-gb/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576 for more information on Pivot tables.

Edit Query Results in the worksheet

Oracle Autonomous Database add-in automatically manages the appearance of an integrated Excel workbook through built-in styles and data format types.

Once the plug in runs the query and retrieves the data into the worksheet, you can view the details of the plug-in’s automatically generated query results by hovering over each cell of the workbook. The details you view are the Timestamp, User, AV-query, ADB URL and SQL-query of the Analytic View in the automatically generated query results

Sorting the data

When you are working with a lot of data in the worksheet, you need to sort it to optimize your view.

Right click the cell you want to sort, go to Sort and select a sorting option (for example, Sort Oldest to Newest and Sort Newest to Oldest).

Drilling the data

Many of the results in the worksheet represent hierarchical data structures. The metadata specifies these hierarchies, and this enables you to access the different levels of detail within them. Drilling is a way to navigate through data in views quickly and easily.

  • Drill down to display data in more detail, which displays more members.
  • Drill up to display less data.

When you drill down in a table, the detail level data is added to the current data.

For example, when you drill from a month, the table displays data for the month and for the days in that month.

  1. Hover over the cell in the spreadsheet which contains + sign.
  2. Click the + sign besides the member you want to drill.

The details are added to the pivot table.

You can now create, manage, and run queries directly with the analytic views in the autonomous database and create powerful data driven reports.

FAQs for Troubleshooting errors with Excel Add-in

If you experience issues with Oracle Autonomous Database Add-in for Excel refer frequently asked questions mentioned in this section to identify and resolve issues.

Why is the My Add-ins icon from the Insert ribbon in the MS Excel workbook greyed out?

Even before installing the Excel add-in, sometimes the My Add-ins icon from the Insert ribbon in the MS Excel workbook appears to be greyed out.

  1. From the File menu in Excel ribbon, go to Account and select Manage Settings from the Account page.
  2. Ensure that you select the Turn on optional connected experiences.
  3. From the File menu in Excel ribbon, go to Options and select the Trust Center option from Excel Options.
  4. Click Trust Center Settings and ensure that you deselect Disable all Application Add-ins (if selected) from the Add-ins tab in the Trust Center dialog box.
  5. Select the Trusted Add-in Catalogs menu from the Trust Center dialog box and ensure that you deselect the Don’t Allow any web add-ins to start checkbox.

Why doesn’t the sign-in page of the Excel Add-in load or appear?

At times you might encounter issues with the Excel Add-in even after it is loaded correctly. For example, an add-in fails to load or is inaccessible. Check the compatibility version of the Excel and the operating system you use.

If the compatibility is correct and the sign-in page to the Excel Add-in still does not show up, or it does not load properly, we recommend applying all pending Windows, Office, and browser updates.

  1. From the Windows Start menu, select Settings, Update & Security, and then Windows Update.
  2. If updates are available on the Windows Update page, review the updates and click Install Now.

Note:

The details of applying Windows updates can vary from version-to-version and if required, check with your system administrator for assistance.

Why doesn’t the add-in work correctly after re-installing?

Configure the Excel trusted Add-in catalog to set the add-in correctly after re-installation.

To configure the Excel add-in, check or remove the add-in if it is pointing at a wrong location in the Trusted catalog address. This address should be the same as the location (share path) of the shared manifest folder.

Click Excel’s File> More> Options>Trust Center >Trust Center Settings> Trusted Add-in Catalogs
Description of adp-trusted-catalogs.png follows
Description of the illustration adp-trusted-catalogs.png

Checking is only required the first time you use the installer, or if the shared manifest folder is changed. The change occurs during uninstalling and re-pointing to a new ADB.

To remove the catalog from the trusted table and add a new catalog pointing to a different address:
  • Select the Catalog you want to remove from the trusted catalog table and click Remove.
  • Enter the correct share path of the shared manifest folder in the Catalog url field and click Add catalog to add the shared folder to trusted catalog.

Restart Excel to make the new shared folder active to access the add-in.

Why doesn’t the add-in work correctly even after configuring the Excel trusted Add-in catalog?

Let’s say you configure the Excel trusted add-in catalog after re-installing the add-in but even then, it does not load correctly. Sometimes the database server changes are not reflected in Excel even after you set the share path of the shared manifest folder as a trusted add-in catalog. Clear the Office cache to resolve this issue.

Refer to this page https://docs.microsoft.com/en-us/office/dev/add-ins/testing/clear-cache#clear-the-office-cache-on-windows to clear the Office cache on Windows and Mac.

Clearing the Office cache unloads the Excel add-in. Install the add-in and check the configuration of the Excel trusted add-in catalog. This should solve the issue of incorrect loading of the Excel add-in.

Oracle Autonomous Database add-on for Google Sheets

The Oracle Autonomous Database add-on for Google Sheets enables you to query tables using SQL or Analytic Views using a wizard directly from Google Sheets for analysis.

The data retrieved from the Autonomous Database is available locally in Google Sheets for further analysis. The results are stored in the local copy and cannot be written back to the Autonomous Database. You can run custom SQL queries or query Analytic Views and view their results in the worksheet. The add-on allows you to apply a filter on the query results, perform table joins and calculations.

How does the add-on for Google Sheets work?

To query an Analytic View or Tables from the Autonomous Database, you must select an Analytic View or Table to work with. While retrieving data from the Analytic View, you can configure the query according to your requirements. You can select specific hierarchies and create your own calculations on the wizard. The add-on configures your query and returns the result to the Google Sheets. You can save the results of your queries locally in the Google Sheet. The add-on can also be used to query the schema directly to which you have access. You can also view reports and analyses you create in the Data Analysis menu in the Data Studio tool using the Web UI.

You need to enable Web Access on Autonomous Database account to use the add-on. You must have the CONNECT, DWROLE, and RESOURCE roles granted in the SQL worksheet to access the Google Sheets add-on.

Install the Oracle Autonomous Database add-on for Google Sheets

Before you can install the Oracle Autonomous Database add-on for Google Sheets, download the oracleGoogleAddin zip file from your Database Actions instance.

You can now view a zip file in the Downloads folder of your system. Extract the contents of the zip file onto your system.

To setup the Oracle Autonomous Database add-on for Google Sheets, you must import the files in the oracleGoogleAddin folder to Google Apps Script.

Note:

Importing the files is a one-time activity and that, typically, this is done by an administrator.

After you import or upload the files to Google Apps Script follow these steps to complete the setup of Oracle Autonomous Database add-on for Google Sheets:

Upload oracleGoogleAddin files to Google Apps Script using Clasp

To upload all the files present in the oracleGoogleAddin folder, you must use Command Line Apps Script Project (clasp).

Clasp is an open-source tool to develop and manage the Google Apps Script projects from your terminal.

Note:

Clasp is written in Node.js. and distributed via the Node Package Manager (NPM) tool. It is required to install Node.js version 4.7.4 or later to use clasp.
  1. Enter sheet.new in the address bar of the web browser to open Google Sheet. Make sure you are logged in with your Google account.
  2. Select Apps Script from the Extensions menu. You can view the Apps Script editor window.

  3. Select the Code.gs file in the Apps Script editor window which already exists by default. Click on the vertical dots besides the Code.gs file. Select Delete to delete the existing Code.gs file.
  4. After you install Node.js, enter the following npm command in the command prompt to install clasp. You must enter this command in the location where you have downloaded and extracted the oracleGoogleAddin folder.
    C:\Users\username\Desktop\oracleGoogleAddin>npm install @google/clasp -g

    To run the command as an administrator for UNIX- and Linux-based systems, enter the following command:

    sudo npm install @google/clasp -g

    After you install clasp, the clasp command is available to use from any directory on your computer.

  5. Enter the following command to login and authorize management of your Google account's Apps Script projects.
    clasp login
    Once this command is run, the system launches the default browser and asks you to sign into your Google account where your Google Apps Script project will be stored. Select Allow for clasp to access your Google Account.

    Note:

    If you have not enabled the Apps Script API in Google Apps Script, the above command will not be successful. Enable the API by visiting https://script.google.com/home/usersettings site and enabling the Google Apps Script API by selecting the On button.
  6. In your existing Google Apps Script project, click the Project Settings in your left pane. Click Copy to Clipboard to copy the Script ID under IDs.
  7. Go back to the command prompt and enter the following command with the Script ID you copied in the previous step:
    clasp clone <Script ID>
  8. Push all the files from your folder to the Google Apps Script files by specifying the following command:
    clasp push

    This command uploads all of the script project's files from your computer to Google Apps Script files.

  9. Go to the newly created Google Sheet and click on the Extensions menu and select Apps Script. Under Files, you can view all the files present in the oracleGoogleAddin folder.
  10. After you import or upload the files to Google Apps Script follow these steps to complete the setup of Oracle Autonomous Database add-on for Google Sheets:

Deploy the Google script as a web app

After all the files from the oracleGoogleAddin folder are imported or uploaded to the Google Apps Script files, you must deploy the Google script as a web app.

To deploy the Google script as a web app:
  1. Click on the Extensions menu in the Google Sheet you are working on and select Apps Script. This opens the window.
  2. Click Deploy button on the top right and select New deployment. A New deployment window opens.
  3. Next to Select type, click the settings icon and select Web app.
  4. Under Configuration, specify Description of the deployment in the Description field. For example, Web app deployment.
  5. Under Web app , select your Google account that you have used to login from the Execute as drop-down. Optionally, you can choose anyone who has access to this deployment.
  6. Select Deploy.

    Note:

    • If you receive a window which asks you to Authorize access, select it. This will redirect you to the Google accounts page where you need to select your Gmail account.
    • Click Advanced and select the Go to Untitled project (unsafe) link.
    • Selecting the link opens a new window which ensures that you trust the application. Click Allow to continue.

    If you Authorize access at this stage, you need not follow steps 2-4 in the Authorize Google Sheets to use Autonomous Database.

  7. Click Done, to close the New Deployment window.
  8. Click Deploy button on the top right and select Manage Deployments.
  9. On the Manage Deployments page, you can view a Web app URL. Use the Copy to Clipboard to copy the Web app URL. For example, here is a sample of the web app URL:

    After the deployment completes, you will view a Web app URL in a new New deployment window. Use the Copy to Clipboard to copy the Web app URL. For example, here is a sample of the web app URL:

    https://script.google.com/macros/s/AKfycbwFITvtYvGDSsrun22g7TrbrfV-bUVoWKs7OrA_3rtRAlmcGFe8bejNprZML7gFPzQ/exec

    This is the Web application deployment URL. You will paste this value in the Google Sheet Redirect URL field you view in the Download Connection File section of Working with Connections when you download the connection file from the Database Actions or when you manually create a connection from the Google Sheet to the Autonomous Database.Description of download-connection-file.png follows
    Description of the illustration download-connection-file.png

  10. You can close the Apps Script browser tab at this point and navigate back to the Google Sheets browser tab. You are now ready to create a connection to the Autonomous Database.
  11. Ensure to save the worksheet after you upload all the files to Apps Script. Click the Refresh button once you have uploaded all the files. You can now view a new menu Ask Oracle in the Google Sheet.Description of ask-oracle-menu.png follows
    Description of the illustration ask-oracle-menu.png

    Note:

    Generate OAuth Client ID and OAuth Client Secret fields by using the UI.

Generate Client ID and Client Secret using UI

You can alternatively obtain the client_id and client_secret values using the UI.

You generate the client keys by accessing the Autonomous Database instance URL appending with oauth/clients.

For example, if your instance is "https://localhost:port/ords/schemaName", you need to sign in to the link "https://localhost:port/ords/schemaName/_sdw/?nav=rest-workshop&rest-workshop=oauth-clients" to generate new client. Be sure to include the trailing slash.

  1. Sign in to Database Actions with "https://machinename.oraclecloudapps.com/ords/SchemaName/oauth/clients/" link. You can view an OAuth Clients page in the link "https://localhost:port/ords/schemaName/_sdw/?nav=rest-workshop&rest-workshop=oauth-clients".
  2. Click +Create OAuth Client button to create a new client.Description of create-client.png follows
    Description of the illustration create-client.png
  3. From the Grant type drop-down, select the type of client connection you want. You can select the following options:
    • AUTH_CODE: Select this option for implicit connection. Use this response type when the autonomous database is in a private subnet or within a customer firewall.
    • IMPLICIT: Select this option for explicit connection. This is the more secure method and is preferred to use.
  4. Enter the following fields. The fields with an asterisk (*) are mandatory:
    • Name: Name of the client.
    • Description: Description of the purpose of the client.
    • Redirect URI: web application deployment URL
    • Support URI: Enter the URI where end users can contact the client for support. Example: https://script.google.com/
    • Support Email: Enter the email where end users can contact the client for support.
    • Logo: Select an image from your local system to insert a logo for your new client.
  5. Navigate to the Roles tab to select the roles of the client. This is not a mandatory field. You have the following role options:
    • OAuth Client Developer
    • RESTful Services
    • SODA Developer
    • SQL Developer
    • Schema Administrator
  6. Progress to the Allowed Origins tab. Specify and add the list of URL prefixes in the text field. This is not a mandatory field.
  7. Progress to the Privileges tab to add any privilege. You do not require any privilege to create an OAuth Client.
  8. Click Create to create the new OAuth Client. This registers the OAuth Client which you can view on the OAuth Clients page.Description of new-client.png follows
    Description of the illustration new-client.png
  9. Click the show icon to view the Client ID and the Client Secret fields.

Working with Connections

From the launchpad of your Database Actions instance, select the DOWNLOAD MICROSOFT EXCEL/ GOOGLE SHEETS ADD-IN Card. Click the Download Connection File button in the Google Sheets tab of the Downloads page to import the connection file to the Google Add-in.

Download Connection File

This connection file will allow you to connect to the Autonomous Database with the logged in user. You can import only those connection files to Google Add-ins that you download from the current autonomous database instance.Description of download-connection-file.png follows
Description of the illustration download-connection-file.png

Selecting the Download Connection File button opens a Download Connection File wizard. Specify the following field values in the wizard:
  • Google Sheet Redirect URL: This is the Web application deployment URL you copied from the Deploy the Google Script as a Web app section.
  • Response Type:
    • Select Token as the response type when the autonomous database is in the private subnet or within a customer firewall. When creating a connection using this response type, choose the connection type as implicit.
    • Select CODE as the response type when you need a more secure method. This method is preferred to use. When creating a connection using this response type, choose the connection type as explicit.

Click Download to download the JSON connection file in your local.

You can import this connection from the Import tab in the Connections wizard. This will automatically generate all the field values without having to manually fill in the fields.

Create Multiple Connections with Google Sheets

The Oracle Autonomous Database add-on for Google Sheets enables you to connect to multiple autonomous databases with a single add-on using the Connections feature.

The add-on connects to Google Sheets by providing authentication to Google. Multiple users or databases can connect simultaneously to the add-on, however, only one connection can remain active.

The connection icon enables you to manage one or more connections to databases in your Google Sheets. You can connect, edit, duplicate, and remove a connection. The Add-on connects with the database using implicit and explicit types of connections. You use the OAuth credentials to access the Oracle Autonomous Database for Google Sheets. You can access Autonomous Database using OAuth authentication. You will need the OAuth Client ID and OAuth Client Secret values to authenticate and authorize Google Sheets to use the Autonomous Database. This is an explicit connection. To access the autonomous database implicitly, you will just need an OAuth Client ID.

You can generate the OAuth Client ID and OAuth Client Secret fields by following method:Using UI. You can also download the connection file and import the connection from your system. For more details, refer to the Download Connection File section in Working with Connections.

The above methods require a web application deployment URL. You obtain this URL when you deploy a Google Script as a web app.

The following sections demonstrate how to connect using implicit and explicit connections. Google Sheets needs permission to access the Autonomous Database. You must first complete the authorization to connect to the autonomous database. The add-on requires one-time authentication for the setup.

  1. On the Google Sheet, click Ask Oracle and select Connections.
  2. Selecting Connections requires one-time Google authentication. Clicking Connections opens a pop-up window that asks your permission to run the authorization. Click Continue.Description of auth-continue.png follows
    Description of the illustration auth-continue.png
  3. You will now view a window that informs you that the application requests access to sensitive information in your Google account. Click Advanced and select the Go to Untitled project (unsafe) link.
  4. Selecting the link opens a new window which ensures that you trust the application. Click Allow to continue. You have now completed the setup.
  5. Select Connections from Ask Oracle menu in the Google sheet.
  6. On the Connections wizard, click Add Connection to add a connection.Description of add-connection.png follows
    Description of the illustration add-connection.png
  7. Selecting Add Connection opens an Add Connection wizard in the connection list panel of the Connections wizard.Description of add-connection-fields.png follows
    Description of the illustration add-connection-fields.png
  8. Specify the following field values in the wizard:

    Connection Name: Enter the name of the connection. For example, TestConnection.

    Autonomous Database URL: Enter the URL of the Autonomous Database you wish to connect to. For example, “https://domainname/ords/username/sign-in/

    In the OAuth Client Grant Type field, select one of the two options based on the type of connections you want. Refer to the Generate Client ID and Client Secret using UI section.

    This option varies with implicit and explicit connections.

    Implicit: Select this option for implicit connection. Use this response type when the autonomous database is in a private subnet or within a customer firewall.

    Authorization Code: Select this option for explicit connection. This is the more secure method and is preferred to use.

    When you select the Implicit option, you can view the following fields:Description of implicit.png follows
    Description of the illustration implicit.png

    OAuth Client ID: client_id you generate using the Create New Client wizard in the UI. Refer to the Generate Client ID and Client Secret using the UI section.

    Schema Name: Specify the name of the schema.

    When you select Authorization Code, you can view the following fields:

    Description of explicit.png follows
    Description of the illustration explicit.png

    OAuth Client ID: client_id you generate using the Create New Client wizard in the UI. Refer to the Generate Client ID and Client Secret using the UI section.

    OAuth Client Secret: client_secret you generate using the Create New Client wizard in the UI. Refer to the Generate Client ID and Client Secret using the UI section.

    Schema Name: Specify the name of the schema.

    Click Save.

    After you click Save you can view the new connection in the connection list panel. The connection list displays the name of the connection, the name of the schema, and the OAuth type you grant. However, it is still in a disconnected state.

  9. Click the three vertical dots beside the connection name and perform the following operations:

    Connect: Select Connect to connect to the Autonomous Database and change the status of the connection to active. Selecting Connect opens the sign in page of the autonomous database. After you login you will view a page that says that the database access is granted to you. Close the window and return to Google Sheets. You will now see that the connection is active.

    Edit: Select Edit to update any value of the connection. Click Save to update the edited values.

    Duplicate: Select Duplicate to create a duplicate connection.

    Remove: Select Remove to remove the connection from the connection list.
  10. You can export an existing connection without having to add a connection again. Click the Export tab in the Connections wizard to export the selected connection, select the connection you want to export, and click Export.Description of export.png follows
    Description of the illustration export.png

    The exported connection downloads in your local system. The connection file is saved as spreadsheet_addin_connections.json.

  11. Import an existing connection by clicking the Import tab in the Connections wizard. Click the drop connection area and drag and drop the connection file saved in your local system to import the connection. You can import the Connection File you downloaded from the Download Connection File section in Working with Connections. Click Import.Description of import.png follows
    Description of the illustration import.png

    After you import the connection, you can view the connection in the list of connections.

    You can view the new connection in the Connection. Select the three vertical dots beside the connection name and click Connect to connect to the database.

Authorize Google Sheets to use Autonomous Database

After your identity is determined using OAuth authentication, Google Sheets needs permission to access the Autonomous Database.

The client_id and client_secret values you generate during OAuth authentication is used for authorization.

  1. Click on the Ask Oracle menu in the Google Sheet you are working on and select Register. This requires one time Google authentication.
  2. Clicking Register opens a pop-up window which asks your permission to run the authorization. Click Continue. Selecting Continue will redirect you to the Google Accounts page where you need to select your Gmail account.
  3. You will now view a window which informs you that the application requests access to sensitive information in your Google account. Click Advanced and select the Go to Untitled project (unsafe) link.
  4. Selecting the link opens a new window which ensures that you trust the application. Click Allow to continue.
  5. You have now completed the setup. Select Register from Ask Oracle menu in the Google sheet.
    This opens an Oracle Autonomous Database wizard in the Google sheet. Specify the following fields:
    • ADB URL: Enter the ADB URL. For example, https://DBADDRESS.oraclecloudapps.com/ords/USER.
    • OAuth Client ID: client_id you generate during authentication.
    • OAuth Client Secret: client_secret you generate during authentication. Refer to the Create Connections with the Google spreadsheet section for more details.
    Description of adb-google-wizard.png follows
    Description of the illustration adb-google-wizard.png
  6. Select Authorize.

After successful authorization of the credentials, you can now view Query wizard, Native SQL and Analyses and Reports, Clear Data, Delete All Sheets, About, and Sign Out menu items under Ask Oracle.

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 a Query by 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 also have options to add filters and calculated measures to the query and view the query 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 enables you to view query results that can be customized with selected columns using a faceted filter. For example, suppose you are looking at a customer sales report that includes a breakdown of sales by menu item. In that case, you can select a menu item (AVs or Tables) and expand it to view it drilled down containing education details, discount type, and payment method details specific to the selected menu item. You want to view the income level, marital status and operating system of all the customers. The AVs can be drilled down to hierarchies and the tables can be drilled down to columns.

To run a custom query using the add-on:
  1. On the Google Sheet, select the menu item Ask Oracle.
  2. Select Data Analysis. Selecting Data Analysis opens a Data Analysis wizard.
  3. On the Data Analysis wizard, select Query from the drop-down, the schema you wish to use from the drop-down, and the table on which you perform the query. For example, if you want to view all the columns of the CUSTOMER_CA table from the qteam schema, you will select, Query from the Query or AV drop-down, qteam from the schema drop-down and CUSTOMER_CA from the table drop-down.

  4. You will view the default query in the query editor area. You can select any of the three modes to visualize the results of the SQL query report you generate:

    • Base Query: This type of view is by default. 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 which enables you to drag and drop selected columns from the Table browser. By dropping the selected columns in the drop zone, you can 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 results of the SQL query 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.
  5. Click the funnel icon (Faceted Filter) to add filters to the result. The wizard generates a filter for each value in the column that is 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 of males, click the faceted filter and Male under Gender. Click Save to view the results.

  6. Click Run to view the results in the worksheet.

To query an Analytic View and explore the Data Analysis menu in the Google Sheets:
  1. On the Google Sheet, select the menu item Ask Oracle > Data Analysis. Selecting Data Analysisopens a Data Analysis wizard in the Google task pane.
  2. Select AV from the AV or Query drop-down, select qteam from the schema drop-down, and the AV from the available AV's.

  3. You can select any of the two 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 which enables you to drag and drop selected columns from the Table browser. By dropping the selected columns in the drop zone, you can 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.
    • 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 selected 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 Filter list. The wizard generates a filter for each value in the column that is 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 movies with the genre as Drama, select Drama from the faceted filter and click Save.
    Description of ff-av.png follows
    Description of the illustration ff-av.png

  5. Click Run to view the results in the worksheet you select consisting of the Drama genre.

Run SQL Queries

The Oracle Autonomous Database add-on for Google Sheets lets you run SQL queries to work with your data in a Google Sheet. With the add-on, you can type your SQL code in the SQL editor area and click Run to run the command.

The add-on loads the result in the Google Sheet. The time taken to load the results depends on the number of records and the complexity of the query.

To run a query using the add-on, open Google Sheet and open a blank workbook.
  1. In the Google Sheet, select the menu item Ask Oracle.
  2. Select Native SQL to type and run the SQL command.
  3. The Oracle Autonomous Database wizard opens Tables and Views icons and a search field beside it.Description of native-sql-tables.png follows
    Description of the illustration native-sql-tables.png
  4. Select Table to view all the tables in the database. Perform the same operations for Views.
  5. You can right click on the table whose data you want to query and choose Select to view all the columns of the table. The column names will be displayed in the Write a Query section. You can click on the table and view individual columns as well. Click the Run button to run the commands in the query editor. You can also select the consumer group options such as, low, medium, and high. You can edit the existing query from the query editor.Description of native-sql-select.png follows
    Description of the illustration native-sql-select.png
  6. Click Run to run the query and display the results in the worksheet. You can click the + sign beside the Select worksheet drop-down to display the results in a new worksheet.
  7. The worksheet also displays information such as the timestamp, the user who creates and runs the query, the ADB URL and the SQL Query.Description of nativesql-results.png follows
    Description of the illustration nativesql-results.png

Reporting and Analysis in Google Sheets

You can view Reports and Analytic Views or visualize data for analysis purposes.

The reports and charts can be viewed in of a different range of charts, namely, Bar Charts, Area charts, Line Charts, and Pie Charts. Reports provide analytical insights that you create from the Analytic Views. An Analysis can contain multiple reports. The Analyses and Reports icon enables you to retrieve Analyses and Reports from the Autonomous Database.

View Analysis

To view Analysis and explore the Analyses and Reports menu:
  1. Select Analysis under Output format.
  2. Use the Select an Analysis drop-down to choose the Analysis you want to view.
  3. Click View Analysis to view the analysis in the Google Sheet.Description of view-analysis.png follows
    Description of the illustration view-analysis.png

View Report

To view Reports :

  1. Select the Analyses and Reports menu from the Ask Oracle menu. This opens the Analyses and Reports wizard.
  2. Select Report under Output Format.
  3. Use Select an Analysis drop-down under Choose Analysis to choose the Analysis you want to view.
  4. After you select the Analysis, to view the report present in the Analysis, click the Select a report drop-down and select the report you wish to view.
  5. Click View Report Detail to view more information about the report: Analytic View Name, Type of visualization, and rows, columns, and values you select while creating the report.Description of reports.png follows
    Description of the illustration reports.png
  6. Select the worksheet from the drop-down where you would want to view the report.
  7. Click View Report to view the report in the sheet you select from the previous step. You can now view the report in the worksheet you select.Description of view-reports-sheet.png follows
    Description of the illustration view-reports-sheet.png

Clear Sheet

Once the add-on runs the query and retrieves the data into the worksheet, you can view the Timestamp, User, AV-query and SQL-query of the Analytic View in the automatically generated query results.

The worksheet displays the result of the query at one go. Consider, for example, if you want to modify the query and generate the query result in the same sheet. You must clear the existing data in the sheet.

To clear query results in the Google sheet, click the menu item Ask Oracle and select Clear Sheet.

This option erases all types of data including images and formatting in the selected sheet.

Delete all sheets

Use this option to delete all the sheets existing in the spreadsheet.

Select Delete All Sheets from Ask Oracle menu to delete all sheets from the spreadsheet.

About menu

Use this option to view details about the add-in

The About menu from Ask Oracle displays if the add-on is connected to server, ORDS version, the add-on version, ORDS Schema version and the database version.

Sign Out

Use this option to sign out.

Select Sign Out menu from Ask Oracle to sign out from the database session.

Share or Publish

Once you generate the query results in the Google Sheet, you can share it with other users. With sharing, creates a copy of the worksheet and sends it with the design tools hidden and worksheet protection turned on.

The recommended steps to take before you publish are:
  1. Review and inspect to remove personal or sensitive information.
  2. Save the source version of the worksheet. Consider adding a file name suffix of –src for the source worksheet. Then, remove the suffix in the distributed copy.

    Once you are ready to distribute to the users, click Share.

    • In the Share window that appears, add the user email IDs with whom you want to share the Sheets and to whom you want to provide permissions for accessing the Sheets.
    • You can select the permission of the users from the drop-down. Select Editors if you want the user to share the worksheet. Viewers and commenters can see the option to download, print and copy but not share the sheets.
    • Select Notify people check-box to notify the users of the share.
    • Under General access, select Restricted from the drop-down to share it with people who have access to the link. You could also share it with people who do not have access by selecting Anyone with the link from the drop-down.