Bulk querying and caching of Cube data

[question]

Hi,



I have an excel report which used to access an SQL Server 2000 Analysis
Services database. We have created a custom addin for the excel sheet using
VBscript. The excel sheet used a VBscript function for getting data. The
excel sheet has so many group id's. These group id's are passed as parameters
to the VBscript function so as to get the data. There are some 30000+ group
id's like this. The function will be called for each group id. This was
working fine with our 2000 database.



Ex:-



Group ID Formula



1 function(1)

2 function(2)

3 function(3)

........





We migrated our database to SQL Server 2005. After this we are facing a
huge performance issue. The report which used to run in 2 mins is taking
15-20 mins now. While migrating to SQL Server 2005, we have transferred the
VBscript to VB.net code.



The same report is giving a better performance with the Microsoft addin
available with Excel 2007. But as the microsoft addin doesn't have certain
functionalities which we need, we are not able to use that.



Any ideas why this is happening? Any possible areas which I should look
into?



The microsoft addin uses bulk querying and caching which results in
faster performance. Any idea how to implement this?



Please reply at the earliest possible...

Thanks in advance....

 

[JW]

Don't have SQL Server, so I can't speak directly about it, but.....
Have you considered placing the entire query into a separate sheet
within your workbook and then using VLookup, SumIF, a UDF, or whatever
function you need to use to get data from it? You can accomplish that
by Date--Import External Data-->New Database Query. Then, through
code, if needed, you could have the workbook automatically refresh the
query each time the workbook is opened.