Our organization has a very strict limit of ten (10) SESSIONS_PER_USER on the Oracle 12c databases against which I am developing reports using DirectQuery mode. With as few as four or five separate queries, Power BI Desktop will cause Oracle errors because it is attempting to open new sessions instead of re-using existing sessions. This is unacceptable behavior, especially because it requires more of my work time every day as reports become more complex.
For your situation, the best way is expanding the limit for sessions_per_user.
For another way, I'm not sure if that will work but you could have a try.
You could load your table for one time and then create the blank query to reference the other tables in Query Editor.
In addition, you could refer to this similar thread.
Thank you for the courtesy of your reply. Unfortunately, as I mentioned in the original post, the SESSIONS_PER_USER value is considered a very strict limit and is not eligible to be changed.
For optimization, performance and usability reasons we do not constrain sessions to back end systems.
If you wanted the team to consider adding your vote to this idea:
That said with only ~3 votes the team is unlikely to get to it any time soon.
Thank you for this response. My concern is, Power BI seems to open new database sessions when a refresh is requested, instead of closing and/or re-using existing sessions. This is very frustrating behavior.
I also see other users have similar concerns.
If anyone has any suggestions to minimize or eliminate this issue, I would appreciate a post from you.
Check out the changes to the Power BI Community announced at Build.
Find out more about the May 2023 update.
Visit our Data Stories Gallery and give kudos to your favorite Data Stories.