none
The attribute Key Can not be found

    Question

  • Hi all,

    I am processing a cube with two fact tables, one fact table processes successfully and the other doesnot because the attribute key can not be found.

    The command error is as below;

     

    <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
      <Parallel>
        <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
          <Object>
            <DatabaseID>Analysis_Trial</DatabaseID>
            <CubeID>Patient Care</CubeID>
          </Object>
          <Type>ProcessFull</Type>
          <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
        </Process>
      </Parallel>
    </Batch>
     Processing Cube 'Care' failed.
      Start time: 7/17/2006 9:04:17 PM; End time: 7/17/2006 9:04:44 PM; Duration: 0:00:27
      Processing Measure Group 'Patient Diagnosis' completed successfully.
       Start time: 7/17/2006 9:04:17 PM; End time: 7/17/2006 9:04:38 PM; Duration: 0:00:21
       Processing Partition 'Patient Diagnosis' completed successfully. 49 rows have been read.
        Start time: 7/17/2006 9:04:17 PM; End time: 7/17/2006 9:04:38 PM; Duration: 0:00:21
        SQL queries 1
         SELECT [dbo_PatientDiagnosis].[dbo_PatientDiagnosisAge0_0] AS [dbo_PatientDiagnosisAge0_0],[dbo_PatientDiagnosis].[dbo_PatientDiagnosis0_1] AS [dbo_PatientDiagnosis0_1],[dbo_PatientDiagnosis].[dbo_PatientDiagnosisDiagnosisCode0_2] AS [dbo_PatientDiagnosisDiagnosisCode0_2]
      FROM
         (
        
        SELECT [Age] AS [dbo_PatientDiagnosisAge0_0],1   AS [dbo_PatientDiagnosis0_1],[DiagnosisCode] AS [dbo_PatientDiagnosisDiagnosisCode0_2]
      FROM [dbo].[PatientDiagnosis]
         )
         AS [dbo_PatientDiagnosis]
      Processing Measure Group 'Prescription' failed.
       Start time: 7/17/2006 9:04:17 PM; End time: 7/17/2006 9:04:44 PM; Duration: 0:00:27
       Processing Partition 'Prescription' failed.
        Start time: 7/17/2006 9:04:17 PM; End time: 7/17/2006 9:04:44 PM; Duration: 0:00:27
        SQL queries 1
         SELECT [dbo_Prescription].[dbo_PrescriptionQuantity0_0] AS [dbo_PrescriptionQuantity0_0],[dbo_Prescription].[dbo_PrescriptionPrice0_1] AS [dbo_PrescriptionPrice0_1],[dbo_Prescription].[dbo_PrescriptionDiagnosisCode0_2] AS [dbo_PrescriptionDiagnosisCode0_2],[dbo_Prescription].[dbo_PrescriptionService_Order0_3] AS [dbo_PrescriptionService_Order0_3],[dbo_Prescription].[dbo_Prescription0_4] AS [dbo_Prescription0_4],[dbo_Prescription].[dbo_PrescriptionMedicalProviderId0_5] AS [dbo_PrescriptionMedicalProviderId0_5],[dbo_Prescription].[dbo_PrescriptionServiceId0_6] AS [dbo_PrescriptionServiceId0_6],[dbo_Prescription].[dbo_PrescriptionPatientId0_7] AS [dbo_PrescriptionPatientId0_7],[dbo_Prescription].[dbo_PrescriptionDate0_8] AS [dbo_PrescriptionDate0_8],[dbo_Prescription].[dbo_PrescriptionMedicineCode0_9] AS [dbo_PrescriptionMedicineCode0_9],[dbo_Prescription].[dbo_PrescriptionAllergyCode0_10] AS [dbo_PrescriptionAllergyCode0_10],[dbo_Prescription].[dbo_PrescriptionSeverityCode0_11] AS [dbo_PrescriptionSeverityCode0_11],[dbo_Prescription].[dbo_PrescriptionProjectId0_12] AS [dbo_PrescriptionProjectId0_12],[dbo_Prescription].[dbo_PrescriptionMedicineTypeCode0_13] AS [dbo_PrescriptionMedicineTypeCode0_13]
      FROM
         (
        
        SELECT [Quantity] AS [dbo_PrescriptionQuantity0_0],[Price] AS [dbo_PrescriptionPrice0_1],[DiagnosisCode] AS [dbo_PrescriptionDiagnosisCode0_2],[Service_Order] AS [dbo_PrescriptionService_Order0_3],1   AS [dbo_Prescription0_4],[MedicalProviderId] AS [dbo_PrescriptionMedicalProviderId0_5],[ServiceId] AS [dbo_PrescriptionServiceId0_6],[PatientId] AS [dbo_PrescriptionPatientId0_7],[Date] AS [dbo_PrescriptionDate0_8],[MedicineCode] AS [dbo_PrescriptionMedicineCode0_9],[AllergyCode] AS [dbo_PrescriptionAllergyCode0_10],[SeverityCode] AS [dbo_PrescriptionSeverityCode0_11],[ProjectId] AS [dbo_PrescriptionProjectId0_12],[MedicineTypeCode] AS [dbo_PrescriptionMedicineTypeCode0_13]
      FROM [dbo].[Prescription]
         )
         AS [dbo_Prescription]
        Error Messages 2
         Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Prescription, Column: PatientId, Value: 10001; Table: dbo_Prescription, Column: Date, Value: 2/9/2000. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Initial Visit of Dimension: Initial Visit from Database: Analysis_Trial, Cube: Care, Measure Group: Prescription, Partition: Prescription, Record: 1.
         Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Prescription, Column: PatientId, Value: 10001; Table: dbo_Prescription, Column: Date, Value: 2/9/2000. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Initial Visit of Dimension: Initial Visit from Database: Analysis_Trial, Cube: Care, Measure Group: Prescription, Partition: Prescription, Record: 1. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while processing the 'Prescription' partition of the 'Prescription' measure group for the 'Care' cube from the Analysis_Trial database.
    Errors and Warnings from Response
     Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
     Errors in the OLAP storage engine: An error occurred while processing the 'Prescription' partition of the 'Prescription' measure group for the 'Care' cube from the Analysis_Trial database.
     Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
     Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Prescription, Column: PatientId, Value: 10001; Table: dbo_Prescription, Column: Date, Value: 2/9/2000.
     Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Initial Visit of Dimension: Initial Visit from Database: Analysis_Trial, Cube: Care, Measure Group: Prescription, Partition: Prescription, Record: 1.

     

    Please can you help me and hand in my project in time.

    Thanx in advance.

    Ronaldlee

    Monday, July 17, 2006 6:25 PM

