MS SQL Server OLAP - Linked Server to Analysis Services 2005 gets Access denied.

Author

Linked Server to Analysis Services 2005 gets Access denied.

Renato

2005-11-17, 8:24 pm

Has anyone had any success with setting up a linked server or MSOLAP
oledb connection to SQL2005 Analysis Services??
My main issue is I am having trouble getting drill-through to work via
SQL Reporting services.
I would like to do this as a SQL query accessing AS via linked server
set up using "Microsoft OLEDB Provider for Analysis Services 9.0".

Please read on if you have had any experience with this area:....

If I run this from SQL I get the following error:

select * from openquery(wrypgpvmw0
6AS, '
SELECT
[Measures].[Sales] ON 0 FROM [Sales]
WHERE
([Date].[Year Periods].[Year].&[2005].&[2005-P02],
[Products].& #91;ProductMPGHierar
chy].[Product Origin Group].&[CV]
)')

------------------------------->
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP" for linked server "wrypgpvmw06AS" reported
an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSOLAP" for
linked server "wrypgpvmw06AS".

I am running the RTM version of 2005 Enterprise.
The query is running in a SQL window of the management studio running
directly on the server itself. I am logged under my own login, which is
a local administrator.
The server is both the SQL server and the Analysis Services server.
I am using the same domain account to run both SQL and AS and this
account is a local admin on the server.
Another thing is Analysis Services is getting and parsing the query
because if I introduce a syntax error I get the appropriate error
message.

Darren Gosbell

2005-11-17, 8:24 pm

If you are doing drilltrough in SSRS you must be linking to a separate
report. Have you looked into just getting SSRS to use SSAS as a data
source directly?

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1132273048.128531.63230@g47g2000cwa.googlegroups.com>,
renato_buda@iprimus.com.au says...
> Has anyone had any success with setting up a linked server or MSOLAP
> oledb connection to SQL2005 Analysis Services??
> My main issue is I am having trouble getting drill-through to work via
> SQL Reporting services.
> I would like to do this as a SQL query accessing AS via linked server
> set up using "Microsoft OLEDB Provider for Analysis Services 9.0".
>
> Please read on if you have had any experience with this area:....
>
> If I run this from SQL I get the following error:
>
> select * from openquery(wrypgpvmw0
6AS, '
> SELECT
> [Measures].[Sales] ON 0 FROM [Sales]
> WHERE
> ([Date].[Year Periods].[Year].&[2005].&[2005-P02],
> [Products].& #91;ProductMPGHierar
chy].[Product Origin Group].&[CV]
> )')
>
> ------------------------------->
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "MSOLAP" for linked server "wrypgpvmw06AS" reported
> an error. Access denied.
> Msg 7350, Level 16, State 2, Line 1
> Cannot get the column information from OLE DB provider "MSOLAP" for
> linked server "wrypgpvmw06AS".
>
> I am running the RTM version of 2005 Enterprise.
> The query is running in a SQL window of the management studio running
> directly on the server itself. I am logged under my own login, which is
> a local administrator.
> The server is both the SQL server and the Analysis Services server.
> I am using the same domain account to run both SQL and AS and this
> account is a local admin on the server.
> Another thing is Analysis Services is getting and parsing the query
> because if I introduce a syntax error I get the appropriate error
> message.
>
>

Renato

2005-11-18, 3:23 am

Thanks Darren.

It seems that SSRS does not understand the DRILLTHROUGH syntax of MDX
but I am just trying to use DRILLTHROUGH in the DMX pane with some
success.

I will keep this thread posted.

As an example of what I mean try something like this in SSRS:

DRILLTHROUGH
SELECT
FROM [Sales]
WHERE
([Date].[Year Periods].[Year].&[2005].&[2005-P02],
[Products].& #91;ProductMPGHierar
chy].[Product Origin Group].&[CV]
,[Measures].[Sales])

Renato

2005-11-18, 3:23 am

Thanks Darren,

I did try to run this direct in SSRS connected to Analysis Services
data source:

DRILLTHROUGH
SELECT
FROM [Sales]
WHERE
([Date].[Year Periods].[Year].&[2005].&[2005-P02],
[Products].& #91;ProductMPGHierar
chy].[Product Origin Group].&[CV]
,[Measures].[Sales])

And get:

TITLE: Microsoft Visual Studio
------------------------------
Query preparation failed.
Failed to parse the query to detect if it is MDX or DMX.
(MDXQueryGenerator)

It seems SSRS wants to parse the MDX and does not understand the
drillthrough syntax.

Darren Gosbell

2005-11-19, 3:23 am

