Forum Discussion

H_Jones's avatar
H_Jones
Helper I
8 years ago
Solved

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 usi...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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-Oracle-database-connections/td-p/153324


    Regards,
    Lydia

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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