Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jmiridium
Helper IV
Helper IV

Connect To Oracle Database

I need a connection solution for Oracle. This shouldn't be this difficult. I am trying to connec to an Oracle server and get the following:

oracleissue.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

However, the Oracle client is installed. I try to connect:

 

oracleissue2.JPG

 

When I connect I get the following error:

 

oracleissue3.JPG

1 ACCEPTED SOLUTION

Figured it out. After finding the path for the .ora file I entered the information in the file. Then in PowerBI I added the connection name from the .ora file to the connection string

View solution in original post

28 REPLIES 28

Try BI Publisher Connector. Its the easiest way to directly connect Power BI to Oracle Fusion Cloud database (ERP, HCM, SCM, and CX). It also helps connect Power BI to the physical layers of OBIEE/OAC/OAS via OTBI data models and visualize data in minutes.

 

BI Publisher Connector is compatible with both Power BI Desktop and Service and all editions including Pro and Premium. It doesnt store any data anywhere, ensuring secure data connection between Power BI and Oracle. 

 

Benefits

Hassle-free one-time set up

Error-free reporting & data visualization
Automate in Power BI Service

Time and cost savings

 

Hope this was useful.

sophiexu
Frequent Visitor

Get data/ oracle database, then use this string to server: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)))

see https://blogs.solidq.com/en/businessanalytics/get-data-in-power-bi-from-oracle-database/. succeed to connect to oracle now.

Thanks alot, this worked for me, if you dont use a service_name but just a SID you can change the last part 

 

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=XE)))

ketakibavbande
New Member

Hello,

 

I am new to learning Power BI. I need to connect Oracle Cloud Database to Power Bi.Can anyone pls guide me steps as how should i bring data to power bi and create reports. I have only found that this can be done using API's. Hence, i connected power Bi with Powershell and using those cmdlets i have login into power bi. Am i on the right path?What should i do further to connect with Oracle Cloud database

Greg_Deckler
Super User
Super User

I'm not an Oracle guy but my impression from being around it is that the words "Oracle" and "not difficult" do not belong in the same sentence. 🙂

 

Perhaps this will help? http://www.dbasupport.com/forums/showthread.php?61511-ORA-12504-TNS-listener-was-not-given-the-SERVI...

 

One other thing, do the bits match between Power BI Desktop and Oracle Client?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

This made me laugh.

It ended up being a firewall issue. Everything was done correctly. 

Did all the steps get a different failure:

 

oracle4.JPG

Figured it out. After finding the path for the .ora file I entered the information in the file. Then in PowerBI I added the connection name from the .ora file to the connection string

What? Huh?

 

So you identified where the TNSNames.ora file was after you installed the Oracle client, right?

 

What do you mean by "I entered the information in the file"? What information? What file?

 

What do you mean by "I added the connection name from the .ora file to the connection string"? What connection name? What connection string?

 

Here's a TNSNames.ora entry:

 

XXXP4J,
XXXP4J.ORACLEOUTSOURCING.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXXodmcp1234.oracleoutsourcing.com )(PORT = 5010))
)
(CONNECT_DATA =
(INSTANCE_NAME = XXXP4J)
(SERVICE_NAME = XXXP4J.ORACLEOUTSOURCING.COM)
)
)

What? Huh?

 

So you identified where the TNSNames.ora file was after you installed the Oracle client, right?

 

What do you mean by "I entered the information in the file"? What information? What file?

 

What do you mean by "I added the connection name from the .ora file to the connection string"? What connection name? What connection string?

 

Here's a TNSNames.ora entry:

 

XXXP4J,
XXXP4J.ORACLEOUTSOURCING.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXXodmcp1234.oracleoutsourcing.com )(PORT = 5010))
)
(CONNECT_DATA =
(INSTANCE_NAME = XXXP4J)
(SERVICE_NAME = XXXP4J.ORACLEOUTSOURCING.COM)
)
)

I have the same error Details: "Oracle: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA"

and i couldn't Fix it . I am New to power BI . Can you please tell me how to fix it

 

OK what you do is install the 12c client. When it is installing make sure you are aware of the installation paths. You will need to modify things later.

 

Install the client then edit with admin rights the file tnsnames.ora. You will need to add the following connection:

 

YOURSERVERDESC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ServerName or IP Address)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SERVICENAME)
    )
  )

 

Once you do that test this connection with your ODBC data source. Use admin rights to add a connection:

 

oracle1.JPGoracle2.JPG

 

The Oracle ODBC Driver Configuration will come up. Put in your connection information and click Test Conection:

 

oracle3.JPGoracle4.JPGoracle5.JPG

 

Once you have verified via ODBC the same connection should work:

 

oracle6.JPG

Great Post... thanks a lot!!!

ORA-12154: TNS:could not resolve the connect identifier specified 

 

that's what I get after the ODBC steps

You edit the ora file then try to connect what you named the identifier as?

Not sure, but here's everything in tnsnames.ora: 

 

BI_PRICE_USER1 XXX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lxx2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xxxx.company.com)))

 

Does "identifier" = "service name" or does "identifer" refer to something else in the tnsnames.ora file? 

 

FYI - on Excel connect to Oracle, I get a timeout error instead. 

So you're connecting using this?

 

BI_PRICE_USER

 

In my case I'm using servicename.domain.xxx so I connect to servicename.domain.xxx

BI_PRICE_USER is the "connection name" in the info given. 

 

So should I have something else there instead like xxxx.company.com? 

 

The server name goes at the beginning? Should my tnsnames.ora file look like:? 

 

du1p.company.com =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lira2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = du1p)))

 

In either case, i get the exact same error....

Try IP address?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.