Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Error Message - Server Closed Connection Unexpectedly

Hello PBI Community   Just thi smonth I started getting the error message below. Is there a setting in Power BI  that times out if the query is running too long? If so, where can I find it?    ...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Anonymous ,

     

    For existing query, please try to modify it in the Power Query Editor's Advanced Editor.

     

    let
Source = AmazonRedshift.Database("my_server", "my_db", [BatchSize = 1000]),
....

     

    Best Regards,
    Winniz

v-kkf-msft's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi Anonymous ,

 

Please try the following methods to see if they solve the issue.

 

1. Please check if the issue is in the communication between client and server. Please review the following blog to troubleshoot this issue.

Connecting from Outside of Amazon EC2—Firewall Timeout Issue

2. In the advanced options of the Amazon Redshift connector, change the Batch Size to be 1000, so that it can send the data in batches which should make it run faster and not wait for the entire dataset.

 

 

Best Regards,
Winniz


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello v-kkf-msft

     

    Per your suggestion above, can I change the batch size to 1000 for a .pbix, I have already developed for my end users?

     

    Screen Shot 1 & Screen Shot 2

    Note: I'm currently using an ODBC connector for this existing .pbix

    Screen Shot 3 & Screen Shot 4

    Re: Changing the Batch size to 1000, I found Batch Size in Advance Options, but when I make the change to the size from 100 to 1000 my .pbix is prompting me to select my Sales and Revenue Table again from Redshift.

     The batch size may affect the data for these tables when I pull the data in again, but I need the batch size to affect the existing .pbix I have already built. I’m currently getting the error message per my current refresh attempts.

     

    Is there a way for me to change the batch size for my existing .pbix?

     

    Regards,

     

     

     

    Screen Shot 1

     

    Screen Shot 2

     

    Screen Shot 3

    Screen Shot 4

     

    • v-kkf-msft's avatar
      v-kkf-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi Anonymous ,

       

      For existing query, please try to modify it in the Power Query Editor's Advanced Editor.

       

      let
Source = AmazonRedshift.Database("my_server", "my_db", [BatchSize = 1000]),
....

       

      Best Regards,
      Winniz

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Winniz

        Going to give your suggestion a try.

         

        Much appreciated

        Thank you!