I get the same error trying to do a drillthrough against the Adventure
Works database. I pasted a query that was working in SSMS into SSRS and
it failed with the same error. I even defining the query in an
expression in an attempt to delay the parsing, but this did not work
either. This is probably worth taking up with Microsoft.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1132280697.688049.138650@f14g2000cwb.googlegroups.com>,
renato_buda@iprimus.com.au says...
> Thanks Darren,
>
> I did try to run this direct in SSRS connected to Analysis Services
> data source:
>
> DRILLTHROUGH
> SELECT
> FROM [Sales]
> WHERE
> ([Date].[Year Periods].[Year].&[2005].&[2005-P02],
> [Products].& #91;ProductMPGHierar
chy].[Product Origin Group].&[CV]
> ,[Measures].[Sales])
>
>
> And get:
>
> TITLE: Microsoft Visual Studio
> ------------------------------
> Query preparation failed.
> Failed to parse the query to detect if it is MDX or DMX.
> (MDXQueryGenerator)
>
> It seems SSRS wants to parse the MDX and does not understand the
> drillthrough syntax.
>
>

Deepak Puri

2005-11-19, 3:23 am

How about trying the OLE DB option? I couldn't get the following AW
Drillthrough query to work with the RS 2005 Analysis Services Provider;
but it returned 8 records when I used OLE DB for OLAP 9.0 instead:
[color=darkred]
Drillthrough
Select [Ship Date].[Calendar].[Calendar Year].&[2001] on columns,
[Customer].[Customer Geography].[State-Province].&[TAS]&[AU] on rows
from [Adventure Works]
where [Measures].[Internet Order Quantity][color=dark
red]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Darren Gosbell

2005-11-20, 8:23 pm

Excellent suggestion Deepak! This works for my test example too.

This probably means that the issue is isolated to the ADO MD provider in
SSRS. As going down a layer to OLE DB works a treat.

Renato, I don't know if anyone from MS is tracking this thread so I
logged this issue in the product feedback site here:
http://lab.msdn.microsoft.com/Produ...wFeedback.aspx?
feedbackId=FDBK40868

But for the time being Deepak's workaround is an excellent way to get
the results back from a drillthrough query, and really only results in
the minor inconvenience of having to set up one extra data source.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <uBlYeRO7FHA.4012@TK2MSFTNGP14.phx.gbl>,
deepak_puri@progress
ive.com says...
> How about trying the OLE DB option? I couldn't get the following AW
> Drillthrough query to work with the RS 2005 Analysis Services Provider;
> but it returned 8 records when I used OLE DB for OLAP 9.0 instead:
>
> Drillthrough
> Select [Ship Date].[Calendar].[Calendar Year].&[2001] on columns,
> [Customer].[Customer Geography].[State-Province].&[TAS]&[AU] on rows
> from [Adventure Works]
> where [Measures].[Internet Order Quantity]
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.droptable.com ***
>

Renato

2005-11-22, 7:23 am

Thanks Darren and Deepak for your suggestions and feedback to
Microsoft.

Tht means there are 2 options that work - the OLAP 9 provider and using
and DMX type query with the RS 2005 Analysis Services Provider. And
good to know about the product feedback site - I think Ill be making
use of that.

I have been using the DMX drillthru for a few days now, and it works a
treat.
I am going to try the OLAP 9 provider to see what this does.

(by the way - a bit off topic - do either of you find that RS can
develop some inconsistencies between the parameters specified at the
report level and those specified in the dataset. This has happened to
me when writing MDX queries and changing the parameters manually (e.g.
to provide dynamic defaults and more complex sets of allowable
values).)

Darren Gosbell

2005-11-22, 8:24 pm

In article <1132664401.588073.168240@g43g2000cwa.googlegroups.com>,
renato_buda@iprimus.com.au says...
> (by the way - a bit off topic - do either of you find that RS can
> develop some inconsistencies between the parameters specified at the
> report level and those specified in the dataset. This has happened to
> me when writing MDX queries and changing the parameters manually (e.g.
> to provide dynamic defaults and more complex sets of allowable
> values).)
>

I have had instances where I was manipulating parameters in the dataset
and RS either created additional report parameters or lost some of the
links between the report and dataset parameters. This was using SQL data
sources, so I don't think this is an MDX related issue.

I just got into a habit of double checking the parameters if I did any
playing with them.

I do remember that I started noticing a pattern of what would cause
these issues, but this was over 6 months ago and I can't remember the
details anymore. What I do remember is thinking that in some
circumstances, what the report designer was doing would have made sense,
but in my case it was just annoying. :)

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

Sponsored Links