Online refresh is failing :The received uncompressed data on the gateway client has exceeded limit

[tejald]

Need Help: Manage storage is showing dataset size is 44 MB and over all size is 170 MB. Dataset has ~5M records. Desktop version refreshes in 5-7 mins but online refresh fails with error "The received uncompressed data on the gateway client has exceeded limit" 

[MuraliOSShankar]

We are facing similar issue.  The In-Memory refresh fails after 2 hours.  Size of uncompressed data that we are trying to refresh is 93 GB.

[ascendbi]

Hi Everyone, 

For those following this thread, I received a call from Microsoft Support today indicating that there is a Bug in the change made that implimented the limit of 10 GB of Uncompressesd data refreshed via the Data Gateway. The ticket is still open because I was pushing back on the fact that they imposed a limit but have provided no method to measure or manage the limit beyond the phrase "reduce the data size" 

The message is that "... RCA revealed a bug. A corrective action is in process and will be completed at the end of  August 2017 on 31."

It is unclear as to what the bug is or whether it is directly affecting all or some of my data models. It was also unclear as to whether th 10 GB limit was being rolled back or how it is measured is being corrected. 

Anyways, it more than I knew yesterday, so I thought I would share. 

[Anonymous]

Thanks @ascendbi

It is good to know that there is something going on. We hope to get more info on this in any channel. I agree that it is not fair to introduce a limitation without any capability to measure where people are compared to the limit. Not to mention the need to announce such limits in advance, and not make everyone discover them the hard and painful way: their reports are broken.

I opened an idea for having a memory consumption indicator, or something like that.

Dear all: feel free to vote for this idea if you agree that it would be useful to have this feature.

[ascendbi]

Just adding to the thread the workaround we now have working...

We are now refreshing and publishing the same data modelS via Power Update. (http://poweronbi.com/power-update-features/) This software, on a schedule, opens PBIDesktop, refrshes the data and publishes it with Replace to the wworkspace. The software runs on a Windows 2008 R2 VM with 48 GB RAM that is also running the SQL Server DB that the model sources some of its data from.   

Note Power Update gave timeout errors when the refresh took over 90 minutes, but works well if the refresh is under an hour. We have a suppoert ticket open with them to resolve the timeout as it occurs when the data model approachs the 1 GB Model Size limit accepted by power BI.  

Note also that since they pulled Active Directory from the new Data Gateway - Personal (the old one will stop working July 31, 2017), we are refreshing models with AD connection with Power Update as well.

[ascendbi]

Hi Everyone, 

We received a response from Microsoft and they are now declaring that our issue is not a bug as first suggested, but the new limitations of refreshing data via Gateway with the Power BI Service. 

I agree with @Anonymous - this is a bait and switch. Adding an additional limitation to the 1 GB .pbix a full year after expanding it from 250 MB leaves a very bad taste in our mouth. We are now left with the choice of 1) eliminating rows 2) figuring out how to reduce data size with methods proposed by @iJuancho or 3) fork over $60,000 and move to premium (not knowing what limitations with slowly creep into that approach over time) 

The most likely option is #2 but this is a grind and we don't even know how to measure how close to the limit we are. 

Sigh. 

Microsoft Response: 

After extensive investigation come to a conclusion, the challenge you are experiencing is a direct result of the new changes applied since June 1er 2017, now the gateway only allowed 10 gigabit of flow through, as solution you will have to modify your query to reduce the data,  and this experience is a design limitation, this is the :

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.

and this leads us to believe that the problem no longer exists or you have other more pressing business priorities. If I do not hear to the contrary, I will close this incident tomorrow at close of business on 07/06/2017.

Please e-mail me at your earliest convenience in case you have contrary elements and want to continue with the troubleshooting.

[Anonymous]

@ascendbi

Regarding option 2) reducing data size being imported:

I opened an idea for having a memory consumption indicator, or something like that. Exactly for this very purpose. Because it is very difficult and cumbersome to figure out your actual data size, and having the only feedback is the refresh via the Power BI Service is... let's say sub-optimal, and not the most efficient way of figuring out whether you are still above the limit or you managed to slide under the door.

Dear all: feel free to vote for this idea if you agree that it would be useful to have this feature.

[Anonymous]

Thanks @ascendbi for the update on your situation. It is good to see the clear picture now.

So it is official now. I wonder if this new limitation will show up in any documentation sometime soon. Not to mention to let the community know in advance, and not via error messages breaking their solutions which used to work for a year...

[ascendbi]

Hi Everyone, 

I receive the following notice from Microsoft Support and Engineering yesterday. They have confimred that this is a bug. No ETA other that "soon". 

Date: Tue, 20 Jun 2017 18:28:39 (UTC -04:00)

Hello David,

After extensive research we found out that this is a bug. I have notified the product group and I have created bug tracking number for them. They are working on this issue and this issue should be resolved soon. I will get back to you once I receive an update from my product group.

Regards,

Engineer - Microsoft Business Intelligence

[tejald]

Thanks for providing update to us.

Please update on this thread once you receive further communication on this bug. 

[iJuancho]

