MSSQL2005 Analysis Service Distinct Count

MSSQL2005 Analysis Service Distinct Count

• vikionline

  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

  
  

• naiad

  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

  
  

• ewillie

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

  Thanks

  
  

• MSDN Forum User

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

  
  
  

• chen55347

  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
  
  
  

• Alberto Borbolla MVP

  OK found why I got the diff :

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

  
  

• Jackie8640

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

  Regards

  
  
  

• DiegoCrespo

  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.

  
  
  
• MSSQL2005 Analysis Service Distinct Count