Forum Discussion

piyushj's avatar
piyushj
Frequent Visitor
9 years ago

Power Bi Service Refresh - timeout expiration issue

Hi PBI Team,

 

We are facing an issue in power bi service as well as in desktop app. We have huge amount of data available in tables (>10GB). We are importing data using SQL Server database. Previously, we have only 50k records but now it has more than 500millions rows. Now when we are trying to refresh the data, we are getting below error -

Power Bi Service (Screenshot Attached)

Power Bi Desktop Issue -

Error Description -

Microsoft SQL: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This failure occurred while attempting to connect to the routing destination. The duration spent while attempting to connect to the original server was - [Pre-Login] initialization=217; handshake=664; [Login] initialization=0; authentication=0; [Post-Login] complete=217;

 

 

Appreciate your quick help on this.

 

 

 

 

 

Need Help
Show and Tell

25 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi piyushj,

    Firstly, based on this blog, in Power BI Service, refreshing the dataset will give an error message letting you know your dataset is too large, and it will fail to refresh until you filter the data model back down under 1 GB.

    Secondly, optimize your data model following the guide in this blog in Power BI Desktop, the blog also applies to Power BI Desktop though it is about PowerPivot.

    And specify appropriate value in “Command timeout in minutes (optional)" following steps below, then check if you can  refresh successfully in Power BI Desktop.

    1. Click on the arrow for "Edit Queries" in your current Power BI Desktop file, select "Data Source Settings” in the dropdown.
    2. Click on "Change Source..." in the Data source settings pop-up window.
    3. Click on Advanced Options in the pop-up window, enter 60 minutes in the "Command timeout in minutes (optional)" textbox, then click OK.

    Thanks,
    Lydia Zhang

    • piyushj's avatar
      piyushj
      Frequent Visitor
      9 years ago

      Hi Anonymous

       

      I'm using power bi pro trial version. I have huge amount of data more than 2 million rows. I have also used direct query option but still direct query is not enough for us. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi piyushj,

        The 1GB limitation of data model during refresh in Power BI Service also applies to users who have pro license, this limitation is different from the 10GB data capacity limit for pro users. The 1GB limitation is for the data model within each PBIX file you upload to Power BI Service.

        You can rename your current PBIX file to .zip and check the size of the data model within it. For more details, please review this blog. If the data model size is too large to refresh in Power BI Service, please break up your dataset into smaller datasets and refresh them in Power BI Service.

        Besides, have you specified value in "Command timeout in minutes" in Power BI Desktop?

        Thanks,
        Lydia Zhang

  • michaelmof's avatar
    michaelmof
    Regular Visitor
    8 years ago

    We have a PowerBI Premium account but are getting consitent timeout after 120 minutes when using Excel as a datasource. The Excel is hosted in a SharePoint library. I do not see anywhere to put the advnaced timeout setting for this datasource.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     

    I am getting the same error's, no idea why this is happening.

     

    I only have around 4million rows too...

     

    Is it an internet connection issue or a microsoft issue

     

    • GilbertQ's avatar
      GilbertQ
      Super User
      6 years ago

      Hi there


      The error is from your SQL Server database and it appears that it took too long for the query to return any rows.

       

      I would suggest working with your DBA to fine tune the query