<p class="MsoNormal">I’m involved in the development of a reporting application
for analyzing medical claims data.<span style="mso-spacerun:yes"> </span>Users
of our application often want to run reports that generate very high
cardinality queries.<span style="mso-spacerun:yes"> </span>This could mean
queries with very large dimensions (e.g. “Patients”, which could have millions
of members) or deeply crossjoined axes (e.g. 15 nested dimensions) or a
combination of both.<span style="mso-spacerun:yes"> </span>Since they are
executed NON EMPTY and usually are tightly constrained, the final results often
involve only a few dozen tuples.<span style="mso-spacerun:yes"> </span>While
Mondrian does have some facilities to help push NON EMPTY into native
evaluation, we have run into a number of limitations that have prevented us
from taking advantage of the feature.<span style="mso-spacerun:yes"> </span>In
particular it fails if the CrossJoin contains complex expressions or calculated
members (which occur in nearly all of our queries).</p>
<p class="MsoNormal">The existing native code will look for certain features
within the set it is evaluating. For example, it will look for
<Level>.members, or <Member>.children within a crossjoin arg.
If it finds a construct that it can’t recognize, or if any number of conditions
fail to hold, then it will abort native evaluation. If everything goes
correctly, then it will use SqlTupleReader to load the set of tuples with non
empty data using the individual members within the set as a constraint.
The key piece here is to come up with that list of individual members for the
constraint. So if I have a CrossJoin of ( Product.[Product Name].members,
{ Time.1997.Q1 } ), the native query should be constrained to Q1 1997.</p>
<p class="MsoNormal">An alternative to get the set of applicable constraints would
be to use a multi-pass approach, first transforming the query to a simpler form
and using the results of that query to determine how to natively evaluate
it.<span style="mso-spacerun:yes"> </span>So for example:</p>
<p class="MsoNormal" style="margin-left:.5in;text-indent:-.25in;mso-list:l0 level1 lfo1;
tab-stops:list .5in"><span style="mso-fareast-font-family:
Arial"><span style="mso-list:Ignore">1)<span style="font:7.0pt "Times New Roman"">
</span></span></span>Given an input set, modify any references to
<Level>.members, <Member>.children, etc. to be a set consisting of
a single calculated member with a scalar value (note that the scalar value is
not important—we just want to replace it with something that needs no further
evaluation).</p>
<p class="MsoNormal" style="margin-left:.5in;text-indent:-.25in;mso-list:l0 level1 lfo1;
tab-stops:list .5in"><span style="mso-fareast-font-family:
Arial"><span style="mso-list:Ignore">2)<span style="font:7.0pt "Times New Roman"">
</span></span></span>Evaluate the modified set. This should
produce a comparatively small set of tuples that captures the individual values
of any other dimensions in the set. Any dimension that contains only
individual members at a single level can be used as constraints, since the
<Level>.members intersects with them.</p>
<p class="MsoNormal" style="margin-left:.5in;text-indent:-.25in;mso-list:l0 level1 lfo1;
tab-stops:list .5in"><span style="mso-fareast-font-family:
Arial"><span style="mso-list:Ignore">3)<span style="font:7.0pt "Times New Roman"">
</span></span></span>Fire a native query to get the tuples that have
non empty data with the constraints identified in (2), expanding back out the
expressions that were modified in (1). (or, take an approach like
crossjoin optimizer and actually evaluate the remaining intersections).</p>
<p class="MsoNormal">I implemented a quick and dirty proof-of-concept function
which uses the above approach. So with a query like the following:</p>
<p class="MsoNormal"></p><p class="MsoNormal"></p><p class="MsoNormal"></p><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;">SELECT NativizeSet( Crossjoin( Product.[Product Name].members,<br>
{Time.[1997].LastChild, Time.[1997].LastChild.PrevMember} )) on 0<br>from sales</blockquote>
<p class="MsoNormal">(1) The function will first transform the query to</p>
<blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;">with member [Product].[substitution-Product] as '101010.0'<br> set [setProduct] as
'{[Product].[substitution-Product]}'<br>select NativizeSet(Crossjoin([setProduct],
{[Time].[1997].LastChild, [Time].[1997].LastChild.PrevMember})) ON COLUMNS<br>from [Sales]</blockquote>
<p class="MsoNormal">(2) It will then evaluate the transformed set, which
will return a list of tuples</p>
<blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"> ( product.[substitution-Product], Time.1997.Q4)<br> ( product.[substitution-Product], Time.1997.Q3)</blockquote>
<p class="MsoNormal">(3) The function can then fire a native query to
retrieve the actual [Product Name] members that intersect with Q4 and Q3.</p>
<p class="MsoNormal">This works nicely with sets that have arbitrarily complex
expressions, but with some caveats:</p><p class="MsoNormal">* the set expression must contain an expression that can
actually be modified (like <Level>.members).</p>
<p class="MsoNormal">* expressions replaced in (1) can’t be inside of functions
like filter, topcount, or head, since the expression actually needs to look at
the set of members to determine what to return.<span style="mso-spacerun:yes">
</span>I’m sure there are other cases like this where the “substituted” set is
dynamic and the transformation described above would not result in the desired
result.</p>
<p class="MsoNormal">Even with the caveats above I believe that an approach like
the above will work for most of our use cases. </p><p class="MsoNormal"></p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">Let me know if anyone has feedback.</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal"> </p>