Long Time Waiting

[Anonymous]

My dataset is taking a long time to update (I'm on the desktop). Too long, like two hours. I'm importing 10 million rows from an OLTP database (it's not ideal, but it's the only alternative for now). I believe that the delay time is due to the fact that the bank is poorly performing and because it is OLTP, and not because of the performance of my machine. Am I right to think that?
I think that too because I make an equally large query from another server that takes much less time.

[Anonymous · Accepted solution]

Create partitions and load simultaneously. If you can't create partitions like in SSAS, then you can create several tables in Power Query with exactly the same spec apart from a filtering condition. I don't know what fields you have in the big table, but you could certainly retrieve a portion of rows based on some condition(s). Just retrieve different portions into different tables. If you have a date field, then you could create partitions based on year or month...

However, if the problem is with your data source not being capable of retrieving rows quickly enough... You're out of luck. There are system performance counters that are able to tell you what the true speed of retrieval is. Use it to diagnose the problem.

Best
D

[AllisonKennedy]

How many columns are in each dataset? As @Greg_Deckler  it's often more about what is going on in the query and the number of merges and columns than the rows. Power BI is generally ok with lots of rows, but the columns, calculations and merges are what can slow it down. 

Have a look at some suggestions in this article to reduce the size of the dataset you're pulling through: https://docs.microsoft.com/en-us/power-bi/guidance/import-modeling-data-reduction

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[Greg_Deckler]

That's generally a long time for 10 million rows although it depends on a number of factors. How compex is your query, any merges, things like that? Any unnecessary things like reordering columns? Perhaps simplify it to just get the raw data in with as little transformation as possible? Have you experimented with the query performance tools?

[Anonymous]

My query uses only a few JOINS and a WHERE filter. I think this is not complex ... What performance tools do you refer to? I'm new to Power BI and I don't know about these tools.