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

Handling large data/timeouts in power bi (blob storage)

I got around 260 MB of data stored in blob storage which takes around 4 hours to refresh in power bi desktop

When I publish this onto the service and set the scheduled refresh, the refresh doesn't even happen due to hitting the timeout of 120 min.

 

I would like to know any reasons behind this massive refresh time and probably be advised of some efficient ways to handle this.

 

Do reasons lie on the data source side (heard blob storage is slow in terms of refresh) or my querying side (got couple of slicers, filters and few calculations)? 

 

And what would be some efficient ways to address this apart from increasing the timeout limit of power bi?

 

I tried this: Making the table narrower (but didn't help too much).

 

I was going through "incremental refresh" but realized that it is only for premium users (Mine's pro).

So, is there a way that I could split the dataset into smaller sets and only set the refresh for the last 10 days (and leave the historical data as is)?

 

 

 

8 REPLIES 8
Community Support Team
Community Support Team

Re: Handling large data/timeouts in power bi (blob storage)

Hi @MissMintox,

 

Could you refresh your dataset successfully with clicking Refresh Now?

 

For better performance, you could have a good look at this blog.

 

In addition, you could have a reference of this two similar threads below. 

 

https://community.powerbi.com/t5/Service/Refresh-Failing-Timeout/td-p/331492

https://community.powerbi.com/t5/Service/Power-Bi-Service-Refresh-timeout-expiration-issue/td-p/1648... 


So, is there a way that I could split the dataset into smaller sets and only set the refresh for the last 10 days (and leave the historical data as is)?


For your refresh requirement, it seems that there is no option for us to set the refresh for the last 10 days and leave the historical data.

 

From my understand, you could show the data of last 10 days when you create your report then your could set the schedule refresh.

 

You could submit your idea in Power BI ideas Forum.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MissMintox Regular Visitor
Regular Visitor

Re: Handling large data/timeouts in power bi (blob storage)

Hi @v-piga-msft

 

Thanks for the response.

Yes I could refresh the dataset on Power bi desktop which goes for around 4.5 hours (this has increased since 2 days with the dataset size). Obviously this cannot be done on Power bi service due to 120 min timeout.

 

I did already go through that blog and realized that some of them cant be applied in my case.

 

I just went through those two threads. One of the thread's accepted answer was pointing to the data gateway which I haven't got one. Will setting data gateway helps refresh faster (reducing 4.5 hour to less than 2 hours)?

 

I think refreshing only the last 10 (any number) days data and retaining the historical data seems similar to the incremental refresh? doesn't it?

 

I tried enabling the incremental refresh under power bi settings and then looked under "manage parameters" in the query editor but was unable to find the RangeStart and RangeEnd default parameters. So I manually created those parameters with the same name and then filtered my date/time column in the query editor which again takes 4+ hours. Is this normal?

 

I'm basically trying to follow this: https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh

Community Support Team
Community Support Team

Re: Handling large data/timeouts in power bi (blob storage)

Hi  @MissMintox,

 

Based on your description, I understand that you have a Premium license, if you have, you could configure the incremental refresh for your requirement.

 

In addition, what is your data source?

 

Normally, we need to configure on-premise gateway in Power BI Service and add data source when the data source is on-premise. If your data source is online, you don't need to configure the Data Gateway.

 

If you need to configure the Data gateway, you could follow this document

 

For reference, you could have a look at this article about Data refersh in Power BI Service.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MissMintox Regular Visitor
Regular Visitor

Re: Handling large data/timeouts in power bi (blob storage)

Hi @v-piga-msft

 

My data source is Blob storage. So guess I don't need a data gateway?

 

I don't think I have premium license as it shows 10GB storage at the top right hand corner on my Power bi Service. Does this mean that I wont be able to set the incremental refresh?

 

But I gave a try as below:

 

As mentioned in my previous response, I manually created RangeStart and RangeEnd parameters in the manage parameters as below (as there were no default ones), but not sure about the "Current value" field (See below)? So I left it blank and sorted the date/time column according to these parameters 

 

parameters.PNG

 

 

And the next step, while enabling the incremental refresh, the button has been greyed out as below:

 

incremental refresh.PNG

 

 

Not sure how to proceed. 

 

 

 

 

 

 

Community Support Team
Community Support Team

Re: Handling large data/timeouts in power bi (blob storage)

Hi @MissMintox,

 

It seems that your data source is Azure Blob Storage which is online source, so I think you don't need to configure the on-premises gateway.

 

In addition, the incremental refresh you mentioned is supported for Premium license currently as I know. If you don't have Premium license, you could not have that feature.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MissMintox Regular Visitor
Regular Visitor

Re: Handling large data/timeouts in power bi (blob storage)

Hi @v-piga-msft

 

Thanks for the clarification. 

So what would be an efficient way to handle this as it is really frustrating that I have to manually refresh the dashboard every morning and then publish it after 5 hours being waited.

 

How can I practially split the dataset into smaller datasets as suggested below? However I doubt that, 270 MB of data is not too much and theres's no point in splitting it up.

 

Refresh timeout.PNG

 

 

 

Community Support Team
Community Support Team

Re: Handling large data/timeouts in power bi (blob storage)

Hi @MissMintox,

 

You could use parameter feature when you get data so that your data source will be filtered by the parameter.

 

In addition, you could have a view of Dashboard performance inspector which could help you see alerts that identify the potential issue and suggest a fix.

 

Best Regargds,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
MissMintox Regular Visitor
Regular Visitor

Re: Handling large data/timeouts in power bi (blob storage)

Hi @v-piga-msft

 

But using Parameters and filtering the data would affect the dashboard (As I also want the historic data to be visible in the dashboard)

 

Just looked at the Performance inspector and it looks like below.

I got all the visuals in a single report and pinned that report to the dashboard. I usually do this and it didn't affect any of my previous dashboards. And I believe this would not be the thing affecting the refresh time heavily.

 

performance inspector.PNG

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 65 members 1,221 guests
Please welcome our newest community members: