MSSQL2005 Analysis Service Distinct Count

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

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.

|||

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

|||

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

Thanks

|||

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

|||

OK found why I got the diff :

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