Performance issues on loading data from Oracle Autonomous Datawarehouse to Power BI desktop

[Münin]

Hi everyone,

I hope you're all doing well! I’ve been struggling with a challenging issue for the past couple of months and would greatly appreciate your insights or suggestions.

Context:
I’m attempting to load 150 million records from a partitioned table in an Oracle Autonomous Data Warehouse (ADW)—a PaaS service on Oracle Cloud—into Power BI Desktop. The Power BI Desktop is installed on a Windows instance, which is also hosted on Oracle Cloud Infrastructure (OCI). Both the database and the server are located in the same region.

To retrieve the data, I’m using a SQL query via the Oracle Database connector in Power BI Desktop and also the ODBC connection, I've tried both. The query is not a simple SELECT * FROM TABLE; instead, it selects only the 36 columns needed for the analysis. The connection between the server and the database is stable, as we’ve successfully loaded data from multiple other tables in the same database without issues. However, this specific table with 150 million records is causing problems.

The machine with the PBI Desktop instalation is quite powerful:

• 120 GB of RAM

• Intel Xeon Platinum 8167M CPU @ 2.00 GHz

• SSD storage

The Problem:
I’m encountering 3 main issues while attempting to load this dataset:

1. Memory Allocation Failure:
   As the data load progresses, the RAM usage steadily increases until Power BI Desktop crashes with the following error:
   “Memory error: Memory Allocation failure. Try simplifying or reducing the number of queries.”
   The server is completely free of other processes, and no additional RAM is being used by other applications. Despite this, the error persists. I’ve tried loading smaller portions of the data by filtering with a WHERE clause on partitioned columns (e.g., loading half the dataset at a time), but this isn’t a sustainable solution. It seems concerning that Power BI Desktop struggles to handle larger datasets in a single load.

2. Database Errors:
   On other occasions, I encounter what appears to be a database-related error:
   
   

3. Data load speed performance: Basically, the data load speed is terrible... we have cases on which we tried to load 11 million records and it takes almost 4 hours.

The thing is that our DBAs have confirmed that there are no issues on the database side—no sessions were killed, and there were no timeouts. Another thing to consider is the data load speed processs... is extremely slow. I’ve heard from some Power BI consultants that they’ve successfully loaded much larger datasets in significantly less time during networking sessions.

Key Questions:

1. Best Practices for Loading Large Datasets into Power BI from Non-Microsoft/Azure Data Sources:

   • Are there effective strategies for loading a large volume of data like the one I’m working with? (I don’t even consider this dataset to be exceptionally large compared to others.)

   • Has anyone in the community successfully loaded a larger dataset with a similar number of columns in a shorter timeframe? If so, how was this achieved?

2. Impact of Bandwidth, Firewalls, and Networking Settings on Loading Speed:

   • Could bandwidth limitations, firewall rules, or other networking-related configurations be affecting the data loading speed?

   • Has anyone made specific network or configuration adjustments to improve data loading performance?

Additional Context:

I’m already aware of the incremental refresh feature in Power BI. However, the challenge is that to enable it, I first need to perform an initial full load of 100% of the data up to this point. All subsequent updates can then be handled incrementally. The problem is that I’m unable to complete the initial load of 150 million records.

I hope I’ve explained the situation clearly. I apologize for any language issues, and I truly appreciate any insights or solutions you can provide.

Thank you all in advance for your help! Best regards.

[Anonymous]

Hi  @Münin ,

-- Memory error.

1. Use the 64-bit version of PBI desktop and the matching 64-bit version of driver. 
Download Microsoft Power BI Desktop from Official Microsoft Download Center
Evaluation configuration settings for Desktop

-- ORA-03113... Impact of Bandwidth, Firewalls, and Networking Settings on Loading Speed.
2. Even within the same region, if the VM has limited network bandwidth, transferring 150 million rows could be slow. Maybe check the VM's network specs and see if there's a way to increase throughput.

-- Incremental refresh

3. If you have a PPU or higher license, the documentation referenced to avoid loading data on the first refresh.

Advanced incremental refresh and real-time data with the XMLA endpoint in Power BI - Power BI | Micr...

If the problem is still not resolved, please open a ticket here and our engineers will contact you proactively.
Get support

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Münin]

An update. Last night I made a 80 millios rows load using filtered query on a partitioned column (a date one) also using ODBC and the load ended fine.... it takes 6-8 hours, but at least this time ended. Now I see another issue, I save the .pbix and its a 13 GB file, I've never seen a file this big, actually I think this is not normal or is not around best practices, but what do you guys think.