CODEWITHSUNDEEP
ssasolapcubes
rgargente
rgargente

Reputation: 1840

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?

Upvotes: 2

Views: 9370

Answers (8)

pso
pso

Reputation: 889

I was facing the same problem and I found that there were special characters/symbols in my data which prevented from processing the cube. Most probably, the cube was unable to sort data with special characters.

I was able to solve the issue by converting the data into hashbyte string using following function:

SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', [column_name])), 3, 32)

Upvotes: 0

user3246543
user3246543

Reputation: 11

Derby provided the correct answer in my situation. In my case, I was trying to process a Mining Model in SSDT (VS 2012) and I was getting the error.

The error began after I made some corrections to a query that generated a table I used as my data source. My guess is that it introduced some NULLS somewhere that I did not have before I made the corrections to my query, and therefore source table (the Mining Model processed just fine before that). I am unable to vote up Derby due to my noob reputation.

Upvotes: 0

Davos
Davos

Reputation: 5415

I had this problem trying to make a distinct count on an INT ID column. After spending ages looking at Unknown Member settings and dimension usage settings, I found another suggestion that Distinct Count measures don't like NULL values. Adding WHERE COLUMN IS NOT NULL to the partition queries for this distinct count measure group solved my issue.

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

Check that the distinctcount column does not contain nulls. If it does, these can be omitted from the measure group via a query in the partition definition (assuming the distinct count measure is in its own measure group)"

Upvotes: 1

derby
derby

Reputation: 71

Google and in particular Eggheadcafe came to the rescue with a solution:

  • Connect to Analysis Services from SQL Server Management Studio
  • Right Click on the Analysis Services Server name (ie the top most Analysis Services link)
  • Choose properties
  • Check Show Advanced (All) Properties
  • Find the following setting
  • OLAP \ Process \ CheckDistinctRecordSortOrder
  • Set this value to 0
  • Click OK
  • Open your OLAP in Visual Studio or BIDS.
  • Process the cube.

Upvotes: 3

kclim1002
kclim1002

Reputation:

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

Upvotes: 8

Kanishk
Kanishk

Reputation:

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

Upvotes: 0

Darren Gosbell
Darren Gosbell

Reputation: 1940

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.

Upvotes: 0

rgargente
rgargente

Reputation: 1840

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.

Upvotes: 0

Related Questions