SSAS : How to avoid 2 Billion distinct values in a Column limit

Question

Hello All,

We have our tabular models with 25+ Billions of records with aggregated fact tables.

Recently when we processed our one of fact table, we got the error on a whole number data type column as

Error:
The 'column' of the 'table' cannot be processed because it contains more than two billion unique values.

We managed that by splitting the column by length, and using those two columns in report by using DAX.

Now we got the same error on a decimal column.

Splitting the columns and using those wont solve this issue as we have more than 100 columns in a table.
and we may get the same error on another column going forward.

I see that, limitation was mentioned in maximum-capacity-specifications-analysis-services , but nowhere it mentioned how to avoid this.

Any suggestion or tips and tricks on this.

Thanks,
Mohan V.

Answer 1

The first thing to consider is whether Tabular is actually a good fit for the scale of your data.
The three ideas for squeezing 2B+ distinct values into Tabular I have are split columns, split tables, and lowering precision, but of course each is but a workaround. To actually solve the problem will require Microsoft to release new code.

Answer 2

I hope you have made progress on you issue.

If you are not satisfied with the workaround , you could submit the requirement at https://feedback.azure.com/forums/908035-sql-server.
If the requirement mentioned by customers for many times, the product team may consider to add this feature in the next SQL Server version. Your feedback is valuable for us to improve our products and increase the level of service provided.

Answer 3

I am facing this error while processing the fact partition stating more than 2 billion unique values are not allowed in a column. But If I look at the total number of rows in the table there were 20 million only. So, 2 billion unique values in a column is not possible.

Could you please suggest me what could be the issue.

We are using SSDT 2013 version.

Regards,
Jayasimha