High CPU in client (Excel, OWC, Proclarity, etc.) accessing Analysis Services

From: Kory Skistad (kskistad_at_hotmail.com)
Date: 02/29/04 

Date: 28 Feb 2004 20:33:52 -0800

I've been dealing with this issue for two years and haven't found a
satisfactory answer yet. Based on the following postings, this does
not seem to be an isolated incident:

http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&threadm=%23KtwKPLbDHA.652%40tk2msftngp13.phx.gbl&rnum=1&prev=/groups%3Fq%3DCPU%2Bexcel%2BOLAP%26hl%3Den%26lr%3Dlang_en%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3D%2523KtwKPLbDHA.652%2540tk2msftngp13.phx.gbl%26rnum%3D1
http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&threadm=01f201c36dfe%24fd440500%24a001280a%40phx.gbl&rnum=2&prev=/groups%3Fq%3DCPU%2Bexcel%2BOLAP%26hl%3Den%26lr%3Dlang_en%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3D01f201c36dfe%2524fd440500%2524a001280a%2540phx.gbl%26rnum%3D2
http://groups.google.com/groups?q=CPU+excel+OLAP&hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&selm=b08201c3b8c5%249c70eba0%24a601280a%40phx.gbl&rnum=3
http://groups.google.com/groups?q=CPU+excel+OLAP&hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&selm=3eac01c37c30%24738b9db0%24a501280a%40phx.gbl&rnum=4
http://groups.google.com/groups?q=CPU+excel+OLAP&hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&selm=6633def8.0402210218.165afdfd%40posting.google.com&rnum=5
http://groups.google.com/groups?q=CPU+excel+OLAP&hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&selm=OORF%24WySBHA.2140%40tkmsftngp05&rnum=6
http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&threadm=975fe525.0304211032.6c0fbedd%40posting.google.com&rnum=9&prev=/groups%3Fq%3DCPU%2Bexcel%2BOLAP%26hl%3Den%26lr%3Dlang_en%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3D975fe525.0304211032.6c0fbedd%2540posting.google.com%26rnum%3D9
http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&threadm=%23CXuDQc%24CHA.2132%40TK2MSFTNGP11.phx.gbl&rnum=15&prev=/groups%3Fq%3DCPU%2Bexcel%2BOLAP%26hl%3Den%26lr%3Dlang_en%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3D%2523CXuDQc%2524CHA.2132%2540TK2MSFTNGP11.phx.gbl%26rnum%3D15
http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&threadm=c27ce0e1.0209170645.67d936a4%40posting.google.com&rnum=17&prev=/groups%3Fq%3DCPU%2Bexcel%2BOLAP%26hl%3Den%26lr%3Dlang_en%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3Dc27ce0e1.0209170645.67d936a4%2540posting.google.com%26rnum%3D17
http://groups.google.com/groups?q=CPU+excel+OLAP&hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&scoring=d&selm=04e901c36752%24c24ad850%24a101280a%40phx.gbl&rnum=23
http://groups.google.com/groups?q=CPU+OWC+OLAP&hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&selm=uZy%24imbDDHA.2892%40TK2MSFTNGP11.phx.gbl&rnum=8
http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&threadm=ueDLI3SfDHA.1060%40TK2MSFTNGP12.phx.gbl&rnum=7&prev=/groups%3Fq%3DCPU%2BOWC%2BOLAP%26hl%3Den%26lr%3Dlang_en%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3DueDLI3SfDHA.1060%2540TK2MSFTNGP12.phx.gbl%26rnum%3D7

I currently have an open case with Microsoft PSS for a week, and they
haven't been able to provide me with a solution yet.

I have a cube based on a single fact table with 2.8 million rows, 8
dimensions and 12 measures. Originally I also had 8 calculated
measures. Originally I also had 8 partitions. I created a view in
the OWC10 with 3 dimensions on the row axis, one on the column axis.
The largest size of any of these 4 dimensions < 360 members. On the
page axis I have the remaining dimensions, and I filter two of the
smaller dimensions. This returns data within 3 minutes. Then I need
to filter the last remaining dimension which has a single level of
3000 members, and I need to exclude only 2 members. This is when the
CPU = 100% and never, ever, ever, ever returns.

I tried this query in OWC, pasted the MDX in the MDX Sample App,
pasted in Proclarity 5 client, and in Excel XP all with the same
results... 100% CPU and no returned data.

