The Data Analysis Tool
The Data Analysis tool enables you to create Analytic Views with multidimensional metadata.
To reach the Data Analysis page, select the Data Analysis menu in the Data Studio tab of the Launchpad.
Description of the illustration data-analysis-pane.png
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 the illustration dataanalysis-homepage.png
Analyses
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.
- 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 the illustration adp-analytic-views.png
- 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
- 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
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. - Creating Analytic Views
You can create Analytic Views and view information about them. You can also edit and perform other actions on them. - 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. - Viewing Analyses
Analyses provide you an insight into the performance of your data. - Workflow to build Analyses
Here is the workflow to build an analyses. - 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. - Creating Reports
A single report you generate analyzes an AV based on the Levels and measures you select. - Using Calculation Templates
The Data Analysis tool provides templates for all of the calculations typically in demand for business intelligence applications. - 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 also directly run SQL queries to view their results in the worksheet. - 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.
Parent topic: Analyze
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.
Parent topic: The Data Analysis Tool
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.
- 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
- 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.
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.
Parent topic: The Data Analysis Tool
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.
Parent topic: The Data Analysis Tool
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.
Parent topic: The Data Analysis Tool
Workflow to build Analyses
Here is the workflow to build an 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.
Parent topic: The Data Analysis Tool
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.
- On the Data Analysis home page, click the Create Analysis button.
- You can make changes to existing Analyses by adding different values from hierarchies and measures.
- 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. - 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.
- 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.
Parent topic: The Data Analysis Tool
Saving Analyses
You can save the personalized settings you made for the Analysis and use them on any other Analyses for future reference.
- Open the analysis for editing from the Data Analysis home page. Select Edit from the Analysis tile you wish to edit.
- Click on the Save As icon. Enter a descriptive name for your Analysis.
- Click Save.
Parent topic: Creating Analyses
Creating Reports
A single report you generate analyzes an AV based on the Levels and measures you select.
- Open the Analysis for editing from the Data Analysis home page. Select Edit from the Analysis tile you wish to edit.
- 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.
- Click on the report to select it. The resize arrow in the report resizes the report window.
- Click the cross icon on the selected report to delete the report from the Analysis.
- The header displays the name of the Analytic View you select.
- You can expand or collapse the report with their respective arrows.
- 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.
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 the illustration sql-output.png
-
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.
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 the illustration performance-output.png
- 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.
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.- 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.
- 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
- 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. - Creating Reports on a Query
This section describes the steps to create reports on an SQL query. - Creating Reports on an Analytic View
This section describes the steps to create reports on an Analytic View:
Parent topic: The Data Analysis Tool
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.
- 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 the illustration table-browser.png - 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 the illustration run.png - 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_planwhen running an SQL statement using the Autotrace feature.
- 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 typeerror. 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.
- 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 typeerror. 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.
- 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 the illustration analyses-report.png
The Analyses page when you create a report on an Analytic View looks like this:
Description of the illustration analyses-av.png
The following topics describes how to create a report and access the Analyses page:
- You can create Reports using either of the following ways:
- Adding filters to a report you generate on an Analytic View
Parent topic: Creating Reports
Creating Reports on a Query
This section describes the steps to create reports on an SQL query.
- 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. -
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.
-
Add a filter to the report by displaying the Sales Records of only Asia region. Select Asia from the faceted search panel.
Description of the illustration filter.png - 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.
Parent topic: Creating Reports
Creating Reports on an Analytic View
This section describes the steps to create reports on an Analytic View:
- 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.
- 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.
- 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
- 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.
- You can select more filters by selecting the values from the faceted search panel, or by clicking the funnel.
- 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.
- Select Deselect All to remove all filters.
You can now view the original report result that does not consist of any filters.
Parent topic: Creating Reports
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. - Prior and Future Period
The Data Analysis tool provides several calculations for prior or future time periods. - Period to Date
Period-to-date functions perform a calculation over time periods with the same parent up to the current period. - 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. - Moving Aggregates
Moving aggregates are performed over the time periods surrounding the current period. - 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. - 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.
Parent topic: The Data Analysis 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
Parent topic: Using Calculation Templates
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
Parent topic: Using Calculation Templates
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
Parent topic: Using Calculation Templates
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
Parent topic: Using Calculation Templates
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
Parent topic: Using Calculation Templates
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
Parent topic: Using Calculation Templates
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,4for 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,5for 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,5for 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
Parent topic: Using Calculation Templates
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 also directly run SQL queries to view their results in the worksheet.
- Install the add-in on Mac
The Oracle Autonomous Database add-in for Excel is supported on Mac OS running Microsoft Office 365. - Install the add-in on Windows
The Oracle Autonomous Database Add-in for Excel is supported on Windows 10 and Windows 11 operating systems running Microsoft Excel 365. - Uninstall the add-in
The following section describes the steps to uninstall the Oracle Autonomous Database add-in. - Using Oracle Autonomous Database Add-in for Excel
After you install the add-in, a new ribbon tab, Autonomous Database appears in MS Excel. - Connection management
Each time you start the add-in for Excel, you must create a connection. - Import a Connection
You can import a connection file that you can download from the Database Actions launchpad. This file is in JSON format. - Add a connection
You can manually create a connection to an autonomous database. Adding a connection allows you to specify the connection credentials to the database in which you will connect to the schema of your Autonomous Database. - Share a connection
You can import or export a connection using the Import and Export buttons on the Connections panel. - 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. - 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 not write back to the Autonomous Database. - Data Analysis in Excel Sheet
The Data Analysis panel enables you to perform SQL queries. - FAQs for Troubleshooting errors with Excel Add-in
If you experience issues with Oracle Autonomous Database Add-in for Excel, refer to frequently asked questions in this section to identify and resolve issues.
Parent topic: The Data Analysis Tool
Install the add-in on Mac
The Oracle Autonomous Database add-in for Excel is supported on Mac OS running Microsoft Office 365.
To install the Autonomous Database Add-in for Excel, run the installer script file from your Autonomous Database instance by following the steps below:
-
Open the Database Actions Launchpad.
- On the Downloads tab of the Database Actions page, click the Download Microsoft Excel/Google Sheets Add-in pane.
- Click Download.
- Click the Microsoft Excel tab and select the Download Add-in button to download the oracleplugin.zip file.
- You can now view the zip file in the Downloads folder.
- Create a new folder named Add-in on your system.
- Extract the contents of the zip file in the Add-in folder.
Follow these steps to install the add-in.
- Quit Excel before you run the installer.
- Navigate to the install.sh file in the Add-in folder.
- Right-click install.sh and select the following options as shown : Open With -> Other… -> Enable: All Applications ->Utilities->Terminal
- On completion, close the Terminal window.
- Start Excel and open a new or existing workbook.
- From the Insert menu in the Excel ribbon, select the drop-down menu of My Add-ins.
A new Oracle Autonomous Database entry appears under the Developer Add-Ins dialog box.
Description of the illustration developer-add.png - Select Oracle Autonomous Database.
A new Autonomous Database ribbon tab appears in MS Excel.
Parent topic: Oracle Autonomous Database Add-in for Excel
Install the add-in on Windows
The Oracle Autonomous Database Add-in for Excel is supported on Windows 10 and Windows 11 operating systems running Microsoft Excel 365.
To 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.
- On the Downloads tab of the Database Actions page, click the Download Microsoft Excel/Google Sheets Add-in pane.
- Click Download.
- 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 file 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.
- Quit Excel before you run the installer.
- 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.
- Select Run as administrator.
Note:
You must have Administrator privileges to install the Excel add-in for Oracle Autonomous Database successfully. - Start Excel and open a new or existing workbook.
- From the Developer menu in the Excel ribbon, click Add-ins, select the SHARED FOLDER tab on the pop-up window and select Oracle Autonomous Database.
- After you install the add-in, a new Autonomous Database ribbon tab appears in MS Excel.
Note:
You can re-run the installer after the initial installation. Re-run the installer and choose the option of your preference. You can repair your existing installation by deleting it, selecting the installed trusted catalog or adding another manifest to the working installation.
Parent topic: Oracle Autonomous Database Add-in for Excel
Uninstall the 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:
- Delete the manifest.xml file from the folder located in %LOCALAPPDATA%\Oracle\Autonomous Database\manifest .
- Click Refresh in the Office Add-ins window to remove the Autonomous Database tab from MS Excel.
Note:
After uninstalling the Add-in, if you re-install it from a different Autonomous Database (ADB), the add-in attempts to load the old ADB. You must check if the shared manifest folder's location (share path) points to the correct location. For more details, refer to Configuring the Excel Trusted Add-in Catalog in FAQs for Troubleshooting errors with Excel Add-in.
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.
The Oracle Autonomous Database Add-in is uninstalled from Excel for Mac.
Parent topic: Oracle Autonomous Database Add-in for Excel
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 let 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 Settings to view the logging level settings of the Excel Add-in. You can also clear the logs or export the log files by copying the logging information to the clipboard.
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. It also shows version information for the database and Oracle Rest Data Services.
Click Native SQL to 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, and choose the output format from tabular and pivot formats.
Click Analyses and Reports to view the Analyses and reports the Excel Add-in created using the web UI.
Click Data Analysis to query an existing Analytic View and run queries.
Selecting Native SQL icon or Query Wizard icon from the ribbon launches the Oracle Autonomous Database wizard in the Excel task pane.
Parent topic: Oracle Autonomous Database Add-in for Excel
Connection management
Each time you start the add-in for Excel, you must create a connection.
The connections feature lets you manage and connect to multiple Autonomous Databases with a single add-in. Multiple connections can be created. However, only one connection can remain active.
The connection panel lets you connect to the Autonomous Database through a connection where you provide the login credentials and access the Autonomous Database.
- 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.
Note:
This is an implicit type of connection. Refer to Authenticate using Implicit connection to understand more about implicit connection.
- Refresh: You can refresh the connection with this icon. The green icon besides the connection name indicates that the connection is active. A red icon beside the connection name suggests 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.
Parent topic: Oracle Autonomous Database Add-in for Excel
Import a Connection
You can import a connection file that you can download from the Database Actions launchpad. This file is in JSON format.
- From the Downloads page of the Database Actions instance, click the Download Connection File icon to download the connection file in your system.
- The file is downloaded onto your system.
Note:
This connections file can be used with the add-in installed from the same Autonomous Database instance.- Select Connections from the Autonomous Database menu in the Excel sheet. This opens the Connections wizard.
- Click Import to import the connection file you downloaded from the Database Actions instance.
- 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 want to import from the file.
Note:
A connection file can have multiple connections in it. You can import a connection you downloaded from the same Autonomous Database instance. If you use the add-in to connect to a different Autonomous Database, you must manually create a connection. Refer to the Add a Connection section for more details. - Click ok to proceed.
- Click the three vertical dots beside the connection file and select Connect.
Note:
If you view a red icon beside the connection file, 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/"
- Specify the schema name in the username field and the corresponding password in the credentials screen you view. Click Sign in to log in to the autonomous database.
You will view “Active Connection” beside the connection name.
Parent topic: Oracle Autonomous Database Add-in for Excel
Add a connection
You can manually create a connection to an autonomous database. Adding a connection allows you to specify the connection credentials to the database in which you will connect to the schema of your Autonomous Database.
- Click on the Add button on the header of the Connections pane to add a connection. This opens an Add new connection dialog box.
- Specify the following fields on the Add new connection dialog box:
- Alias: Enter the Alias name for the Autonomous Database URL. For readability purposes, Oracle recommends using a name different name from the 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. Refer to the Generate the client ID for a connection section below for details.
- Click Save to save the connection.
You should be able to view the new connection now.
- Generate Client ID for a connection
The OAuth Client key is generated using SQL.
Parent topic: Oracle Autonomous Database Add-in for Excel
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 contains PL/SQL code that generates an OAuth Client ID. To create the Client ID, copy and run this PL/SQL code in the worksheet editor.
This section describes how to generate a client ID.
- On the Development section of the Database Actions Launchpad, select SQL card. This opens the SQL page.
Description of the illustration sql-card.png - Paste the implicit connection query template you copied as explained in the previous section. Here is a sample of the implicit connection query template in the image below.
- 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.
- In the worksheet editor, replace the"[PROVIDE_THE_SCHEMA_NAME]" text with your schema name in the variable name field.
- 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 otherwise, you might view errors that will cause the unsuccessful creation of the Client ID. The p_redirect_uri field is auto-generated and is different for each Autonomous Database.
- Click the Run Script icon on the worksheet toolbar to run the PL/SQL code.
Description of the illustration worksheet-editor.png - The following is the sample output you will view in the Script Output tab after you run the PL/SQL code.
- 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...
- Paste the Client ID on the Client ID value field of the Add new connection dialog box. Refer to the Add a connection for more details on this.
- 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 no connection. Whereas a check mark indicates the connection is successful.
- An actions icon rightmost to the connection panel.
Description of the illustration adp-connection-panel.png
- 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.
You will view a notification page that says the authorization of the Excel with Autonomous Database is successful.
- Activate:
There can be only one active connection when you connect to multiple Autonomous Databases. Click Activate to make the selected connection functional. 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 connection status.
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 connection-based information. Selecting Edit opens the same dialog you view when you add a connection. Edit any 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 beside 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.
Managing the Excel Add-in Panel
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. This option allows you to resize the wizard window by moving the double-headed arrow sideways. The wizard expands when you move the arrow to the left and contracts when you drag it to the right.
Click Move out of Tab to move the add-in from the task pane.
Click Close to close the wizard.
Parent topic: Add a connection
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 beside 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 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 connection 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) to your local device. The exported connection file is named spreadsheet_addin_connections.json.
Parent topic: Oracle Autonomous Database Add-in for Excel
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 current worksheet or different worksheets.
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 and run them. The worksheet displays the results of queries from the retrieved data in tabular format.
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 must log in again. You can change the active connection from the connections panel. The image shows the results from a single query, but you can insert many queries in a single workbook.
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.
- In the Excel ribbon, select the Autonomous Database.
- 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.
- Select Table to view all the existing tables in the schema. Click Views to see the current views in the schema.
- You can right-click the table whose data you want to query and choose Select to view all the table's columns. 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 SQL query 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. - You can click + sign beside the Select worksheet drop-down to display the results in a new worksheet.
- The worksheet also displays the timestamp, the user who creates and runs the query and the autonomous database URL.
To run another query follow these steps:
- Click the eraser icon to clear the previous query from the SQL editor and write a new query.
- 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 work on the same sheet, the Add-in refreshes the data in the existing worksheet.
- 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 asks if you want to view the rest of the results.
Description of the illustration adp-entire-result-set.jpg
Select Yes to view the entire result set. Loading all the data may take a while. You must fetch all data before working with Pivot tables, or 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.Parent topic: Oracle Autonomous Database Add-in for Excel
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 not write back to the Autonomous Database.
You can query an Analytic View to visualize the result data in the worksheet. You can search for the Analytic View, and select measures, hierarchies, and levels from the query. You can also add filters and calculated measures to the query and view the 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:
- 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 the illustration av-new.png - Filter panel: The Filter panel displays to the right of the Analytic View panel when you click Next on the wizard. You can create filter conditions to filter the data and also add manual calculations to the Analytic View query in this panel.
Description of the illustration filter-new.png - Query Result panel:When you click Next on the wizard, the Query Result panel displays to the right of the Filter panel. You run the query once you select the filter criteria and determine what calculated measures to add to your 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 a Pivot table.
Description of the illustration query-result-new.png
To query an analytic view and explore the Query Wizard menu in the MS Excel ribbon:
- On the ribbon, select the Query Wizard icon.
- Selecting the Query Wizard opens an Oracle Autonomous Database dialog box in the Excel Task Pane.
- 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.
- Select your choice of measures, hierarchies, and levels the available measures, hierarchies and levels associated with the Analytic View. Click Next.
- The wizard window progresses to the Filter pane where you can add or edit filters to query.
- 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 containing your selected column members. 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, the filter uses values from the column greater than 100,000. You can use this information in an analysis to focus on products performing well. For multiple values use “:” as the separator.
- Click Add Filter to add another filter condition.
- 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.
- Click Next to progress to the Query Result.
- You can view, edit, and review the query you have generated from the Query Review editor.
- Select Remove empty columns to remove columns with no values returned in the result.
- Select Column per level to retrieve all hierarchy levels in a single column.
- Select the worksheet from the drop-down where you want to view the result.
- Click Execute to run the query.
- You can view the result of the query in the worksheet you select.
- You can always modify the query in the Oracle Autonomous Database dialog box editor even after results are generated.
- Select Table in the Query Result pane to view the results in the worksheet in a tabular format.
- Select Pivot in the Query Result pane to view the results in a new worksheet in Pivot format.
View the results in Pivot tables
A Pivot table view is interactive and allows you to transpose rows and columns. A pivot table can summarize, sort, reorganize, count the total and perform an 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 the 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 the 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.
Parent topic: Oracle Autonomous Database Add-in for Excel
Data Analysis in Excel Sheet
The Data Analysis panel enables you to perform SQL queries.
The add-on enables you to receive a copy of data from the Autonomous Database to the Excel sheet. You can query an existing Analytic View and run SQL Query using the Oracle Autonomous Database wizard. You can retrieve the Analytic View and manipulate the query according to your requirements to visualize the result data in the worksheet. You can search for the Analytic View and select measures, hierarchies, and levels from the query. You can also add filters and calculated measures to the query and view the result in the sheet. By default, the data is retrieved in tabular format.
You can run custom queries. The add-on enables you to apply a filter to the query results. The add-in lets you view query results that can be customized with selected columns using a faceted filter.
-
On the Excel Sheet, select the menu item Autonomous Database.
- Select Data Analysis. Selecting Data Analysis opens a Data Analysis wizard. 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.
- You can select a column of the table and right-click the column, click Select to assist the add-in in forming a select query of the column from the table. Alternatively, you can drag and drop the selected column to the query area which enables the wizard to produce a select query of the column in the query display area.
You will view the default query in the query editor area.
- You can select any of the four modes to visualize the results of the SQL query report you generate. You can select any of the four modes to visualize the results of the SQL query report you generate:
- Base Query: This type of view is by default. The query written in the SQL editor is the Base Query.
- Table: You can view the SQL results in tabular form. By selecting this view, a column drop zone appears, enabling you to drag and drop selected columns from the Table browser. Moving the selected columns in the drop zone allows you to view only those columns in the Result data generated in the worksheet. Select the cross mark beside the Column name to remove it from the drop zone.
- Pivot: You can view the SQL query results in pivot format. By selecting this format, an X and Y drop zone appears where you can drag and drop the selected columns from the Tables browser to the drop area.
- Chart: You can view Area Chart, Bar Chart, Line Chart, or Pie Chart when you select this option.. The mappings displayed when you select one of the options are as follows:
- Orientation: Choose between horizontal and vertical orientation types from the drop-down list.
- X axis label and Y axis label: Optionally enter labels for X axis and Y axis.
- Click the funnel icon (Faceted Filter) to add filters to the result. The wizard generates a filter for each value in the column retrieved from the query result. You can filter different columns on the faceted filter panel and view the results in the worksheet to view only the data you wish to view. For example, to view the customer reports by Region, click the faceted filter and select Asia under
Region_ID. - Click Save to view the results. Click Back to go back to the main wizard.
-
Select Run to generate the results of the custom query in the worksheet. Click Pause to make any changes to the query, such as updating the columns of the table without updating the worksheet.
Perform aggregate functions using the Excel add-in
SUM, MIN, MAX, AVG, COUNT, and DISTINCT COUNT. In this example, we’re primarily going to focus on using the Data Analysis feature to gain insights from our sales data.
- Select Data Analysis. Selecting Data Analysis opens a Data Analysis wizard. 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.
Drag and drop the sales value to the query editor and click Chart to view the sales in chart format.
-
To calculate the maximum sale value, right-click the sales value and select Max from the list of available aggregate functions.
Click Run to generate the maximum sales amount in the chart format.
You will view the result generated in the excel worksheet.
- Select the menu item Autonomous Database > Data Analysis on the Google Sheet. This opens a Data Analysis wizard in the Excel sheet pane.
- Select AV from the AV or Query drop-down, select a schema you can access from the schema drop-down, and the AV from the available Analytic Views.
-
You can select any of the three modes to visualize the results of the AV query you generate:
- Table: You can view the AV query results in tabular form. By selecting this view, a column drop zone appears, enabling you to drag and drop selected columns from the Table browser. Moving the selected columns in the drop zone allows you to view only those columns in the Result data generated in the worksheet. Select the cross mark beside the Column name to remove it from the drop zone.
- Pivot: You can view the SQL query results in pivot format. By selecting this format, an X and Y drop zone appears where you can drag and drop the selected columns from the Tables browser to the drop area.
- Chart: You can view the results of the AV query in chart format. By selecting this format, an X and Y drop zone appears where you can drag and drop the chosen hierarchies and measures from the AV browser to the drop area.
Note:
You are allowed to drop measures in the Y-axis.
- Click Run to view the results in the worksheet . You can view the total Sales generated along with it’s year of generation.
Parent topic: Oracle Autonomous Database Add-in for Excel
FAQs for Troubleshooting errors with Excel Add-in
If you experience issues with Oracle Autonomous Database Add-in for Excel, refer to frequently asked questions 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 greyed out.
- From the File menu in the Excel ribbon, go to Account and select Manage Settings from the Account page.
- Ensure that you select the Turn on optional connected experiences.
- From the File menu in the Excel ribbon, go to Options and select the Trust Center option from Excel Options.
- Click Trust Center Settings and ensure that deselect Disable all Application Add-ins (if selected) from the Add-ins tab in the Trust Center dialog box.
- 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?
Sometimes you might encounter issues with the Excel Add-in even after loading it correctly. For example, an add-in fails to load or is inaccessible. Check the compatibility version of 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 does not load properly, we recommend applying all pending Windows, Office, and browser updates.
- Select Settings, Update & Security, and then Windows Update from the Windows Start menu.
- 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 the wrong location in the Trusted catalog address. This address should be the same as the shared manifest folder's location (share path).
Click Excel’s File> More> Options>Trust Center >Trust Center Settings> Trusted Add-in Catalogs
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 the 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, 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 the Excel add-in needing to be correctly loaded.
- What should you do if you cannot view My Add-ins icon from the Excel ribbon?
After installing the add-in once, if you cannot view the My Add-ins icon from the Insert ribbon and instead you view the Add-ins icon
Description of the illustration add-ins-icon.png.Follow these instructions:-
From the File menu in the Excel ribbon, go to Options and select the Customize Ribbon option from Excel Options.
-
Select All Commands drop-down from the Choose commands from drop-down.
-
Click My Add-ins and click Insert option from the Main Tabs list.
-
Click New Group to add a new menu item under the Insert menu. You will view a New Group (Custom) menu option added to the Insert menu.
- Click Rename to rename the newly created menu.
- Specify the name of the add-in. For example, My Addin. Click OK.
-
Click My Add-ins[Insert and Add-in] from All Commands list and select Add. My Add-ins menu is added to the newly created menu “MyAddin”.
- Click OK to save the changes.
- Clicking OK takes you to the main Excel sheet page where you can view “MyAdd-ins” menu under the Insert menu.
- Click My Add-ins. You can now view the Oracle Autonomous Database add-in loaded.
-
- What happens when you cannot view the latest added menu items in the Oracle Autonomous Database for Excel add-in?
Sometimes when you cannot view the changes updated in the latest version of the plug-in, you must:
- Select My Add-ins from the Insert menu on the Excel ribbon.
- Click the Shared Folder tab of the Office Add-ins dialog box. You will see the add-in under the Shared Folder list.
- Click Refresh.
After the add-ins are refreshed, you will receive a notification on the dialog that says “My Add-ins refreshed or updated”. The refresh button loads the manifest file again for the latest changes to appear.
- Click Close to close the dialog box.
Parent topic: Oracle Autonomous Database Add-in for Excel
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.
Note:
The Oracle Autonomous Database add-on for Google Sheets must comply with Privacy Policy. For information on details of privacy policy, see Oracle Autonomous Database Privacy Policy Details.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 custom 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 query the schema directly to which you have access. Using the Web UI, you can also view reports and analyses you create in the Data Analysis menu in the Data Studio tool.
To use the add-on, you must enable Web Access on the Autonomous Database account. You must have the CONNECT, DWROLE, and RESOURCE roles grant in the SQL worksheet to access the Google Sheets add-on.
- Install the add-on for Google Sheets
Before you install the Oracle Autonomous Database add-on for Google Sheets, download the oracleGoogleAddin zip file from your Database Actions instance. - Upload oracleGoogleAddin files to Google Apps Script using Clasp
To upload all the files present in the oracleGoogleAddin folder, you must use the Command Line Apps Script Project (clasp). - 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. - Download Connection File
To connect to the Autonomous Database, you can download a connection file from the Database Actions instance and import it to the Google Sheet add-on you have setup. - Connection Management in 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. - Generate Client ID and Client Secret using UI
In this section you use the Web UI to obtain theclient_idandclient_secret. - Authorize Google Sheets to use Autonomous Database
After your identity is determined using OAuth authentication, Google Sheets needs permission to access the Autonomous Database. - Data Analysis in Google Sheets
Selecting Data Analysis opens an Oracle Autonomous Database wizard in the Google sheet. - Run Direct 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. - Reporting and Analysis in Google Sheets
You can view Reports and Analytic Views or visualize data for analysis purposes. - 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. - Delete all sheets
Use this option to delete all the sheets existing in the spreadsheet. - About menu
Use this option to view details about the add-in - Sign Out
Use this option to sign out. - 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. - Oracle Autonomous Database Privacy Policy Details
This topic covers details for writing policies to control access to Autonomous Database resources.
Parent topic: The Data Analysis Tool
Install the add-on for Google Sheets
Before you install the Oracle Autonomous Database add-on for Google Sheets, download the oracleGoogleAddin zip file from your Database Actions instance.
- Open the Database Actions Launchpad.
- Under the Downloads tab, select the DOWNLOAD MICROSOFT EXCEL/GOOGLE SHEETS ADD-IN pane.
Description of the illustration download-add.png - This opens a Download screen with Microsoft Excel and Google Sheets tabs. Click the Google Sheets tab and select Download Add-in.
You can now view a zip file in the your system's Downloads folder. Extract the contents of the zip file onto your system.
Note:
Importing the files is a one-time activity, and 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 the Oracle Autonomous Database add-on for Google Sheets:
Parent topic: 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 the Command Line Apps Script Project (clasp).
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.- Enter sheet.new in the web browser's address bar to open Google Sheets. Make sure you are logged in with your Google account.
- Select Apps Script from the Extensions menu. You can view the Apps Script editor window.
- Select the Code.gs file in the Apps Script editor window, which already exists by default. Click on the vertical dots beside the Code.gs file. Select Delete to delete the existing Code.gs file.
- 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 -gTo run the command as an administrator for UNIX- and Linux-based systems, enter the following command:
sudo npm install @google/clasp -gAfter you install Clasp, the command is available from any directory on your computer.
- Enter the following command to log in and authorize managing your Google account’s Apps Script projects.
clasp loginOnce 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 the https://script.google.com/home/usersettings site and allow site and enabling the Google Apps Script API by selecting the On button. - 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.
- Go back to the command prompt and enter the following command with the Script ID you copied in the previous step as displayed in the image below:
clasp clone <Script ID> - Push all the files from your folder to the Google Apps Script files by specifying the following command:
clasp pushThis command uploads all of the script project's files from your computer to Google Apps Script files.
- Go to the newly created Google Sheet, click the Extensions menu, and select Apps Script. Under Files, you can view all the files in the oracleGoogleAddin folder.
-
After you import or upload the files to Google Apps Script, follow these steps to complete the set up of the Oracle Autonomous Database add-on for Google Sheets:
Parent topic: 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.
- Click on the Extensions menu in the Google Sheet you are working on and select Apps Script. This opens the window.
- Click Deploy button on the top right and select New deployment. A New deployment window opens.
- Next to Select type, click the settings icon and select Web app.
- Under Configuration, specify a Description of the deployment in the Description field. For example, Web app deployment.
- Under Web app , select the Google account you used to log in from the Execute as drop-down. Optionally, you can choose anyone who has access to this deployment.
-
Select Deploy.
Note:
- If you receive a window that asks you to Authorize access, select it. This will redirect you to the Google Accounts page where you must to select your Gmail account.
- Click Advanced and select the Go to Untitled project (unsafe) link.
- Selecting the link opens a new window, ensuring 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.
- Click Done to close the New Deployment window.
- Click the Deploy button on the top right and select Manage Deployments.
- 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 "https://script.google.com/macros/s/AKfycbwFITvtYvGDSsrun22g7TrbrfV-bUVoWKs7OrA_3rtRAlmcGFe8bejNprZML7gFPzQ/exec". This is the Web application deployment URL.
-
Save this URL, which you will use later in the Google Sheet Redirect URL field when downloading a connection file from Database Actions or manually creating a connection from the Google Sheet to the Autonomous Database.
Description of the illustration download-connection-file.pngFor details on selecting Response Type, see Download Connection File.
- You can close the Apps Script browser tab and navigate to the Google Sheets browser tab. You are now ready to create a connection to the Autonomous Database.
- Ensure you save the worksheet after uploading all the files to Apps Script. Click the Refresh button once you have uploaded all the files. You can now view a new Oracle Autonomous Database menu in the Google Sheets.
Description of the illustration ask-oracle-menu.png
Parent topic: Oracle Autonomous Database add-on for Google Sheets
Download Connection File
To connect to the Autonomous Database, you can download a connection file from the Database Actions instance and import it to the Google Sheet add-on you have setup.
- Navigate to the launchpad of your Database Actions instance, and 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.
-
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 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 step number nine of Deploy the Google Script as a Web app section.
- Choose a Response Type:
- Explicit Connection
You use the OAuth Client ID and OAuth Client Secret values to authenticate and authorize Google Sheets to use the Autonomous Database. Use this when you use CODE as the Response Type while downloading the connection file from the Database Actions page. This is the more secure method and is preferred to use if the Autonomous database has public access.
- Implicit Connection
You will need an OAuth Client ID to implicitly access the Autonomous Database. Use this when you use Token as the Response Type while downloading the connection file from the Database Actions page. Use this when the autonomous database is in a private subnet or within a customer firewall.
- Explicit Connection
Parent topic: Oracle Autonomous Database add-on for Google Sheets
Connection Management in 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 database connections to databases in your Google Sheets. You can connect, edit, duplicate, and remove a connection.
You use the OAuth authentication and credentials to access the Oracle Autonomous Database for Google Sheets. The Add-on connects with the database using implicit and explicit types of connections.
Explicit Connection
You use the OAuth Client ID and OAuth Client Secret values to authenticate and authorize Google Sheets to use the Autonomous Database. Use this when you use CODE as the Response Type while downloading the connection file from Database Actions page.
Implicit Connection
You will need an OAuth Client ID to implicitly access the Autonomous Database. Use this when you use Token as the Response Type while downloading the connection file from the Database Actions page.
Connecting to Autonomous Database
Import the connections file
- 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.
- Click Import. After you import the connection, you can view the connection in the list of connections.
Description of the illustration import.png - Select the three vertical dots beside the connection name and click Connect to connect to the database.
Manual connection to the database
If you do not have a connections file to connect to the autonomous database and have access to the Schema via SQL Developer web, follow the steps outlined here to connect to the database.
Parent topic: Oracle Autonomous Database add-on for Google Sheets
Generate Client ID and Client Secret using UI
In this section you use the Web UI to obtain the client_id and client_secret .
You generate the client keys by accessing the Autonomous Database instance URL appending with oauth/clients.
For example, if your instance is “ https://<hostname>-<databasename>.adb.<region>.oraclecloudapps.com/ords/<schema Name>/_sdw/", you need to sign in to the link " https://<hostname>-<databasename>.adb.<region>.oraclecloudapps.com/ords/<schema Name>/oauth/clients/". Be sure to include the trailing slash.
- Sign in to Database Actions with the "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".
- Click the +Create OAuth Client button to create a new client.
Description of the illustration create-client.png - 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 if the Autonomous database has public access.
- 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 you copied from step 10 of Deploy the Google Script as a Web app
- 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: Optionally, select an image from your local system to insert a logo for your new client.
- Progress to the Allowed Origins tab. Specify and add the list of URL prefixes in the text field. This is not a mandatory field.
- Progress to the Privileges tab to add any privilege. You are not required to have any privileges to create an OAuth Client.
- Click Create to create the new OAuth Client. This registers the OAuth Client which you can view on the OAuth Clients page.
Description of the illustration new-client.png - Click the show icon to view the
Client IDand theClient Secretfields.
How do I connect manually?
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.
- On the Google Sheet, click Oracle Autonomous Database and select Connections.
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 the illustration auth-continue.png - 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. Selecting the link opens new window, ensuring you trust the application. Click Allow to continue. You have now completed the setup.
- Clicking Connections opens a pop-up window that asks your permission to run the authorization. Click Continue.
- On the Connections wizard, click Add Connection to add a connection.
Description of the illustration add-connection.png - Selecting Add Connection opens an Add Connection wizard in the Connections wizard's connection list panel.
Description of the illustration add-connection-fields.png -
Specify the following field values in the wizard:
Connection Name: Enter the connection's name—for example, TestConnection.
Autonomous Database URL: Enter the URL of the Autonomous Database you wish to connect to. For example, “https://<hostname>-<databasename>.adb.<region>.oraclecloudapps.com/”
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 the 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 the illustration implicit.pngOAuth Client ID:
client_idyou 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 the illustration explicit.pngOAuth Client ID:
client_idyou 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_secretyou 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 connection's name, the schema's name, and the OAuth type you grant. However, it is still in a disconnected state.
-
Click the three vertical dots beside the connection name and perform the following operations:
Connect: Select Connect to the Autonomous Database and change the connection status to active. Selecting Connect opens the sign-in page of the Autonomous database. After you log in, you will view a page that shows that database access has been 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.
Exporting Connections
- 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 the illustration export.png - Click Export.
-
The exported connection downloads in your local system. The connection file is saved as spreadsheet_addin_connections.json.
Parent topic: Oracle Autonomous Database add-on for Google Sheets
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 are used for authorization.
- Click on the Oracle Autonomous Database menu in the Google Sheet you are working on and select Register. This requires one-time Google authentication.
- Clicking Register opens a pop-up window that asks your permission to run the authorization. Click Continue. Selecting Continue will redirect you to the Google Accounts page, where you must select your Gmail account.
- 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.
- Selecting the link opens a new window, ensuring you trust the application. Click Allow to continue.
- You have now completed the setup. Select Register from the Oracle Autonomous Database 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://<hostname>-<databasename>.adb.<region>.oraclecloudapps.com/ords/<Schema Name>".
- 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 the illustration adb-google-wizard.png - Select Authorize.
After successfully authorizing the credentials, you can view Connections, Direct SQL, Data Analysis, Analyses and Reports Clear Sheet, Delete All Sheets, About Autonomous Database, and Sign Out menu items under Oracle Autonomous Database.
Parent topic: Oracle Autonomous Database add-on for Google Sheets
Data Analysis in Google Sheets
Selecting Data Analysis opens an Oracle Autonomous Database wizard in the Google sheet.
The add-on enables you to receive a copy of data from the Autonomous Database to the Google sheet. You can query an existing Analytic View and run SQL Query using the Oracle Autonomous Database Wizard.
You can retrieve the Analytic View and manipulate the query according to your requirements to visualize the result data in the worksheet. You can search for the Analytic View and select measures, hierarchies, and levels from the query. You can also add filters and calculated measures to the query and view the result in the sheet. By default, the data is retrieved in tabular format.
You can run custom queries. The add-on enables you to apply a filter to the query results. The add-on lets you to view query results that can be customized with selected columns using a faceted filter.
- On the Google Sheet, select the menu item Oracle Autonomous Database.
- Select Data Analysis. Selecting Data Analysis opens a Data Analysis wizard. 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.
-
You will view the default query in the query editor area. You can select any of the four modes to visualize the results of the SQL query report you generate:
- Base Query: This type of view is by default. The query written in the SQL editor is the Base Query.
- Table: You can view the SQL results in tabular form. By selecting this view, a column drop zone appears, enabling you to drag and drop selected columns from the Table browser. Moving the selected columns in the drop zone allows you to view only those columns in the Result data generated in the worksheet. Select the cross mark beside the Column name to remove it from the drop zone.
- Pivot: You can view the SQL query results in pivot format. By selecting this format, an X and Y drop zone appears where you can drag and drop the selected columns from the Tables browser to the drop area.
- Chart: You can view Area Chart, Bar Chart, Line Chart, or Pie Chart when you select this option.. The mappings displayed when you select one of the options are as follows:
- Orientation: Choose between horizontal and vertical orientation types from the drop-down list.
- X axis label and Y axis label: Optionally enter labels for X axis and Y axis.
- Click the funnel icon (Faceted Filter) to add filters to the result. The wizard generates a filter for each value in the column retrieved from the query result. You can filter different columns on the faceted filter panel and view the results in the worksheet to view only the data you wish to view. For example, to view the customer reports by Gender, click the faceted filter and select Male under Gender.
Use the Visual facet: Use the visual indicator to graphically represent the faceted filter.
- Click Load Analysis to view the list of Analyses, Analytic Views, and Tables from the schema you select. You can select All, Analysis, Analytic View and Table from the Entity Type drop-down.
The list displayed on the dialog box varies with the choice you make with the type of entity.
Click View All to view all the entities present in the schema.
Selecting an Analysis will also display the Reports associated with the selected Analysis.
Click the search field and start typing the name of the Analysis, Analytic View, or Table you are looking for. For example, if you are looking for an Analytic View named
ANALYTIC_VIEW.You will view the selected search entity below the search field.
Click
Xto close the Load Analysis dialog. - Select Run to generate the results of the custom query in the worksheet.
Using natural language to interact with your database data is now achievable with Oracle Autonomous Database add-on for Google Sheets. This means you can use natural language, for example, plain English, to query the database. This means you can provide a natural language prompt instead of SQL code to interact with your data. When you select Use Natural Query the add-on converts natural language to SQL.
- On the Google Sheet, select the menu item Oracle Autonomous Database.
- Select Data Analysis. Selecting Data Analysis opens a Data Analysis wizard. 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.
- Select Use Natural Query. Let’s say you want details of employees who are male, over 30 years old and work in the IT department. Enter the following natural language query in the query display area:
Give me data for individuals who are male, over 30 years old, and work in the IT department. - Click Generate Query to produce the equivalent SQL query in the bottom query display area.
- You will view the following code in the bottom SQL code area.
SELECT "EEID", "LEAVE_DATE", "CITY", "COUNTRY", "BONUS_PERCENT", "ANNUAL_SALARY", "HIRE_DATE", "AGE", "ETHNICITY", "GENDER", "BUSINESS_UNIT", "DEPARTMENT", "JOB_TITLE", "FULL_NAME" FROM "ADPTEST2"."AA_EMP_PIV" WHERE "GENDER" = 'Male' AND "AGE" > 30 AND "DEPARTMENT" = 'IT' - 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.
- Select the menu item Oracle Autonomous Database > Data Analysis on the Google Sheet. This opens a Data Analysis wizard in the Google task pane.
- Select AV from the AV or Query drop-down, select a schema you can access from the schema drop-down, and the AV from the available Analytic Views.
-
You can select any of the three modes to visualize the results of the AV query you generate:
- Table: You can view the AV query results in tabular form. By selecting this view, a column drop zone appears, enabling you to drag and drop selected columns from the Table browser. Moving the selected columns in the drop zone allows you to view only those columns in the Result data generated in the worksheet. Select the cross mark beside the Column name to remove it from the drop zone.
- Pivot: You can view the SQL query results in pivot format. By selecting this format, an X and Y drop zone appears where you can drag and drop the selected columns from the Tables browser to the drop area.
- Chart: You can view the results of the AV query in chart format. By selecting this format, an X and Y drop zone appears where you can drag and drop the chosen hierarchies and measures from the AV browser to the drop area.
Note:
You are allowed to drop measures in the Y-axis.
-
Click the funnel icon to view the Faceted and Visual Filter list. The wizard generates a filter for each value in the column retrieved from the query result. You can filter different columns on the faceted filter panel and view the results in the worksheet to view only the data you wish to view.
- Click Save.
- Click Run to view the results in the worksheet you select consisting of the Drama genre.
Parent topic: Oracle Autonomous Database add-on for Google Sheets
Run Direct 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.
- In the Google Sheet, select the menu item Oracle Autonomous Database.
- Select Direct SQL to type and run the SQL command.
- The Oracle Autonomous Database wizard opens Tables and Views icons and a search field beside it.
Description of the illustration native-sql-tables.png - Select Table to view all the tables in the database. Perform the same operations for Views.
- You can right-click on the table whose data you want to query and choose Select to view all the table's columns. The column names will be displayed in the Write a Query section. You can click on the table and view individual columns as well.
Note:
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 the illustration native-sql-select.png - 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.
- The worksheet also displays the timestamp, the user who creates and runs the query, the ADB URL, and the SQL Query.
Description of the illustration nativesql-results.png
Parent topic: Oracle Autonomous Database add-on for Google Sheets
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 various charts: 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
- Select Analysis under Output format.
- Use the Select an Analysis drop-down to choose the Analysis you want to view.
- Click View Analysis to view the analysis in the Google Sheet.
Description of the illustration view-analysis.png
View Report
To view Reports :
- Select the Analyses and Reports menu from the Oracle Autonomous Database menu. This opens the Analyses and Reports wizard.
- Select Report under Output Format.
- Use the Select an Analysis drop-down under Choose Analysis to choose the Analysis you want to view.
- 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.
- 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 the illustration reports.png - Select the worksheet from the drop-down where you would want to view the report.
- Click View Report to view the report in the selected sheet from the previous step. You can now view the report in the worksheet you select.
Description of the illustration view-reports-sheet.png
Parent topic: Oracle Autonomous Database add-on for Google Sheets
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.
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 in 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 Oracle Autonomous Database and select Clear Sheet.
This option erases all data types in the selected sheet, including images and formatting.
Parent topic: Oracle Autonomous Database add-on for Google Sheets
Delete all sheets
Use this option to delete all the sheets existing in the spreadsheet.
Select Delete All Sheets from the Oracle Autonomous Database menu to delete all sheets from the spreadsheet.
Parent topic: Oracle Autonomous Database add-on for Google Sheets
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.
Parent topic: Oracle Autonomous Database add-on for Google Sheets
Sign Out
Use this option to sign out.
Select Sign Out menu from Oracle Autonomous Database to sign out from the database session.
Parent topic: Oracle Autonomous Database add-on for Google Sheets
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.
- Review and inspect to remove personal or sensitive information.
- 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.
Parent topic: Oracle Autonomous Database add-on for Google Sheets
Oracle Autonomous Database Privacy Policy Details
This topic covers details for writing policies to control access to Autonomous Database resources.
The Oracle Autonomous Database Add-on for Google Sheets must comply with Privacy Policy. The Oracle Autonomous Database add-on for Google Sheet’s use and transfer to any other app of information received from Google APIs will adhere to Google API Services User Data Policy, including the Limited Use requirements.
Parent topic: Oracle Autonomous Database add-on for Google Sheets