[Mondrian] RE: VBA and Excel functions implemented in Java, for use inmondrian OLAP engine

Julian Hyde jhyde at pentaho.org
Thu Jan 3 12:40:16 EST 2008


Funny, I've been doing something kind of similar. So of course I think it's
a great idea. :)

I implemented a lot of the VBA functions as static methods in a class called
Vba, then use reflection to call them.

I thought about annotations, but decided not to go there because they
probably don't work on JDK 1.4. However if this is a problem in JDK 1.4, I
guess we could solve this by generating an auxilliary XML file from the
annotations, and JDK 1.4 could read that at runtime; later JDKs would read
the annotations.

I definitely want to use reflection for the VBA functions, and maybe for
some of the existing functions (e.g. || and +), but I'm not sure reflection
is appropriate for all functions.

For one thing, there's an efficiency penalty. I noticed that even calling a
simple function like replace("foo","bar","baz",1+2) you need to do some work
on the arguments because the + operator might return its result as a Double.
My guess is that this would be a bit less efficient than the current
Calc-based system. But we'd need to do some performance testing to really
know.

Second, functions need differing amounts of context. There are 3 kinds:

(a) The VBA functions are simple; they need no context.
(b) Dimensional functions such as Filter need the full dimensional context
passed in as the Evaluator, and they need to pass this context on to
functions they call. And in between,
(c) DateTime functions need to know the connection's timezone; formatting
functions need the connection's locale; and random number generators need to
know the current seed.

I guess we could do type-(c) functions as non-static regular java functions,
or pass in an extra context parameter.

Lastly, reflective functions can't do lazy evaluation; if we implemented Iif
reflectively, it would always evaluate all 3 arguments.

Let me take a look at the files you sent and see if I can merge it into what
I've been working on.

Julian

> -----Original Message-----
> From: Will Gorman [mailto:wgorman at pentaho.org] 
> Sent: Thursday, January 03, 2008 6:53 AM
> To: jhyde at pentaho.org
> Subject: RE: VBA and Excel functions implemented in Java, for 
> use inmondrian OLAP engine
> 
> Hi Julian,
> 
> I've started taking a look at making function declarations more
> efficient.  How do you feel about using custom annotations?  
> Here is an
> example:
> 
>     @Description("Concatenates two strings.")
>     @FunctionName("||")
>     @SyntaxDef(Syntax.Infix)
>     public static String concat(String s0, String s1) {
>         return s0 + s1;
>     }
> 
> The FunctionName and SyntaxDef have default behaviors if not
> specified.  
> 
> My second question has to do with incorporating these functions into
> Mondrian's framework.  I wrote a quick helper class that uses 
> reflection
> to make the appropriate calls into the defined static 
> functions.  I was
> wondering if you would rather see a code generation tool vs. dynamic
> reflective calls to the static methods.
> 
> Attached is the source code if you are interested.  Thanks in advance
> for your feedback!
> 
> Will
> 
> 
> 
> 
> On Mon, 2007-12-31 at 11:26 -0800, Julian Hyde wrote:
> > Thomas,
> >  
> > I sent this message to open office a couple of weeks ago, didn't get
> > any reply, and don't even see it in the open office 
> archives, so maybe
> > some spam filter/moderator squished it. 
> >  
> > I'm guessing you have the identical problem - being able to use VBA
> > functions in formulas. If so, how do you solve it? I'd like to reuse
> > whatever you have.
> >  
> > Matt,
> >  
> > Do you have a similar problem? I'm guessing that you need a rich set
> > of functions, but it's not quite as important that they strictly
> > adhere to the VBA (ahem) standard.
> >  
> > Julian
> >  
> >  
> >  
> > 
> >         
> >         
> ______________________________________________________________
> >         From: Julian Hyde [mailto:jhyde at pentaho.org] 
> >         Sent: Monday, December 17, 2007 1:46 PM
> >         To: 'dev at openoffice.org'
> >         Cc: William Gorman; Thomas Morgner
> >         Subject: VBA and Excel functions implemented in 
> Java, for use
> >         in mondrian OLAP engine
> >         
> >         
> >         
> >         Hi,
> >          
> >         I'm Julian Hyde, lead developer the Mondrian 
> open-source OLAP
> >         engine (http://mondrian.pentaho.org). I'm also a friend &
> >         colleague at Pentaho with Thomas Morgner, who developed
> >         Pentaho Reporting and helped integrate it into OpenOffice.
> >          
> >         As you may know, Mondrian implements the MDX query language,
> >         which was originally specified by Microsoft and therefore
> >         contains all of the standard VBA (Visual Basic for
> >         Applications) and Excel builtin functions as part of its
> >         expression language. We have implemented all of the
> >         MDX-specific functions, but we have never bitten the bullet
> >         and implemented all of the VBA and Excel functions.
> >          
> >         The list includes arithmetic functions (Sin, Power), string
> >         functions (Left, RTrim), date/time functions
> >         (DateDiff), financial functions (FV, DDB, IPmt), and type
> >         conversions (CInt, IsNumeric).
> >          
> >         For mondrian release 3.0, we want to fix this. We would need
> >         an implementation of the VBA and Excel functions in 
> Java, and
> >         rather than building it ourselves, I thought I'd 
> check whether
> >         it existed in OpenOffice. Can anyone tell me whether
> >         OpenOffice has implementations of these functions in Java?
> >          
> >         If this library exists and packaging/licensing 
> issues allow us
> >         to use it in mondrian, we will of course be delighted to
> >         contribute tests & bug-fixes. For more details of the
> >         functions included in MDX, see the specification,
> >         
> http://msdn2.microsoft.com/en-us/library/aa178229(SQL.80).aspx.
> >          
> >         Thanks,
> >          
> >         Julian Hyde
> >         Mondrian lead, and OLAP architect, Pentaho
> >          
> 




More information about the Mondrian mailing list