[Mondrian] Native Member Ranges

Will Gorman wgorman at pentaho.com
Fri Mar 28 10:23:45 EDT 2014

Hi Luc,

This is a great spare time project!  :-)  Do you think this type of capability may apply to all dimensions that have an ordinal where the Between operator applies, in the same manner that dates use it?  By the way, here’s a JIRA case related to the topic:


In your example “( [Time].[April] : [Time].[December] )”,  you mention you can’t get to the order key without loading all the members, can’t you load individual members with their metadata vs. loading the entire dimension?  For instance, name=”April”, ordinal=4, name=”December”,ordinal=12?  Or are you trying to avoid loading the member all together?



From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On Behalf Of Luc Boudreau
Sent: Tuesday, March 25, 2014 7:16 AM
To: Mondrian developer mailing list
Subject: [Mondrian] Native Member Ranges

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.


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!


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20140328/cc139a30/attachment-0001.html 

More information about the Mondrian mailing list