1. I'm experiencing a slow OLAP cube pivoting in a WAN network. Pivoting in a
cube taking more than 15 minutes when I try to drill down to the deepest
level in a dimension. This works well when the OLAP cubes in stay in my Local
Host. It takes merely less than 1 minute on average.
2. Some specification of the fact table and cube:
a. Fact Table
i. Number of rows in the fact table: 4,000.
ii. Fact table is a view. 3 columns use in some calculated members is a
SQL expression to gather 3 month's quantity. E.g. Moving Quantity for Feb
2004 = Summation of Quantity in Dec 2003, Jan and Feb 2004.
b. Cube
i. MOLAP with 2 aggregations.
ii. Number of measures = 12
iii. Number of calculated members = 16, 6 of the calculated members use
another 6 calculated member to perform calculation
iv. Cube Size 0.12 MB
v. Number of row when drill down to the deepest level in a dimension = 100
items in level 3, 20 items in level 2 and 6 items in level 1. Number of
measures = 8. In month, quarter and year. A total of 800 cells with figure in
OWC 11 (100*8).
c. Server Configuration:
i. 4-way processors. 2GB RAM
3. Some trial and error has been done but neither of them helps.
a. Execution Location = 3 and Isolation Mode = 1
b. Partitioned by Year.
4. It works pretty good when I reduce the calculated members to 3.
5. Can anyone shed some lights what the problem is? Any idea on how to
figure out the real problem?

Assuming that you are using AS 2000, then there can be problems with the
PTS client layer messaging over a WAN with low bandwidth and/or high
latency. Mark Hill discussed the problem (and possible solutions) in
some of his blog entries below. But AS 2005 might be a good solution for
such problems by now:

http://markiehill.blogspot.com/2005/...-problems.html
To quickly paint the picture, we have a large Analysis Services
deployment which suffers badly from poor network performance caused
mostly by the poor infrastructure and compounded by the way that MSAS
uses the network.

For those of you that don’t know MSAS uses PTS. PTS is short for Pivot
Table Services and is the software on the client which talks to the
Analysis Services server. PTS is great when you have a pretty capable
network as it performs a load of caching and delivers a pretty good
experience. The first cracks in this architecture appear when you
introduce latency and bandwidth into the equation, by restricting these
you find that PTS becomes rapidly unworkable below speeds of 64Kbps,
latency of over 200ms also has a huge impact on the user experience.
...
http://markiehill.blogspot.com/2005/...roblems-part-i
i.html
into trying to run PTS in poor network environment. We discussed how to
get XMLA as far as the desktop however did not discuss how we can get
client tools to consume this data with the minimum of hassle.
...
So let’s talk about performance and the improvements that we can
quantify.

Connection to our main cube used to take in order of around 10-20
minutes in some locations even with all the different PTS connection
properties set on the client, with the new provider around 10-20seconds
is pretty representative now.

In terms of queries themselves, we are seeing on average over 500%
performance increase, however in some cases reports which we could not
run at all or would have taken all day are now taking 5 minutes. All in
all a success story from the user perspective.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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