[Mondrian] RE: Parameterization of MDX queries

Julian Hyde jhyde at pentaho.com
Tue Jul 21 18:44:06 EDT 2009



> Thomas Morgner wrote:
> 
> Kurtis and I currently run though the new reporting
> datasources and wonder how MDX query parametrization
> works.
> 
>  From digging into the Mondrian and Olap4J code, I
> know that Mondrian supports prepared-statement-style
> parameters. (I really hate the string-concatenation
> way the platform uses in all examples. This smells
> too strongly like SQL-Injection to me.)

I like prepared-statement-style parameters too. MDX-injection is less a
concern than SQL injection (because MDX has good access control) but still a
worry; and string-concatenation parameters are less useful because they have
less metadata. The MDX standard didn't have them until recently, so I added
parameters as the solution.

Microsoft's MDX now has LIMITED support for parameters. You can parameterize
only scalar values, whereas Mondrian can also have member-valued parameters.
See http://msdn.microsoft.com/en-us/library/ms145518.aspx. 

> I also found the "Parameter" function and have the
> feeling that this is how parameters are specified.

Correct. I propose that we use Mondrian's parameters (using the 'Parameter'
and 'ParamRef' functions) rather than Microsoft-style parameters (using '@')
for now. Usually I bow to Microsoft, but Mondrian's parameters are superior.

I know it will create interop problems if someone wants to run the same MDX
via olap4j and talk to both Mondrian and SSAS. We can fix that later.

The olap4j API supports parameters as you would expect. Once you've prepared
a statement, you can call PreparedOlapStatement.getParameterMetaData. This
returns a OlapParameterMetaData, which is a subclass of the JDBC
ParameterMetaData object.

http://www.olap4j.org/api/org/olap4j/OlapParameterMetaData.html

> 
> Now here's the real question:
> 
> (1) Is this the right spot, or is there some other
> magic. The function says: Internal use only, so I'm
> cautious.

Yes, it's the right spot.

> (2) Where can we use it? Are there restrictions like
> on JDBC-Prepared-statements where placeholders can
> appear?

I don't think there are restrictions. You can use a parameter anywhere that
the value (scalar or member) would be valid.

> (3) Is there syntax documentation out there? Microsoft's
> MDX documentation is not very helpful on that one.

As I already said, we're not going with Microsoft's standard on this one.

There's a very brief mention of parameters in
http://mondrian.pentaho.org/documentation/mdx.php. Very terse, and it still
manages to get it wrong; the function is called Parameter, not Param. The
functions are actually Parameter and ParamRef.

A call to Parameter defines a parameter's name, data type and default value.
A parameter always has a default value (it makes life simpler for the UI).
If the parameter is a member, then the data type is the name of a hierarchy
that the parameter must belong to. This metadata potentially allows a user
interface to generate a list of values for the parameter. (CDF folks, take
note!)

By the way, the purpose of ParamRef is if you want to define a parameter
once and use it several times in the same query. You could use Parameter
several times but you'd have to make sure the definitions (data type,
default value) are identical otherwise mondrian would complain.

> (4) Do you have examples of this functionality or
> can you point me to testcase code that I can use as
> example for how to parametrize queries?

Your best documentation is in the testcase:
http://perforce.eigenbase.org:8080/open/mondrian/testsrc/main/mondrian/test/
ParameterTest.java

> Thanks for any help and insight you can offer.

You're welcome.

Cc: mondrian developers.

Julian





More information about the Mondrian mailing list