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
zeel801p
Frequent Visitor

Oracle Connector Leaving Open Connections

So I recently started using Power BI with our oracle server to build some reports. What I have noticed and it has caused severe issues is that when I go to refresh my data, Power BI (via "Oracle Connection" and a tnsnames.ora file) are opening about 20-30 new connections on the server (Red Hat with Oracle DB, currently on-prem in process of being moved to AWS). This is not too much of an issue but the connections are not being dropped once the data refresh is done. I do not know too much about Linux but they are sitting in an "ESTABLISHED" status. This obviously presents a problem because 30 new connections will max out most servers within 3-4 refreshes (considering other outside users, connections, etc). Even more odd is that OLE DB connection via Excel on the same machine using the same tnsnames.ora files does not cause this issue. Any help would be greatly appreciated. 

22 REPLIES 22
PierreC
Frequent Visitor

Any updates on this issue?

We have the same problems with September 2022 server version.

Microsoft should add a way to turn off connection pooling. Just like you can define it with .NET Connection Strings:
https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ole-db-odbc-and-oracle-connection-poo... 

popotam
Regular Visitor

We are also getting the same issue when it comes to Netezza. 

Is there any solution idea to avoid multiple connection sessions?

Rafaelhk
Frequent Visitor

Hi there!

 

Any updates on this issue?

We are facing the same problems, running PBI desktop, Jan 22 version.

wobi
Frequent Visitor

We too face open Oracle connections - when PBI reports are scheduled on our PBI Server - any hints how to get them closed automatically - cleaning up from Oralce site is not a solution!

Wobi

wobi
Frequent Visitor

Well what i found out about open Oracle connections when using PBI Reportserver Schedules: They stay open -until closed by killing the processes or by reboot  - so we do regular reboots of the PBI server - sorry for the short service interruption - but MS is not giving any parameters to get the connections closed otherwise.

wobi
Frequent Visitor

We observerd that hanging / not closed mesh container processes keep the connection open - kill the process and the connection @ oracle is closed - are there any options to controll the mesh container prozesses cleanup - would be helpful!

Wobi

Rafaelhk
Frequent Visitor

We are trying to avoid rebooting the servers (DB server is unthinkable, PBIRS server is not recommended, but not off the table).

 

We found that manually refreshing, by opening the report in the PBI desktop and refreshing from there, has a different behavior and actually closes the connections as the refresh ends.

We cheched th ODAC driver from our desktops and servers and they are both running the same versoin, so it`s not a problem with the driver version.

If anyone is facing the same problem, please vote this idea to fix the bug:

https://ideas.powerbi.com/ideas/idea/?ideaid=a3059350-4a14-ed11-b5cf-281878deb618

v-yuezhe-msft
Employee
Employee

@zeel801p,

Consider to add Table.Buffer function in the codes of your advanced editor to reduce the load on the database server, or kill the connection from the Oracle server side after you refresh data in Power BI Desktop.

Regards,
Lydia

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.

Thanks @v-yuezhe-msft,

 

I was about to try the Table.Buffer method, but am now noticing that I cannot even do a refresh without PBI opening too many simultaneous connections with even one refresh. Is this just behavir on Oracle connections or is this specific to my situation?

@zeel801p,

Where do you add the Table.Buffer method? Do you get any error messages when refreshing in this case?

Regards,
Lydia

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.

@v-yuezhe-msft

 

I did not get that far because I noticed taht it is opening too many simultaneous connections.

 

Something else I noticed is that our new AWS server is handling the connections much better then our on-prem one. It is flushing connections pretty frequently and routinly. 

 

The main issue I am having is the number of connections it opens at one time. I did read somewhere that you could disable PBI refreshing in parallel. Any chance this can be explained if it would help?

@zeel801p,

Disable the following option in Power BI Desktop.
1.JPG

Regards,
Lydia

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.

@v-yuezhe-msft

 

Yeah this helps in pushing a bunch of connections at the same time but the issue with not flushing properly still persists, now on two completly different Red Hat Oracle servers. 

@zeel801p,

I haven't found any methods in Power BI Desktop that can be used to flush connections. Please try to kill these connections from your server side. And for those old tables that don't need to be refreshed, disable "Include in report refresh " option in Query Editor of Power BI Desktop.
1.JPG


Regards,
Lydia

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.

Yeah no luck. As soon as I flush them they are being recreated the next refresh. And now that it is creating 30+ connections per refresh I cannot even flush it after every refresh since it does not complete a refresh.

@zeel801p,

I test the similar scenario that refresh the data from SQL Server database, as long as I click refresh button in Power BI Desktop, it will open connections in the server side. Currently, we can only kill these connections from database side after the refresh.

Regards,
Lydia

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.

Any idea if you guys will be fixing it or is it more realistic to just look into alternatives?

Anonymous
Not applicable

would be great to have a resolution to this as it is a persistant problem

I find it hard to believe that other Oracle users are not having issue with this many open connections. I am working on splitting my reports into several smaller reports to account for this but it is not an ideal situation. 

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