Unicode causes Dimension build failure?

UtterMan

Dimension Full build fails with 2 error messages below.

Customer dimension LastName column populated with ALL Chinese unicode characters.

Customer dimension FirstName column also populated with ALL Chinese unicode characters.

Columns are not used as name property for the key column.

Columns have been demoted to member properties by turning AttributeHierarchyEnabled to false

This occurs when final SELECT DISTINCT (all columns) statement is executed:

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dm_DimConsultant, Column: LastName, Value: ¹þ˹ҲÌᣮ¡¡. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Consultant of Dimension: Consultant from Database: Intelligence_v2, Record: 567894.

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dm_DimConsultant, Column: LastName, Value: ¹þ˹ҲÌᣮ¡¡. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Consultant of Dimension: Consultant from Database: Intelligence_v2, Record: 567894.

The collation settings are as follows:

Windows Collation: Latin1_General

Language: English (United States)

Yan

Chris,

I tried similar dimensions with Chinese unicode characters and I was able to build the dimension with no problem, with the same collation and settings. Can you try to execute the last SELECT DISTINCT statement on its own and see what happens.

Yan

Christopher Webb

Can you check to see whether the collation of your Analysis Services instance matches the collation of the underlying SQL Server database (assuming you're using SQL Server) I've seen similar errors in cases where they're different.

Chris

UtterMan

The SQL Server is using Collation SQL_Latin1_General_CP1_CI_AS

The Analysis Server is set to Latin1_General, with Accent-Sensitive enabled.

These appear to be the same, are these considered the same

Christopher Webb

Yes, I guess so. Have you tried using a Chinese collation

UtterMan

I ran the select distinct statement as you suggested, in SQL Management Studio as a TSQL statement. The query completed successfully, no error messages were returned.

UtterMan

Would you recommend setting the single dimension to Chinese, or the whole project

Christopher Webb

I would have thought just the dimension would have been enough, although it can't hurt to test the whole cube too (assuming all the text in the dimensions is in Chinese).

Chris

UtterMan

Changed the dimension collation to Chinese_PRC and received the same error while processing:

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dm_vwDimConsultantUS, Column: FirstName, Value: ¬¿. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Consultant of Dimension: Consultant from Database: Intelligence_DEV, Record: 137799. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while the 'Consultant' attribute of the 'Consultant' dimension from the 'Intelligence_DEV' database was being processed.

Christopher Webb

Have you tried setting the 'Ignore Error Count' property when you're processing the dimension (when you process click the 'Change Settings' button and go to the Dimension Key Errors tab) What do you see then

Chris

UtterMan

Using the "Ignore Failures" setting is the only way the dimension will build.

After further review, the failing unicode string were traced back to the source SQL tables, where it was discovered that SQL Server was treating them as bad characters (displaying as ). It appears that the error is not in SSAS processing or collating, but the data itself was bad, and was unable to be processed by SSAS.