I have the same problem. In another topic with the same problem, the answer of moderator was "This is due to the cutoff of data volume/size <= 10GB as enforced by DS"

http://community.powerbi.com/t5/Issues/Knowing-the-uncompressed-dataset-size-on-the-gateway/idc-p/200271#M9657

[Anonymous]

I also ran into the "The received uncompressed data on the gateway client has exceeded limit" error with some 325 MB dataset (import from text files).

Something tells me that they re-allocated existing resources to the premium tenants, and introduced some not-disclosed limits on the non-premium tenants in order to make the premium work. If so, then this is quite a dirty way of managing limited resources... And unacceptable if you ask me.

[v-shex-msft]

Hi @tejald,

There are other users report this issue to power bi team with CRI 39670466, I will update here once it has any modifies.

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @tejald,

Basd on you description, I think you use the directquery mode to connect your datasource, right?

If this is a case, I'd like to suggest you take a look at below article first, current direct query has limitation about 1m records:

Use DirectQuery in Power BI Desktop

Power BI creates queries that are as efficient as possible. Under certain situations however, the generated query may not be efficient enough to avoid refresh that would fail. One example of this situation is when a generated query would retrieve an excessively large number of rows (more than 1 million) from the back-end data source, in which case the following error occurs:

The resultset of a query to external data source has exceeded
the maximum allowed size of '1000000' rows.

This situation can occur with a simple chart that includes a very high cardinality column, with the aggregation option set to Don’t Summarize. The visual needs to only have columns with a cardinality below 1 million, or must have appropriate filters applied.

Regards,

Xiaoxin Sheng

[tejald]

Thanks Xiaoxin for your response. I am using import mode. Does same restriction apply to import mode as well? I can load up to ~ 3+Millions but failing after ~4 Millions rows. Data is coming from 4 different views(second one has 500k records, 3rd and and 4th views having unique records used for data modeling) with maximum of 20 columns in total. I am using filters for charts but if none is selected, it can load entire data specially table having details. Please advice.

[ascendbi]

Hi @tejald and @v-shex-msft

We are also using Import Mode and now have 2 data models that are suddenly failing with the same error. We connect to SQL and Folder (.csv) files. 

One is 76 MB (45M Rows, 10 columns, Single Table) on the Group Storage (Gear > Manage Group Storage) after publishing and the other is 458 MB (Very complex, 1000+ measures, 12M row Fact Table, Many lookup tables, bi-directional cross filtering etc.). 

We have a ticket open with Microsoft (Via Office 365 Support Incident) but I am not sure why I bother. I am tired of training them on the product they are supporting. Waiting to get escalated i guess. 

Observations

We started having "Out of Memory" errors about 4 weeks ago for the large data model mentioned above. We opened a ticket and MS finally responded that they made some changes to the resources in the North Central US Data Center and the next day the errors went away. 

Then on June 7, 2017 the Uncompressed errors appeared and no Scheduled refresh has been OK since for either Dataset. What is strange is that there was  actually NO NEW data being made available for the smaller data model. It just suddenly became too large... This leads me to beleive that Microsoft Made some change likely due to / related to the Premium Product. I say this because the the wording on this page: Troubleshooting refresh scenarios 

• 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. 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.

We have a workaround: Refresh and republish the .pbix in Power BI Desktop and the data will "refresh"  even though the immediate refresh attemmpt back via the Data Gateway reports as another failure. 

Refresh history for large 450 MB Dataset

ScheduledOneDrive

Details Type Start End Status Message

Show	On demand	6/9/2017, 8:10:58 AM	6/9/2017, 9:00:30 AM	Failed	The received uncompressed data on the gateway client has exceeded limit.
Show	On demand	6/9/2017, 6:34:03 AM	6/9/2017, 7:25:01 AM	Failed	The received uncompressed data on the gateway client has exceeded limit.
Show	Scheduled	6/9/2017, 3:30:10 AM	6/9/2017, 5:12:10 AM	Failed	The received uncompressed data on the gateway client has exceeded limit.
Show	On demand	6/8/2017, 9:49:25 AM	6/8/2017, 10:42:41 AM	Failed	The received uncompressed data on the gateway client has exceeded limit.
Show	Scheduled	6/8/2017, 3:31:29 AM	6/8/2017, 5:17:17 AM	Failed	The received uncompressed data on the gateway client has exceeded limit.
Show	On demand	6/7/2017, 3:04:13 PM	6/7/2017, 3:44:43 PM	Failed	The received uncompressed data on the gateway client has exceeded limit.
Show	On demand	6/7/2017, 2:11:54 PM	6/7/2017, 3:04:12 PM	Failed	The received uncompressed data on the gateway client has exceeded limit.
Show	On demand	6/7/2017, 7:05:17 AM	6/7/2017, 7:55:56 AM	Failed	The received uncompressed data on the gateway client has exceeded limit.
Show	Scheduled	6/7/2017, 3:31:20 AM	6/7/2017, 5:42:40 AM	Failed	The received uncompressed data on the gateway client has exceeded limit.
	Scheduled	6/6/2017, 3:31:24 AM	6/6/2017, 5:16:02 AM	Completed
	Scheduled	6/5/2017, 3:31:15 AM	6/5/2017, 5:14:06 AM	Completed
	Scheduled	6/4/2017, 3:31:12 AM	6/4/2017, 5:09:03 AM	Completed
	Scheduled	6/3/2017, 3:31:10 AM	6/3/2017, 5:15:39 AM	Completed
	Scheduled	6/2/2017, 3:31:35 AM	6/2/2017, 5:03:35 AM	Completed
	Scheduled	6/1/2017, 3:31:19 AM	6/1/2017, 6:55:43 AM	Completed
	Scheduled	5/31/2017, 3:31:20 AM	5/31/2017, 5:07:06 AM	Completed
	Scheduled	5/30/2017, 3:31:04 AM	5/30/2017, 4:59:47 AM	Completed
	Scheduled	5/29/2017, 3:31:07 AM	5/29/2017, 4:59:02 AM	Completed
	Scheduled	5/28/2017, 3:31:19 AM	5/28/2017, 5:02:21 AM	Completed
	Scheduled	5/27/2017, 3:31:06 AM	5/27/2017, 6:08:17 AM	Completed

