MS SQL Server OLAP - Improving the performance of Excel Pivot Tables

Author

Improving the performance of Excel Pivot Tables

2005-11-10, 9:23 am

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?

Mike Austin

2005-11-10, 11:23 am

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?
>
>
>

2005-11-10, 1:23 pm

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=MSOLA
P.2;Persist Security Info=True;Data
Source=XYZ;Initial Catalog=Warehouse;Cl
ient Cache Size=25;Auto Synch
Period=10000"

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

"Mike Austin" < MikeAustin@discussio
ns.microsoft.com> wrote in message
news:A3C28CC4-D26B-46FB-AD07- 5964E7D7C89F@microso
ft.com...[color=darkred]
> 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:
>

Tiago Rente

2005-11-12, 1:23 pm

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=MSOLA
P.2;Persist Security Info=True;Data
> Source=XYZ;Initial Catalog=Warehouse;Cl
ient Cache Size=25;Auto Synch
> Period=10000"
>
> 'Client Cache Size' and 'Auto Synch Period' seem candidates for
> investigating.
>
> "Mike Austin" < MikeAustin@discussio
ns.microsoft.com> wrote in message
> news:A3C28CC4-D26B-46FB-AD07- 5964E7D7C89F@microso
ft.com...
>
>
>

Darren Gosbell

2005-11-14, 3:24 am

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@microso
ft.com>,
TiagoRente@discussio
ns.microsoft.com says...[color=darkred]
> 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:
>

2005-11-22, 8:24 pm

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

"Tiago Rente" < TiagoRente@discussio
ns.microsoft.com> wrote in message
news:AF1A8207-747A-4DB2-9181- 604687415572@microso
ft.com...[color=darkred]
> 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:
>

Sponsored Links