Data Refresh Error

[Noak]

Hi ,

My organization is working with SQL Server pbi connection.

It was a rough journey to integrate SQL Server with pbi , last week we succeed to do it and then got this error while refreshing data:

 please help.

[v-shex-msft]

Hi  @Noak,

Can you share some detail information to help us clearly know your issue?

For example:

1. Database type(SQL Server, SSAS, or other type of sql?)

2. Connect mode(Import, directQuery, liveconnection?)

3. Refresh type(manually or scheduled refresh?)

In addition, can you share some screenshots of your dataset? It will be help to know the connection state of gateway.(you can find it at Settings -> Dataset)

Regards,

Xiaoxin Sheng

[Noak]

Hi @v-shex-msft,

---

1. Database type(SQL Server, SSAS, or other type of sql?) SQl Server

2. Connect mode(Import, directQuery, liveconnection?) Import

3. Refresh type(manually or scheduled refresh?)Manually & Schadualed

I removed the gateway an reinstall it , the data succeed to refresh 2/3 times and then again I'v got an error please via screen shots:

 

[GilbertQ]

Hi @Noak, it appears that you are getting timeouts, which would be consistent as to why it sometimes works and then other times it does not work.

You would need to ensure that connectivity is fast enough from the Power BI Service, to where you have installed the Data Gateway?

As well as how much data you are returning, and how efficient your SQL Query is to import the data. Those could all affect why it is timing out.

One way to test is when there are few people in the office, and see if you manually refresh if that is successful? If it is, then it can indicate that during the data there is not enough bandwidth to refresh?

[Noak]

Hi @GilbertQ@v-shex-msft

2 days ago the data succeed to refresh , yasterday same error again.

We are looking now at the SQL Code , I have a question:

when the data refresh is it refresh all the data from the start or the "delta" I mean adding new/modified records only.

waiting for your reply .

BR,

NOA.

[Baskar]

No , It will get the entire record .

This is not incremental refresh concept.

when ever the gateway runs it will load the entire data .

[GilbertQ]

Hi @Noak, when you refresh by design it currently will refresh all the data.

The other question then is with regards to your SQL Code.

If you have to take your SQL Code and run it on the actual SQL Server, how long does it take to complete?

That is the only outstanding thing I can think of where it is is potentially a very busy SQL Server system or the SQL Code is not as efficient as it can be. Which is causing the results to take a long time to be returned? Or the volume of data is quite large, which is causing the data to take longer than the timeout period to complete?

And finally see the image below where you can specify the command timeout (In Minutes). I would first run it on the SQL Server to get an expected time for the query to complete. And then adjust the command timeout as shown below to be longer than what it actually took.

[Noak]

Hi @GilbertQ,
thank you for your reply.

I changed the comant timeout to 20 minutes and the refreshing is working until it gets to 900,000 rows. At that point it stops. and doesn't send any error - just stops (like it finished but it didn't there ar more rcords to update).

I know the pbi can refresh up to 100 million records.
I asked our SQL engineer to look at the code, what do you think is the reason for this simultaneous stopping?

BR,

Noa.

[GilbertQ · Accepted solution]

Hi @Noak, if it is always getting to the same amount of rows then it could possibly be a timeout on the SQL Server side? Or somewhere inbetween.

I am sure that the SQL Engineer will be able to check the code and possibly add indexes to make the query return faster?

[Noak]

Hi @GilbertQ ,
we tried to manually refresh during the weeknd (0 people in the office) still got the same error.
.