Hi there,
does Microsoft give any comment on the Excel high CPU use
on OLAP query problem described by Jon Pearce on Aug 27
2003?

I have seen several postings regarding this problem,
dating all the way back to 2002. But I have not found an
answer by a Microsoft representative yet.

Is the fact that Excel grabs a hold of 100% cpu time and
loads of memory due to bad dimensional design ( and hence
a mistake on behalf of the programmer)? Or is there a
solution for the problem? I have not even seen a post,
where Microsoft acknowledges that there is a problem...

Anyway, my company intends to continue using Excel 2002
as OLAP client. Could someone shed some light on the
issue?

Is shifting the workload from client to server the only
viable solution at present?

TIA
Lutz Morrien

Lutz, I sure hope you get some responses. I'm always interested in this.
I'm also interested in if/how the various Excel add-ins improve the
situation. I reviewed the information available on John Keeley's site, but
didn't notice any comparison of query speed.

For my group, the Excel query speed issue, with calculated members, is the
greatestand only really significant--issue keeping us from using Excel
exclusively.

--Bill Koran

"Lutz Morrien" <lutz.nospam.morrien (AT) ocb (DOT) com> wrote

See reply to Jon's original post.

--
==================================================
Mosha Pasumansky - www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

John,
There are really 3 places that can cause performance degradation
within Excel ..

1.
Excel overhead of executing formulas - the more formulas you have the
bigger the overhead and depending upon the Excel version, at certain
trigger points in the number of calculations to be performed Excel
starts to behave differently.

This calculation effect can be significant depending
upon how the function calls are exposed to Excel
ie. in VBA or via an XLL.

2.
The number of MDX calls for the cellset.

You mentioned version 1 of XLCubed having problem with thousands of
cells. This is not entirely true as it depended upon what you were
doing.

For standard reporting of thousands of cells and then changing
a filter the performance is fine in version 1.5 and above at
least comparable with Plain, IA et al.

3.
The MDX that is being executed and how it handles the dimensional
meta data, particularly with respect to large dimensions.

As you are aware with the XLCubed version 2 development
has significantly focused on performance.

To get the maximal performance vs functionality we will be offering
2 modes - the formula based approach and the XLCubed Explorer mode

(which BTW we released for sale today !!!).

On my laptop using the Explorer Direct MDX support - if I run

Select [Time].Members on columns,
Crossjoin([Product].Members,[Store].[Store Country].members) on rows
from Sales

This brings back 243,682 (yep quarter of a million) cells in 3.3
seconds.

For Select [Time].[1997] on columns,
Crossjoin([Product].Members,[Store].[Store Country].members) on rows
from Sales

This bring back 20,305 cells in 751 ms.

We are not doing anything fancy with the result set just getting it
into Excel so this is raw speed but I think you will agree that this
is pretty damn quick.


Anyway I hope that is helpful to people.

Thanks


Mark Scanlon
XLCubed

For those of you who are interested in the query response times of
Excel add-ins please see the latest article on my website:

http://www.johnkeeley.com/excel_add-ins_speed_test.htm

Regards,

John




"Bill Koran" <bkoran (AT) qwest (DOT) net> wrote

Jon,
I took a look on your website and I would like to clarify a couple of
things ..

You Said

This was version 1 which was entirely formula driven (i.e. every cell
was an MDX query).

executing 1 MDX query per cell - this is what cubecellvalue does - we
try and rationalise down the number of MDX queries through analysing
what the user is asking for. We have speeded it up more in version 2
by using new techniques - I wish it was as simple as reducing the MDX
queries .. it has not been !!


You also said
This experience, along with others, spurred them into action to create
a faster version 2, the first part of which has been released as
"Explorer".
I have now put Explorer to the test alongside it two main rivals.
These are the results:

XLCubed IntelligentApps MIS Plain
Query 1 (c.100k cells) 2.2 sec.'s 19.6 sec.'s 8.8 sec.'s
Query 2 (c. 1 million cells) 13.6 sec.'s 228 sec.'s 49.0 sec.'s

Of course most users will not be running such queries & in everyday
scenarios I haven't found time to be a big problem.
Functionality, ease of report creation & ease of use are just as
important

I agree with this but raw speed when doing adhoc analysis has been our
goal .. the interesting thing is also to see what happens when some of
the formatting get turns on as in my experience that is where the
speed differences can be unbelievably dramatic ..

Anyway I do think that these kind of numbers demonstrate that all 3 of
the major players in this market can perform better than people
expect.

cheers

Mark