Refresh history for smaller Dataset

ScheduledOneDrive

Details Type Start End Status Message

Show	Scheduled	6/9/2017, 9:30:13 AM	6/9/2017, 10:11:01 AM	Failed	The received uncompressed data on the gateway client has exceeded limit.
Show	Scheduled	6/8/2017, 9:31:27 AM	6/8/2017, 10:27:08 AM	Failed	The received uncompressed data on the gateway client has exceeded limit.
Show	Scheduled	6/7/2017, 9:31:14 AM	6/7/2017, 10:15:21 AM	Failed	The received uncompressed data on the gateway client has exceeded limit.
	Scheduled	6/6/2017, 9:31:22 AM	6/6/2017, 10:03:42 AM	Completed
	Scheduled	6/5/2017, 9:31:19 AM	6/5/2017, 10:01:24 AM	Completed
	Scheduled	6/4/2017, 9:31:23 AM	6/4/2017, 10:00:34 AM	Completed
	Scheduled	6/3/2017, 9:31:25 AM	6/3/2017, 10:00:53 AM	Completed
	Scheduled	6/2/2017, 9:32:05 AM	6/2/2017, 10:02:07 AM	Completed
	Scheduled	6/1/2017, 9:31:55 AM	6/1/2017, 10:14:58 AM	Completed
	Scheduled	5/31/2017, 9:31:26 AM	5/31/2017, 10:00:27 AM	Completed
	Scheduled	5/30/2017, 9:31:26 AM	5/30/2017, 10:01:03 AM	Completed
	Scheduled	5/29/2017, 9:31:22 AM	5/29/2017, 9:58:28 AM	Completed
	Scheduled	5/28/2017, 9:31:09 AM	5/28/2017, 9:58:07 AM	Completed
	Scheduled	5/27/2017, 9:31:04 AM	5/27/2017, 9:51:40 AM	Completed
	Scheduled	5/26/2017, 9:31:22 AM	5/26/2017, 10:00:34 AM	Completed
	Scheduled	5/25/2017, 9:31:20 AM	5/25/2017, 10:01:43 AM	Completed
	Scheduled	5/24/2017, 9:31:07 AM	5/24/2017, 10:02:36 AM	Completed
	Scheduled	5/23/2017, 9:31:38 AM	5/23/2017, 9:58:49 AM	Completed
	Scheduled	5/22/2017, 9:30:16 AM	5/22/2017, 10:11:08 AM	Completed
	Scheduled	5/21/2017, 9:33:38 AM	5/21/2017, 10:02:38 AM	Completed

[tejald]

As mentioned my PBIX size is 86.5 MB and after compression it shows 37 MB still I am getting this error. Uploading PBIX is manual effort and we want to get it refreshed frequently during business hours. It seems to be restriction with number of records.

Any help is appriciated.

Thanks,

Tejal

[CasperAbakion]

We also have this problem with two of our customer's solutions. I found four  posts on it on the forums. It would be really nice to get this solved.

Last refresh failed: Wed Jun 14 2017 10:40:20 GMT+0200 (Romance Daylight Time)
The received uncompressed data on the gateway client has exceeded limit.Hide details

Cluster URI:	WABI-NORTH-EUROPE-redirect.analysis.windows.net
Activity ID:	d748407e-178a-d40e-e1d4-bca80d794676
Request ID:	5f15b9a9-10ab-fe8d-98be-bfdb9257dd61
Time:	2017-06-14 08:40:20Z

Last refresh failed: Wed Jun 14 2017 00:25:38 GMT+0200 (Romance Daylight Time)
The received uncompressed data on the gateway client has exceeded limit.Hide details

Cluster URI:	WABI-NORTH-EUROPE-redirect.analysis.windows.net
Activity ID:	be47e442-a91a-49d7-9f9f-5e4e6562c964
Request ID:	07225bac-8945-48b9-8e5a-0171afa8c1fe
Time:	2017-06-13 22:25:38Z