This is a strange one that I've been working on all week. I've come to the
conclusion that summing 1 day is slower than summing 7 days:

MDX1:
with member [Time].[CustomRange] as
'Sum([Time].[2005].[Jan].[31]:[Time].[2005].[Jan].[31])'
select {[Measures].[MyMeasure]} on columns,
{[Stores].[My Store]} on rows
from MyCube
where ([Time].[CustomRange])

MDX2:
with member [Time].[CustomRange] as
'Sum([Time].[2005].[Jan].[24]:[Time].[2005].[Jan].[31])'
select {[Measures].[MyMeasure]} on columns,
{[Stores].[My Store]} on rows
from MyCube
where ([Time].[CustomRange])

So MDX1 is summing up just 1 day. MDX2 is summing up 7 days. That's the ONLY
difference. You'd think that MDX2 would be slower, but it takes half a second
while MDX takes 5 seconds. I'm getting these statistics from looking at the
ReportServer.ExecutionLog.TimeDataRetrieval column for a data-driven
Reporting Services subscription that we run every day. It runs MDX1 for 600
stores then MDX2 for 600 stores. I thought the performance difference was due
to caching, so I switched the order... run MDX2 for 600 stores then MDX1 for
600 stores. MDX2 was still half a second each, and MDX1 was still 5 seconds
each.

I don't believe it could be aggregations because the Time dimension doesn't
have a Week level.

I get MDX1 to return in half a second when I change the MDX to:

select {[Measures].[MyMeasure]} on columns,
{[Stores].[My Store]} on rows
from MyCube
where ([Time].[2005].[Jan].[31])

Can anyone explain this? Has anyone else seen this? We're still under AS2000
SP3a.

What happens when you run the same queries in MDX Sample App on a cold cache
(the easiest way to do this is to stop and start the service before each
query)? Do you still get the same timings?

Chris
--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"FurmanGG" wrote:

Chris-

Thanks for the reply. I just tested it from the sample app restarting the
service between each query, and I get the same results. Summing 1 day is much
slower than summing 7 days. Thoughts?

As a side note, I believe that the MDX Sample App (or the MSOLAP provider)
was doing some client side caching cause all queries were flying even after
restarting the service and rerunning the query. So every time I restarted the
service, I closed and reopened a new MDX Sample App to eliminate all
caching... then the performance was as I had reported before... slow to sum 1
day... fast to sum 7 days.

Any help would be appreciated.

"Chris Webb" wrote:

Hmm, I've come across several examples of of poor performance with this kind
of query, but none specifically with this kind of bizarre behaviour. Some
questions/things to try:
- Is Measures.MyMeasure a calculated measure?
- What happens when you rewrite your first query as follows:
with member Measures.Test as
'Sum([Time].[2005].[Jan].[31]:[Time].[2005].[Jan].[31], Measures.MyMeasure)'
select {[Measures].Test} on columns,
{[Stores].[My Store]} on rows
from MyCube
- Can you run the same tests on a machine with SP4 installed?

Chris


--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"FurmanGG" wrote:

Chris-

Whether or not Measures.MyMeasure is a calculated measure doesn't matter.
I've tried both and get the same performance.

Rewriting the query as you suggested doesn't fix the problem either. Same
performance.

Unfortunately, I don't have access to an SP4 machine, and we won't be able
to upgrade our dev box for about a month. If it fixes the problem, I'll post
back here then. (That's not quite the explanation I was looking for, though
;-)

"Chris Webb" wrote:

It sounds like one of those things that only the likes of Mosha can explain,
to be honest. However, one last thing to check (also on the subject of
service packs): are you sure you've got the sp3a version of PTS installed on
the client machine you're using to run queries? It's easy to forget that you
can't just install service packs on the server. And I'll mention this because
it's also easily forgotten - are you sure you've got the Analysis Services
service pack installed and not just the SQL Server one (they are separate
downloads)?

Chris
--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"FurmanGG" wrote:

We definitely do have SP3a for Analysis Services installed, not just the one
for the relational engine. For this particular query we're not using PTS to
execute any queries. In this case, the "client" is Reporting Services which
is on the same box. Good thought though.

If the client for our cube is a web server (and a custom ASP.NET
application), is the easiest way to get the right drivers on the client
machine to just install Analysis Manager and then AS SP3a?

If the client for our cube is the Microsoft Excel Add-In, do we need to
install anything but the latest Excel Add-In on the client machines to get
the latest drivers? (Is there a particular file I can check the version of?)

Thanks for your help. Interested to hear if Mosha can explain it.

"Chris Webb" wrote:

I think there's some confusion here: when I talk about PTS I don't mean Excel
pivot tables, I mean the client components of AS (what I think you're
referring to in your second paragraph when you talk about 'drivers'). It's a
bit of a confusing name. But anyway, you can install latest version on a
client machine by running either PTSFull.exe or PTSLite.exe in the
\SQL2KSP4\msolap\install\PTS folder under wherever you've unpacked the
service pack. You might also want to look at the following KB article:
http://support.microsoft.com/default...b;en-us;312876

I would have thought that both the RS and the Excel addin installs would
have included the sp3a versions of PTS but it might be a good idea to install
them again just to make sure. I'm not sure this will solve your problem but
you should be doing this anyway just to make sure you don't run into any
other issues!

HTH,

Chris
--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"FurmanGG" wrote: