cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bo_afk
Post Patron
Post Patron

Unable to refresh dataset - Error message

Hi, I'm getting the error message below when I try to refresh one of my datasets in PowerBI service. I've re-published the report and it works but I'm just unable to refresh it. The PBIX file is "only" 64mb and I have around 7GB of free space remaining in the workspace so theoretically it sounds that space should not be an issue.

 

Any idea what's causing this ad how it can be fixed?

"The amount of data on the gateway client has exceeded the limit for a single table. Please consider reducing the use of highly repetitive strings values through normalized keys, removing unused columns, or upgrading to Power BI Premium."

1 ACCEPTED SOLUTION
v-polly-msft
Community Support
Community Support

Hi @bo_afk ,

I have found a similar psot, please refer to it to see if it helps you.

The amount of data on the gateway client has exceeded the limit for a single table. 

Actually, the resolution of this issue is exposed in the Error message itself, remove the columns with high cardinality or move to Premium. The easiest way to find the columns with high cardinality is to use the DAX Studio tool:

1 Connect to the model/PBIX file

2 Go to Advanced -->View Metrics

ry to remove as much as you can from the columns that have the highest cardinality. In the above sample if we can remove “Order ID”, it will help reduce the size significantly.   

 

Reference: https://docs.microsoft.com/power-bi/connect-data/refresh-troubleshooting-refresh-scenarios#uncompres...

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-polly-msft
Community Support
Community Support

Hi @bo_afk ,

I have found a similar psot, please refer to it to see if it helps you.

The amount of data on the gateway client has exceeded the limit for a single table. 

Actually, the resolution of this issue is exposed in the Error message itself, remove the columns with high cardinality or move to Premium. The easiest way to find the columns with high cardinality is to use the DAX Studio tool:

1 Connect to the model/PBIX file

2 Go to Advanced -->View Metrics

ry to remove as much as you can from the columns that have the highest cardinality. In the above sample if we can remove “Order ID”, it will help reduce the size significantly.   

 

Reference: https://docs.microsoft.com/power-bi/connect-data/refresh-troubleshooting-refresh-scenarios#uncompres...

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-polly-msft  thanks for your suggestion.

What would be considered a "high cardinality"? I've checked my table and the highest cardinality column is 19,796.

Actually Polly, while your answer is technically correct, it doesn't address the real issue here.

 

The real issue, which is woefully pervasive throughout the PowerBI Service, is that the standard error messages are far too vague and unhelpful.

 

For instance, the message that @bo_afk experienced, which I myself have experienced quite a number of times, tells us that some table somewhere in the data model is larger than is permitted.  That's all.  It doesn't tell us which table.  It doesn't tell us how far above the limit the table is.  It doesn't tell us anything that's actually helpful in our troubleshooting efforts, other than the issue is somehow size related.  Even though I'm quite sure that the engine inside the Service knows perfectly well which table, which column(s), and what the storage delta actually is.

 

Honestly, it wouldn't take a Microsoft engineer half of an afternoon to add this informatin to the standard error message, which would a) save us developers a heck of a lot of our valuable time, and b) save you Community Support Team members a heck of a lot of your valuable time answering questions that shouldn't need to be asked.

v-polly-msft
Community Support
Community Support

Hi @bo_afk ,

To make the refresh works again, reducing the use of highly repetitive, long string values and instead using a normalized key or removing the column (if not in use) will help.

If you don't want to reduce anything in your pbix, you could consider upgrading to Power BI Premium where you can have more storage and function.

https://powerbi.microsoft.com/en-us/power-bi-premium/

 

If you are using Power BI Premium,please try to Enable large datasets.

  1. Create a model in Power BI Desktop. If your dataset will become larger and progressively consume more memory, be sure to configure Incremental refresh.

  2. Publish the model as a dataset to the service.

  3. In the service > dataset > Settings, expand Large dataset storage format, click the slider to On, and then click Apply.

    vpollymsft_1-1654838238988.png

     

  4. Invoke a refresh to load historical data based on the incremental refresh policy. The first refresh could take a while to load the history. Subsequent refreshes should be faster, depending on your incremental refresh policy.

More details:Enable large datasets 

 

I have also found a similar post, please refer to it to see if it helps you.

Premium account : The amount of data on the gateway client has exceeded the limit for a single table 

  • Settled with Large dataset storage format

If it does not help, please provide more details with the error.

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi @v-polly-msft , thanks for the response and info.

I am able to publish the report with the same dataset and current size (after refreshing locally on desktop), should I be able to refresh the dataset in PBI Service with no issues either? 

 

Additionally, I have other reports in the same workspace which are much bigger than this and they have been able to refresh successfully.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors