Using SQL Server Data Tools, added a new dimension to our data source. Unfortunately, for this added dimension we have yet to actually define the database foreign key constraints and so within our “Data Source View” we manually hand-wired the relationships between the foreign key table (Fact table) and the primary key (Dimension table) .
Everything went well with building the project. But, ran into error with actual deployment.
Internal error: The operation terminated unsuccessfully. 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 'FactSales' partition of the 'FactSales' measure group for the 'IISLogDW' cube from the FullData 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. Server: The current operation was cancelled because another operation in the transaction failed. Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_fact', Column: 'UserID' Value: '293379'. The attribute is 'UserID'. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute User Custno of Dimension: DimCust from Database: Sales_FullData, Cube: IISLogDW, Measure Group: Fact Sales, Partition: FactSales, Record: 15520831.
Fix – Cube Process
- Select Cube
- Right click on the cube
- And, from the drop down menu, Select the “Process” menu
- In the “Process Cube” screen, click on the “Change Settings …” button
- In the “Change Settings” window, access the “Dimension Key Errors” Tab
- The default choice is “Use default error configuration”, select the “Use custom error configuration“
- Within the “Use Custom error configuration” Tab, select the following options
- Key error action :- “Convert to Unknown”
- Processing error limit \ Ignore errors count
- Error log path :- file name for a valid on your machine
- Click the OK button to exit the “Change Settings” window
- In the Process Cube window, click the “Run” button
Upon re-run, the errors are logged as warnings, and cube rebuild completes.
The downside of this quick fix is that your changes will be lost once the current processing session is complete.
If you return to the “Process cube” by following the steps listed below:
- In the Solution Explorer, Select Cube
- Right Click on the selected Cube, and from the drop-down menu select the “Process…” button
- Click on the “Change Settings” button
- Access the “Dimension Key errors” tab
- You will notice that the “Use default error configuration” chosen is chosen and even upon clicking on the “Use custom error configuration” all previously entered choices are lost
Use Default error configuration
Fix – Cube’s Error Handling Configuration
Change Cube’s error handling configuration
- In the Solution Explorer, Select the Cube
- Double-click on the selected cube
- In the properties panel, transverse to the “ErrorConfiguration” property and you will notice that the current choice is “(default)“
- Within the”ErrorConfiguration” property choices, please choose “Custom“
ErrorConfiguration – Default Selections
Here are the default selections.
ErrorConfiguration – Suggested Changes
Here are the suggested changes:
It seems that error suppression makes sense when one is dealing with a very large databases.
You want to capture the offending errors and you also want to complete processing and simply classify the “orphaned” records as “unknown” per this specific attribute / dimension.
References – General
- Error messages when you try to process a database or a cube in SQL Server 2005 Analysis Services: “The attribute key cannot be found” and “The record was skipped because the attribute key was not found”
References – Cube
- Error Configuration in SSAS ( by James Serra )
References – Partition
- Error Configuration (Partition Properties Dialog Box) (Analysis Services – Multidimensional Data)
References – Process
- Change Settings Dialog Box (Analysis Services – Multidimensional Data)