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. Just have in mind, that this method might prevent query folding, so it is mostly suitable for scenarios where you have to pull the full tables anyway.

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 (16) Write a comment

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

    Reply

  2. Hi Imke,
    The problem you’re describing is actually caused by Oracle drivers default configuration, and not Power BI connector. Both ODP.NET and OLEDB drivers from Oracle assume that you have fast network between your Oracle DB and DB clients. To be honest, it makes some sense; but in real world we often need to import data from Oracle databases where the network is slow and the number of database round-trips is huge. Not only standard Oracle connector for Power BI will be slow in this situation – you can look at ancient post from 2010 about the same problem with OLEDB: https://scottadamsbi.wordpress.com/2010/11/02/sql-server-analysis-services-and-oracle-ole-db/.
    What is true regarding OLEDB is that finding and using the FetchSize parameter to control data cache for retched data is much easier. Indeed, you just pass a connection string parameter, and you can set it independently for different queries, it doesn’t affect any other queries etc. But, FetchSize can be controlled in ODP.NET as well – so, surprisingly, you can speed up your Power BI import with the standard Oracle connector 🙂

    In ODP.NET, you can set FetchSize in Windows Registry and XML configuration files for .NET. Therefore, if you have sufficient permissions, you may want to set it in Registry or “machine.config” file; if not, you still have an ability to edit “Microsoft.Mashup.Container.NetFX45.exe.config” in your Power BI Desktop folder.
    You can find a good example of XML piece in the official documentation from Oracle: https://docs.oracle.com/cd/E11882_01/win.112/e23174/featConfig.htm#ODPNT158. After making such a changes, all Mashup Engine processes spawned by Power BI Desktop will use the fetch size specified in config file, resulting in the same performance improvement.

    One important note is that ODP.NET implementation of FetchSize operates with bytes, not rows. So you may want to set it to something like 2-5 MB (again, that’s the matter of trial and error tests).

    I have to admit that I have also came up with OLEDB first time I faced such a problem. But ultimately I believe that pasting a few rows in “Microsoft.Mashup.Container.NetFX45.exe.config” is definitely a way to go as well – and it preserves all existing code of your queries (not even speaking about query folding and other advantages of standard ODP.NET connector).

    When you have a minute, give it a try 🙂

    Reply

    • Thank you so much Dima, for this additional insight. Excellent!
      Have you used this with a gateway as well? How could one adjust that there?
      Thanks and best regards,
      Imke

      Reply

      • With regard to the topic discussed, there is no difference between Power Bi Gateway and Power BI Desktop, because both of them are spawning Mashup Engine processes in order to get data from Oracle. So you can use either of the above ways (editing Windows Registry, “machine.config” file or “C:\Program Files\On-premises data gateway\Microsoft.Mashup.Container.NetFX45.exe.config” file) to set FetchSize value.

        Some things that become more important when we talk about Gateway reconfiguration:
        – First two methods are machine-wide. You can imagine Oracle connections from clients other than Power BI Gateway on the gateway server, and you probably do not want to change their behaviour.
        – Third one is a “gateway-wide”, meaning all the Oracle connections through the gateway will use FetchSize you’ve set in config file (even the connections which are not yours). That’s not so critical because all these connections are basically intended for the same purpose of getting rather big amounts of data from Oracle to Power BI; yet it means you have to be much more careful while trying to determine optimal FetchSize value.
        – You have to have admin permissions on the Gateway server.

        As one can guess from the above, OLEDB method might turn to a better approach when it comes to enterprise configurations (as opposed to Desktop case). Still the point is we CAN speed up a data import using standard Power BI connector to Oracle, not only with OLEDB.

        Reply

    • Could you please be a little more explicit about what goes into the files? I’ve tried adding
      <oracle.dataaccess.client>

      </oracle.dataaccess.client>

      to the
      “Microsoft.Mashup.Container.NetFX45.exe.config”

      But then Power BI stopped reading from any database SQL and Oracle. Once I removed that, it started reading again.

      Reply

    • Yes, this usually works via a gateway server.
      You have to implement the odata drivers on the server as well.

      Reply

  4. Hi Imke,

    Sorry, I probably wasn’t clear. Did you get this working with the Oracle drivers on a Data Gateway.

    The users don’t want to go with OLD DB due to a lack of query folding, but we want to boost the speed of the standard ODAC drivers.

    Thanks,

    Michael

    Reply

Leave a Reply