How to create Distinct count measure on uniqueidentifier
ProblemCreating 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.
SolutionThe workaround of this problem is quite simple:
- Go to the data source view in Solution Explorer
- Find a table which contains the GUID column which needs to be aggregated
- Right-click on the header of the selected table and select 'Create Named Calculation'
- Give it a name
- Type the following in the Expression field:
CAST(ColumnName as varchar(36))
- Use the new measure as a base for measure creation