Referential Integrity error with dimension processing for cube

Question

One of our production cube failed with the following error message:

Source: Analysis Services Execute DDL Task Analysis Services Execute DDL Task Description: Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_vw_FACT_OperationalTimeSheetData', Column: 'CurrentHomeLaborOrg', Value: '117-MARKETPLACE-38'. The attribute is 'KeyOrganisation'. End Warning Warning: 2013-07-22 06:26:07.51 Code: 0x811F0002
Source: Analysis Services Execute DDL Task Analysis Services Execute DDL Task Description: Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute KeyOrganisation of Dimension: Current Home Organisation from Database: LadbrokesReporting2005, Cube: MIS Clone, Measure Group: Kronos Time Sheet, Partition: 201307, Record: 179774. End Warning

On extensive google searches for the above error message I learnt this is due to referential integrity i.e some rows in the fact table could not be linked to the dimension table. In my previous analysis I figured that the for some of the values like the one given above in the error message '117-MARKETPLACE-38' in the fact table , the format in the dimension table was '117_Marketplace_38'. However, I have unearthed some striking facts :-

1. The query for the view which populates the fact converts data inthe the format '117-MARKETPLACE-38'. The format of the data in the source tables is '117_Marketplace_38'.

2. There are many other entries of the format '117-Marketplace-38'. However, the error seems to be present only for that entry. So how come others are getting accepted but this isnt?

I've run out of ideas. Hence, it would be great if someone could help me out on this.

Answer 1

Are you sure that the dimension is processed? Remember that as it links the facts to the dimensions, it does so on the SSAS dimension, not the underlying SQL table. Try running a process(Update) on the dimension first?

In your original post you're mixing upper and lower case, MARKETPLACE and Marketplace. Is this how it is in the source? It could be that it isn't treating them as the same. I've seen SSAS have issues with "Id" versus "ID"

Possible relative of ssas attribute key cannot be found even though it exists and is not due to duplicates

Edited comments into answer after acceptance.

Answer 2

I got this same error message, but had to handle it differently in my case: details below for others who run into the same issue.

In my scenario, I was connecting my fact table to another fact table via a bridge table. This bridge table was formed via an INNER JOIN, so it only had records that matched in both; legitimately, then certain rows in FactTableA were not in the BrgTable joining to FactTableB.

To allow FactTableA to process even when there were no valid links to BrgTable, I followed these instructions from TechNet:

Referential Integrity Issues in Fact Table

The sales fact table has records with product_id that does not exist in the product dimension table. The server will produce a KeyNotFound error during partition processing. By default, KeyNotFound errors are logged and counted towards the key error limit, which is zero by default. Hence the processing will fail upon the first error.

The solution is to modify the ErrorConfiguration on the measure group or partition. Following are two alternatives:

 Set KeyNotFound=IgnoreError.

 Set KeyErrorLimit to a sufficiently large number.

The default handling of KeyNotFound errors is to allocate the fact record to the unknown member. Another alternative is to set KeyErrorAction=DiscardRecord, to discard the fact table record altogether.

For me, I had to go to the partition associated with the failing measure group, open up the Properties pane, set ErrorConfiguration to Custom, and then expand Custom and set KeyNotFound to IgnoreError.