Guest Post by Andrew Todd
Intro by Avi: Andrew has been a part of PowerPivotPro family for a while now and has been doing wonders for the clients he has been working with. You have probably heard Rob talk about the “Data Gene”, well Andrew is the ideal specimen for that breed.
Recently I had worked with a client where we spent quite some time just trying to figure out how to connect Power Pivot to their Oracle database. Andrew here lays it all out in simple step by step approach. Andrew’s got a lot of wisdom to share and you should all look forward to more posts from him. Take it away Andrew…
The amount of data around us is staggering. Statistics citing the amount of data being generated daily describe it in terms of exabytes (1 exabyte is 1 BILLION gigabytes!) In fact, the amount of data generated daily now exceeds the entire storage capacity of the entire world in 1986!
In a world with that much data, we’re lucky to have tools like Power Pivot and Power Query that offer easy connection to data sources that just sort of … clicks! However, if you’ve tried connecting to an Oracle database in Power Pivot you might have had some trouble. Fear not, here’s how you can make your Oracle database connections click just like all the rest!
Why aren’t my Oracle connections ‘clicking’?
For Excel pros, Oracle databases are a bit different, because there are a few key connection enabling components that are not automatically included with most users’ setups:
1.) Connection details – for Oracle databases, the connection details are included either in the connection string itself (EZConnect) or in a flat file called TNSnames.ora. Don’t worry, it isn’t as complicated as it sounds!
2.) Oracle data providers – You can think of these as the communication link between Power Pivot and the Oracle database.
Make a Call to your DBA for some details!
Before you attempt to connect to your Oracle database, you’ll need to contact your friendly Oracle DBA for the following information:
Username/Password to the database
Database Host Name – AKA the server on which the database is residing. (Example format: myserver-1)
Port Number – This is like the zip code on the target server, that helps route your query to the correct ‘address’ (typical format: 1521)
Database Service Name – The database service routes your query to the database, sort of like the Postal Carrier. The database service is not the name of the actual database, but rather the service that enables the database connection. (Example format: sales_database)
Oracle Data Providers
You’ll need to download necessary data providers that enable communication with the Oracle database. Data providers enable communication between Power Pivot/Power Query or Tabular and the Oracle database you are querying. Without the necessary data providers, a signal is not able to be sent to the database and no query is executed!
All necessary data providers are provided by Oracle in one convenient package called ODAC (Oracle Data Access Components). If you are modeling a Tabular database, the Oracle Developer Tools (ODT) utility will prove useful, and the download contains all of the necessary data providers for connecting to an Oracle database.
As of today, you can follow this link to download ODAC:
All of the needed data providers will be installed when you install ODAC on your computer. If you are connecting to an Oracle database with just Power Pivot or Power Query, then you only need to install either the 64 bit ODAC or 32 bit ODAC depending on your OS version. If you are going to be connecting to an Oracle database for SSAS Tabular, then you will need to install the 32 bit ODAC regardless, as SSDT is a 32 bit application and will not be able to use 64 bit data providers.
Server Side Considerations
If you or your DBA are going to be enabling automatic refresh on your Tabular server using SQL agent for example, you will need to make sure that ODAC or the necessary data providers are installed on the server running SSAS Tabular. Otherwise, when the refresh is executed, an error will be thrown because the server will not be able to communicate with the Oracle database.
The same goes for SharePoint automatic refresh. Tell your SharePoint architect to install ODAC and list the Oracle data providers as ‘Trusted Data Providers’ in central administration.
Now with all of the setup out of the way – let’s get to the actual connection!
Connecting to the Oracle Database via EZConnect connection string
The path of least resistance to connect to an Oracle database is through an EZConnect connection string. The EZConnect connection string is entered where Power Pivot or Power Query ask for the server name. EZConnect can be used for version 10g Oracle databases or greater, where EZConnect has been configured. You can reach out to your DBA to see if EZConnect will work, or give the below a shot first!
Building an EZConnect string
The EZConnect connection string format is as follows: (host name):(port number)/(service name)
Taking our example connection details from earlier, here’s what we get:
To connect to an Oracle database using EZConnect in Power Pivot, under ‘Get External Data’ select ‘From Other Sources -> Oracle’ and enter the EZConnect connection string where server name is requested.
To connect to an Oracle database using EZConnect in Power Query, select ‘From Database -> From Oracle Database’ and enter your EZConnect connection string for ‘server’.
Connecting to Oracle Using TNSnames.ora
If you are not able to connect to an Oracle database using EZConnect, either because of the database version or your database just doesn’t have the ‘secret sauce’, then you need to enter the connection details in a flat file called TNSnames.ora. TNSnames.ora will be installed on your computer when you install ODAC. It can be tricky to locate TNSnames.ora, so either get help from your DBA or run a search from Windows and then open the file location.
The TNSnames.ora file is basically a repository to store all of your Oracle database connection details in one spot. If you have more than one Oracle database that you connect to for example, you could store multiple sets of connection strings in TNSnames.ora.
The easiest way to enter the connection details in TNSnames.ora is to write the details in the file manually. You’ll want to save a copy of TNSnames.ora before you make any changes just in case. After you make a backup copy, go ahead and change the file extension to .txt and open TNAnames.txt in notepad.
When you have TNSnames.txt open in notepad, you’ll want to enter the connection details in the following format:
MyConnection (you can choose whatever you want to call this) =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myserver-1)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = sales_database))
Now that you have added the connection details to your TNSnames.txt file, re-save the file as TNSnames.ora. You should be able to reference the connection name that you entered in the TNSnames.ora file now to query the Oracle database from Power Pivot.
Pulling Tables in Power Pivot with TNSnames.ora
Open the Power Pivot window, and under ‘Get External Data’ select ‘From Other Sources’ (Oracle database is not listed under ‘From Database’. Click on ‘Oracle’ and hit ‘Next’. Enter the connection name that you saved in your TNSnames.ora file. In this example, I would enter MyConnection:
For Power Query, select ‘From Database’, then select ‘From Oracle Database’. Enter the connection name and any requested credentials.
Happy Oracle Pivoting!