Hello all – I’m running into an issue that has me a little stuck and I was hoping to get your advice.I have an SSIS package which runs after my dimension / cube processing that iterates through a relational table containing MDX statements (from several key reports) and executes them to warm the cache.
This has been a very successful strategy for me until the recent addition of a MDX statement that absolutely refuses to be executed via SSIS using the ADO.NET connection type / MSOLAP.3 provider.This MDX statement will run fine in Management Studio as well as from the report.To make matters worse, if I run the MDX statement from the report or from Management Studio, the SSIS package will not fail on this particular statement.It only fails if the cache is cold:
{SQL Server Analysis Services 9.0 build 3042 (SP2)}
Error: 0xC002F210 at Run MDX Query, Execute SQL Task: Executing the query " SELECT NON EMPTY { [Measures].[Volume - Sales Forecast], [Measures].[Volume - Prior Year Actuals], [Measures].[Volume - Sales Plan], [Measures].[Estimated Sales Volume], [Measures].[Volume - Financial Forecast], [Measures].[Volume - Open Orders], [Measures].[Volume - Actuals] } ON COLUMNS, NON EMPTY { ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS * [Location].[Location Name].[Location Name].ALLMEMBERS * [Profile].[Profile].[Profile].ALLMEMBERS * [Location].[Location ID].[Location ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Closure Flash Current] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS" failed with the following error: "Errors in the back-end database access module. The data provider does not support preparing queries.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
SELECT NON EMPTY
{[Measures].[Volume - Sales Forecast], [Measures].[Volume - Prior Year Actuals],
[Measures].[Volume - Sales Plan], [Measures].[Estimated Sales Volume],
[Measures].[Volume - Financial Forecast], [Measures].[Volume - Open Orders],
[Measures].[Volume - Actuals] } ON COLUMNS,
NON EMPTY { ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS *
[Location].[Location Name].[Location Name].ALLMEMBERS *
[Profile].[Profile].[Profile].ALLMEMBERS *
[Location].[Location ID].[Location ID].ALLMEMBERS ) } ON ROWS
FROM [Closure Flash Current]
I’m sure I’m missing something obvious, but whatever it may be is successfully stumping me.I appreciate any help or advice you can provide!
I figured it out; thought I would share it with all in-case you run across a similar scenario (I know when I was searching for this problem I found very little out there in the way of help):
When I ran profiler against the SSAS instance I noticed that it was trying to resolve the offending MDX statement into T-SQL statements (like you would expect to see in ROLAP storage) but it was attempting to PREPARE them against the SSAS instance, which of course would never work.
After some investigation I found that one of the partitions on the cube had been set to ROLAP and was causing the issue. After converting to MOLAP and deploying / processing, the issue went away and now my cache warming SSIS package is successful.
I would argue that this is a bug since the provider from SSIS is trying to prepare the T-SQL statements for a ROLAP cube against SSAS, but the same behavior isn't experienced in SSMS / SSRS.
