cancel
Showing results for 
Search instead for 
Did you mean: 
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

4 REPLIES 4
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

 

 

 

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

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 June10th!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it Out!

Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors