SSAS: How to solve column of table cannot be processed bcz it contains more than 2B distinct records

Question

Hello All,

We are using SSAS tabular models for our power bi reports.

Our models are pulling data from SQL Server and the data is increasing rapidly in billions.

One of facttable, which has more than 10Billions of records, when we are trying to process that table,

we got below error

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

The column which we got this error is a decimal data type

But earlier also we got the same error on a string column, and we resolved it by splitting the column into two, and using those two columns in report by concatenating through measure.

I have gone through below url

https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/olap-physical/maximum-cap...

and in there, under the tabular section,

Rows in a table Unlimited
Warning: With the restriction that no single column in the table can have more than 1,999,999,997 distinct values.

Now, my question is, how we can keep a check on our table columns, where we can know that, any of the our table's column is reaching the limit of 2B distinct values, so that we can take prior action on it to resolve.

Any suggestions or tips and tricks would be very helpful.

Thanks,

Mohan V.

Answer 1

If you wanted to check manually you should be able to use the View Metrics button in the advanced ribbon in DAX Studio

Answer 2

I'd set up snapshots of count(distinct) over all suspect columns. Daily or more frequently if your data flow warrants it. The results can then be analysed with you tools of choice: a chart, a forecasting solution, a threshhold check, etc.

Answer 3

From your description, I would suggest to try to take this "rapid" growth into consideration and redesign the data warehouse work flow.

Depends on how many rows you get per months or per quarter, you could set some automation to create new fact table for a period, so that one table could contain less than 2 billion records.

This is not for the sake of SSAS tabular, it should also benefit the SQL Server performance. But in case we also need to be careful and not overkill on this. Because too many fact tables could also be nightmare for management.