Error processing cubes

Question

Post reply

Error processing cubes

rc 77550

Say Hey Kid

Points: 709
More actions
September 7, 2015 at 4:12 am

#149012

Hello,
I have a problem processing Cubes.
They have been online for a couple of years, but recently I got the following error in Job Scheduler on the step where the cubes are being processed.
Executed as user: DIPDOMAIN\s_sql_nav_ag. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 21:55:31 Error: 2015-08-10 22:12:12.30 Code: 0xC1000007 Source: Process Cube Analysis Services Execute DDL Task Description: Internal error: The operation terminated unsuccessfully. End Error Error: 2015-08-10 22:12:12.30 Code: 0xC112001A Source: Process Cube Analysis Services Execute DDL Task Description: Errors in the high-level relational engine. The following exception occurred while the managed IDataReader interface was being used: Conversion overflows.. End Error Error: 2015-08-10 22:12:12.30 Code: 0xC11F000E Source: Process Cube Analysis Services Execute DDL Task Description: Errors in the OLAP storage engine: An error occurred while processing the 'Fact Calculation Budget' partition of the 'Calculation Budget' measure group for the 'ABIQS Opportunity' cube from the AS_Intrion database. End Error Error: 2015-08-10 22:12:12.30 Code: 0xC11F0006 Source: Process Cube Analysis Services Execute DDL Task Description: 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. End Error Error: 2015-08-10 22:12:12.30 Code: 0xC11C0002 Source: Process Cube Analysis Services Execute DDL Task Description: Server: The operation has been cancelled. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 21:55:31 Finished: 22:12:16 Elapsed: 1004.7 seconds. The package execution failed. The step failed.
When I use SQL Profiler, The SQL Query runs, but while it is reading the data, it fails after 100K rows.
Any idea's on how to solve this?
Kind regards,
Ruben

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

rc 77550 Say Hey Kid Points: 709 More actions · Answer 1

The profiler when the failing measure group processes.

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 2

seems your getting dimension processing errors , have you tried processing the cube by changes the default processing options, can you post the xmla being used to process the partition of the fact table?

Jayanth Kurup[/url]

rc 77550 Say Hey Kid Points: 709 More actions · Answer 3

I'm doing a full process of the cubes.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object>

<DatabaseID>AS_Intrion</DatabaseID>

</Object>

<Type>ProcessFull</Type>

<WriteBackTableCreation>UseExisting</WriteBackTableCreation>

</Process>

</Batch>

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">

<LocaleIdentifier>2067</LocaleIdentifier>

</PropertyList>

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 4

Have you checked the sql syntax for the partitions being processed , are they same in all aspects except the where clause ? do they overlap in datasets ?

Jayanth Kurup[/url]

Martin Schoombee SSCoach Points: 19128 More actions · Answer 5

Description: Errors in the high-level relational engine. The following exception occurred while the managed IDataReader interface was being used: Conversion overflows.. End Error

I've highlighted what I think to be the problem above...

I don't think it's a dimension error. It seems to be a data conversion/overflow error from the message returned by Analysis Services.

I'd recommend that you check that measure group, and see if there could be any overflow errors when calculating aggregates. You may have allocated a data type in Analysis Services for a measure, but that data type is no longer large enough for the data and/or aggregate values.

If all your data types check out, I'd do the following in an effort to troubleshoot:

Process each dimension individually, and see if there are any errors
Process each measure group or partition of the cube individually, and see of there are any errors

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

Process the cube with Visual Studio.

Don't look at the errors, but look for a warning message.

For some reason, SSAS hides it most useful messages in a warning instead of an error.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rc 77550 Say Hey Kid Points: 709 More actions · Answer 7

I have processed all the dimensions individually, and then each cube.

I get an error on 2 measure groups.

Same conversion overflow. error.

For the measure group which causes the problem, alle measures are doubles.

I'll try to process the cube with Visual studio and look for a warning.

Thanks for the help already.

rc 77550 Say Hey Kid Points: 709 More actions · Answer 8

I seem to get no warning messages in Visual Studio.

Only the warning that the number of errors exceeded the max number of errors.

rc 77550 Say Hey Kid Points: 709 More actions · Answer 9

xmla command to process the cube:

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<Object>

<DatabaseID>AS_Intrion</DatabaseID>

<CubeID>ABIQS Opportunity</CubeID>

</Object>

<Type>ProcessFull</Type>

<WriteBackTableCreation>UseExisting</WriteBackTableCreation>

</Process>

Return value:

<return xmlns="urn:schemas-microsoft-com:xml-analysis">

<results xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults">

<root xmlns="urn:schemas-microsoft-com:xml-analysis:empty">

<Exception xmlns="urn:schemas-microsoft-com:xml-analysis:exception" />

<Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception">

<Error ErrorCode="3238002695" Description="Internal error: The operation terminated unsuccessfully." Source="Microsoft SQL Server 2008 Analysis Services" HelpFile="" />

<Error ErrorCode="3239837698" Description="Server: The operation has been cancelled." Source="Microsoft SQL Server 2008 Analysis Services" HelpFile="" />

</Messages>

</root>

</results>

</return>

Martin Schoombee SSCoach Points: 19128 More actions · Answer 10

Still seems to be the same "Conversion Overflows" error. You either have really large numbers in your measure group, or there is some bad data that cannot be converted to the double data type.

What are these values stored as in your source database?

rc 77550 Say Hey Kid Points: 709 More actions · Answer 11

All the measures are SUM aggregates from the fact table.

These are all Decimal(38,20) fields.

There is 1 count measure.

Martin Schoombee SSCoach Points: 19128 More actions · Answer 12

rc 77550 (9/9/2015)
These are all Decimal(38,20) fields.

Herein lies your potential issue...your source data may not be compatible with the data type in your cube. Please see the following: https://technet.microsoft.com/en-us/library/gg471558(v=sql.110).aspx

rc 77550 Say Hey Kid Points: 709 More actions · Answer 13

I updated the fields used in the aggregate with convert(decimal(38,15),decimalfield) in the facttable.

Then processed the dimensions, and then the cube.

But I get the same error.

Shouldn't this fix the issue if this was the problem?

Martin Schoombee SSCoach Points: 19128 More actions · Answer 14

rc 77550 (9/10/2015)
I updated the fields used in the aggregate with convert(decimal(38,15),decimalfield) in the facttable.
Then processed the dimensions, and then the cube.
But I get the same error.
Shouldn't this fix the issue if this was the problem?

If the number of decimals were the issue, yes...you'll have to keep on digging. How many measures in that measure group, and do you have any calculated members? How large are the numbers of these measures typically?

How about removing the measures one by one and reprocessing to see which is the culprit? Or how about profiling the data to ensure that there are no bad data?