“Layers. Onions have layers. Ogres have layers. Onions have layers. You get it? We both have layers.” – Shrek, 2001
Standards have layers too. Sometimes they can be as easy to get to work together without crying as to peel an onion without crying.
Seriously, however, in every single interaction we have with our computers, there are many different layers of standards involved, and all need to work well together. Getting your Excel workbook to connect to an Oracle database is fairly straightforward, but you do have to take care to get all the steps right.
There are three layers involved in setting up this kind of connection. Just like building a house, you need to start at the bottom and work your way up. Configure each layer, and the move on to the next one. In that order, the layers are:
- The Oracle TNS (Transparent Network Substrate)
- The Data Source Name, or DSN
- The Office Data Connection, which can be saved as a .odc file
The steps required to configure each layer is a bit much for a blog post like this, but if you would like to see a demo of how all this is done, I have created a short video that walks through the required steps. Feel free to follow along, pausing as necessary to create your own DSN and connection as I describe each step.
As you follow along, you will no doubt feel that creating the Data Source Name can be the most problematic, trying to get the bitness right – which ODBC Administrator am I running and which one do I need? Thankfully, there is good news – you can skip that entire step of finding the right ODBC Administrator to run to create your DSN. If you have installed the Simba MDX Provider for Oracle OLAP, just launch Excel. When you get to the Data Connection Wizard, in the Connection tab, you will see an “ODBC Admin” button. Clicking this will launch the appropriate ODBC Administrator based on the bitness of both your operating system and of Excel!
After you are done, you can see the benefit of using a layered approach. Each layer is unaware of the details of the layer below it. Microsoft Excel has no idea it is talking to an Oracle database – in fact, your Oracle databases work in the Excel world exactly the same as any Microsoft database would!
Hopefully this information will help you define each of the layers quickly and efficiently, allowing you to connect your Excel spreadsheet directly to your Oracle database, and won’t leave you feeling like an ogre.