Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bulk querying and caching of Cube data
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.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bulk querying and caching of Cube data
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. question wrote: 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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
suppress warning and locate cube in a pivot table from olap cube | Excel Discussion (Misc queries) | |||
Querying data | Excel Discussion (Misc queries) | |||
best method of bulk move of data? | Excel Programming | |||
Linked files - Caching of data | Excel Discussion (Misc queries) | |||
What is the fastest way to bulk load data into Excel? | Excel Programming |