Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Import data from Oracle is very slow

Hi Experts,

 

With my requirement Live connection is not an option I have to go with import.

 

I am trying to load 10 million rows from oracle to power bi. It is very slow loading (200k for 10min, Roughly 6hrs). What can I do to improve performance?

 

FYI, I am selecting columns instead of all columns and also passing SQL query in advanced options instead of selecting table/view.

 

@amitchandak @GilbertQ @Greg_Deckler @parry2k @mahoneypat 

 

Thanks in Advance!

1 ACCEPTED SOLUTION

Hi @Anonymous ,

DECLARE could not support in power bi desktop. Power BI Desktop will always resolve the front end input SQL statement as a derived table. So when passing the SQL statement, the "Decalare"statement cause syntax error. 

You can refer this way to pass parameters by sql in power query, it could help you better:

  1. Declare parameters for SQL query in Power BI 
  2. Power BI Desktop Query Parameters, Part2 – Dynamic Data Masking and Query Parameters 

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
MallikarjunaBan
Helper II
Helper II

Has Any one found a solution please update have got 32million records in largets table using odac drivers and dataflow for refreshing

Anonymous
Not applicable

@amitchandak @v-yingjl  Thanks you for the reply. I tried giving where condition in my select and that helped and it ran very fast.

 

I am trying to load 10 million so I put a where condition <= 20000000 (20million) and data loaded very fast.

 

Now, It solves my main problem and it leads to another one. How can I Make it dynamic?

First, I need to count the rows in that table and assign it to a variable, and then I have to use that variable in the where the condition. 

 1. Does the Power BI to oracle connector allow us to use the below query? I tried but not working.

 

Example:  

DECLARE   variable_A NUMBER;

BEGIN
   SELECT COUNT(*) INTO variable_A FROM my_table;
END;

SELECT COL1, COL2, COL3, FROM TABLE 
WHERE ROWNUM <= variable_A;

Thanks in Advance

Hi @Anonymous ,

DECLARE could not support in power bi desktop. Power BI Desktop will always resolve the front end input SQL statement as a derived table. So when passing the SQL statement, the "Decalare"statement cause syntax error. 

You can refer this way to pass parameters by sql in power query, it could help you better:

  1. Declare parameters for SQL query in Power BI 
  2. Power BI Desktop Query Parameters, Part2 – Dynamic Data Masking and Query Parameters 

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

v-yingjl
Community Support
Community Support

Hi @Anonymous ,

Before you load the data from Oracle into power bi desktop, You'd better shape the data set in Edit Queries,

for example:

transform data.png

You could remove the useless columns, filter data, etc.  

These actions could reduce the size of the dataset and improve the performance of import data

You could also use DirectQuery instead of Import.

 

In addition, here is a document about optimization in power bi that you can refer.

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , There was the idea for that and got delivered long back - https://ideas.powerbi.com/ideas/idea/?ideaid=4cfb2858-ef99-4a62-9fa1-94d5a2b1dc75

 

But it is still slow, users are complaining on the same idea. I logged a new one to keep track. Vote for it

https://ideas.powerbi.com/ideas/idea/?ideaid=3ea4da28-52c8-ea11-bf21-0003ff528e0d

 

Also, refer to what this user has done : https://community.powerbi.com/t5/Desktop/Oracle-data-load-speed-into-Power-BI-Desktop/td-p/655019

 

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.