posted 6/30/2010 by MikeMollenhour - Views: [4661]
Recently while processing a cube I ran into an error that like many other errors in Analysis Services seemed somewhat cryptic. However after analyzing the error in question I found it to be much more descriptive than I had thought. So I would like to share how I was able to determine what the error meant. First I needed to re-create the error, to do this I edited my partition source and set the order date key to equal a constant of three question marks. The reason this will error is due to the fact that my measure for this order date key is set up as a date data type. Following is the query that I used and commented out.
When I try to process the cube here is the error I get:
Errors in the back-end database access module. OLE DB was unable to convert a value to the data type requested for column 14. Errors in the OLAP storage engine: An error occurred while processing the 'Internet_Sales_2001' partition of the 'Internet Sales' measure group for the 'Step-By-Step' cube from the MDX Step-by-Step database.
At first glance this appears to be a fairly well defined error. It is instructing me that column 14 has an invalid data type. As I research further I tried to determine what column 14 meant to me. I checked the table behind the scenes and column 14 of the table seemed to have no bearing on the error. So after looking further I pulled up the SQL query that was being run in this section as shown below.
SELECT [dbo_FactInternetSales].[dbo_FactInternetSalesSalesAmount0_0] AS [dbo_FactInternetSalesSalesAmount0_0],[dbo_FactInternetSales].[dbo_FactInternetSalesOrderQuantity0_1] AS [dbo_FactInternetSalesOrderQuantity0_1],[dbo_FactInternetSales].[dbo_FactInternetSalesExtendedAmount0_2] AS [dbo_FactInternetSalesExtendedAmount0_2],[dbo_FactInternetSales].[dbo_FactInternetSalesTaxAmt0_3] AS [dbo_FactInternetSalesTaxAmt0_3],[dbo_FactInternetSales].[dbo_FactInternetSalesFreight0_4] AS [dbo_FactInternetSalesFreight0_4],[dbo_FactInternetSales].[dbo_FactInternetSalesUnitPrice0_5] AS [dbo_FactInternetSalesUnitPrice0_5],[dbo_FactInternetSales].[dbo_FactInternetSalesTotalProductCost0_6] AS [dbo_FactInternetSalesTotalProductCost0_6],[dbo_FactInternetSales].[dbo_FactInternetSalesProductStandardCost0_7] AS [dbo_FactInternetSalesProductStandardCost0_7],[dbo_FactInternetSales].[dbo_FactInternetSales0_8] AS [dbo_FactInternetSales0_8],[dbo_FactInternetSales].[dbo_FactInternetSalesPromotionKey0_9] AS [dbo_FactInternetSalesPromotionKey0_9],[dbo_FactInternetSales].[dbo_FactInternetSalesSalesTerritoryKey0_10] AS [dbo_FactInternetSalesSalesTerritoryKey0_10],[dbo_FactInternetSales].[dbo_FactInternetSalesProductKey0_11] AS [dbo_FactInternetSalesProductKey0_11],[dbo_FactInternetSales].[dbo_FactInternetSalesCustomerKey0_12] AS [dbo_FactInternetSalesCustomerKey0_12],[dbo_FactInternetSales].[dbo_FactInternetSalesCurrencyKey0_13] AS [dbo_FactInternetSalesCurrencyKey0_13],[dbo_FactInternetSales].[dbo_FactInternetSalesOrderDateKey0_14] AS [dbo_FactInternetSalesOrderDateKey0_14],[dbo_FactInternetSales].[dbo_FactInternetSalesShipDateKey0_15] AS [dbo_FactInternetSalesShipDateKey0_15],[dbo_FactInternetSales].[dbo_FactInternetSalesDueDateKey0_16] AS [dbo_FactInternetSalesDueDateKey0_16]
As you may notice in the select statement above I have a column with a suffix of 14 and the lights came on this is actually the column 14 that they are referring to. I hope this helps others to not have to go through the troubleshooting I went through.