Forum Discussion
High Cardinality solution by SQLBI not working
Unfortunately, there is no such thing as a free lunch.
What we describe in the article works as long as the data is small and the dictionary size is big.
When you reduce the dictionary, you also increase the data size (this depends on the combination of different values in the split columns - it's a problem of statistical distribution, but to keep it simple, let's say that the more rows, the larger the number of combinations, the lower the compression.
Now, if you have a relatively large dictionary with a relatively small compressed data size, the trick works, and you have the same RAM.
The bigger the table, the larger the data size - at a certain point, the saving in the dictionary no longer replaces the saving in data size. There is a point where the optimization is actually worsening the situation also in data size.
It seems you are at the sweet point, meaning you should return to the single column.
The good news is that if you publish the database on Power BI service with the large format, the columns are loaded in memory only when it's used, so you don't pay the full memory price unless someone actually uses it (hopefully, no).
Unfortunately, there is no such thing as a free lunch.
What we describe in the article works as long as the data is small and the dictionary size is big.
When you reduce the dictionary, you also increase the data size (this depends on the combination of different values in the split columns - it's a problem of statistical distribution, but to keep it simple, let's say that the more rows, the larger the number of combinations, the lower the compression.
Now, if you have a relatively large dictionary with a relatively small compressed data size, the trick works, and you have the same RAM.
The bigger the table, the larger the data size - at a certain point, the saving in the dictionary no longer replaces the saving in data size. There is a point where the optimization is actually worsening the situation also in data size.
It seems you are at the sweet point, meaning you should return to the single column.
The good news is that if you publish the database on Power BI service with the large format, the columns are loaded in memory only when it's used, so you don't pay the full memory price unless someone actually uses it (hopefully, no).
Hi marcorusso , thank you for your immediate and detailed response.
Unfortunately, in my case the distinct count of that column is my main desired metric. So it needs to be used constantly and that results in visuals not loading and the capacity usage limits being stretched and breached.
Also, the dataset size is hitting the capacity's hard limits.
Thanks again for all your work and support.