I am trying to execute a query against Analysis Services from within Query
Analyzer (at this point). Here is the query:
With Member Measures.PeerGroup As
'LowNall2CustDim.currentmember.parent.parent.uniqu ename'
select { Measures.[PeerGroup], Measures.[baseamt], Measures.[Count]} on
columns,
{crossjoin(LowNall2CustDim.[Id].members, [RecvPay].[RecvPay].members)}
Dimension PROPERTIES [Id].Name,
[RecvPay].[recvpay].Name on rows
from LowNall2 where ([bookdate].&[2006].&[1].&[3])

I get the following error:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'MSOLAP'.
[OLE/DB provider returned message: The operation requested failed due to
timeout]
OLE DB error trace [OLE/DB Provider 'MSOLAP' ICommandPrepare::Prepare
returned 0x80040e14].

When trying to execute this query from the MDX application I get the
following:
Unable to display opened cellset
Unable to Allocate Memory For FlexGrid

This only happens for a few cubes. Other virtually identical cubes return
data just fine.

Anyone has any idea what's going on?

Unfortunately I think you have hit a bug in AS 2005. I have details of
it on my blog here
http://geekswithblogs.net/darrengosb.../14/65848.aspx

From what I understand this is meant to have been fixed in SP1, but I
have no idea when this is due. I would guess that it would still be at
least a few months away.

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

In article <#H0UByUSGHA.4452 (AT) TK2MSFTNGP12 (DOT) phx.gbl>,
bzakharin (AT) primeassociates (DOT) com says...

I am actualy using AS 2000 (8.4.194). Is the bug present in this version as
well? If so, what would be a workaround?

"Darren Gosbell" <jam (AT) newsgroups (DOT) nospam> wrote

No the same bug does not exist in AS 2000. Sorry about that I should
have read to the end of the post. I saw the "error on prepare" from a
linked server and the error I had seen on AS 2005 came to mind.

What are the sizes of the two dimensions that you are crossjoining? It
looks like your query must be returning a resultset with a lot of rows.

I am pretty sure that you only get "Unable to Allocate Memory For
FlexGrid" when an enormous resultset comes back which exceeds the limits
of the grid control in the MDX sample. This means you might have in
excess of 64,000 rows.

Have you tried putting a NON EMPTY clause in front of your rows

eg.

'LowNall2CustDim.currentmember.parent.parent.uniqu ename'
select { Measures.[PeerGroup], Measures.[baseamt], Measures.[Count]} on
columns,
NON EMPTY {crossjoin(LowNall2CustDim.[Id].members, [RecvPay].
[RecvPay].members)}
Dimension PROPERTIES [Id].Name,
[RecvPay].[recvpay].Name on rows
from LowNall2 where ([bookdate].&[2006].&[1].&[3])

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

In article <#M6M1gCTGHA.2156 (AT) tk2msftngp13 (DOT) phx.gbl>,
bzakharin (AT) primeassociates (DOT) com says...

The cubes this error occurs for are 1.08 MB and 13.2 MB. They each use three
dimensions. The *CustDim can have variable number of members and, I suspect,
is the largest. The other 2 are 1680 and 2 members each.

"Darren Gosbell" <jam (AT) newsgroups (DOT) nospam> wrote

Did the NON EMPTY clause help? Without it the query could return a lot
of rows. Even if your customer dimension varied between 2,000 and 5,000
(assuming it is the largest dimension) this means there could be between
3.3 and 8.4 Million rows in your query if you are crossjoining at the
lowest level (if you do not exclude empty tuples)

Have you setup any a query timeout in the server options of the linked
server? It defaults to 0 (which means an infinite timeout)

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

In article <Oo#or5bTGHA.4340 (AT) TK2MSFTNGP10 (DOT) phx.gbl>,
bzakharin (AT) primeassociates (DOT) com says...

NON EMPTY didn't help. Which timeout are you talking about?
sp_addlinkedserver does not have a timeout option.

"Darren Gosbell" <jam (AT) newsgroups (DOT) nospam> wrote

Since there is a calculated measure included in the query, the NON EMPTY
clause may not be effective unless the NonEmptyBehavior Property can be
defined for this measure, for example:

'LowNall2CustDim.currentmember.parent.parent.uniqu ename',
NON_EMPTY_BEHAVIOR = '[Measures].[Count]'

select { Measures.[PeerGroup], Measures.[baseamt], Measures.[Count]} on
columns,
NON EMPTY {crossjoin(LowNall2CustDim.[Id].members, [RecvPay].
[RecvPay].members)}
Dimension PROPERTIES [Id].Name,
[RecvPay].[recvpay].Name on rows
from LowNall2 where ([bookdate].&[2006].&[1].&[3])
http://support.microsoft.com/default...b;en-us;304137
Keyword

Article ID : 304137
Last Review : July 15, 2004
Revision : 1.1
This article was previously published under Q304137

SUMMARY
In some cases, a query slows down considerably when you use both the NON
EMPTY keyword on an axis of a Multidimensional Expression together with
a calculated member.

This article describes how you can optimize a query, by using the Non
Empty Behavior property for the calculated member.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

No, you don't get to set these options from sp_addlinkedserver, so if
you added the linked server this way you probably have the timeouts set
to their default of 0 (which means no timeout).

You can find these settings if you right click on the linked server in
Enterprise Manager, go into it's properties and have a look in the
Server Options tab.

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

In article <OWrVowrTGHA.1708 (AT) TK2MSFTNGP14 (DOT) phx.gbl>,
bzakharin (AT) primeassociates (DOT) com says...

But if the default is zero, there should be no problems by default, right?

"Darren Gosbell" <jam (AT) newsgroups (DOT) nospam> wrote