cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zeel801p Frequent Visitor
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. 

14 REPLIES 14
Moderator v-yuezhe-msft
Moderator

Re: Oracle Connector Leaving Open Connections

@zeel801p,

 

To force Power BI gateway to flush out its connections after refreshing, you would need to periodically run a similar script suggested by brendan_w in the following thread to restart gateway.

 

http://community.powerbi.com/t5/Integrations-with-Files-and/Possible-BUG-Power-BI-Personal-Gateway-D...

Replace GWConfig.exe with EnterpriseGatewayConfigurator.exe in the script that brendan_w provides , and the EnterpriseGatewayConfigurator.exe locates in C:\Program Files\On-premises data gateway.

 

Regards,

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

Re: Oracle Connector Leaving Open Connections

Thanks for the suggestion, is there any way to apply this to PowerBI Desktop. We do not currently have the gateway setup because of some firewall (and the flushing) issue and your advice will be helpful for down the road. But currently we are just having this issue when we click refresh on the desktop application. 

 

Edit: I also noticed that once I close out of the desktop application, the connection still continues to remain open. 

Moderator v-yuezhe-msft
Moderator

Re: Oracle Connector Leaving Open Connections

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

Re: Oracle Connector Leaving Open Connections

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?

Moderator v-yuezhe-msft
Moderator

Re: Oracle Connector Leaving Open Connections

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

Re: Oracle Connector Leaving Open Connections

@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?

Moderator v-yuezhe-msft
Moderator

Re: Oracle Connector Leaving Open Connections

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

Re: Oracle Connector Leaving Open Connections

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

Moderator v-yuezhe-msft
Moderator

Re: Oracle Connector Leaving Open Connections

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 61 members 1,232 guests
Please welcome our newest community members: