|
Home > Archive > MS SQL Server OLAP > March 2006 > Error executing MDX query
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Error executing MDX query
|
|
| Boris Zakharin 2006-03-16, 8:24 pm |
| 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.uniquename'
select { Measures.[PeerGroup], Measures.[baseamt], Measures.[Count]} on
columns,
& #123;crossjoin(LowNa
ll2CustDim.[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::Pre
pare
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?
| |
| Darren Gosbell 2006-03-18, 7:23 am |
| Unfortunately I think you have hit a bug in AS 2005. I have details of
it on my blog here
http://geekswithblogs.net/darrengos...1/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@TK2MSFTNGP12.phx.gbl>,
bzakharin@primeassoc
iates.com says...
> 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.uniquename'
> select { Measures.[PeerGroup], Measures.[baseamt], Measures.[Count]} on
> columns,
> & #123;crossjoin(LowNa
ll2CustDim.[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::Pre
pare
> 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?
>
| |
| Boris Zakharin 2006-03-21, 3:32 am |
| 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@newsgroups.nospam> wrote in message
news:MPG. 1e86a0f0a6a1e1719898
c5@news.microsoft.com...[color=darkred]
> Unfortunately I think you have hit a bug in AS 2005. I have details of
> it on my blog here
> http://geekswithblogs.net/darrengos...1/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@TK2MSFTNGP12.phx.gbl>,
> bzakharin@primeassoc
iates.com says...
Query[color=darkred]
return[color=darkred
]
| |
| Darren Gosbell 2006-03-21, 8:32 pm |
| 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.
[color=darkred]
With Member Measures.PeerGroup As
'LowNall2CustDim.currentmember.parent.parent.uniquename'
select { Measures.[PeerGroup], Measures.[baseamt], Measures.[Count]} on
columns,
NON EMPTY & #123;crossjoin(LowNa
ll2CustDim.[Id].members, [RecvPay].
[RecvPay].members)}
Dimension PROPERTIES [Id].Name,
[RecvPay].[recvpay].Name on rows
from LowNall2 where ([bookdate].&[2006].&[1].&& #91;3])[color=darkre
d]
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <#M6M1gCTGHA.2156@tk2msftngp13.phx.gbl>,
bzakharin@primeassoc
iates.com says...[color=darkred]
> 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@newsgroups.nospam> wrote in message
> news:MPG. 1e86a0f0a6a1e1719898
c5@news.microsoft.com...
> Query
> return
| |
| Boris Zakharin 2006-03-22, 9:30 am |
| 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@newsgroups.nospam> wrote in message
news:MPG. 1e8b1a4d95762af69898
c7@news.microsoft.com...[color=darkred]
> 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.
>
> With Member Measures.PeerGroup As
> 'LowNall2CustDim.currentmember.parent.parent.uniquename'
> select { Measures.[PeerGroup], Measures.[baseamt], Measures.[Count]} on
> columns,
> NON EMPTY & #123;crossjoin(LowNa
ll2CustDim.[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@tk2msftngp13.phx.gbl>,
> bzakharin@primeassoc
iates.com says...
as[color=darkred]
on[color=darkred]
[RecvPay].[RecvPay]. members)}[color=dark
red]
DB[color=darkred]
due to[color=darkred]
ICommandPrepare::Pre
pare[color=darkred]
| |
| Darren Gosbell 2006-03-23, 7:43 am |
| 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@TK2MSFTNGP10.phx.gbl>,
bzakharin@primeassoc
iates.com says...[color=darkred]
> 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@newsgroups.nospam> wrote in message
> news:MPG. 1e8b1a4d95762af69898
c7@news.microsoft.com...
> as
| |
| Boris Zakharin 2006-03-23, 8:37 pm |
| NON EMPTY didn't help. Which timeout are you talking about?
sp_addlinkedserver does not have a timeout option.
"Darren Gosbell" <jam@newsgroups.nospam> wrote in message
news:MPG. 1e8d486b750b59559898
cd@news.microsoft.com...[color=darkred]
> 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@TK2MSFTNGP10.phx.gbl>,
> bzakharin@primeassoc
iates.com says...
three[color=darkred]
suspect,[color=darkr
ed]
rows.[color=darkred]
limits[color=darkred
]
on[color=darkred]
version[color=darkre
d]
details of[color=darkred]
| |
| Deepak Puri 2006-03-24, 8:26 pm |
| 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:
[color=darkred]
With Member Measures.PeerGroup As
'LowNall2CustDim.currentmember.parent.parent.uniquename',
NON_EMPTY_BEHAVIOR = '[Measures].[Count]'
select { Measures.[PeerGroup], Measures.[baseamt], Measures.[Count]} on
columns,
NON EMPTY & #123;crossjoin(LowNa
ll2CustDim.[Id].members, [RecvPay].
[RecvPay].members)}
Dimension PROPERTIES [Id].Name,
[RecvPay].[recvpay].Name on rows
from LowNall2 where ([bookdate].&[2006].&[1].&& #91;3])[color=darkre
d]
http://support.microsoft.com/defaul...kb;en-us;304137[color=darkred]
INF: How to Increase the Speed of MDX Queries that Contain the NON EMPTY
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.
...[color=darkred]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.droptable.com ***
| |
| Darren Gosbell 2006-03-26, 7:39 am |
| 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@TK2MSFTNGP14.phx.gbl>,
bzakharin@primeassoc
iates.com says...[color=darkred]
> NON EMPTY didn't help. Which timeout are you talking about?
> sp_addlinkedserver does not have a timeout option.
>
> "Darren Gosbell" <jam@newsgroups.nospam> wrote in message
> news:MPG. 1e8d486b750b59559898
cd@news.microsoft.com...
> three
> suspect,
> rows.
> limits
| |
| Boris Zakharin 2006-03-27, 8:27 pm |
| But if the default is zero, there should be no problems by default, right?
"Darren Gosbell" <jam@newsgroups.nospam> wrote in message
news:MPG. 1e910f3a6728ddbe9898
cf@news.microsoft.com...[color=darkred]
> 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@TK2MSFTNGP14.phx.gbl>,
> bzakharin@primeassoc
iates.com says...
5,000[color=darkred]
between[color=darkre
d]
use[color=darkred]
should[color=darkred
]
from a[color=darkred]
crossjoining? It[color=darkred]
in[color=darkred]
| |
| Darren Gosbell 2006-03-28, 7:29 am |
| That is correct.
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <#cTLwZeUGHA.776@TK2MSFTNGP09.phx.gbl>,
bzakharin@primeassoc
iates.com says...[color=darkred]
> But if the default is zero, there should be no problems by default, right?
>
> "Darren Gosbell" <jam@newsgroups.nospam> wrote in message
> news:MPG. 1e910f3a6728ddbe9898
cf@news.microsoft.com...
> 5,000
> between
> use
|
|
|
|
|