Dataset size Limitation in Power BI PRO - any alternate solution or workaround ?

[TanviChourasia]

Hi

I am getting the following error while  Auto Refresh in Power BI PRO.

Data source error: Unable to save the changes since the new dataset of size 1053 MB exceeds the limit of 1024 MB. If the dataset is hosted on a PowerBI Premium capacity, you may also reach out to your capacity administrator to see if the limit can be adjusted.

Just want to know if is it true that if the Dataset size exceeds 1 GB in Power BI Pro, then there is no alternate solution but to go for Power BI Premium only. Coz for the last few months, Auto Refresh was working fine and the dashboard was getting updated properly. But I think as data is getting added on daily basis, the file went beyond 1 GB and I have Power BI PRO only.

So I need a clearcut answer to convey to the client that there is no other solution but to go for the Premium version or else we need to cut down the old months' data to continue working in PRO license.

Looking for answers in this regard from experts.

Thanks.

[kumar27]

@TanviChourasia was your problem solved ?

How about a direct query option.

[kumar27]

@TanviChourasia was your problem solved ?

How about a direct query option.

[v-stephen-msft]

Hi @TanviChourasia ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.

Best Regards,
Stephen Tao

[v-stephen-msft]

Hi @TanviChourasia ,

Two blogs explain how to reduce the size of the data set, please kindly refer to:

https://maqsoftware.com/expertise/powerbi/power-bi-best-practices

https://docs.microsoft.com/en-us/power-bi/guidance/import-modeling-data-reduction

In addition, you may try to change the connection mode, such as using Direct Query mode to connect to the data source. 

Tips: To refresh the data set connected by Direct Query, the gateway need to be configured.

Best Regards,

Stephen Tao

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

[GilbertQ]

Hi @TanviChourasia 

You can also use Vertipaq Analyzer within DAX studio to analyze your model and see what is taking up all the space.

Here is a great video by Guy in a Cube: https://guyinacube.com/2019/10/29/quickly-review-your-power-bi-data-model-with-vertipaq-analyzer/

[colivier]

Hi @TanviChourasia 

You can try using PBI Spy to identify unused columns in your dataset. Removing these can help to reduce the size of your report to below the 1024MB limit. You can dowload it from www.pbispy.com.

Don't hesitate to reach out to me if you need any assistance with it.

Some other things to consider:

Creating your own Date dimension table and turning of the Auto date/time feature in Power BI Desktop can result in a smaller report. See the following article for details: https://blog.crossjoin.co.uk/2016/12/16/power-bi-model-size-bloat-and-auto-datetime-tables/

Also: using DateTime columns with many unique don't compress well. Consider creating a separate time table and using this as a dimension. See the following article for details: https://radacad.com/how-to-use-time-and-date-dimensions-in-a-power-bi-model

Using DateTime columns may also result in columns that don't compress well in reports. If you do use DateTime columns with a lot of unique values consider rather using separate Date and Time dimension tables. This