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.
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.”
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
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)
On the Insert tab on the ribbon, click the down arrow on the Add-ins / My Add-ins option:
Under Developer Add-ins, you will see the Oracle Autonomous Database Add-in.
Click to select this Add-in.
At the bottom, you see a notification about the Add-in being loaded:
After the Add-in is loaded successfully, you will see the following message:
Also, a new ribbon item, “Autonomous Database.”
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.)
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:
Click Yes to allow the script to install the Add-in (make changes to your computer)
On the Insert tab on the ribbon, click on My Add-ins:
Under “SHARED FOLDER,” click on the Oracle Autonomous Database Add-in and click Add to load it:
Connecting to Autonomous Database
When the Add-In has loaded, notice a new ribbon item, “Autonomous Database.”
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.)
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.
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
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:
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…
Create a new Calculated field Avg Price as shown below:
Click Add and then click OK.
Now set up the pivot table as shown below.
Users can now format the data and add conditional formatting as shown below to analyze the data.
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.
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.
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.