Answers

  • You have a problem with refenetial integrity. Looks like problem processign partition Prescription.

    Your partition has a record with keys that are missing from dimension.

    The most important error you got is: "The attribute key cannot be found: Table: dbo_Prescription, Column: PatientId, Value: 10001; Table: dbo_Prescription, Column: Date, Value: 2/9/2000

    Edward.
    --
    This posting is provided "AS IS" with no warranties, and confers no rights.

    Monday, July 17, 2006 6:59 PM
    Owner

All replies

  • You have a problem with refenetial integrity. Looks like problem processign partition Prescription.

    Your partition has a record with keys that are missing from dimension.

    The most important error you got is: "The attribute key cannot be found: Table: dbo_Prescription, Column: PatientId, Value: 10001; Table: dbo_Prescription, Column: Date, Value: 2/9/2000

    Edward.
    --
    This posting is provided "AS IS" with no warranties, and confers no rights.

    Monday, July 17, 2006 6:59 PM
    Owner
  • Hi,

     

    Could you please throw more light about this. Is this concerned with the date 2/9/2000 within the Prescription Partition. Could you please throw more light.

    thanx in advance

    Monday, July 17, 2006 9:35 PM
  • In your time dimension and in your patient dimension you dont have members with date 2/9/2000  and patient id 10001.

    Make sure your relational database has recordes for these members in the dimension ( domain) tables. That is one.

    Second during dimension processing try and change default processing error configuration options to report on every error in your dimension structure.

    After processing dimensions you should be able to process your partiton.

    Edward.
    --
    This posting is provided "AS IS" with no warranties, and confers no rights.

    Monday, July 17, 2006 9:54 PM
    Owner
  •  

    Hi,

    i am carrying out the first step. Am I supposed to change the Error configuration property of each dimension from default to custom to report on every error within my Dimension structure.

    Please advice.

     

    Thanx

    Monday, July 17, 2006 10:19 PM
  • You can ether change the error configuration property of every dimension or change properties of the processing command.

    Edward.
    --
    This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, July 18, 2006 12:27 AM
    Owner
  • Edward,

    Should I Change this property from default to custom or it should remain like the way it is (default)?

    Thanx

    Tuesday, July 18, 2006 12:56 AM
  • Edward

    I have a similar problem (AS 2005) :

    I have a cube on a server which is processed and I can browse it. There have been errors due to missing attribute keys, but I build the cube by setting the ignore error options.

    Now I want to get a local "copy" of this, I want a cube file.

    I used the "CREATE GLOBAL CUBE" command as parameter to connection.execute many times before, which works fine for other cubes without attribute key problems.

    When I try to get a local .CUB from the server cube (that has attribute keys missing, but was processed in advance) , I get the errors
    >>>
    Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
    Errors in the OLAP storage engine: An error occurred while processing the 'Sika' partition of the 'Periodenwerte' measure group for the 'Sika' cube from the Sika database.
    Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
    <<<

    How can I "tell" my connection to ignore missing attribute keys (like in the VS IDE) ?

    Regards
    Klaus
    Friday, August 18, 2006 3:06 PM
  • You can try and change ErrorConfiguration on your dimension. But I would say it is not a best way to operate.

    Ignoring referential integrity errors means your users might not see data they are expecting. I would go tweaking ErrorConfiguration only in initial stages of your project. Once you see all the data you should go back and work through problems with the data and not force Analysis Server to ignore problems.

    Edward.
    --
    This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, August 22, 2006 6:08 PM
    Owner
  • Hi Klaus,  did you ever resolve your local cube problem?

    I'm having the same problem.

    I am aware that the preferred method is to fix the errors, however, due to a small time frame, that won't be complete before the local cube is needed.

     

    Any help would be much appreciated.

     

    Regards,

    Phil

    Thursday, January 29, 2009 4:21 PM
  • Hi Phill,
    This is because your fact table doesn't have corrospoding foreign key for your Dimension id,
    This is why you should have referential integrity(pk-fk),Now question is how to handle that ,well ,You should have
    corrosponding ID if not then your fact table have a Logic which handles the null (keys) before loading to fact table

    After Generating the Keys you have to take care of NULL Which you can do using DERIVE COLUMNS tRANSFOMATIONS
    and replace those columns which might have null values with this expression and reload your fact (Those will have -1 or -2 or 0 as a key value ) I.E.      ISNULL( [Company_ID] ) ? -1 : [Company_ID]
    YOU WILL HAVE TO RELOAD YOUR FACT TO ASSIGN THESE VALUES IN THE SSIS Package Level(if you are using ssis to load fact table)

    Now you have to Reprocess your dimensions(First) and cube,Now you should be fine

    Thanks


    Dibyant S U padhyay
    Friday, January 30, 2009 9:36 PM
  • Excellent, thanks Dibyant.  I'll give it a try.

    Tuesday, February 03, 2009 11:28 AM
  • For anyone else stumbling across this, I just spent half an hour scratching my head over this issue, because I knew that the referential integrity was intact.

    The reason for the error was because I had manually regenerated one of the dimension tables (which involved some new primary keys being created) and manually fixing up the fact table. I then naively thought that reprocessing the cube would be sufficient to sort everything out.

    Instead, I had to reprocess the dimension that I had been working on, and THEN reprocess the cube. Problem solved.

    Thanks to DSU007 for giving me the hint.

    • Proposed as answer by brianmorrison2 Wednesday, April 18, 2012 5:08 PM
    Monday, March 22, 2010 3:38 PM