Forum Discussion

Noak's avatar
Noak
Helper IV
9 years ago
Solved

Data Refresh Error

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...
Need Help
  • GilbertQ's avatar
    GilbertQ
    9 years ago

    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?

GilbertQ's avatar
GilbertQ
Super User
9 years ago

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's avatar
Noak
Helper IV
9 years ago

Hi GilbertQAnonymous

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.

  • GilbertQ's avatar
    GilbertQ
    Super User
    9 years ago

    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's avatar
      Noak
      Helper IV
      9 years ago

      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's avatar
        GilbertQ
        Super User
        9 years ago

        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?

  • Baskar's avatar
    Baskar
    Resident Rockstar
    9 years ago

    No , It will get the entire record .

     

    This is not incremental refresh concept.

     

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