Using the Excel Add-in to Query Autonomous Database

May 3, 2023 | 9 minute read
Ashish Jain
Senior Principal Product Manager, Autonomous Database
Text Size 100%:

Overview

With the new Data Tools available in Database Actions of the Autonomous Database, users can quickly load data, view dependencies using the catalog, derive insights using the Data Insights tool, and create business models using the Data Analysis tool.

We are excited to announce the launch of a new Excel Add-in that allows users to query data in the Autonomous Database directly from Excel and perform their analysis. This new add-in will enable users to run native SQL queries and a wizard to query Analytic Views created by the Data Analysis tool.

Autonmous Database Excel Plug-in
Excel Add-in to run Native SQL on Automous Database

In this introduction, we look at how to set up this add-in for Excel in MS Office 365 on both Windows and macOS. We then run SQL queries on the Autonomous Database and leverage the native Excel capabilities to analyze data. You can refer to the documentation here.

The data used in the example below is the same that is available in this livelab.

Download the Add-in

  • Login to the web UI for the DB Actions page.
  • On the right-side navigation menu, click on the link to “Download Add-in for Excel.”
Download Add-in
Download Excel Add-in
  • This will download the zip file for installation.
  • Follow the steps outlined below based on the platform (Windows or Mac) you are running on.

Installation on macOS

  • Unzip the downloaded zip file
  • Open a terminal window and navigate to the unzipped folder
  • Ensure that Excel is not running.
  • The install.sh file does not have to execute permissions
chmod 764 install.sh
./install.sh

Change Permissions

  • Change the permissions for the install.sh and run it (Note: use 774 if you belong to the same group as the user or 777 if you are not the owner or belong to the same group in the below command)

Run install MacOS

  • Launch Excel
  • On the Insert tab on the ribbon, click the down arrow on the Add-ins / My Add-ins option:

Excel Add-in Step 1

  • Under Developer Add-ins, you will see the Oracle Autonomous Database Add-in.
  • Click to select this Add-in.

Excel Add-in Insert 2

  • At the bottom, you see a notification about the Add-in being loaded:

Excel Add-in Insert 3

  • After the Add-in is loaded successfully, you will see the following message:

Excel Add-in Insert 4

  • Also, a new ribbon item, “Autonomous Database.”

Excel Add-in Insert 5

  • Close and Quit Excel
  • Launch Excel and insert the Add-in again. (You must perform these steps 6 thru 9 mentioned above every time you launch Excel.)

Excel Add-in 6

  • You are now ready to connect to the Autonomous Database, run native SQL, and use the Analytic View Query wizard.

Installation on Windows

  • Ensure that Excel is not running
  • Unzip the downloaded zip file (oracleplugin.zip)
  • Right-click on install.cmd and select Run as administrator
  • A warning box will appear. It is safe to run the command, so click on More info, then Run anyway:

Excel Add-in Win 1

  • Click Yes to allow the script to install the Add-in (make changes to your computer)
  • Launch Excel
  • On the Insert tab on the ribbon, click on My Add-ins:

Excel Add-in Win 2

  • Under “SHARED FOLDER,” click on the Oracle Autonomous Database Add-in and click Add to load it:

Excel Add-in Win 3

Connecting to Autonomous Database

  • When the Add-In has loaded, notice a new ribbon item, “Autonomous Database.”

Excel Add-in Connect 1

  • Click on Sign In to connect to the database. Make sure you type your username in all lower case! (Note: you did not have to install any Oracle Client or wallet files. This add-in uses ORDS and REST API to connect with Autonomous Database.)

Excel Add-in Connect 2

  • You are now connected to the Autonomous Database and ready to run native SQL and use the Analytic View Query wizard.
  • On the Autonomous Database ribbon tab, click the About button.

Excel Add-in Connect 3

  • This provides information about the Add-in and Autonomous Database version, which is helpful while working with support on any problems you face with the Add-in.

Run Native SQL for analysis using Excel Pivot tables

  • Launch the Native Sql panel by clicking the button in the Autonomous Database ribbon.
  • Let’s run a SQL query on the data we just loaded in the Autonomous Database.
select a.continent, a.country, b.form_factor, b.device, c.month, d.day,
e.genre, e.customer_segment, e.sales, e.purchases
from countries a, devices b, months c, days d, movie_sales_2020 e
where e.country = a.country and
e.day = d.day and
e.month = c.month and
e.device = b.device and
e.country = a.country
order by c.month_num;
  • Add the above query in the text box under the Write a query label on the right-side panel. Remove any existing SQL you might see from the previous run.
  • Check the Pivot table checkbox.
  • Under Select worksheet, click the “+” icon and provide MovieSales as the name.
  • Click the check button
  • Click Execute

Excel Add-in Direct SQL MOVIESTREAM

  • Two new tabs are created, viz. MovieSales and Sheet2 (Sheet number might vary in your case)
  • On Sheet2, an Excel pivot table is created with the data fetched from the Autonomous Database.
  • Setup the Pivot table options as shown on the screen below:

Excel Add-in Native SQL Pivot Table

  • The data for this pivot table is fetched from the MovieSales worksheet.
  • Now you can use the native Excel capabilities to analyze data.
  • Let’s add a calculated field.
  • Click the Fields, Items & Sets button on the PivotTable Analyze ribbon and select Calculated Field…

Excel Pivot Calculated Field

  • Create a new Calculated field Avg Price as shown below:

Excel Pivot Avg Price Calculation

  • Click Add and then click OK.
  • Now set up the pivot table as shown below.

Excel Pivot Native Calculation Pivot setup

  • Users can now format the data and add conditional formatting as shown below to analyze the data.

Excel Pivot Formatted report

  • This seems an interesting metric (Avg Price) to identify which genre fetches the highest overall avg price per movie and users with what devices generally pay a higher average price per movie.
  • With the above insight, users can now create a model in the Autonomous Database with this metric pre-defined for all users across different tools they might use for analysis.

Conclusion

We saw that users could easily download the Excel Add-in from Database Actions, set it up, and run queries directly on Autonomous Database. Additionally, users can leverage native Excel capabilities to determine the right metrics to be included in Business Models developed using the Data Analysis tool. This way, users can report consistent numbers using tools like Oracle Analytics or Tableau.

Ashish Jain

Senior Principal Product Manager, Autonomous Database

Product manager responsible for Essbase, both cloud and on-premises including Exalytics. The current focus is on building the next generation data managment capabilities on the Autonomous platform.


Previous Post

Selecting the best Data Integration tool – A guide to Oracle Data Integrator editions

Jayant Mahto | 6 min read

Next Post


Data Loading using REST Data Services APIs

William Endress | 10 min read
Everything you need to know about data warehousing with the world's leading cloud solution provider