How to set up Oracle drives for Excel PowerPivot

My colleague tried to find simple instructions how to set up Oracle drivers in case for Excel PowerPivot but there wasn’t any that wouldn’t miss some critical points. So here’s the simple instruction on how to do it.

1.      Check the version of the Excel (or any other software you wish to use to browse the Oracle database), is it 32-bit or 64-bit?

2.      Load Oracle Data Access Components –package (ODAC) based on bits of the target software:

a.      32-bit version:
http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html
        
b.      64-bit version:
http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

Download the ODAC XCopy version. The newest release is on the top.

3.      Unzip the package to your computer on some easy-to-access folder (e.g. c:\mytempfolder)

4.      Start command prompt (cmd.exe) as an administrator, go to your chosen folder.

5.      Look up the install command from the README.html file and run it in the command prompt (e.g. install all c:\oracle odac)

• Don’t use any special characters in a name of the installation folder (which is c:\oracle in the example above)
• Installing can take some time and you can see that it’s done when you can write again to the command prompt window. Yes, that's right, there's no other indication.
• Installation has succeeded if the set up folder is created and have stuff in it (c:\oracle if you decided to use the example folder)

6.      Open Control Panel -> System -> Advanced System Settings: Environment Variables.

• Add your set up folder (e.g c:\oracle) and both bin and network\admin folders  to the end of PATH-variable. (As default it would be ;c:\oracle;c:\oracle\bin;c:\oracle\network\admin;)

7.      Restart the computer

8.      Create tnsnames.ora file to the network\admin –folder in the setup folder. Add TNS entries to that file.

9.      Start Excel and PowerPivot, choose Oracle database source and additional setting: Oracle Data Provider for .Net. Fill up connection details and test connection.

•  All the other providers seem work after using that provider if they weren’t working before.
• You might get some ”missing DLL-file” notifications while setting up connection for the first time, but those are not damaging and next time you won’t see them anymore.