OLAP Linked Server Problem

RealTime
RealTime used Ask the Experts™
on
Hi, I am trying to execute an MDX query against a SQL 2008 cube, by running a passthrough query using OPENQUERY function.

The query executes fine when running it in a MDX query window

SELECT {[Measures].[PersonCount]} on COLUMNS,{[DimDate].[CalendarYear].Members} on ROWS FROM [OLAPDemo]

But when running it against the linked server via OPENQUERY i receive the following error

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP.4" for linked server "LINKED_OLAP" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSOLAP.4" for linked server "LINKED_OLAP".


Here is the code i used to set up the linked server, and the actual passthrough query

EXEC sp_addlinkedserver
@server='LINKED_OLAP', -- local SQL name given to the linked server
@srvproduct='', -- not used
@provider='MSOLAP.4', -- OLE DB provider (the .4 means the SQL2K8 version)
@datasrc='localhost', -- analysis server name (machine name)
@catalog='OLAPDemo' -- default catalog/database

GO

SELECT *
FROM OpenQuery(LINKED_OLAP,'SELECT {[Measures].[PersonCount]} on COLUMNS,{[DimDate].[CalendarYear].Members} on ROWS FROM [OLAPDemo]')

Any help much appreciated
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
OS is Windows Vista Home Edition

Commented:
It is an alchemy. Try different combination of @provider, impersonification and server name - not sure about the localhost.
Also look at the analysis services profiler - you should see some authorisation requests e.g. for an anonymous user.
Commented:
This is now solved. I changed provider from MSOLAP.4 to MSOLAP and also disabled then re-enabled the Allow Inprocess setting of MSOLAP linked server provider.

Commented:
Thanks for info.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial