Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
roelf
Helper II
Helper II

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
Anonymous
Not applicable

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)

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?

Anonymous
Not applicable

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors