SSAS Cube reprocessing fails - then succeeds if I try again

Question

So I'm basically brand new to the concept of BI, and I've inherited an existing ETL process that is a two step process:

1. Loads the data into a database that is only used by the cube processing

2. Starts off the SSAS cube processing against said database

It seems pretty well isolated, but occasionally (once a week, sometimes twice) it will fail with the following exception:

"Errors in the OLAP storage engine: The attribute key cannot be found"

Now the interesting thing is that:

1. The dimension having the issue is not usually the same one (i.e. there's no single dimension that consistently has this failure)

2. The source table, when I inspect it, does actually contain the attribute key that it says could not be found

3. If I then immediately reprocess the dimensions and cubes manually through SSMS, they reprocess successfully and without incident.

In both the aforementioned job and when I reprocess them through SSMS, I am using "ProcessFull", so it should be reprocessing them completely.

Has anyone run into such an issue? I'm scratching my head about it... because if it was a genuine data integrity issue, reprocessing the cube again wouldn't fix it. What on earth could be happening? I've been tasked with finding out why this happens, but I can neither reproduce it consistently nor can I point to a data integrity problem as the root cause.

Thanks for any input you can provide!

Answer 1

I ran into a simular issue and i found that processing the dimentions before processing the cube worked for me.

Answer 2

You said the package starts the processing, what are the settings for the processing?

I'm wondering if it is processing the dimensions last, that could explain it. That would probably only happen on a full process if the number of transactions and parallelism are unusual. Check the settings on your manual full process, versus the settings on the automated process.

Answer 3

Dimensions should always be processed first.

How else will the cube know how to map things out?

There may have been a sale of a new item that appears in the fact table, but which has not been added to the products dimension, for example. It will not be able to perform the join and will fail.

Answer 4

I have seen something similar with both AS2005 and AS2008. We get the Dimension Key not found error, despite the dimensions having been processed.

We load and process dimensions daily. On the first of the month, new partitions are created. On the first weekend of the month, after the daily dimension processing, fact tables are loaded and the partitions processed. All this is done via SSIS. For several months now, partition processing has been failing. If we wait a day, let the daily dimension processing run once, the partitions then process OK. Testing has shown that running ProcessUpdate on one key dimension AFTER a fact table has loaded allows the associated partition to process. Note that the interaction is specific to each partition and depends on the fact table being loaded. If we load fact table A, process the dimension, then partition A will process. If we're late loading fact table B, we'll have to wait for the dimension processing to run again before partition B will process.

It's well known that a dimension ProcessUpdate will invalidate associated partition indexes and aggregations, so there are ties between dimension and partition processing. I suspect we may be seeing some additional undocumented interaction.