Problems processing a measure group in SSAS 2012

Question

Problems processing a measure group in SSAS 2012

Alan Burstein

SSC Guru

Points: 61136
More actions
April 22, 2016 at 11:56 am

#150977

I've run into something I haven't seen before. I'm working on an SSAS 2012 instance adding a new measure group to my cube. The source table is a bridge table with two columns. When I attempt to process the measure I get this error:
"Errors in the back-end database access module. OLE DB was unable to convert a value to the data type requested for column '1'. Errors in the OLAP storage engine: An error occurred while processing the <my new bridge table/fact> partition of the <my new bridge table/fact> measure group for the <my cube> cube from the <my ssas db> database."
The query that's failing is:
SELECT
1 AS [XXX_0_0],
[col1] AS [XXX_0_1],
[col2] AS [XXX_0_2]
FROM <my sql table>
I can copy/paste the failing query into SSMS, run it and it's fine.
The first column, which gets the name ending with _0_0 is a derived column created by SSAS and represents the partition number (there's only one partition so all the values are 1. It's an int)
The second column is the one I believe is causing the error. It's nvarchar(40) in the SQL table and system.string (Length = 40) in the DSV. The third is an int in the SQL table and system.int and the DSV.
My understanding is that, by column '1' they're referring to the column that ends with _0_1 (the second column in my example above).
Any suggestions on how to troubleshoot this would be great. Thanks!
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."
-- Itzik Ben-Gan 2001

Viewing 4 posts - 1 through 3 (of 3 total)

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

PB_BI SSCoach Points: 17461 More actions · Answer 1

Hi Alan,

Usually an error like this is due to a change in the underlying structure not being propagated through the entire cube design. For example if you have a measure group/dimension already in place and you change a data type in SQL even if you refresh the DSV (have you done that by the way?) then the dimension attributes/measures will have the old data type and it will fail processing.

So firstly, if you haven't already done this, refresh the DSV and make a note of any changes it flags up. Then check your dimension attributes/measures to make sure that they have the expected data type. If they do not then change them and process again.

I'm on LinkedIn

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

Thanks PB_BI. After a little more analysis I discovered that it was in fact an issue related to stale metadata which was resolved by restarted the SSAS service.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

PB_BI SSCoach Points: 17461 More actions · Answer 3

Just to add something else.....If you have BIDS Helper installed (https://bidshelper.codeplex.com/) then if a data type/size changes in the source, after refreshing the DSV you can use the Dimension Data Type Discrepancy Check (https://bidshelper.codeplex.com/wikipage?title=Dimension%20Data%20Type%20Discrepancy%20Check&referringTitle=Documentation) to quickly update dimensional metadata so that it's in line with the source. Saves having to go into each attribute and manually change them.

I'm on LinkedIn