I followed virtually every suggestion in the Analysis Services
Performance Guide to optimize the query, the cube, the server, etc.
Tried Execution location=3, Default Isolation=1; Server is dual
1.26Gz, 4GB RAM, RAID 5 100GB; partitioned the cube; set the slicer on
the partition and adjusted the member counts of the slicer dimension;
used Usage-based optimization based on the query submitted and
reaggregated using the *EXACT* levels and dimensions needed to resolve
the query; reinstalled PTSLite from SP3a on the client and already
have SP3a on the server; ran the query from different PCs; set up
performance counters (Agg Cache, Query, Processor, Process-msmdsrv,
Physical&Logical Disk, Memory) and recorded activity for 1 hour...

While the query was grinding, the server was doing absolutely nothing.
 Also the Direct Hit and Filter Hit/sec counters never > 0. Also the
Indexes Read/sec = 0 always as well. I even submitted several of the
sample queries from the Foodmart2000 database and none of these 3
counters shows activity.

Here is exactly what happens, doesn't matter which client the query is
submitted from: Query is executed. Query performance counters on
server show activity and CPUs on server jump. Client machine shows <
5%. Then 2 minutes later the server drops to 0, and is completely
idle. Client jumps to 100% and stays there. One hour later client is
still 100% and server hasn't shown any activity on the Analysis
Services or other counters.

PSS called me back and said, "Too many calculated members (8)" So for
troubleshooting I dropped them all and left only the base measures.
Same issue. I had 8 partitions originally, so I deleted 7 and
reprocessed the single, smallest partition. Same issue. I had 4
dimensions that weren't needed for the query so I dropped them as
well. No dice. PSS also said my 3000 member dimension was "very
large" and should have more than 1 level because hierarchies perform
better. They confirmed that *not* filtering on the remaining
dimension by excluding a single member allowed the results to return.
I knew this. I told them this. What have they been doing for a week?
 I asked them about the performance counters, and they said they would
have to get back to me because they didn't know what direct
hits/filtered hits/index reads were actually supposed to do... yikes.
They also said I should look at re-writing my MDX to get better
efficiency, but while I may be afforded this luxury my uers will not
because they will use tools like Excel, OWC or Proclarity that write
the MDX for them.

I can run this query using the same 2.8 million rows within SQL Server
and filter and exclude the same members to get the desired resultset
in less than 2 minutes.

I hope PSS finds an answer because this is not the first query I've
ran into this issue with, and judging by the numerous postings listed
above, others have seen this type of behavior as well.

I suspect PTS is the culprit, but there doesn't seem to be a lot of
tweaks that we can do with PTS and no easy way to debug, trace, or
monitor it.

In my opionion, the benchmark OLAP needs to measure itself by is the
relational database (SQL Server in my case). For any given query, you
should be able to do a GROUP BY and aggregate on all the desired
measures, and then store that in a table. Now perform a simple select
statement against that aggregated table. If this takes 13 seconds to
return, it should take roughly 13 seconds to return from an OLAP cube
that is properly aggregated at the same levels. All of this should be
done on the server and the results returned to the client. Then PTS
can cache the necessary results and subsequent queries can be resolved
from the cached data, or requested additional data from the server
cache if necessary, and from the server disk if not yet in server
cache. All this seems straight forward and simple to comprehend, but
just doesn't seem to be what is happening in actual practice and no
one I've discussed this with knows exactly what is happening.

Please, someone help.

Relevant Pages

  • Re: Performance Benchmarks?
    ... Are attribute relationships defined properly on the dimensions? ... If you run Profiler against the server, how long is spent in "Query ... I have created aggregations (Partitions tab in cube design). ...
    (microsoft.public.sqlserver.olap)
  • Re: High CPU in client (Excel, OWC, Proclarity, etc.) accessing Analysis Services
    ... We have encountered similar problems not limited to Excel, but every client. ... occur when there are many dimensions nested on each other on a single axis ... > Performance Guide to optimize the query, the cube, the server, etc. ... > While the query was grinding, the server was doing absolutely nothing. ...
    (microsoft.public.sqlserver.olap)
  • Re: Inconsistent MDX query performance
    ... Interestingly as I broke my query down bit by bit (basically I chopped the ... all of the RAM and brought the server down. ... Fortunately because my queries are generated programmatically it is fairly ... that have extreme numbers of members along one or more dimensions. ...
    (microsoft.public.sqlserver.olap)
  • Re: Single Cube from Multiple sources
    ... I think your errors are coming from not being able to query one server ... process the resulting cube, SSAS generates a query that accesses all ... One limitation is that you can't chain together referenced dimensions, ...
    (microsoft.public.sqlserver.olap)
  • Re: MDX Query Performance
    ... > executes an MDX Query on analysis server. ... the MDX Query response is taking lot of time. ... > dimensions are having 170,000 members at the lowest level. ...
    (microsoft.public.sqlserver.olap)