Fact Table Not Processing

Question

Post reply

Fact Table Not Processing

robshobs

SSC Eights!

Points: 961
More actions
November 8, 2012 at 2:25 pm

#150121

I really could use some help figuring this one out. We started receiving the following error today.
Errors in the back-end database access module.
OLE DB reported an overflow of the data type for column 4.
Errors in the OLAP storage engine: An error occurred while processing the 'Fact Service Request' partition of the 'Service Request' measure group for the 'Service Request' cube from the Service Request database.
I tracked down "column 4" by reviewing the SQL:
[dbo_Fact_Service_Request].dbo_Fact_Service_RequestSR_REC_REST090_4] AS [dbo_Fact_Service_RequestSR_REC_REST090_4]
I've done the following investigation:
I executed the full SQL from the fact processing. It ran without errors.
SR_REC_REST09 is defined in the database as numeric(38,0). We use it to store durations in seconds which can get quite large.
I did a select distinct on the field SR_REC_REST09 and found values from 0 to 8,452,743,600, plus NULL, all of which are valid values for numeric(38,0).

I do not see how the value 8,452,743,600 is causing a data overflow condition.

I would appreciate thoughts on what may be the cause and how to troubleshoot it. I am currently at a loss.

Rob

hrvoje.piasevoli Ten Centuries Points: 1377 More actions · Answer 1

Hi Rob,

That number converted to something understandable is roughly 270 years!? For a service request? 🙂

The easiest way to see if your processing is failing because of this number would be to create a new partition in SSMS, change to query binding, and use a query that selects just this single fact row.

What is set as the datatype for that measure in SSAS?

Hrvoje Piasevoli

robshobs SSC Eights! Points: 961 More actions · Answer 2

hrvoje.piasevoli (11/8/2012)
Hi Rob,
That number converted to something understandable is roughly 270 years!? For a service request? 🙂
The easiest way to see if your processing is failing because of this number would be to create a new partition in SSMS, change to query binding, and use a query that selects just this single fact row.
What is set as the datatype for that measure in SSAS?
Hrvoje Piasevoli

Hrvoje,

I did some digging last night and found the errent record. Someone had entered in 3012 for the year. Strangely enough that field is supposedly system set and uneditable. The application people are looking into that problem.

Regardless, I was thinking that the cube data type would support a number that size. In the DSV, the DataType field shows System.Decimal with a Length of -1. Each of those are grayed out and uneditable.

In the cube, the DataType is BigInt.

I've not had good luck finding documentation on data types for SSAS. However, SQL Server's BigInt datatype supports 9,223,372,036,854,775,807. That is far larger than my value.

Rob