Re: Improving the performance of Excel Pivot Tables

The PivotTable Service Programmer's Reference describes this property only
as "Reserved for future use".
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmpr/pt_pgref_3fjt.asp

"Tiago Rente" <TiagoRente@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:AF1A8207-747A-4DB2-9181-604687415572@xxxxxxxxxxxxxxxx
> 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?
>> >>
>> >>
>> >>
>>
>>
>>


.

Loading