How to configure Microsoft Excel to connect to an Oracle Database

Mike van der Velden, Product Manager at Simba Technologies, provides a step-by-step guide for configuring Microsoft Excel to connect with Oracle Database OLAP Option. Mike walks through the one-time set-up of Oracle's Transparent Networking Substrate (TNS), followed by the Data Source Name (DSN) and Office Data Connection (.odc). Once configured, Microsoft Excel connects easily to Oracle Database OLAP Option for ad hoc data query, data analysis and BI reporting.

---

Closed Caption:

hi I'm Mike Vanderbilt a product manager
at symbol technologies here at simba are
often asked to clarify how do i
configure Microsoft Excel to connect to
an Oracle database
let's have a look first some background
as you know Microsoft Excel can connect
to many external data sources including
an oracle database
this involves three different connection
layers and from the top down they are
the office data connection or ODC the
data source name or DSN and Oracle's
transparent networking substrate more
commonly known as TNS you'll need to
take special care when configuring a
data source for 32-bit clients on a
64-bit operating system and i'll show
you how to do that i'm going to show you
a demo and encourage you to follow along
on your system
pausing and rewinding this video as
necessary we'll start at the lowest
level with Oracle's TNS articles TNS is
configured to be a text file called TNS
names that are a this file contains a
list of database addresses to specify
the hostname or IP address where the
database resides the tcp port number
where the database will be listening for
incoming connections as well as the
Oracle sit or system identifier
you can create this file yourself or
more commonly it will be provided to you
by your IT department where will contain
a list of databases that you're allowed
to connect to you can save this file
anywhere locally on your system
but once you've done so you do need to
tell windows where to find it
there are many ways to do this but I
find the easiest is via windows
environment variable
this can be said via the start menu
right click on computer properties
advanced system settings and environment
variables and here we have it TNS admin
is a user environment variable that
specifies the folder where TNS names or
resides here you see the last stated in
my documents my data sources
now once you've created your
tnsnames.ora file and you've told
windows where to find it
you're ready to create a DSN or data
source name to do this you would go to
the start menu control panel
administrative tools and here we see
data sources already is the odbc now
here's where you need to be careful
i'm running a 64-bit operating system
this datasource administrator will
create a data source for 64-bit client
if you have a 32-bit client on a 64-bit
operating system you need to create an
appropriate data source
here's how to do each if I right click
on the data source administrator found
in the administrative tools and find
properties you will see that the file
that were running is called odbc a t32
dxc I've created a shortcut on my
desktop to point to the 32 bit odbc
administrator and click on properties
I'll show them side by side note that
the 32 bit odbc administrator is also
called odbc ad 32 . exe that differences
where they found the 64 bit odbc
administrator on a 64-bit operating
system is found in to see back glass
windows system32 directory the 32-bit
administrator on a 64-bit operating
system is found under the sea windows
system while 64 directory this file
sounds for windows on windows 64
that's right the 32-bit administrator is
found in the system while 64 directory
and a 64-bit administrator is found in
the system 32 directory
once you know the business of your
client you do need to create the
appropriate data source in my case I
have excel 2010 32 bit on my system so i
know that i do need to create a data
source for 32-bit client
i launched the 32-bit administrator from
the shortcut on my desktop you can
create two kinds of the sense either a
user DSN or system DSN a user dsm can be
created by anybody on the system whereas
the system DSN can only be created by
those who have administrative privileges
for our purposes other users ESN or
system DSN will suffice
so because user DSM's are easier to
create will create one of those are
click on add which will bring up a
dialog box with all the database drivers
that i have on my system here have the
oracle database driver which i will
select now here is where we define or
DSN first it needs a name
they can be anything you want but i do
recommend a descriptive name
the description again the description
can be anything you like so let's do
something descriptive how to create a
DSN for oracle DB ok now the TNS service
name is the name of one of the entries
of your tnsnames.ora file
now you can either remember exactly what
you call this or this handy drop down
we'll look at your tnsnames.ora file and
this to you all of the database
addresses that you have specified and
here you see the only 1i have a my file
i will select it and I can test my
connection now the database i have
installed as an oracle database is
normal olap database
it's a sample data set from the Oracle
technology Network called Olaf train and
username is a lot of train and now i
specify the password click OK
and here we are so now we've created the
TNS names that or a file with told
windows where to find it
we've created add ESN that refers to an
entry into tienes names the or file and
now we're ready to make a connection
so now i'm going to start my 32-bit
clients oh and here you can see the DSN
I've just created
now we're ready to start my 32 big
client which is office excel 2010 to
create a connection
I go to data from other sources from
data connection wizard and here we have
a dialogue box
I select other advanced next here we
have a list of all the database
providers available to me as i mentioned
before i have installed the olap train
data set that's available for free from
the Oracle technology Network to access
the multi-dimensional olap database
I need to use a Simba mdx provider for
oracle app I was like that now at this
point I need to tell it where is the
data source name
now you can remember exactly what you
typed over this dialog box we can select
the DSN I've just created which I will
do again I need to know to specify the
username and password to use to connect
to the database and once I've connected
to the database
I do need to specify which schema or
catalog to use this drop-down box will
allow me to select from the list of
available catalogs and I've got the old
of training data set and we click OK now
you'll see another dialog box that asks
you which cube to access in your schema
I'm going to connect to the sales cube
next
here's the final step we're now going to
save this data connection as a office
data connection file so we can reuse it
later
we have to give it a file name by
default it shows you the database name
and a cute name but you may end up
having lots of data collection files
with similar name so i suggest using a
more descriptive name
video demo and is same goes for friendly
name in case you have multiple data
connection files with similar names
now i'm going to save my password in the
file so that every time I connected on
to retype it if you choose not to save
your password in the file
every time you connect to the database
you'll need to reenter your password and
it gives me or appropriate warning
that's good and I'm always going to use
this file to refresh the data so I'm not
prompted which cube to use and so forth
click finish and now we have an
opportunity to select where the data
should appear
this is going to set up a pivot table
for me in cell a1 i will click OK and
now you can browse the data in a
database a great quick pivot table
showing sales by geography by year and
now you can access the data live in the
database with a live connection
i will save this excel spreadsheet
sample sales data save and close
if I now return to my documents i will
launch the spreadsheet and we'll be back
at exactly the same state there was when
I close it and as you see when I drill
down i'll be worn again this is and
external data source and after
refreshing our connection
we are able to drill down into the
database
alternatively I can just open my data
connection
the one I just created and it will start
a blank spreadsheet but with the same
connection to the database so i can
start a brand new pivot table with a
brand new analysis on my data and we get
started again and sales and costi
by-product
ok let me sum up I've shown you that
there are three steps necessary to
configure excel to connect to an Oracle
database
first you needed at ens names the or
file either creating the file yourself
or using the one that's applied to you
by your IT department
next you need to tell windows where to
find this file using a Windows
environment variable called TNS of men
next you create a data source name or
DSN and you have to be careful of the
business of both the operating system
and a client that's going to use the
data source
finally you created an office data
connection and you say the file locally
which you can then share with your
colleagues for more information
including white papers product
information and manuals
please see our website and thank you for
watching