Connect To Oracle Database

[jmiridium]

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:

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

When I connect I get the following error:

[AnalyticsNinja6]

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.

[Anonymous]

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.

[ocatt]

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]

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]

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?

[sqldusty]

This made me laugh.

[dniedrauer]

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

[jmiridium]

Did all the steps get a different failure:

[jmiridium · 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

[JimKingPowerBI]

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)
)
)

[JimKingPowerBI]

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)
)
)

[Brindha]

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

[jmiridium]

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:

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

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

[dmvega]

Great Post... thanks a lot!!!

[dniedrauer]

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

that's what I get after the ODBC steps

[jmiridium]

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

[dniedrauer]

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. 

[jmiridium]

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

[dniedrauer]

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....

[jmiridium]

Try IP address?