Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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:
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.
Has Any one found a solution please update have got 32million records in largets table using odac drivers and dataflow for refreshing
@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:
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.
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:
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.
@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