Performance tuning Oracle ODAC driver with FetchSize option on OnPremises Data Gateway

Frankly, Power BI is not as fast as a rival product in loading information. I will skip any typical poster's points of why would you need that much data / use a star schema / etc.

We are no longer using ODAC or instant client here to connect to Oracle's ADW from Power BI. We are using Oracle Client for Microsoft Tools (the latest Oracle-Client-for-Microsoft-Tools.exe) after Oracle's security changes in early March. I assume you are reaching an ADW but if not I still would expect this to be your best tools. We decided to scrap our gateway server and rebuild for this new setup. Registry key settings are still in effect with this different solution. I do not recall changing the machine.config manually and I no longer have access to that server to verify.

We run this setup on both the gateway server and on Power BI Desktop users machines.

Good luck!

Sources

https://www.oracle.com/sn/a/ocom/docs/database/microsoft-powerbi-service-gateway-adw.pdf

https://www.oracle.com/database/technologies/appdev/ocmt.html