Unable to refresh dataset - Error message

[bo_afk]

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

[v-rongtiep-msft · Accepted solution]

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.

[bo_afk]

Hi @v-rongtiep-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.

[ctmullins]

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-rongtiep-msft]

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.

   

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.

[bo_afk]

Hi @v-rongtiep-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.