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…

Oracle Databases

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’?

oracle

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! Smile

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!

DBA to the Rescue

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:

http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

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:

myserver-1:1521/sales_database

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.

image9

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’.

image13

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.

TNS what…?

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) =

(DESCRIPTION =

(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:

image21

For Power Query, select ‘From Database’, then select ‘From Oracle Database’. Enter the connection name and any requested credentials.

image17

Happy Oracle Pivoting!

  Subscribe to PowerPivotPro!
X

Subscribe

Andrew Todd

I am a BI professional from Seattle, WA. I enjoy working with all of the Microsoft BI stack, especially Power BI. 

This Post Has 14 Comments

  1. Andrew,

    Thank you for the insights for connecting to Oracle databases. Like several other recent posts, it has appeared at just the right time–I am currently in negotiation with a company that is totally new to Power Pivot and whose data resides mostly in Oracle databases.

  2. Anyone have experience writing SQL query against Oracle DB tables? I am using a JOIN statement but continue to get errors. I ran the query in Oracle DB and it worked fine. Should I be using Microsoft SQL syntax? thanks for any help!!

  3. I have replaced ORacle OBIEE and ORacle Discoverer with Power BI more than 2 years ago in my company. Lots of savings… I’m using Attunity Connector provided with SSIS on SQL Server 2012. I was able to copy and paste OBIEE/Discoverer base queries into SSIS without any issue. I created an entire Data Model for all the Oracle modules we are using (AR/AP/GL/PO/OM/OKS/EBTAX and many other). I had similiar issues when I tried to use the ORacle ODBC connector provided by Microsoft…

  4. Hi, I get the following error
    Unable to Connect
    We encountered an error while trying to connect.
    Details: “Object reference not set to an instance of an object”

    1. This is how I was able to fix my problem.

      I uninstalled all my Oracle Clients (32 & 64)
      I uninstalled “64-bit ODAC 12c Release 4 (12.1.0.2.4) Xcopy for Windows x64”
      I installed “64-bit ODAC 11.2 Release 6 (11.2.0.4.0) Xcopy for Windows x64” to match the version of my Oracle Database (11.2.0.3).
      Our Oracle database uses SID and not ServiceNames, so I created a TNSNames.ora file like this:

      PROD01_SID =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora_prod.example.com)(PORT = 1521))
      )
      (CONNECT_DATA =
      (SID = prod01)
      )
      )

      Then it worked for me.
      Not really sure what step really fixed the problem, but I am speculating that it was dropping the ODAC down to 11.2.0.4.0 to match the version of our database (11.2.0.3 I think).

  5. Thank you so much! I watched a ton of YouTube videos and read through various articles and your post solved issues I was running into!

  6. Is there any way to force the connection to a materialized view instead of a table or standard view of the same name? Thanks for all the feedback.
    Cheers…

  7. I can connect to Oracle from PowerPivot (Successful test connection). I can see all my tables all write my own SQL (can see all table from Oracle), but when I try to load data – I get an error wothout any description. Only: There was a system error and nothing else. What I should do?!

  8. I’m stumpped. I use a TNSNAMES.ORA file to connect to my PLSQL developer, but this seems to be saved in a super user account on my work laptop, not available to pbi when using my account. . . So I’m left with ezconnect strings. Is there any syntax I can add that power bi will ignore that will allow me to have multiple users for same string? for example an User1 and User2 login? Currently the connection string is treated as the 1st one I entered and authenticated with. Any attempt to authenticate with other user fails because pbi assumes it’s the one it already has. Any help pointing me to a better blog post on pointing a tnsnames.ora file stored in a different acct on my work lap or some syntax for ezconnect that will trick it into thinking it’s different so I can use different creds is really helpful as I try to get through a POC. Thanks.

Leave a Comment or Question