18 January 2012

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

The following error message had appeared while I was trying to create the "bridge table" that appeared so nicely in the Many to Many examples by Marco Russo & Alberto Ferrari:
Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: '
', Column: '', Value: ''. The attribute is ''. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Client Group of Dimension:
from Database: , Cube: TestMtoM, Measure Group: Bridge Client Adviser A, Partition: v Bridge Client Adviser A, Record: 97.


The view:

CREATE VIEW [dbo].[vBridge_ClientAdviserA]
AS

SELECT 
     
    distinct

      [ClientGroup]

      ,[dimAdviserId]

  FROM [dbo].dimClient



As I've explained in my previous post I needed to create those "bridge tables" with the ClientGroup (that's the unique ID or "natural key" of the Client that was necessary for finding out the changes in the SCD) and the Adviser Id. The problem was with the linkage between the dimension and the measure group: SSAS must have the linkage (foreign key - primary key relationship) set up. If there's a violation then it stops and gives you this error.
Of course, I set up keys for indexing and that should have done that trick, correct? The problem was that I've created here a view for the bridging based on the source (dimension) table, which means that errors that would have otherwise been raised hadn't.
So where did the problem lie? In the simple fact that in my factless fact table (which is another view that refers to dimClient) there had been some "client group" missing. That's due to the fact that actually, though I was sure I had data integrity, it's been missing for some of the clients. I was sure that a certain structure of the data existed (that's what I was specifically told!) and I didn't bother to set up keys/checks/conditions to check it.
So I had to check it up (I did that using LEFT JOIN on the factless fact table, found out that some (it's enough to have one, but I believe there had been several cases) weren't strictly adhering to the business roles that were set up so carefully by my Business Analyst. As he kindly told me to "ignore it", I had, and created the bridge table as follows:

SELECT 
     
    distinct

      [ClientGroup]

      ,[dimAdviserId]

  FROM [dbo].factClient


 Please keep in mind that "fact Client" is a view that is based on dim Client, and picks up all the clients with a certain condition (or WHERE clause) and cross join them with the dimDate table.

Conclusion:
The Moral of the story is obviously: don't ever assume data integrity! Always check it manually either by a check constraint, a key, or a simple check. If you don't then SSAS would do it for you - and you know how descriptive those error messages are!

No comments:

Post a Comment