cancel
Showing results for 
Search instead for 
Did you mean: 

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

Hi, 

 

I am trying to refresh a dataset but getting the following error:

 

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

 

The workspace storage is as follows:

danielcontente_0-1645115324251.png

 

 

I appreciate your help.

 

Thank you!

Status: Investigating

Hi @danielcontente 

 

I found an similar issue with similar refresh errors. It should be by-design limitation. 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

vcazhengmsft_0-1645162559316.png

 

3 Try 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...

 

Please try the workaround above and let me know whether it could solve your issue. Thanks in advance!

 

Best Regards,

Community Support Team _ Caiyun

Comments
v-cazheng-msft
Community Support
Status changed to: Investigating

Hi @danielcontente 

 

I found an similar issue with similar refresh errors. It should be by-design limitation. 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

vcazhengmsft_0-1645162559316.png

 

3 Try 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...

 

Please try the workaround above and let me know whether it could solve your issue. Thanks in advance!

 

Best Regards,

Community Support Team _ Caiyun

danielcontente
Helper I

Hi @v-cazheng-msft , 

I found a column with very high cardinality, but I cant delete this column.

I see I have a lot of free storage (please see the picture on my first post - I only used 976MB), but still cant refresh it.

Do you know why is that?

Is there another solution?

 

v-cazheng-msft
Community Support

Hi @danielcontente ,

 

Thanks for your reply! I understand. If so, may I know whether you have Premium Per User or Premium license? If you have either of them, you could move your workspace to Premium Per User capacity or Premium capacity. After that, you will get more resources to support the refresh your dataset. In such case, you should aviod such issues in some extent.

 

According to the design of Power BI, your data in the model will be highly compressed and saved in the Storage. When there is any report viewing activities or dataset refresh activities, report dataset will be loaded into Memory and be decompressed. In this process, the size of the dataset will grow to even 10 times larger than its original size. Obviously, it will easily exceed the limitation of Shared capacity and refresh fail.

 

Just as the error info prompts, we need reduce data amount or provide more resource to make the refresh through.

 

Best Regards,

Community Support Team _ Caiyun

v-cazheng-msft
Community Support

Hi @danielcontente ,

 

Thanks for your reply! I understand. If so, may I know whether you have Premium Per User or Premium license? If you have either of them, you could move your workspace to Premium Per User capacity or Premium capacity. After that, you will get more resources to support the refresh of your dataset. In such case, you should aviod such issues in some extent. For how to move your workspace to these kinds of capacity, you may refer to Configure and manage capacities in Power BI Premium - Power BI | Microsoft Docs.

 

According to the design of Power BI, your data in the model will be highly compressed and saved in the Storage. When there is any report viewing activities or dataset refresh activities, report dataset will be loaded into Memory and be decompressed. In this process, the size of the dataset will grow to even 10 times larger than its original size. Obviously, it will easily exceed the limitation of Shared capacity and refresh fail.

 

Just as the error info prompts, we need reduce data amount or provide more resource to make the refresh through.

 

In addition, if you would like to learn something about Premium Per User or Premium license. You may refer to these official documents.

Power BI Premium Per User - Power BI | Microsoft Docs

What is Microsoft Power BI Premium? - Power BI | Microsoft Docs

 

Best Regards,

Community Support Team _ Caiyun