cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bapt69
Helper IV
Helper IV

Refresh failed only online

Hi everyone,

I got a problem with the scheduled refresh online.

Severals reports can't be refresh online. The other ones are ok.

No problem with desktop to refresh all of ones (use max 15Go/32Go memory).

 

But the message is clear :

"Memory allocation failure. Try simplifying the number of queries"

Which memory? As I said, when I refresh with desktop, it uses 15Go/32Go memory.

 

Thanks for all of you

1 ACCEPTED SOLUTION
zoloturu
Memorable Member
Memorable Member

@bapt69,

 

There is no fixed capacity per Pro user. That's why it is called SHARED. It depends on how much users are using Power BI Service with shared capacity. 

 

As per my feeling usually, it is around 3-5 GB.

 

  1. Remove not used columns in data load. It means if you want to load Clients table, then prepare view/table on database side with needed fields. (Or while connecting to DB write SQL script where choose required columns).
  2. Reduce the number of rows in a way I told step before (smaller period or any other filters, if possible).
  3. Sometimes better if you will do maximum SQL JOINes on database side then on Power BI side.

 

View solution in original post

14 REPLIES 14
zoloturu
Memorable Member
Memorable Member

Hi @bapt69,

 

As I understood you work on included shared capacity. If you have such issue with memory you have only three options:

  • Decrease datasets inside your PBIX file and republish it to cloud (rewrite your SQL/MDX and M queries to select only required information in an optimal way)
  • To buy Power BI Embedded capacity (go to the Azure portal and get it there, details see here PBI Embedded pricing)
  • And more advanced capacity - Power BI Premium (you can buy it on the Office 365 portal, details - PBI Premium pricing)

We have PBI Pro pack (not premium) and 10Go.

I see it seems we use only 825Mo. So, no dataset exceeds 1Go, right?

 

2018-04-17_16h13_06.png

 

Where is the "memory pb"? I don't understand

zoloturu
Memorable Member
Memorable Member

@bapt69,

 

Power BI Desktop mainly consists of two applications:

  • Power BI Desktop (UI and back-end)
  • MS SQL Server Analysis Services

 

Main calculations are done by the second one.

You can check memory RAM using Windows task manager and Resource Monitor by tracking msmdsrv.exe:

msmdsrv.exemsmdsrv.exe

 

 

 

 

 

 

 

msmdsrv 2msmdsrv 2

If PBIX file size is 300MB. The same size it will have in Power BI Service (what we can see in your screenshot). But it is just compressed size. When you do refresh the report then it can take up to 20GB for instance. In that case, it is using RAM. When you have just Power BI Free/Pro without Embedded/Premium capacity then you use capacity shared with all other users.

Ok I tracked msmdsrv (max while refreshing = 11Go)

2018-04-20_14h43_37.png

 

and my memory (max while refreshing = 17,6Go)

2018-04-20_14h43_23.png

 

Here is the problem?

I don't really understand which memory is used when a scheduled refresh is working...

What can I track?

When a scheduled refresh is on, I don't see anything increasing. The memory RAM is still 4Go and no msmdsrv active...

zoloturu
Memorable Member
Memorable Member

So @bapt69,

 

The issue here that your report is using11 GB while refreshing. That means you need to buy additional memory (capacity, as I mentioned earlier) and your report will not refresh without it because of a huge amount of data. 

 

If you not willing to do so, you need to optimize your Power BI report data model. Can you share a screenshot of it?

What is my authorised memory capacity? (PowerBI Pro pack) I would know how many data I have to "suppress".

 

A screenshot of one data model :

2018-04-20_15h31_32.png

 

 

 

zoloturu
Memorable Member
Memorable Member

@bapt69,

 

There is no fixed capacity per Pro user. That's why it is called SHARED. It depends on how much users are using Power BI Service with shared capacity. 

 

As per my feeling usually, it is around 3-5 GB.

 

  1. Remove not used columns in data load. It means if you want to load Clients table, then prepare view/table on database side with needed fields. (Or while connecting to DB write SQL script where choose required columns).
  2. Reduce the number of rows in a way I told step before (smaller period or any other filters, if possible).
  3. Sometimes better if you will do maximum SQL JOINes on database side then on Power BI side.

 

View solution in original post

Ok remove columns sounds good.

I got many many columns useless. If it's an issue, I'll do it.

I let you know.

 

Thank you

Ok I remove severals columns and it's working!

 

Thank you,

zoloturu
Memorable Member
Memorable Member

@bapt69,

 

Nice to hear.

v-shex-msft
Community Support
Community Support

Hi @bapt69,

 

Can you please provide more detail contents about your scenario to help us clarify your issue? (please include your data source credentials.)

13.PNG

 

Troubleshooting the On-premises data gateway

Spoiler

Tools for troubleshooting

Collecting logs from the gateway configurator

There are several logs you can collect for the gateway, and you should always start with the logs. The simplest way to collect logs after installing the gateway is through the user interface. In the On-premises data gateway user interface, select Diagnostics and then select the Export logs link near the bottom of the page, as shown in the following image.

On-prem-data-gateway-UI-logs

Installer logs

%localappdata%\Temp\On-premises_data_gateway_*.log

Configuration logs

%localappdata%\Microsoft\On-premises Data Gateway\GatewayConfigurator*.log

On-premises data gateway service logs

C:\Users\PBIEgwService\AppData\Local\Microsoft\On-premises Data Gateway\Gateway*.log

Event Logs

The On-premises data gateway service event logs are present under Application and Services Logs.

On-prem-data-gateway-event-logs

Fiddler Trace

Fiddler is a free tool from Telerik that monitors HTTP traffic. You can see the back and forth with the Power BI service from the client machine. This may show errors and other related information.

Refresh History

When using the gateway for scheduled refresh, Refresh History can help you see what errors have occurred, as well as provide useful data if you should need to create a support request. You can view both scheduled, as well as on demand, refreshes. Here is how you can get to the Refresh History.

  1. In the Power BI navigation pane, in Datasets, select a dataset > Open Menu > Schedule Refresh.

  2. In Settings for... > Schedule Refresh, select Refresh History.

For additional information about troubleshooting refresh scenarios, take a look at the Troubleshooting Refresh Scenarios article.

 

BTW, is there any custom functions or query parameters in your query?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

I got this message :

2018-04-16_08h02_12.png

 

Connected with gateway (not personal mode) and no pb with id informations. The datas come from Dynamics NAV. And I got no pb when I refresh the report with PBI Desktop! (use 15Go/32Go of memory system)

As I said, I got no pb with the others reports (same gateway, same db...).

 

I don't really understand what you need to know.

 

HI @bapt69,

 

I think your issue may related to refresh data limit, please cut down your data amount and try again.

 

For detail information, please refer to following link:

Troubleshooting Uncompressed data limits for refresh

Uncompressed data limits for refresh

The maximum size for datasets imported into the Power BI service is 1 GB. These datasets are heavily compressed to ensure high performance. In addition, in shared capacity, the service places a limit on the amount of uncompressed data that is processed during refresh to 10 GB. This limit accounts for the compression, and therefore is much higher than 1 GB. Datasets in Power BI Premium are not subject to this limit. If refresh in the Power BI service fails for this reason, please reduce the amount of data being imported to Power BI and try again.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors