Is it possible to create an efficient UDF alternative to Excel's CUBEVALUE function?

Posted by bright on Stack Overflow See other posts from Stack Overflow or by bright
Published on 2010-05-12T00:45:30Z Indexed on 2010/05/12 2:04 UTC
Read the original article Hit count: 241

Filed under:

excel

|

udf

|

olap

We'd like to create a simpler alternative to Excel's CUBEVALUE function for retrieving data from an OLAP server. The details aren't critical, but briefly, our function will "know" the source connection and accept a very simple ticker-like parameter and a date, in place of CUBEVALUE's MDX-style parameters. This is for internal use within our firm, just FYI.

However, Excel has optimized CUBEVALUE so that calls to the OLAP server are batched.

Question: Is there a way to code the new function so that it can similarly batch calls rather than issue a separate query for each cell?

© Stack Overflow or respective owner

Related posts about excel

Excel error "This workbook contains Excel 4.0 macros or Excel 5.0 modules"

as seen on Super User - Search for 'Super User'
I have a workbook that was protected via the Protect Workbook feature. It was sent to someone else to modify. When they sent it back, it was unprotected and when I try to reprotect it I get this error, "This workbook contains Excel 4.0 macros or Excel 5.0 modules. If you would like to… >>> More
Open excel 2007 excel files and save as 97-2003 formats in VBA

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a weird situation where I have a set of excel files, all having the extension .xls., in a directory where I can open all of them just fine in Excel 2007. The odd thing is that I cannot open them in Excel 2003, on the same machine, without opening the file first in 2007 and going and saving… >>> More
Creating Excel or Excel compatible Spreadsheets on the server side in C#

as seen on Stack Overflow - Search for 'Stack Overflow'
I'd like to make server-side excel compatible spreadsheets that maybe use OpenXML or a structured data format. I've used Office Interop before to generate Excel spreadsheets, but those apps run on a PC that has office installed. For this web project I'm building, the server doesn't have office installed… >>> More
vba access values from excel workbook from another excel workbook

as seen on Stack Overflow - Search for 'Stack Overflow'
Any ideas why this code will not work? Workbooks.Open Filename:="C:\a.xls" Workbooks("a.xls").Activate Worksheets("b").Select Dim OutputArray(10, 10) OutputArray(1, 1) = Workbooks("c.xls").Worksheets("worksheet_otherthan_default").Select.Range(A1).Value I'm trying to run a macro in one file (unnamed… >>> More
Excel workbook event order and usage when closing Excel

as seen on Stack Overflow - Search for 'Stack Overflow'
Given the following workbook events: BeforeClose BeforeSave Please tell me: - The firing order in the case of multiple workbooks alreay opened (wb1, wb2 and wb3 are opened in this order) and the user closes Excel. You can assume all 3 needs saving. - What happen if user cancels one of the saving… >>> More

Related posts about udf

Format UDF hard drive as NTFS in Windows 7

as seen on Super User - Search for 'Super User'
I have a MyBook hard drive that someone gave to me to format to NTFS. Right now it's UDF and windows treats it like a CD-ROM. I can't seem to figure out how to format it to be NTFS. >>> More
How to create a UDF that takes a query string and returns the query's resultset

as seen on Stack Overflow - Search for 'Stack Overflow'
I want to create a stored procedure that takes a simple SELECT statement and return the resultset as a CSV string. So the basic idea is get the sql statement from user input, run it using EXEC(@stmt) and convert the resultset to text using cursors. However, as SQLServer doesn't allow: select * from… >>> More
Can and should UDF be used as a hard drive format?

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
Several time recently I've seen UDF suggested as the solution to a cross platform format for a drive used on Linux, Mac OS X and Windows XP and above. I've searched here and not found the same suggestion (most are suggesting ntfs-3g which seems to cost money and isn't preinstalled on a Mac). So… >>> More
SQLRally and SQLRally - Session material

as seen on SQL Blog - Search for 'SQL Blog'
I had a great week last week. First at SQLRally Nordic , in Stockholm, where I presented a session on how improvements to the OVER clause can help you simplify queries in SQL Server 2012 enormously. And then I continued straight on into SQLRally Amsterdam , where I delivered a session on the performance… >>> More
T-SQL User-Defined Functions: the good, the bad, and the ugly (part 1)

as seen on SQL Blog - Search for 'SQL Blog'
So you thought that encapsulating code in user-defined functions for easy reuse is a good idea? Think again! SQL Server supports three types of user-defined functions. Only one of them qualifies as good. The other two – well, the title says it all, doesn’t it? The bad: scalar functions A scalar user-defined… >>> More