Dear all,

Previously, I used Excel Pivot Table to make a report according to the
following MDX query.

MDX query:
WITH
MEMBER [Measures].[Net Amount] AS
'[Measures].[Gross Amount] - [Measures].[Discount Amount]',
SOLVE_ORDER = 1
MEMBER [Measures].[Unit Price] AS
'[Measures].[Net Amount] / [Measures].[Qty]',
SOLVE_ORDER = 2
select
{ crossjoin({[Branch].[Store A],[Branch].[Store B], [Branch].[Store C],
[Branch].[Store D], [Branch].[Store E],
[Branch].[Store F],
[Branch].[Store G]},
{[Measures].[Qty], [Measures].[Unit Price],[Measures].[Net Amount]})}
on columns,
non empty{ nonemptycrossjoin({[Supplier].[Suppcname].members}, {[Item
Category].[Chicken].children})} on rows
from
[Receiving Datawarehouse]
where
([Delivery Date].[2004].[June], [Section].[All Section].[Kitchen] )

It took about 15 mins to finish the job. Recently, I discovered that it took
about 1-2 mins to get the same result when I use the "Microsoft Office Pivot
Table 11.0" (a web form component). Its structure is similar to Excel' s
Pivot Table. However, it is not easy to print all the results.This make me
hesitate to introduce this method to my colleagues.

By the way, what are the difference between these two pivot tables. Why the
difference between their query speeds is so great?

Hope somebody can explain this discovery for me! Thanks!

Polly

Hi Polly,

Excel and OWC are two very different front-end tools. They use totally
different techniques for retrieving and caching metadata. While, yes --
they both use PTS -- that has the least of the overhead. The question
assumes that Excel and OWC resolve all of the user's interface techniques
down to the same MDX queries and meta-data access steps -- and that isn't
true. It is HOW they use PTS that is different and makes them have such
totally different perceived peformance differences.

The biggest difference is that Excel pulls down all the data for the
PivotTable where OWC only pulls the data that can be viewed in the current
PivotTable. When paging down a very large Pivot, Excel is much faster
because it already has the data. For OWC the first time paging down is slow
as OWC incrementally retrieve more data. There are other differences but
the initial quantity of data retrieved is what give the appearance of
different performance.

It is not that one technique is better or faster than the other, it is that
they each have different performance characteristics.

HTH,
Bas

"This posting is provided "AS IS" with no warranties, and confers no
rights."