Connection Error when trying to run MDX-queries

To learn MDX (on SQL Server 2005 dev edition) I decided to put up some sample cubes and run MDX-queries from SQL Server Management Studio. When processing the cube in BI Studio I get the following error:

"The datasource , 'Adventure Works DW', contains an ImpersonationMode that that is not supported for processing operations."

Fair enough, then I change the impersonationmode in the datasource to service account. It all processes/deploys well and I can browse the cube in BI Studio. Off to Management studio to run some mdx-queries. Here however I get the error:

"Error (Data mining): Either the user, ######, does not have permission to access the referenced mining model, Analysis Services Tutorial, or the object does not exist."

That is of course due to me using the current user credentials for the query. So I try to change the current connection in management studio but the helpbutton informs me that as of now only windowsauthentication can be used for analysis services. So the only connectionway I can use from here is the only one not supported when processing?! This must mean I've done some childishly simple newbiemistake and I would be very grateful if you pointed it out so I could get started with the MDX.

Perhaps I should add that the user ##### is administrator for the entire server. The server administrator role should take precedence over database roles, right (not that it helped when I added one of those and included me)? The user is also a member of the SQLServer2005OLAPUser group. I also have full access to the underlying relational database. So I can delete or create elements or entire databases at will but not ask simple questions to them...

The other option - that the cube doesn't exist seems dispelled by being able to browse it and the dimensions etc. showing up in object explorers and in the left pane of the MDX Query interface.

Which leaves me at some permission problem again - some help would REALLY be appreciated.

|||Please provide more information as something strange is going on here - given what you have provided, you should not be having a permission error - but, the error message does not make sense as the Analysis Services Tutorial has no data mining component.|||

I don't have the queries I tested with anymore since I got it to work but the bottomline was that I didn't have any "ON ROW|COLUMNS" etc. part of the selection clause which obviously sent the engine looking for something that didn't exist. I.e. just had queries with variations on the form:

SELECT dim

FROM cube

Looked at what the browser sent with the profiler and changed to:

SELECT dim ON 0

FROM cube

and everything has been working well since then. Perhaps a bit strange errormessage but otherwise my mistake. Sorry for taking up your time with this one

|||

I am new to Analysis services. I migared on of the Sql 2000 cube to sql 2005 .I am getting the same message , when i use Pivot table to dipaly data from sql 2005 Cube.With sql 2000, it works great.Here is the eror message.

Error (Data mining): Either the user, Domain/User doen not have permission to access the referenced mining model, SalesHistoryData , or the object does not exist. Error (Data mining): Either the user, DOMAIN\USER , does not have permission to access the referenced mining model, SalesHistoryData , or the object does not exist.

When i ran Profiler , it is trying to run the following sql statement.

SELECT * FROM "SalesHistoryData "

I have granted all high level rights.Still not working.

Expecting fix from someone.

|||

hi, i recv the same while trying to query the cube from SSMS...

Error (Data mining): Either the user, domain\account, does not have permission to access the referenced mining model, MaxMinSalesDM, or the object does not exist.

no error building or deploying the cube..

can see the cube in the database folder as well

please help...

|||

sorry, my mistake...

it should be select from [Cube Name], not select * from [Cube Name]

|||

My issue solved.I just had to change the Data Source Provider to msolap.2 to msolap.3 in the Pivot table properties.

You may also have to try with just msolap(No vesion) in case the above change doesn't fix it.