Re: Improving the performance of Excel Pivot Tables

Tech-Archive recommends: Fix windows errors by optimizing your registry

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:

> 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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:A3C28CC4-D26B-46FB-AD07-5964E7D7C89F@xxxxxxxxxxxxxxxx
> > 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:
> >
> >> 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?
> >>
> >>
> >>
>
>
>
.

Quantcast