ssasssas-2012

Distinct Count on Integer Column breaks on Negative Value


Nightly we process our cube, and last night we had a failure during processing. The error code of interest stated:

Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect.

After much investigation, I was able to narrow it down to a negative (-1) value in the column used for the distinct count. Once I set the value to null, the cube processed without issue.

The column is a foreign key on the fact table to a dimension, and we have a convention where the -1 relates to an "(UNK)" dimension record.

Is there a setting or simple workaround to get the cube to process in this scenario?


Solution

  • So, almost a year later I ran into this issue again! I completely forgot that I had posted this question, until I found it during research! I thought, "that sounds familiar", and then noticed that I had posted it!

    Anyways, after additional research and more thought, there are two answers to this question:

    1. Under OLAP Properties, there is a setting for CheckDistinctRecordSortOrder.
    2. This is the wrong question. Doing a distinct count on the FK is probably a bad idea as the -1 should probably not be counted as a distinct value. So a null value would be a better representation in the distinct count. But to have a proper Dimension Lookup, we would need the -1 in the FK column. So the answer would be to have an additional column or a calculated column for the distinct count.