30

I was trying to rebuild my cube after making some changes the other day to the table/column names in the fact and dimension tables.

The process cube action kept returning the following error message:

The attribute key cannot be found when processing: Table: 'MyFactTableName', Column: 'MyDimensionKey', Value: 'SomeValue'. The attribute is 'MyDimensionKey'.

The other answers to similar questions referred to attributes with duplicate values, and cases where the attribute value was null in the or missing in either the Dimension or the Fact table.

Example related posts are:

There were no duplicate values in my case and the attribute value was not missing from either the Fact or Dimension table.

Thankfully the information in this post here solved my problem.

The suggestion was to process the affected Dimension by itself, and then process the affected measure group.

Any idea why this happens, how I could reproduce this? I'd like to know so that I can avoid it next time or so that it doesn't pop up again at a bad time while deploying an upgrade to the production environment.

Thanks in advance for your help.

EDIT: I'm using SSAS-2008, and the error occurred while trying to process the cube from within BIDS-2008.

Community
  • 1
  • 1
JM Hicks
  • 1,282
  • 1
  • 11
  • 22
  • 2
    This happens because your measure group is processed before the related dimensions. SSAS comes across a foreign key that's used to relate to a cube dimension, but the key is not known to the dimension itself. – Jeroen Bolle Dec 22 '12 at 11:03
  • 2
    That makes sense. I'll try to do a test to see if I can reproduce the error now (next time I get a chance) and if so, ask you to post this as the answer. – JM Hicks Jan 05 '13 at 22:27
  • So was this the answer? – Kyle Hale Mar 05 '13 at 05:55
  • Yes, that was apparently the problem. Sorry for the late reply. Please post as answer so I can mark it. Thank you. – JM Hicks Apr 11 '13 at 20:27
  • I just learned how to use "@" replies. So, @JeroenBolle please post as answer so that it can be marked as such, thank you. – JM Hicks Feb 04 '14 at 11:02
  • @JMHicks Added as answer. – Jeroen Bolle Feb 05 '14 at 12:26
  • sometimes this problem is encountered when you put a filter on the dimension in your DSV, you need to check if you have a filter on the dimension (filter on a date, or a perimeter) if everything is in order, you have to process the dimension first – Gerari Dec 17 '21 at 08:59

5 Answers5

25

This happens because your measure group is processed before the related dimensions. SSAS comes across a foreign key that's used to relate to a cube dimension, but the key is not known to the dimension itself.

Jeroen Bolle
  • 1,836
  • 1
  • 12
  • 15
  • 1
    I read your answer, thank you for posting it, and I processed my dimensions and I still received the same error, but when I looked a second time, I discovered that there was a value that was in my Measure table, but not the Dimension table. I changed the query that created the Dimension table, made sure the missing value was there, reprocessed the Dimension table and then the Measure table and it worked. I thought I'd add this for anyone who has the same issue. – DataGirl Sep 06 '18 at 17:59
  • [This explains how to process the dimensions before the measure group.](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ac64108c-e14b-45c4-b90f-690ffe7ba759/cube-processing-process-dimension-before-measure-groups-how-to?forum=sqlanalysisservices&prof=required) Right click the Project in VS, "Process", and process the whole database - not just the cube. – Max Szczurek Dec 14 '18 at 17:30
16

Root cause of this error: There is some key is present in your Fact Table, which in turn is not present in your Dimension table (Ideally which should not be the case) Try following steps:

  1. Process your dimensions before processing Measure Groups

  2. If your "SomeValue" is 0, then try adding default (0th) row in your dimension table and in Fact table try putting ISNULL(MyDimensionKey,0)

  3. If above 2 solutions doesn't work, try this: a. Right-click on your cube db, go to "process" >> "Change Setting" b. Go to "Dimension key errors" tab c. Check "Use custom error configuration" radio button d. Check 'Ignore error count' radio button. For 'Key not found' select 'Ignore error'. Similarly for 'Null key not allowed' also selecy 'Ignore error' e. Click on OK and try processing your cube

Monica Boyal
  • 191
  • 2
  • 3
2

I had a problem with similar error, the reason was that I had changed the Table name to a view with the same name but the DSV or dimensions couldn't automatically adjust this change.

So I had to manually update the fields behind each dimensions attribute.

It can be done in each dimension attribute's properties : Source>>KeyColumns>>"Attribename">>Source>>TableID, columnID

Tahir
  • 31
  • 2
1

what come across to me is there are two records in my underlying DB, "ABC" and "abc", the Distinct clause make them as 1 ("ABC") record since my sql server is not case sensitive, but the SSAS service is case-sensitive in my case, so the attribute value "abc" can not find it's key when processing the cube. HIH

L_G
  • 209
  • 2
  • 10
0

Test this, let's start tiny like this Dimension:

Dimension

This was my solution:

enter image description here

kcho0
  • 169
  • 1
  • 1
  • 7