cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dgwilson
Resolver III
Resolver III

Oracle data load speed into Power BI Desktop

Hi there... I seeking some advice with a loading speed experience with Oracle Database loads into Power BI Desktop.

Specifically I’m loading data from an oracle database… I have permission allocated etc…
It’s taking in the order of 1 hour to load 2 million rows. I don’t see 2 million as a lot of rows and believe it should be going significantly faster.

When this is fully loaded… I’d been needed to load 48 million rows. Gulp.


Now I admit that I can add a where clause to the select statement. That will happen later. And I can probably be selective about the columns that are loaded. And I’ll have to do both of those.


For now … I’m concerned that it takes 1 hour to load 2 million rows. Would this be a normal experience?

 

This is In the office, LAN connected (Gigabit), loading oracle database to Power BI desktop.
HP laptop 24GB RAM.
February and March desktop releases.
… Import… Databases… Oracle… … so Oracle driver I guess.

Any advice on where to look for performance measure and/or performance tuning?


Any ideas?

 

- David

1 ACCEPTED SOLUTION

The parallel load commands didn't work for me - the schema needs to be configured to deliver for that.

 

I have performed a test foing a SELECT <field>, <field>, <field> from <tableName> - i.e. only a selected numebr of fields rather than a SELECT * from <tablename> and I can say that 1.5M rows loaded in about 2.5 minutes - way better than 30+ minutes.

 

For me the full SELECT * is OK for investigation and understanding the dataset. When it's ramped up to a production scenario it is a good idea to only load what is needed.

 

- David

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi,

I recently worked with SSIS +Oracle combination. I had the same performance issue with the  Oracle OLEDB provider. Fortunately, we have Attunity connectors to Oracle that use a different mechanism to extract or load data to oracle much much faster than the OLEDB. I dont think we have the same support for Power BI (at least I havent found something yet).  May be you can try using /+PARALLEL(X)+/ hints in your select statements.

The parallel load commands didn't work for me - the schema needs to be configured to deliver for that.

 

I have performed a test foing a SELECT <field>, <field>, <field> from <tableName> - i.e. only a selected numebr of fields rather than a SELECT * from <tablename> and I can say that 1.5M rows loaded in about 2.5 minutes - way better than 30+ minutes.

 

For me the full SELECT * is OK for investigation and understanding the dataset. When it's ramped up to a production scenario it is a good idea to only load what is needed.

 

- David

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!