Sql-server – “The attribute cannot be found when processing” error

errorssql serverssas

I'm facing following error while processing SSAS MDX cube:

Errors in the OLAP storage engine: The attribute key cannot be found when     
processing: Table: 'FACT_TABLE_NAME', Column: 'COLUMN_NAME', 
Value: 'VALUE'. The attribute is 'Attribute Name'.

Upon checking, I can see that this VALUE is indeed missing in FACT_TABLE_NAME but it exists in associated DIM_TABLE_NAME. However, this situation for me is perfectly fine and there's a chance that certain values won't exists between DIM and FACT (assuming that DIM can have more data).

Question is – is there anything I can do except processing data with using custom error configuration and ignoring "Key not found" errors?

Additional information

Some screens

enter image description here

enter image description here

DSV View

Error message

I had to blur out value due to data security. Table that is being returned is the view behind WEEKLY MEASURES table.

Join between WEEKLY MEASURES and CUSTOMER happens on CustOrgHier_id both in DSV and in the Cube.

Best Answer

You should only get that error when a value exists in the fact table that doesn't exist in the dimension table, the error is "attribute key", only dimensions have attributes, facts don't, they have measures and dimension relations, maybe you have a dimension based on your fact table?

Having keys in your dimension that don't exist in the fact table is perfectly fine. Think of it this way: you can have a date dimension containing every date, but some days you haven't sold anything, so the date wouldn't appear in your sales fact while it exists in the dimension table. That's a perfectly reasonable scenario.

If you don't want to use a custom error configuration you will have to solve that in your source data (which would be the best solution anyway).
If you have an ETL process that would be the best point to solve your missing foreign keys but if you don't you can resort to named queries in the dmv where you UNION the missing records with the actual data.

SELECT 'missingkeyvalue' as key, 'explanation' as description
UNION
-- select actual data

The (unknown) dimension member doesn't really tell your users anything useful when they view it in a report, and they often forget to select them in filters.

Copied from my answer here:

If you have missing data in your foreign keys in your fact table (i.e. when your ETL process looks up the surrogate keys in your dimension table but the related row doesn't exist in your dimension) you need to decide based on your user requirements, but the general approach is to convert them to blank, 'unknown, invalid or whatever your requirements are.

If you have blank attribute values you should probably leave it as blank as to not confuse the users.

One thing to keep in mind specific to SSAS is that using error configurations in SSAS to let SSAS handle missing dimension keys has a huge impact on processing performance so you should probably solve that in your ETL process. The (unknown) dimension member is also very confusing for your end users as they don't know what it means when seen on a report and often forget to include it in filters (they have to select the unknown member on every dimension or they will be missing data).

As usual Kimball has a best practice on handling missing data in your dimensional model: Design Tip #128 Selecting Default Values for Nulls

Related Question