Analysis Services 2005 OLAP Cubes : Can I create a distinct count measure on a nvarchar column?

Question

I'm trying to create a cube with a single measure. This measure is a distinct count of a "name" column. The cube works perfectly if the measure is set to "count" type. However when I set distinct count I get this error:

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

I have read in some blogs that you can only have a distinct count on a numeric column. I can't see a good reason for this, and I can't find that info on official documentation. However, it may be true. Anyways, I'm really stuck with this issue. What are my options?

Answer 1

I will answer myself, maybe this is helpful for somebody else.

The short answer is YES.

I have created some test tables with the same structure but just a few test rows. The cube works perfectly with this data.

So, I guess there are some corrupt data on the original tables, or maybe some rare chars.

Answer 2

You are right, you can do a distinct count on an nvarchar column.

It could be something to do with strange characters or with your collation setting.

You could try processing smaller subsets of the measure group to try and isolate a problem row.

Answer 3

It can be due to the collation settings. Sql server has by default SQL_Latin1_General_CP1_CI_AS where as the SSAS uses the Windows Collation Latin1_General. If you change the collation on the column on which you are doing the distinct count i think will solve the problem

Answer 4

Hi , my answer may be too late for you, but hope this can help other which have the same problem.

1. Go to the data source view in Solution Explorer
2. Find a table which contains the GUID column which needs to be aggregated
3. Right-click on the header of the selected table and select 'Create Named Calculation'
4. Give it a name
5. Type the following in the Expression field: CAST(ColumnName as varchar(36))

This solution is from this link http://www.bi-dw.info/sql-server-tips/distinct_count-measure-on-uniqueidentifier.htm