Sql Server Tips Distinct_count Measure On Uniqueidentifier



How to create Distinct count measure on uniqueidentifier

Problem

Creating a new Distinct Count measure based on an uniqueidentifier (GUID) source column gives the following error:
Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect. Errors in the OLAP storage engine: An error occurred while processing the 'Facts' partition of the 'Distinct counts' measure group for the 'Sales' cube from the DWDEV database.

The uniqueidentifier data type cannot be sorted and this is why the GUID cannot be used in a GROUP BY statement and used as a measure in an Microsoft SQL Server Analysis Services cube.

Solution

The workaround of this problem is quite simple:
  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))
  6. Use the new measure as a base for measure creation