SSAS & Excel BI Tips Chapter Four: Duplicate property keys found when processing, property keys not found

This is two very common SSAS processing exceptions, there are many articles on the internet can be found to solve the problem, but rarely see the analysis of the cause of the exception, first look at the first " OLAP storage Engine Error: The process of finding duplicate property keys ", a Dimension table key field, That is to say the primary key, is must have unique, if because the dimension primary key field appears duplicate key, really have no say, too should not make this mistake, so usually you see this processing exception information because of a non-primary key attribute, for example [Dim Customer] Dimension [ Education] property, and the primary key [Customer key] is a one-to-many relationship, education education level entries are limited, the same education level of the customer will have a lot, so in a sense [Dim customer] dimension of [education] The property is to allow duplicates.

To illustrate, I first made some update to the English education field of the AW Library's Customer table, and then made a simple demo cube with only one [Dim Customer] dimension, one [中文版 education] Property , the other settings are not modified by default.

A [fact customer] fact, the data source is mapped to an SQL query, where only the dimension table primary key Customerkey is found, so the corresponding fact measure has only one [fact customer Count] customer number.

Then deploy this project, receive the exception information shown, englisheducation in the bachelors appear duplicate attribute key, with Bachelors Bachelor's degree of the customer has a lot of people, this is too normal, what is the situation? We expand the highlighted processing English education log and find the specific SQL script to copy it out to see

The copied script is a distinct query, executed as shown, the problem came, it is not difficult to find that the 2nd and 6th lines are bachelors, it is indeed repeated (the actual production environment may not be able to visually identify duplicates), then why distinct does not work?

We make a simple change to the script, look at the Bachelors of the two records are different, see the first line of bachelors more than an ASCII code 9, that is, the TAB key

Before that, I did update updates with a few special characters appended to one of the customer's education content, with the following script:

Update DimCustomer Set englisheducation = ' bachelors '+char(9) +char(in) where customerkey =11001

So the question comes again, since this is already two different education, why does SSAS report the exception of duplicate keys? We copied the query results into Excel.

Then go to re-operation, you will find Excel to re-success, that is, Excel is the first to trim the two items, remove some special characters (such as space, line breaks, TAB key, etc.)

It is not difficult to see, SSAS in the processing of dimensional data is also done the same data cleaning work, and the database of the distinct can not be so simple and violent to do this cleaning work, SSAS will find duplicate key exception, The simple understanding is that SSAS requests distinct to the database for a education list, the database is queried and given to SSAS, and SSAS does some cleaning work, and then finds duplicates, In short, this problem is due to the inconsistent processing of strings by MSSQL and SSAS . This exception warning is not affected, in the errorconfiguration set keyduplicate for Ignore can, of course, if you have a neat obsessive compulsive disorder, you can also do manual cleaning in the ETL process to remove these special characters ...

As for another handling exception " processing when the following property keys cannot be found", this is usually due to the character set and collation of the database, reproduced in the following article explained very thoroughly

http://www.imkevinyang.com/2009/10/%E5%85%A8%E5%8D%8A%E8%A7%92%E7%A9%BA%E6%A0%BC%E5%AF%BC%E8%87%B4%E7%9A% 84analysis-service%e5%a4%84%e7%90%86%e9%94%99%e8%af%af.html

For example, to make the following update to the Customer dimension table for two customer to append full-width and half-width space, then SSAS in processing 11000 of this customer, found the corresponding education in the database (with full-width space) Not found in the data structure of SSAS, combined with the above understanding, the data structure of SSAS is stored in the results of their own cleansing, the problem is also due to MSSQL and SSAS processing inconsistent strings .

update dimcustomer englisheducation Span style= "Color:gray;" >= bachelors ' +n " where customerkey= 11000

Update DimCustomer Set englisheducation = ' bachelors '+ ' where customerkey=11001

Another thing to add is that this handling exception can also be set to ignore ignored, but note that the consequences are serious, such as after you perform the above two update updates, and set Keynotfound to ignore

At this point, if the [Dim Customer] dimension is processed separately, it can be handled successfully, but the comparison chart before and after processing is as follows, and the data anomaly is not easily found in some situations.

And if the whole cube process full, or will report the following error message, it is strange that the customerkey is completely irrelevant 11918, not clear the specific reason

Since this anomaly is so important that it cannot be simply violent ignore ignored, then how to locate the specific error record, please refer to the following article:

http://www.imkevinyang.com/2010/07/ssas%E5%A4%84%E7%90%86%E6%97%B6%E6%89%BE%E4%B8%8D%E5%88%B0%E5%B1%9E%E6%80% A7%e9%94%ae%e7%9a%84%e8%a7%a3%e5%86%b3%e5%8a%9e%e6%b3%95.html/comment-page-1

After setting Keynotfound to Reportandcontinue, use the record number in the error message to navigate to the specific error data, or you can use the SQL like location described earlier.

SSAS & Excel BI Tips Chapter Four: Duplicate property keys found when processing, property keys not found