Refreshing data in Desktop timing out

[heidibb]

Hello,

I am trying to refresh my data model in Desktop and it's runnig for a very long time and then timing out. I have not had this issue in the past and wondered if there are any tips to diagnose what might be happening?

I have confirmed that there is data in the tables that I'm joining to and all is ok on that end of things, but when I try and refresh in the desktop file, it's running for about 15 minutes and then timing out.

Thoughts?

[v-sihou-msft]

@heidibb

What's your data source?

I suggest you also open monitor tools on your data source side to get some detail error message.

Regards,

[heidibb · Accepted solution]

Hello all,

While I don't know WHY I lost connection, I did figure out that by making a minor change to my sql query reestablished my connection and I was able to refresh the desktop file.

I have six data connections in this report, 3 are direct to tables on our Server and 3 are sql queries.

The first time this happened (on my original post), I removed the "order by" portion of my query and all was fine. I assumed the order by was causing too much processing time and that must have been it.

However, this happened again a few days ago, where all of the sudden, my refresh was failing with a "timeout expired" error.

I verified there is data in the tables on the server, and I tested each of my sql queries in MS SQL and they ran quickly, so it was for sure a connection issue between our server and PowerBI Desktop.

I tried making another minor change to the syntax, this time removing Distinct (making sure my row count did not change) and voila, the connection was reestablished. I went back in and added Distinct BACK in and it updated just fine, so it had nothing to do with the actual Distinct in my syntax. 

I belive somehow my connection between PowerBI and our database server broke and I needed to reestablish it. I have no idea why the connection broke, which is the next step in this mystery. 

One additional quesiton for the group. Is there another way to reestablish the connection between the SQL server and PowerBI outside of making changes to the actual query? I want to be sure I'm handling this using a best practice.

Thanks!

[Anonymous]

@heidibb What is your data source? How much data are you trying to import? Are there a lot of dates in your tables with wide date ranges?

[hugoberry]

You can also get a better understanding of what is happening in your data refresh by having a look at the trace files.