MSSQL2005 Analysis Service Distinct Count

HY

hi,

i am currently trying to build a distinct count on my cube (mssql2005 analysis services).

But after i added the discount count on the field i want to and start the processing, the following errors appear.

- 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 'FACT VIEW STATISTIC' partition of the 'FACT VIEW STATISTIC 1' measure group for the 'Accident Statistic' cube from the OLAP_PROJECT database.

the count measure works fine.

will appreciate any help on this distinct count problem.

thanks in advance.

-

HY

Edward Melomed

Try and see what kind of query Analysis Services sends to the relational database during processing of distinct count measure.

You will see it sending a query containing ORDER BY clause asking relational database to sort results accourding to the distinct count measure.

It it possible the view you defined your partitions on, brings data sorted differently
Any new data becomes avaliable during processing of the partition

The error indicates Analysis Server detecting inconsistencies in sorting of data coming from relational database.

See if you might need to define collation correctly for your sort.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

HY

Thanks edward.

as i drew data from Oracle view, the collation must be specify correctly.

check the Oracle collation and discovered it is binary.

changed the distinct count to binary collation and it works.

thanks.

-

HY

UmaMohan

Could any one explain about the error and solution elaborately. I am not sure how this can be rectified. Appreciate any help.

Thanks

Metrum

Hi there,

We encountered the same issue:

And changing the collation to binary allowed us to process the cube ...

But I still don't understand why I got the error with the collation set to SQL_Latin1_CI_AS

By the way, I also encountered a difference of 1 by browsing the cube and when I count on the table:

Browse on the measure with the distinct count = 800

Result of "select count (distinct (sessionid)) from dbo.facttransaction" = 799

(NB: some sessionid are NULL)

Does the cube take in consideration the NULL values

Thanks

Metrum

OK found why I got the diff :

Analysis Services handle a NULL value like a 0 value in a DISTINCT COUNT measure

cvsan

I am facing a similar problem shile deploying an analysis services project. Could you please let me know the solution for the same.

Regards

James A P

We found that NULL values in the column that the DistinctCount is applied to will cause this error.

Ibrahim Hafidh

I also received this error:
"Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect. "
Running SQL Server 2005 SP2 Enterprise Edition

The collation between SQL Server and Analysis Services was the same.
The distinct count was on a character data type.
There were no NULLs in the data.
The cube was processing fine until new data was added.

After some investigation into the data it seems that the culprit was one row that the data length was 13 characters on the column of the distinct count. Everything else was less than 13 characters. (See results below). Updating this one row solved the problem. The exact value of the data is: '1-4296-175-9'

Here is a result set:
select len(columnname) as data_length, count(*) as count
from [tablename]
group by len(columnname)
order by data_length

data_length count
2 3
5 1
6 3
7 2
9 1
10 856
13 1

My question though is if SQL2005 can do distinct counts on strings then why choke on one row with an extra length

Frank van Zuilen (Pilares)

I faced the same problem with fields with different lenghts and odd characters like _ . etc.

I converted the contents of the field to a hash e.g. by using CHECHSUM, and I had no more problems

MSSQL2005 Analysis Service Distinct Count

Re: MSSQL2005 Analysis Service Distinct Count

Re: MSSQL2005 Analysis Service Distinct Count

Re: MSSQL2005 Analysis Service Distinct Count

Re: MSSQL2005 Analysis Service Distinct Count

Re: MSSQL2005 Analysis Service Distinct Count

Re: MSSQL2005 Analysis Service Distinct Count

Re: MSSQL2005 Analysis Service Distinct Count

Re: MSSQL2005 Analysis Service Distinct Count

Re: MSSQL2005 Analysis Service Distinct Count