cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
meiyl Frequent Visitor
Frequent Visitor

Date Refresh Timeout issue

Dataset exceeds the time out in either Refresh Schedule or Refresh Now(By the way the shedule history marked it as completed after another refresh start). Any suggestions for the timeout issues?

Use PowerBI gateway-Personal

data source: Microsoft SQL Server (SqlClient)

Authentication method: Windows

Capture.PNG

 

8 REPLIES 8
ianbarker Regular Visitor
Regular Visitor

Re: Date Refresh Timeout issue

Do you get any error messages?

meiyl Frequent Visitor
Frequent Visitor

Re: Date Refresh Timeout issue

Yeah. The error messages as follows. Do I need change to enterprise gateway?

 

The refresh operation failed because it took more than 120 minutes to complete. Consider reducing the size of your dataset or breaking it up into smaller datasets.

Cluster URI:DF-MSIT-SCUS-redirect.analysis.windows.net
Activity ID:7678fe84-3b02-49c3-9ff1-67963b1e6359
Request ID:8e3975fc-f3f9-a4ee-1804-19ca5449ca84
Time:2016-07-15 04:47:30

 

ianbarker Regular Visitor
Regular Visitor

Re: Date Refresh Timeout issue

This sounds more like an issue with your dataset than the gateway.

 

Where is the gateway software installed? Are both the SQL database and personal gateway on your own machine?

If so, then Personal Gateway should be fine.

If you have a SQL server and multiple people are viewing the reports in Power BI, then Enterprise is more suited.

 

How many tables are in your data set?

How many rows are in your largest table?

How many columns are in your largest table?

 

It might be that you need to do some optimisation first, either by entering a SQL query to only bring in required data, or to create an optimsed data warehouse. 

I've worked with quite large databases with a number of years of data that take maybe 40 minutes to refresh, so 120 minutes I think is unusual.

 

Regards,

Ian

meiyl Frequent Visitor
Frequent Visitor

Re: Date Refresh Timeout issue

The SQL Server is not in my machine but in the redmond machine and of course multiple people are viewing the reports in Power BI.

So I need use Enterprise right?  How to use Enterprise to connect?

We have 50 tables. The largest have  6563357 rows and 30 columns.

We do some filter in Advance Editor, is it will impact the speed?

ianbarker Regular Visitor
Regular Visitor

Re: Date Refresh Timeout issue

You probably are better using the Enterprise Gateway but I think this won't help with your performance issue.

 

It might be worth creating a view in SQL and then just connecting to that view rather than using the advanced editor.

 

Also, another consideration is if you have done additional modeling using the Query Editor. If you have added additional steps here, this could be cuasing performance issues.

meiyl Frequent Visitor
Frequent Visitor

Re: Date Refresh Timeout issue

Yeah. We want replace a lot of colunm name to another name and filter some colunm in PowerBI, we don't want it change in the SQL Sever (because some other powerbi use the same data), other than write it in advanced editor, any other ways to do this?

 

ianbarker Regular Visitor
Regular Visitor

Re: Date Refresh Timeout issue

My advice would be to not do anything in Power Query/Query Editor other than selecting your data source.

Do all you modelling using DAX.

I've seen this issue multiple times. As soon as you add steps to Query Editor, the performance just tanks.

DAX is the way to go.

gooranga1 Senior Member
Senior Member

Re: Date Refresh Timeout issue

Hi is this still an issue?

 

We are having real problems with data refreshes failing on certain pbix files that are generally more complex in that they use the query editor to stick disparate data sources together (SQL and SSAS) using merge.

 

Worringly these do take the longest to refresh when it does refresh and when it fails we get messages like this below.

 

[DM.Pipeline.Common] Non-gateway exception encountered in activity scope: System.ServiceModel.CommunicationException: The socket connection was aborted. This could be caused by an error processing your message or a receive timeout being exceeded by the remote host, or an underlying network resource issue. Local socket timeout was '00:01:00'.

 

It is a very handy tool to merge data but it looks like it comes at a pretty high cost.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 63 members 985 guests
Please welcome our newest community members: