SQL Server cube to Excel - memory/performance question.

lee_jd used Ask the Experts™
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.
Watch Question

Do more with

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


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

Limits of PivotTables in Excel;

Description of the limits of PivotTable reports in Excel;

How to optimize PivotTable performance in Excel 2000;

The ExcelUser portal offers links to help pages created by top
Excel web sites. This section discusses the use of PivotTables in
Microsoft Excel;

Pivot Table and Pivot Chart Links;


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