Hi,

Running the query below takes a very long time to complete (several
minutes). Most of this time is spent with the client CPU running at 100%,
the OLAP server idling at 0% (or thereabouts), and no network activity.

My questions are:

1. Is there a way to optimize the way the query is written? The query is
simple in concept; the Source has to be one of the 100 listed locations and
the Destination must NOT be one of the listed locations.

2. What is the client doing with 100% of the CPU? If there is no network
activity, what can it possibly be processing or preparing?

Thanks,
Charlie

The query:

WITH MEMBER [Source].[Slicer] AS '[Source].[001] + [Source].[002] + 003,
004, etc ... + [Source].[100]'
MEMBER [Destination].[Slicer] AS 'SUM(Destination.Destination.MEMBERS) -
([Destination].[001] + [Destination].[002] + 003, 004, etc ... +
[Destination].[100])'
SELECT {[MEASURES].MEMBERS} ON COLUMNS,
{[MonthlyTime].MEMBERS} ON ROWS
FROM [MyCube]
WHERE ([Destination].[Slicer],[Source].[Slicer])

One obvious optimization in [Destination Slicer] would be to use an
[All] Member, instead of summing over all leaf members (depending on the
number of members). Beyond that, is there any pattern to the listed
locations, which would permit the use of pre-aggregated cube data?
([Destination].[001] + [Destination].[002] + 003, 004, etc ... +
[Destination].[100])'

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks. That seems to have halved the query execution time. But with 300
source/destination locations, it still takes several minutes to execute the
query.

The user can mix and match any locations as the Source locations. Any
location they don't pick becomes a Destination location. I don't think this
can be pre-aggregated?

Lastly, it appears to me the OLAP server is returning the results to the
Client immediately (< 1 second). The Client then spends the next few minutes
filtering and aggregating the results. Is there any way to force the Server
to do the filtering/aggregating if that would help performance?

Thanks,
Charlie

your query is executed at the client side.
if your user is able to select 100 members has the source and 100 members in
the destination dimension, the result is 100 *100 cells. Every cell is
evaluated by the client...

so you can force the execution of your query at the server side to reduce
the client side calculation time.
Add this to your connectionstring: ;Execution Location=3;Default Isolation
Mode=1

also, make sure your cube has some aggregation at the
"Destination.Destination" level and the "Source.Source" level.
If your execution is at the server side, you'll we be able to identify
exactly what aggregations are required.

there is a good post:
http://groups.google.ca/groups?hl=en...N%26tab%3Dw g

"Charlie" <charlie> wrote

For each [MonthlyTime] member, are there typically many combinations of
the selected sources and destinations that are empty (ie. have no rows
in fact table)? If so, applying NonEmptyCrossJoin() to source and
destination sets may significantly reduce the combinations needing
summation.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks for that read. The problem is not the bandwidth or the sending of the
data. So I don't know how much of a speed difference it will make asking for
server execution. Besides I tried that connection string change yesterday
and it seemed to produce an unstable connection (the application reported
all sorts of strange errors fetching dimension data).

I believe the problem is in the way the client applies the where condition.
See the thread "Why is this query faster?" in this group for the question
this has led me to.

Thanks,
Charlie

executing the query at the server side will use 100% of the server CPU
instead-of 100% of the client CPU.
Because, by default, the client evaluate the formula, so 100% of the
client's cpu is used.

you can improove the performance by setting up the right aggregations in the
cube, I mean insure that the levels used in your queries are aggregated.

what your query do?
you want to display values only for monthtime with source & destination
available?

"Charlie" <charlie> wrote