I have recently inherited a project which involves refreshing about 50
spreadsheet documents (~ 10 worksheets per document) from Analysis Services
2000. So, that's about 500 pivot tables total. The AS data source consists
of a snowflake schema with 2 fact tables, a junction key table, and over 20
dimentions. Currently, the sheets are processed (via Remote Desktop) on the
same server box that hosts AS.

It currently takes about 8 hours to process (automated via VBA scripting),
assuming it runs to completion without getting hung up, and my goal is
reduce this down to 4 or maybe 2 hours.

I suspect that the bottleneck is number crunching by the Pivot Table Service
and perhaps pushing Excel to it's resource limits. Any ideas about how to
proceed?

Are the updates being processed serially or in parallel? If serially, you
might consider breaking it up into 5-10 batches and start them all at the
same time.

Additionally, it sounds like you are using tables as your datasource. You
might consider creating actual cubes in AS and using these cubes as your
pivot table data source(s).

HTH,

Mike

"JT" wrote:

Thanks, I'll try the suggestion of processing several sheets (there are
about 6 companies) in parallel. I will also experiment with processing the
sheets on a local workstation, rather than on the server, so it won't
compete for resources.

The source of the pivot tables is cube. Perhaps the actual connection string
would help:

Connection = "OLEDB;PROVIDER=MSOLAP.2;Persist Security Info=True;Data
Source=XYZ;Initial Catalog=Warehouse;Client Cache Size=25;Auto Synch
Period=10000"

'Client Cache Size' and 'Auto Synch Period' seem candidates for
investigating.

"Mike Austin" <MikeAustin (AT) discussions (DOT) microsoft.com> wrote

Just a small comment regarding the connection string. In order to reduce the
number of queries run agains the AS (you can check that looking at the query
log) by adding the "Cache Policy=7".

Hop it helps.

"JT" wrote:

Have you run the usage based optimisation wizard on the cubes? This
might be able to create a better aggregations and therefore reduce the
amount of time needed to refresh each pivot table.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <AF1A8207-747A-4DB2-9181-604687415572 (AT) microsoft (DOT) com>,
TiagoRente (AT) discussions (DOT) microsoft.com says...

The PivotTable Service Programmer's Reference describes this property only
as "Reserved for future use".
http://msdn.microsoft.com/library/de...pgref_3fjt.asp

"Tiago Rente" <TiagoRente (AT) discussions (DOT) microsoft.com> wrote