June 26, 2017 at 3:51 pm
Hi,
I have a cube that consists of five tables, one fact table and four dimensions.
Fact Table:
factSales
Dimensions:
dimCustomer
dimPerson
dimCalendar
dimAccess
So at the moment I have a SQL Job that runs a SSIS package which will first Process the Dimensions (Customer, Calendar, Person) the Type for these are "Dimensions" and the Process Options for these are "Process Full"
This "Analysis Services Process Task" runs and after this finishes it will Process another "Analysis Services Process Task" which does the following Object Names:
Uncalculated
Factless Cp Cube Access
Both of these have a Type of "Measure Group" and the Process Options is "Process Full".
It then Processes a object called "Sales Profitability" the Type is "Cube" and the Process Options is "Process Full".
Originally the above worked but then I made a Update to a couple of fields on the table "dimCustomer" as per below:
So custId "5685" had the below values for the fields "SalesGroupList" and "VendId"
SalesGroupList = "System Overload - 1"
VendId = 83
Now showing as
SalesGroupList = "SO8"
VendId = 222
I am now getting the following message when running the SSIS package and unsure what to do, as I have limited experience with SSAS:
SSIS package "ProcessSalesCube.dtsx" starting.
Error: 0xC1000007 at Processing Cube, Analysis Services Execute DDL Task: Internal error: The operation terminated unsuccessfully.
Warning: 0x811F0001 at Processing Cube, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_factSales', Column: 'custId', Value: '5685'. The attribute is 'Dim Customer'.
Warning: 0x811F0003 at Processing Cube, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Dim Customer of Dimension: Customer from Database: ssasSales, Cube: Sales Profitability, Measure Group: Uncalculated, Partition: Fact Sales, Record: 4803.
Error: 0xC11F0006 at Processing Cube, Analysis Services Execute DDL Task: 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.
Error: 0xC11F000E at Processing Cube, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'Fact Cp Cust Prof' partition of the 'Uncalculated' measure group for the 'Customer Profitability' cube from the ssasSales database.
Error: 0xC11F0006 at Processing Cube, Analysis Services Execute DDL Task: 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.
Error: 0xC11C0002 at Processing Cube, Analysis Services Execute DDL Task: Server: The operation has been cancelled.
Task failed: Processing Cube
Warning: 0x80019002 at ProcessSales: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "ProcessSalesCube.dtsx" finished: Failure.
June 27, 2017 at 2:34 pm
Did you reprocess the dimensions after making the data change but before rerunning the cube processing?
June 27, 2017 at 3:11 pm
Yes the first "Analysis Services Process Task" Processes the Dimensions, after that task has completed the second "Analysis Services Process Task" will process the Cube
June 27, 2017 at 5:52 pm
From the looks of the error message there's a record in Fact Sales that has a value of '5685' in the CustID field and it can't find a record in Dim Customer with that value. Looks like you validated that record does exist in Dim Customer. Does SSAS access your database directly or is it done via views? In either case, could there be some sort of filter that removes that record from the data SSAS grabs for processing (possibly because of one of the changed values)?
When I've seen this type of error it is because the value was missing from my dimension. I'd fix it and then have to reprocess the dimensions and then the cube.
Unfortunately, sometimes SSAS error messages don't seem to show everything.. You may need to set up or check processing logs.
https://www.mssqltips.com/sqlservertip/2994/configuring-the-analysis-services-processing-task-in-sql-server-2012-integration-services/
https://docs.microsoft.com/en-us/sql/analysis-services/instances/log-operations-in-analysis-services
I'm a little rusty with SSAS and forget how much detail for processing tasks end up in the SSISDB log tables (that may be another place to check).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply