[Mondrian] Native Member Ranges

Luc Boudreau lucboudreau at gmail.com
Tue Mar 25 10:16:09 EDT 2014


Hello fellow mondrian enthusiasts,

So in my spare time, I've been playing with the idea of pushing ranges of
members into SQL predicates.

After some experiments, I've quickly realized that it isn't very easy to do
in most cases. I say *most* because there is one case where it becomes
interesting; time.

Time has this one particularity. However you structure your warehouse, we
can safely assume that time is structured as flowing forward.

With this assumption, I've created a new tuple constraint called the
LevelDateRangeConstraint, which can turn the outer bounds of a range of
time members into SQL predicates.

There are a few caveats. It can only work if the level is based off a field
of type Date or Timestamp. Anything else cannot be reliably nativized. ie.
If I say ( [Time].[April] : [Time].[December] ), I get wrong results, since
I'll get members by alphabetical ordering.

When we get to nativizing this into a range of members, I need to use the
key and the order key, but I can't get to these unless I load all the
members first, thus defeating the purpose of the optimization.

My prototype works well when a range is expressed as member keys, like so:
( [Time].&[2014:01:01] : [Time].&[2014:01-02] )

If I say ( [Time].[2014:01:01] : [Time].[2014:01-02] ), I'm again
expressing the range as names. My code can work around this problem, but
only as long as the same SQL expression is used for the name and for the
key (same column or expression).

So far I didn't get the time to write tests when the range is part of a
calculated member or the slicer, but I intend to in the near future. In the
meanwhile, I submit to you for review the branch called "smr", originally
picked because of the SmartMemberReader, where I started off.

https://github.com/pentaho/mondrian/compare/smr

I'll be working on this some more in the following weeks, but so far it's
looking good. The optimization really does help overall in a couple areas,
not just with ranges of dates. MDX names can be turned into a tuple
constraint if the name expression is the same as the key expression. That's
a great help.

Anyhow. stay tuned for more later!

Luc
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20140325/a15efc75/attachment.html 


More information about the Mondrian mailing list