cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
H_Jones
Helper I
Helper I

Oracle connection, Sessions per user Limit

Hello,

 

I have started noticing a big issue with my oracle connection to Power BI.

 

I currently have numerous reports using Oracle which are published to Apps and are refreshed each morning using Power BI personal gateway. I have got a limit of 60 sessions from my DBA, but all of a sudden I seem to be hitting that limit and then I cant do any work in Oracle.

 

My DBA keeps killing all my sessions for me so I can continue working but within an hour or so I am hitting capacity again even though none of the rpeorts have refreshed in that time and I am just working on one report with 3 objects.

 

The Oracle DBA isnt a user of Power BI so doesnt understand what is happening and also doesnt want to just keep increasing my limit.

 

Please help!

 

Harriet. 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Microsoft
Microsoft

@H_Jones,

Gateway doesn’t release the connections after refreshing datasets. In your scenario, go to Task Manager and kill the process "Microsoft.PowerBI.DataMovement.PersonalGateway.exe", then check if the connections are killed. After that, relaunch the "On-premises data gateway (personal mode)" configurator, and retry configuration.

However, if the issue still persists, I would recommend you use on-premises gateway to refresh your dataset, and periodically run the following script to restart gateway service to force Power BI on-premises gateway to flush out its connections after refreshing.


net stop PBIEgwService
Taskkill /IM EnterpriseGatewayConfigurator.exe /F
timeout /t 30
net start PBIEgwService
" C:\Program Files\On-premises data gateway\ EnterpriseGatewayConfigurator.exe "
Exit


There is also a similar thread for your reference.
http://community.powerbi.com/t5/Integrations-with-Files-and/On-Premises-Gateway-utilizing-many-Oracl...


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.

View solution in original post

7 REPLIES 7
jasonmaskell
Advocate II
Advocate II

A work around that we have found reasonably successful, is a combination of changing settings within Power BI so that it doesn't use simultaneous connections, and being careful when in Power Query, to allow each preview to finish, before clicking the next action.

 

  • On the settings, under Options > Current File > Data Load - unticking Import relationships from data sources on first load(This appears to prevent Power BI spawning multiple queries to discover these relationships).
  • Options > Current File > Data Load - untick Allow data preview to download in the background - preventing Power BI spawning multiple queries to the database to get previews.
  • Options > Current File > Data Load - untick Enable parallel loading of tables - tells Power BI to only perform one query at a time.

 

The second part to this, is your behaviour when using Power Query. In combination to the above settings, you have to ensure that after each action you perform in Power Query (clicking to display a different step, performing different actions), that you always wait until the current preview is finished, before you click on the next action. This means that Power BI will spawn a query to the server, and once complete, close it back down to that single connection - which it will reuse).

If you click again while it's generating a preview and querying the Oracle data-source, this will spawn an additional query/connection - which may cause you to exceed the limit.

 

There may be some tweaks that can be made on the Oracle Server's end, but I'm not familiar with that side.

 

The explanation with this issue, is that each time Power BI queries the Oracle server, it will then spawn multiple processes on the server to fullfill that query, and each of those processes will get split again into multiple threads (e.g. one query, split into four processes, split into four threads each). 

Providing you do the above, once the query finishes, all those threads and processes get closed back down into that single connection. 

However, if you allow parallel loading, or click to perform another action, which requests another query before finishing the first - it will create an additional connection/query, which spawns multiple processes, and each of them are split into multiple threads. And that's where you hit your limits.

 

Apologies if my explanation is not technically correct - but this is my understanding from a user's perspective. YMMV

v-yuezhe-msft
Microsoft
Microsoft

@H_Jones,

Gateway doesn’t release the connections after refreshing datasets. In your scenario, go to Task Manager and kill the process "Microsoft.PowerBI.DataMovement.PersonalGateway.exe", then check if the connections are killed. After that, relaunch the "On-premises data gateway (personal mode)" configurator, and retry configuration.

However, if the issue still persists, I would recommend you use on-premises gateway to refresh your dataset, and periodically run the following script to restart gateway service to force Power BI on-premises gateway to flush out its connections after refreshing.


net stop PBIEgwService
Taskkill /IM EnterpriseGatewayConfigurator.exe /F
timeout /t 30
net start PBIEgwService
" C:\Program Files\On-premises data gateway\ EnterpriseGatewayConfigurator.exe "
Exit


There is also a similar thread for your reference.
http://community.powerbi.com/t5/Integrations-with-Files-and/On-Premises-Gateway-utilizing-many-Oracl...


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.

View solution in original post

I'm not using the gateway for refreshing my data (I'm trying to refresh via Power BI Desktop) and I'm getting this error. Any suggestions?

Apparently, the Power BI team doesn't care to investigate this DEFECT.  It is a clear and distinct issue.  Power BI Desktop in Direct Query mode creates Oracle sessions, uses them, then creates still more Oracle sessions, until it exceeds the user's defined SESSIONS_PER_USER limitation.  Why does Power BI Desktop not RE-USE existing Oracle sessions??

Is this an issue that Microsoft plans on fixing? Either reusing existing connections, or even disconnecting so that it doesn't hit the session limit.

I resolved my problem, putting the data to my data warehouse and connecting the power bi on data warehouse.

 

Flow:

CUSTOMER DATABASE >> ETL >> DATABASE(DATA WAREHOUSE) >> POWER BI

Having same issue here, our session limit is 5, it is frustrating. Is there any solution to this problem? cause the "solution" of this thread is not a solution, is a bandage to a bigger problem.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors