Prevent Self-Abuse: Process The entire Analysis Project!
So I'm working on a small SQL Server 2005 Analysis Services Project in Visual Studio 2005. Anyone who's ever used this tool knows that its less than forgiving if you have foreign key issues. Namely, if you have a table with a foreign key , and the associated key ID doesn't exist in the other table, you'll get an error. So I have not only a data pull into a warehouse, but a second set of scripts which normalize any rouge data / orphaned IDs.
However, after I thought I fixed all the 'gotchas', I was still getting errors that said that the DB was unable to find the associated key. After much soul searching, I saw the key it was referring to was in the same table? how can a Primary key be looked at as a foreign key? Later I found that the answer was, re-process the entire solution, not just the specific cube. Turns out, if you make dimension changes and just try to process the cube, the cube won't process some of the time, depending on the change. So now, even though its more time consuming, no matter what, I reprocess my entire solution to make sure I don't run into these little inconsistencies.