-4

I have a query that has around 160 million rows that would take me 4 hours to import into Power BI.

Ever since my company changed their server to Azure, I can never import this query successfully. It would start loading for 1 million rows-ish, after 1 minute or two this error always pops out.

I tried:

  1. changing the command time out to 200 minutes, still errors out within loading for a minute or two, sometimes within 10 seconds
  2. if I select top 1000 rows in my query, it will complete without error. But when I switch back to the original query, it always fails.

Attaching the error message. I have talked to the DE in my team and they don't seem to have a clue. Does anyone have any idea on how to fix this?

this is the error message

Thom A
  • 88,727
  • 11
  • 45
  • 75
Xiaofan Chen
  • 1
  • 2
  • 2
    [Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) – Thom A Aug 25 '22 at 14:00
  • "Connection was forcibly closed by remote host" means the network communication abruptly ended. Could be a router or load balancer issue, or the server crashed, or just a flaky network connection. Can you test running Power BI Desktop on an Azure VM? Does refreshing in the Power BI service succeed? – David Browne - Microsoft Aug 25 '22 at 22:17
  • What version of the OleDB or Odbc driver are you using? You might find it doesn’t support TLS 1.3. Add that info to the question please and maybe find out what versions of TLS are being allowed from you azure db. – Richard Hooper Aug 25 '22 at 23:11

2 Answers2

0

We had the same problem and went for a delta lake in combination with option 1.

  1. You have to ask your self first why you are importing so much data. Always keep your model as small as possible. I can't imagine you are looking at every row. If this is needed you could you could use a combination of direct query for details and loading an aggregate for your reporting. But load aggregates instead of everything.
  2. Maybe your can load less history, like the last two years.
  3. You could look into loading incrementally, you could load per partition.
  4. You can try to increase the DTU's for your server.
iamcj
  • 27
  • 5
0

Try to clean the data load cache: File -> Options and Settings -> Options -> Data Load -> Clear cache and Save the file and Load again

ram
  • 1
  • 1