Help! Power BI not handling SQL query and Timing Out

[KWind_22]

Hi,

Super new here. My current position requires me to maintain some PBI dashboards up and running. Last week my problem started.

Dashboard was not getting updated automatically. Its crashing because of one SQL table not getting refreshed.

Tried fixing it in the power query edditor but it would always time out:

Message=Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ErrorCode=-2146232060 Number=-2 Class=11

I downloaded Microsoft SQL Server Mgmt studio, running the same query I get the needed update in less than 20 minutes.

Added a timeout value to the query of 30 minutes and now I dont even get an error message. Power BI just crash closes.

My query is the following:

select
--Select task fields

t.type as 'TaskType',

t.id as 'TaskID',

t.CreatedDate as 'Task CreateDate',

t.WhoId as 'Name',

t.status as 'Status',

t.ActivityDate as 'Due Date',

t.OwnerId as 'Assigned to',

--Select user fields

u.name as 'Task Owner',

--Select profile fields

p.name as 'Profile Name',

--Select role fields

r.name as 'Role Name'

from Task t

left join [dbo].[User] u on t.ownerid=u.id
left join Profile p on u.profileid=p.id
left join UserRole r on u.UserRoleId=r.id

where

t.RecordTypeId in ('0121Y0000016Z16QAE') and t.type in ('Sales Signal follow-up')

and u.profileid<>'00e1Y000001RbO2'

The Task table is huge (more than 11 milion lines) but I have filtered out just what I need (7k lines).

And again works flawlessly in Microsoft SQL server... but microsoft power bi ether gives me a time out error or none at all dropping the mike on me.

Any thoughts. Can power bi actually handle SQL querries properly?

[KWind_22 · Accepted solution]

Hi, Ill try that.

I did set it to 30 mins with no success (it crashes the pbi file).

Currently I am running the querry in Microsoft SQL Server Mgmt studio, exporting the querry as csv file and importing it to my data model.

Super counter productive... but its working for now.

Best,

Kurt

[KWind_22 · Accepted solution]

From what I have googled arround and read in the comunity, I guess its an issue on how power query deals with a sql query versus ms sql server manager. Power query seems to load the entire tables before making any filterings and appendings and as one table is 11 milion lines, it cannot handle that load in a short time and simply times out. 

[Anonymous]

Hi @KWind_22 ,

1. check the SQL Server timeout?
Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the se...

2. and set manually set timeout of the report to 120min prior to publishing to PBI service?

Solved: MS SQL server timeout - Microsoft Power BI Community

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[KWind_22 · Accepted solution]

Hi, Ill try that.

I did set it to 30 mins with no success (it crashes the pbi file).

Currently I am running the querry in Microsoft SQL Server Mgmt studio, exporting the querry as csv file and importing it to my data model.

Super counter productive... but its working for now.

Best,

Kurt