SQL Server cube to Excel - memory/performance question.

lee_jd
lee_jd used Ask the Experts™
on
I want to design an excel based reporting package.  My plan is hold the data in a Sql server cube and then bring this into excel via a pivot table.

I will then update the pivot using VBA according to what the user selects and retreive the data.

My question is this - how much data is held in Excel/Memory - is it the total cube or just what ever is selected in the pivot table?  The data in SQL will be around 100,000 records hence I don't want all of this to be loaded into memory as it will slow down the report.  I will also have several pivot tables based on the same data in order to drive different reports.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
any answers on this please?
Software & SQL Server
Commented:
Hi lee_jd,
Sorry for sending links.

Limits of PivotTables in Excel;
http://support.microsoft.com/kb/211517

Description of the limits of PivotTable reports in Excel;
http://support.microsoft.com/kb/820742

How to optimize PivotTable performance in Excel 2000;
http://support.microsoft.com/kb/273583

The ExcelUser portal offers links to help pages created by top
Excel web sites. This section discusses the use of PivotTables in
Microsoft Excel;
http://www.exceluser.com/help/pivot/long001.htm

Pivot Table and Pivot Chart Links;
http://peltiertech.com/Excel/Pivots/pivotlinks.htm

Kosturdur

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial