When I run the following MDX Query in SSMS towards an SSAS 2005 cube, I get
correct results:
with set SelectedStores as {Stores.Stores.members}
select
{[Measures].[Units]} on columns,
nonemptycrossjoin ({Stores.[Store Types].members}, SelectedStores) on rows
from [smCube]

However if I run the same query from SQL Server (relational) using
OPENQUERY, I get an error "OLE DB provider "MSOLAP" for linked server "olap"
returned message "Query (5, 56) The dimension '[SelectedStores]' was not
found in the cube when the string, [SelectedStores], was parsed."."
select *
from openquery(olap,'
with set SelectedStores as {Stores.Stores.members}
select
{[Measures].[Units]} on columns,
nonemptycrossjoin ({Stores.[Store Types].members}, SelectedStores) on rows
from [smCube]
')

What am I doing wrong?

When I run the same query without the named set, I also get correct results:
select *
from openquery(olap,'
select
{[Measures].[Units]} on columns,
nonemptycrossjoin ({Stores.[Store Types].members},
{Stores.Stores.members}) on rows
from [smPuma6]
')

Hello Pat,

You may want to add ' before { to test:

with set SelectedStores as '{Stores.Stores.members}'

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===


This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------

Yes, I had tried that. The MDX version still works once you add the quotes,
and the OPENQUERY version still doesn't work with the quotes.



"Peter Yang [MSFT]" wrote:

I did a further test (this time with the quotes) with OPENQUERY. I get the
following error message:

OLE DB provider "MSOLAP" for linked server "olap" returned message "Query
(5, 56) The dimension '[SelectedStores]' was not found in the cube when the
string, [SelectedStores], was parsed.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "
with set SelectedStores as '{Stores.Stores.members}'
select
{[Measures].[Units]} on columns,
nonemptycrossjoin ({Stores.[Store Types].members}, SelectedStores) on rows
from [smCube]
" for execution against OLE DB provider "MSOLAP" for linked server "olap".

I proceeded to copy the query from the above error message into an MDX
window of SSMS, and the query ran fine.

Could it have to do with the settings of the linked server (the first
parameter of the OPENQUERY function) ?


"Peter Yang [MSFT]" wrote:

Are you using AS 2005? This sounds like a bug that was first mentioned
in this group here.

http://groups.google.com/group/micro...olap/browse_th
read/thread/6f231baedbd9dbbd/e4470df58d3af6ec

I also posted the details of this issue on my blog.

http://geekswithblogs.net/darrengosb.../14/65848.aspx

This is apparently fixed in the SP1 builds, but I have not heard
anything else about this issue. I don't even know if MS has a hot fix
available.

Is restructuring the query to move the set definition inline in the
query an option for you?

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

In article <557937A8-4173-430A-A64A-2C40C1A06986 (AT) microsoft (DOT) com>,
pat (AT) online (DOT) nospam says...

Thanks Darren, definitely looks like it. I had done a search in this
newsgroup but couldn't find similar issues.
I also agree with the suggestions at the end of your blog.
I use the OPENQUERY because some of the info is in the relational DB only,
not available in SSAS. I'd like to investigate your suggestions further
though (.Net Functions and “Stored Procedures” within Analysis Services), do
you have links about the subject that you would recommend?

"Darren Gosbell" wrote:

Sorry, but I don't have that much information on these yet.

Mosha has a good article on creating an AS2005 Stored Proc
http://sqljunkies.com/WebLog/mosha/a...3/31/9920.aspx

I have also seen some good reviews on Teo Lachev's book "Applied
Microsoft Analysis Services 2005" and am waiting on Amazon to delivery
my copy.
http://www.amazon.com/gp/product/097...39467-9999018?
v=glance&n=283155

Books Online is not a bad source of information either
http://msdn2.microsoft.com/en-us/library/ms176113.aspx

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

In article <FDF78F23-8646-4760-BC9C-AB71EA53CCAA (AT) microsoft (DOT) com>,
pat (AT) online (DOT) nospam says...