hello everyone this is matthew today i'm
going to talk about how to use power bi
to connect to oracle databases
so oracle database has been one of the
most popular databases that people use
and people want to use power bi to
connect to the database
and we receive there are some questions
or issues regarding to that
so today i'm going to talk about uh some
topics
so the first i'm going to work with
working through a demo so if you have a
new laptop
uh that installed power bi how to
install this oracle odbc
and to verify and connect to a database
and i believe some of you might have
some issues
when using the database so i'm going to
provide some general suggestions or
troubleshooting guide
to help you through this so uh before i
go into the demo
so um first there are some assumptions
here so
uh first is of the ui services document
and like user agreement there as of
october 20
2020 and in the demo computer i'm going
to use windows 10
and the power bi will be the windows
store version latest
the october version so let's get started
alright here i have a new laptop and
with the power via desktop already
installed and launched so the first
thing i'm going to do is to install this
oracle connector
which is not included in the power bi
desktop so i'm going to click the get
data button
and it will list all the sources that i
have
for for my data so i'm going to type in
oracle
to find the oracle okay here it says
oracle database i'm going to click
connect
so in this case i do not have a have a
connector installed so the system will
complain
and show me that hey the oracle client
is not installed
so i'm going to click learn more to go
to the page
so here i open this microsoft oracle
connector page
and i'm going to scroll down a little
bit
so first i'm going to read some uh
basically the
notes here so it says here it says uh
please note your oracle database has to
be oracle 9
or later so i think most of you the for
the oracle database
it should be it should be more than
version 9.
i'm going to go to here and download the
version since in my laptop i'm using the
64 bit of power bi desktop
so i'm going to click and download the
64-bit
of the oracle client
so here you enter this oracle's external
website
and this is basically the download page
and
what i'm going to do so is to find and
to download the odac
connector so when at the time when you
see this video
this page might look different and there
might be some new version got released
and one trick i can tell you is you can
always find
one of the biggest uh one of the biggest
files that you can download here
so let's see here this says i'm using
the 12.0
10.2 version and you can see this is
only 2 megabytes this is 77 megabytes
and i can scroll down and you can see
here oh this one is huge it
is 400 megabytes and this is one that i
will
i will going to be needing i can see the
other is basically the older version
12.1
and basically so my trick is basically
if you see this oracle
and you see for this version there are
multiple ones
you are going to download one the
biggest one
so i'm going to click and download so
one of the thing here is
you have to review and accept your the
oracle lesson agreement
so after your solo review of the
agreement you click download button
and the next thing you can see here is
it will promote you to do a sign-in
and basically to before you can download
so you can if you don't have an oracle
account
you can click this create account button
it is free to download for sure
so because in this case i already have a
account already registered i'm just
going to click sign in button
once i click sign you can see from my
internet browser
right now on the on the bottom left side
it is already start downloading
so i'm going to pause the video and wait
for this to download
after the downloading i can click this
file that's already downloaded
and the first thing i'm going to do is
to unzip this file
i'd right click and click extract all
and it might take a while
all right after i entered this file i'm
going to click this
unzipped version and you will find
something that is called setup and this
have this little database icon
and i'm going to double click this icon
to launch
the installation i click yes
and basically it's checking some of the
basics here
once it finishes checking you will see
the screen so basically first you can
select the language that you prefer
this is by default english i'm going to
click next and
here you can just go to next there's
nothing we want to check
and here this is important so basically
here it's going to tell you which is
the path that you are going to install
this oracle
so you can access you can use the
default path
and but you you have to remember this
this later
if we have some any troubleshooting we
are going to use this path
so in this case i'm going to change that
to oracle
and also after you do that also remember
this what is your software location
and what is your oracle base
i'm going to click next and basically
that is good i'm also going to click
nest
and next and i don't need
okay now it is doing the prerequisite
checking and installation
so i'm going to skip here basically you
can just wait and click next
until the end all right so after several
minutes of waiting this is already
finished
so i'm going to just click close and
this basic can say here
it says the installation of the oracle
client was successful
i'm going to click close and i'm going
to go back to power bi
i'm going to restart power bi i'm going
to now save this
so you can also reboot your laptop just
in case but let's see
here i'm going to just reboot power bi
once rebooted i'm going to do the same
thing
get data and i'm going to search for
oracle
all right oracle database going to click
connect
and you can see in this case basically
it says the
recommended provider is not installed
but you can continue with or
uh your current provider so this is
basically
very common so even you install the
correct one or the latest version
sometimes powerbear will will also
complain
but we can ignore this message and go
into here and connect
[Music]
and this is basically the oracle page
let's talk about the connection a bit so
basically
the most transitional way is your id
department
might have sent you a tns file so a tns
file basically looks like this
it says the name always has to be tns
names.ora
file and basically you can see it's ora
file
so the most traditional way how to do
this is basically
you're going to place this uh oracle
this ra file into this path so this part
basically when you install the oracle we
were asking you to
to remember the path so
i'm going to basically to show you how
that looks like
i'm going to click basically it's added
with notepad plus plus
but if you don't have you can click open
with
and in the more apps you can find
notepad
i'm not going to click ok and this is
how a traditional tns file look like
so basically it tells you what is the
address of the server
in here i use some fake ones just for
the demo purpose
and basically what is the server and the
server id
and all that stuff and basically this is
server one
and this is server two they use
basically a line to
to break and basically what you're going
to do
once you receive this from your id
department the most transitional way
is to go back to the path that you
installed
okay now i go into the my c drive oracle
which is the
path that i installed earlier so
basically i'm going to click
product i'm going to click my version
and i'm going to click
line to number one and then basically
you can go into
the network and admin this is the path
that you're going to install of course
depending on where your oracle home is
your path and version your path might be
different
so i'm going to copy this file that my
it department sent to me
and i'm going to paste here and that's
pretty much it
so just double check this is the name of
my server it's basically
the nickname is on-prem one i'm just
going to
copy this on-prem on-prem one as
my yeah copy to the clipboard okay go
back to power bi
i'm going to paste here and if
you have some complete queries you can
also type in there like
select and from basically here i'm not
going to put in any query
i'm going to click ok to connect in this
case because i'm using uh
like a fake one so basically it will
fail for sure but this is basically
using the transitional way to do
that but there's another uh so the
reason
for this like i personally dislike is
because
uh you have to upload the same tns file
into your power bi gateway server if
you're going to
schedule the scheduled refresh
and you have to also copy the same file
so there's a lot of work
and you can check my other video on the
popular gateway to basically you have to
match
the nickname so i recommend the other
way basically instead instead of
copy and pasting the tns file you can
ignore that
basically uh what you can do is let's
start over
once it department send you this file
you can open this
and once you open what you can do is
basically you can check my note
here this is this is basically i'm going
to expand this a little bit
so this is basically the format you're
going to do you're going to put in host
column and port slash ice id
so in this case basically one of the if
you can check
my case here uh basically this is a host
basically i copied the host into
into my connection stream basically
let's do this together
so i'm going to go back to the tns file
i'm going to copy the host first i'm
going to paste
here control plus v i'm going to type a
column once it's done what i'm going to
search for my
port number this is always a number so
in my case
it's one five two one i'm going to copy
this number control plus c
and ctrl v all right so once it's done
i'm going to type in a slash and from
right to the left slash
and i'm going to look for the ice id
of my connected connecting data
basically it is sometimes
a string something is a number in this
case it's cellsdb
i'm going to copy here and i'm going to
paste
so this basically you can use the same
string to connect to the oracle database
and basically it
is equal to your user tns file so
basically you just
paste what is in the tns file into the
connection string
and you're going to click the ok to
connect and this is easier because on
the database
on the gateway side it is easier to set
up so
i personally use this for all my oracle
database connection
and i also recommend you to do this
and you can click ok to connect
now we completed this setup now let's
show a real case on how the connection
look like
we are going to click the get data
button
type in oracle click the oracle database
and connect here you still see the
error message but just a compatibility
issue we can ignore that and click
go on here i type in my server
string the port and basic the
server name and i click ok
to connect here i put in my
username and the password under the
database tab
because this is a database credential
i click connect here on the screen
you see that that is successful you can
see all the schemas and the table you
have the access to
and you can also see if you expand the
folder which is the schema
you can see what the table is beneath
and basically here
is the conclusive demo and i will put
the troubleshoot guide in a separate
video