Search code examples
ssasolapcubes

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

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?

Solution

  • 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