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
Mankney
Regular Visitor

On premise Gateway and an Oracle Datasouce

Hi I am trying to configure an Oracle Datasource in our new On premise Gateway and am having difficulties. I have not found much on the web about it. Does anyone know of some good learning materials about connecting Oracle to the On Premise Gateway datasouce? I am specifically looking for examples of how it is configured so I can adapt it to our situation.

 

I do have the Oracle client on the Gateway box and can use the SQLPLUS command to open a connection to the oracle database in question but I just can't get the gateway to work. We do not use Service_Names in our TNSNAMES file and I wonder if that might have something to do with it.

 

A month ago I new nothing about Power BI or Oracle and am trying to come up to speed real quick. Any help would be appreciated on this specific issues and where I can learn more.

Thanks

 

 

1 ACCEPTED SOLUTION

We have solved it. We added the SERVICE_NAME parameter to the TNSNAMES.ORA file. In the Power BI Portal we also configured the server name as //<Host>/<Service_Name>. If we have a Host name of ORAPROD and a SERVICE_NAME of PRODDB then the Server name in the Power BI Console under Settings and then Manage Gateways, under your Gateway and Add Data Source  would be //ORAPRDO/PRODDB. It worked fine then.

 

Thanks for the help.

View solution in original post

6 REPLIES 6
v-yuezhe-msft
Employee
Employee

Hi @Mankney,

What error message do you get when you add Oracle data source under on-premises gateway? As stated in the following article, Power BI gateway will go off of the net_service_name defined in the tnsnames.ora file. If it isn't configured, you will not be able to connect.

https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem-manage-oracle/

In your scenario, please ensure that you install the 64-bit Oracle client, verify that the tnsnames.ora file is properly configured and you are using the proper net_service_name. Also make sure that the net_service_name is the same between the machine using Power BI Desktop and the machine that is running the gateway.

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This is the error I am getting.

 

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

 

Here is my TNSNAMES file with names changed.

 

PROD.WORLD=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)(HOST=ORAPROD)(PORT=1521)
    )
    (CONNECT_DATA=(SID=PROD1)

 

I am working with our Oracle DBA this afternoon to see if he can get the SERVICE_NAME variable working in TNSNAMES.ORA.
    )
  )

 

Here is what I would put into the field when I configure the datasource.

 

Data Source Name:   Oracle Test

Data Source Type:    Oracle

Server:                      PROD.WORLD

Authentication Method:   Basic

Username:   user1

Password: xxxxxxxx

 

Thanks for your reply. I am checking on the other items you mentioned.

We have solved it. We added the SERVICE_NAME parameter to the TNSNAMES.ORA file. In the Power BI Portal we also configured the server name as //<Host>/<Service_Name>. If we have a Host name of ORAPROD and a SERVICE_NAME of PRODDB then the Server name in the Power BI Console under Settings and then Manage Gateways, under your Gateway and Add Data Source  would be //ORAPRDO/PRODDB. It worked fine then.

 

Thanks for the help.

Never has a solution been this good, very well explained and true to your words it fixed the issue! Thank you. The error wasnt to useful and could not have lead to a solution, but thanks to you it fixed the issues in less than 2 seconds.

Many Thanks

Lolu

@Mankney @Lolu @v-yuezhe-msft I had a similar issue and this post helped my configure the Gateway entry for the Oracle server. I was thrilled that it was so simple.

 

But now when I deploy a model that uses that service name to Power BI and try to configure SCHEDULED REFRESH, I get an error under the Gateway section stating that it is "not configured correctly". I also gives me an option to add the data source to the gateway with the following: Oracle{"server":"<service name>"} But when I click Add to gateway, the entry that it wants to add only includes the service name, not the full format you have as your solution, that of "//host/service_name"

 

On the one hand the full syntax works from the perspective of the GATEWAY, but from the perspective of the MODEL, it only wants the service name, which DOESN'T work (can't be added).

 

Curious to know if you ran into this, and what was the resolution.

 

Thanks in advance.

 

PS (an hour later): I solved this. The solution was in the Power BI model itself. When connecting to the Oracle database, use the format as expected by the Gateway, that of "//host/service". Note that this need NOT be edited in TNSNAMES.ORA file becasue the two components of it are each contained in the entry. So when you do a GET DATA on the desktop, specify //host/service instead of just the service. 

 

Maybe that helps someone somewhere.

 

 

Hi @Mankney,

Glad to hear the issue is solved. You can accept helpful reply as answer, this way, other community members would easily find the solution when they have same issues.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Top Solution Authors
Top Kudoed Authors