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

MohanV 1 Reputation point
2021-03-04T13:30:26.987+00:00

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.

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,244 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alexei Stoyanovsky 3,416 Reputation points
    2021-03-05T09:30:59.703+00:00

    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.

    0 comments
  2. Lukas Yu -MSFT 5,816 Reputation points
    2021-03-12T08:26:33.127+00:00

    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.

    0 comments
  3. Reddy Sreeyapu Reddy, Jayasimha 1 Reputation point
    2022-11-06T03:46:19.497+00:00

    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