none
The attribute key cannot be found when processing: Table: 'FactTable', Column: 'CustNo', Value: '10016'. RRS feed

  • Question

  • I am new for cube.

    I got the problem when I process cube, the error message like below:

             The attribute key cannot be found when processing: Table: 'FactTable', Column: 'CustNo', Value: '10016'. 

    I process dimension first, after done I precess cube.

    I check the data, I find some CustNo(s) in Fact table, but not in dim table. if I cannot change the data in both tables.

    how to fix the problem or any advice?

    thanks for your help.

    Thursday, November 20, 2014 6:26 PM

Answers

All replies

  • Hi,

    I am not sure whether this helps you or not but give it a try. Take a look into the following screenshot.

    Once you open the cube go to the ErrorConfiguration section. By default it is set to (Default) option and you have to change this to custom. Then play with the settings highlighted in blue box.

    Refer to the following MSDN link for further understanding about the error processing.

    http://msdn.microsoft.com/en-us/library/ms180058.aspx 

    What I do not understand in your message is "I find some CustNo(s) in Fact table, but not in dim table" section. If "Enforce Foreign Key Constraint" is set to Yes on the Fact Table how were you able to insert data into fact table which does not have a matching key in the dim table?

    Best regards...


    Chandima Lakmal Fonseka


    Thursday, November 20, 2014 6:52 PM
  • thanks for your reply, Lakmal.

    actually, the fact and dim tables come from views. this is why there are 1000 CustNo(s) in fact table, and 900 CustNo(s) in dim table. and my boss doesn't let me change the query for the view. I try your way and not lucky.

    something like:

    fact table includes invoice details for whole year. and dim table has invoice for half year. this is why custNo in fact table, but not in dim table.

    any idea about this case?

    Thursday, November 20, 2014 9:07 PM
  • Hi,

    I actually simulated your issue in my development environment. Take a look into the following screen.

    Note that in Fact Table I have “Ter_Id” field which is the link with the dimension table and I have the Ter_ID as 78 in fact table which I do not have a matching key on Territory dimension. Now with the default processing settings I get the same error as you got. I changed the processing settings as in the following screen and I was able to process and browse the cube without the non-matching data.

    Give it a try...

    Best regards...


    Chandima Lakmal Fonseka

    Friday, November 21, 2014 2:24 AM
  • Hi,

      Bypassing the key errors while processing the cube is the only option available..Please check the screenshot attached

    PPlease notice the Key error action property. You can change it to Discard Record to discard the record altogehter while processing....


    Venkata Koppula

    Friday, November 21, 2014 6:04 AM
  • Hi Albert,

    As you know, this error means that the fact table named "FactTable" contains CustNo column with value "10016" but the same value is not present in your dimension table. There is aprimary key - foreign key relationship exist between the CustNo column of dimension table and fact table named "FactTable" and cube is unable to find the value 10016 in the dimension table.

    So in your scenario, you can modify the query for FactTable in the data source view.
    SELECT * FROM ViewName WHERE CustNo IN (SELECT DISTINCT (CustNo) FROM DimensionTable)

    Reference
    http://msdn.microsoft.com/en-IN/library/ms175683.aspx

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Lakmal_Fonseka Friday, November 21, 2014 3:45 PM
    Friday, November 21, 2014 9:25 AM
    Moderator
  • I did before like you said. but the data will be missing. but I think it should be ok.
    • Edited by AlbertLin Friday, November 21, 2014 5:58 PM
    Friday, November 21, 2014 5:55 PM
  • thanks. the result is same with Chandima said, but I think it is better.

    one more question:

     if I can find more than one rows from Dim table based on CustNo in Fact table, it makes sense?

    Fact:

    CustNo      Value

    10016        5

    Dim:

    CustNo      Status

    10016         Active

    10016         Pending

    I think this is a problem too.

    Thanks Charlie and Chandima


    • Edited by AlbertLin Friday, November 21, 2014 6:15 PM
    Friday, November 21, 2014 6:01 PM
  • thanks. the result is same with Chandima said, but I think it is better.

    one more question:

     if I can find more than one rows from Dim table based on CustNo in Fact table, it makes sense?

    Fact:

    CustNo      Value

    10016        5

    Dim:

    CustNo      Status

    10016         Active

    10016         Pending

    I think this is a problem too.

    Thanks Charlie and Chandima


    Hi,

    If you are looking for details about the Slowly Changing Dimension I am not the one to speak about that because I do not have any exposure on that. May be Charlie or other Pro can answer to this. In the meantime you may take a look on how to implement Type 2 SCD (Slowly Changing Dimension) in the following link.

    http://datawarehouse4u.info/SCD-Slowly-Changing-Dimensions.html 

    Best regards...


    Chandima Lakmal Fonseka


    • Edited by Lakmal_Fonseka Sunday, November 23, 2014 5:03 PM Type 2 SCD link added.
    Friday, November 21, 2014 8:03 PM
  • Thanks, the cube is ready. the problem is the view's logic is not correct.
    Tuesday, November 25, 2014 8:14 PM