cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
roelf Regular Visitor
Regular Visitor

Dataset Refresh Timeout with SQL server

Hi there

 

Wondering if someone can help me:

 

We are having an issue with refreshing our dataset that sources data from an SQL server.

Looking at our graphs on Azure we can see that as soon as we refresh the dataset on PBI, our memory consuption goes up, remains constant for a while and then starts to spike until it eventually timesout:  https://ibb.co/1J9XBp3

 

If I refresh the data locally on PBI desktop and publish (overwriting the dataset), then it is successful. The problem seems to come in with refreshing on the PBI site.

 

I have a theory that there might be a problem with some of our data (and the query not being able to correctly cast the erroneous data). Would this cause PBI memory to spike, as it tries to run the query?

 

This is a part of our query, where I think the problem may lie: 

let
    Source = Sql.Database("name.database.windows.net", 
[Query="select * from source#(lf)UNION ALL#(lf)
select * from source#(lf)UNION ALL#(lf)
select * from source#(lf)UNION ALL#(lf)
select * from source#(lf)UNION ALL#(lf)
select * from source#(lf)UNION ALL#(lf)
select * from source#(lf)UNION ALL#(lf)
select * from source#(lf)UNION ALL#(lf)
select * from source#(lf)UNION ALL#(lf)
select * from source#(lf)UNION ALL#(lf)
select * from source#(lf)UNION ALL#(lf)
select * from source#(lf)UNION ALL#(lf)
select * from source#(lf)UNION ALL#(lf)
select * from source#(lf)UNION ALL#(lf)
select * from source#(lf)UNION ALL#(lf)
select * from source#(lf)UNION ALL#(lf)
select * from source", 
CreateNavigationProperties=false]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"fruitsize_avg"}),
    #"Filtered Rows2" = Table.SelectRows(#"Removed Columns", each ([contextref] <> "hs_name")),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows2", {"rid"}),
    #"Parsed JSON" = Table.TransformColumns(#"Removed Duplicates",{{"totals", Json.Document}}),

    #"Added item Index" =
        Table.TransformColumns(
            #"Parsed JSON",
            {{"totals",
              each List.Transform(
                        List.Zip({_,
                                    List.Transform(
                                        {1..List.Count(_)},
                                        each [Index = _])}),
                        each _{0}&_{1}),
              type list}}),
    #"Expanded totals" = Table.ExpandListColumn(#"Added item Index", "totals"),
    #"Expanded totals1" = Table.ExpandRecordColumn(#"Expanded totals", "totals", {"total", "samples", "value", "Index"}, {"total", "samples", "value", "Index"})
in
    #"Expanded totals1"

 

Alternatively, are there other possible reasons for why the memory consumption would spike during a dataset refresh - causing it to fail/timeout?

 

Thank you

 

Kind regards

3 REPLIES 3
Ross73312 Super Contributor
Super Contributor

Re: Dataset Refresh Timeout with SQL server

Do a comparison of resources between the Server and your local desktop.  Do you have more resources on your local desktop and thus you get a better outcome?

 

Have you tried increasing the timeout amount using:

CommandTimeout=#duration(0, 0, 30, 0)

(my example is a 30 minute timeout)


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Highlighted
roelf Regular Visitor
Regular Visitor

Re: Dataset Refresh Timeout with SQL server

Hi there

 

I think the problem lies elsewhere though, because in the week of the 18th to 22nd our dataset was refreshing on schedule, within minutes. And while our dataset does increase on a daily basis, there wasn't a rapid increase of data overnight to have led to the dataset timing out after an hour of trying to refresh.

 

What we've also experienced now, is that trying to refresh the dataset locally on the PBI app and publishing, is also causing a time out after +- an hour of publishing (with the error message "You do not have access to publish to this workspace" - which I do). Looking at our report though, it was in fact published/refreshed, but the error message appears to be due to a timeout on the app.

 

As for server resources, we have upped our resources to a higher tier, but it yields the same result: when the dataset is refreshing, our memory usage (according to the graphs) will go up to max, remain constant for a while and then suddenly drop and start to spike rapidly until timing out after an hour or so. I believe it is with this spiking in memory usage that leads to the refresh failing.

 

I guess my question remains: are there common causes for a dataset, that was refreshing normally at a time, to suddenly time out when trying to refresh?

Ross73312 Super Contributor
Super Contributor

Re: Dataset Refresh Timeout with SQL server

Short answer to your question is no.

 

Why something timesout will entirely depend on what has changed in your dataset and what import logic complexity you are using.  I mention resources as it might show you a point of difference and give you an idea if your server simply reached a limit before your desktop did.

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

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: 331 members 3,200 guests
Please welcome our newest community members: