Re: Improving the performance of Excel Pivot Tables

Tech-Archive recommends: Speed Up your PC by fixing your registry

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@xxxxxxxxxxxxx>,
TiagoRente@xxxxxxxxxxxxxxxxxxxxxxxxx says...
> 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.
> > >>
.

Quantcast