Your Oracle data import in Power BI and Power Query is slow?

If you’re using the native Oracle connector in Power Query, you will probably experience a very slow import performance. Thanks to Tristan Malherbe for recommending to use the OleDB-connector in Power Query instead. This speeds up import enormously.

How to create the connection string

If you’re using the OleDb.DataSource connector instead, you have to pass a connection string as the first parameter and an optional query record as the second parameter. To speed it up even more, you should use a FetchSize parameter in the connection string. For me, this didn’t work when I pasted it into the popup-window. So I had to manually add it in the query editor:

OleDB connection to an Oracle database

The “:1521” in the connection string is the port number, which is usually 1521 for Oracle databases.

You can play around with the FetchSize to determine the ideal value for your specific use case.
For security reasons, make sure NOT to pass your credentials in the connection string (as mentioned in the link above), but to pass them in the credentials section instead (that’s the 2nd dialogue in the import process)

Query speed had been improved by order of magnitude for me:

Import speed difference

Enjoy and stay queryious 😉

Comments (2) Write a comment

  1. It seems that using OLE DB data source connector disables query folding.

    Reply

    • Hi Kristian,
      that’s possible. I didn’t check that out, actually.
      Thanks for pointing this out.
      /Imke

      Reply

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz