PDA

View Full Version : [Mondrian] MEMBER_ORDINAL: fubar when combined with nativefiltering?



John V. Sichi
02-15-2007, 02:14 AM
Is there a good reason Mondrian attempts to map the ordinal expression
into an absolute numeric ordinal? Because from what I can tell, this
doesn't work unless you manage to fully prime the level cache in order
to get the ordinals set correctly. If you do any native filtering,
leading the cache to get populated incrementally, the assignment of
lastOrdinal within SqlMemberSource is wrong. Wouldn't it make more
sense to preserve the expression as a non-numeric ordinal key which
could be compared without knowing the full extent?

I realize that this could take more space in the cache depending on the
datatype, but if the dimension is small, that's not so bad, and if the
dimension is large, it's much better than being forced to pin the whole
thing just to be able to assign absolute ordinals. MOLAP dimension
processing solves this, but...

To see the problem, run this:

with
member [Measures].[o] as
[Customers].[Name].currentmember.Properties("MEMBER_ORDINAL")
set necj as nonemptycrossjoin(
[Store].[Store State].members, [Customers].[Name].members
)
select tail(necj,5) on rows,
{[Measures].[o]} on columns
from [Sales];

and then this:

with member [Measures].[o] as
[Customers].[Name].currentmember.Properties("MEMBER_ORDINAL")
select tail([Customers].[Name].members, 5)
on rows,
{[Measures].[o]} on columns
from [Sales];

The first one partially fills the cache (assigning incorrect absolute
ordinals, although they are correctly ordered within the scope of that
query), and then the second one shows the mismatch across queries.

Of course, if you set both mondrian.native.crossjoin.enable=false and
mondrian.native.nonempty.enable=false, you get correct results for both.

JVS
_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

Julian Hyde
02-21-2007, 03:52 AM
John,

This change sounds like an improvement. If you can check in the change in
the next day or so (disabled by default) I will run it through the full
regression suite. If nothing seems broken, I will enable this change for
RC1.

Please update schema.html to match the new semantics. I will back out
schema.html if I decide not to include the new behavior in RC1.

Julian

> -----Original Message-----
> From: mondrian-bounces (AT) pentaho (DOT) org
> [mailto:mondrian-bounces (AT) pentaho (DOT) org] On Behalf Of John V. Sichi
> Sent: Tuesday, February 20, 2007 5:38 PM
> To: Mondrian developer mailing list
> Subject: Re: [Mondrian] MEMBER_ORDINAL: fubar when
> combinedwith nativefiltering?
>
> I'm going to check in a limited fix for this. Julian, since this is
> potentially destabilizing, I'm going to check it in with a property
> disabling it by default; it will be necessary to explicitly
> enable the
> property to get the fix. I will leave it up to you about when it is
> safe to permanently enable it, and what to do about whether
> to fix the
> broken absolute ordinals; the link below says
> MDSCHEMA_MEMBERS.MEMBER_ORDINAL is deprecated in MSAS 2005 and always
> returns 0. There's a bunch of code in RolapMember devoted to setting
> them for XML/A support.
>
> http://msdn2.microsoft.com/en-us/library/ms143235.aspx
>
> Also, if you would prefer that I instead check into
> //open/lu/mondrian
> and integrate it up to //open/mondrian only after the
> Mondrian release,
> let me know.
>
> Here's what I'm planning to do:
>
> 1. Add a new attribute orderKey (of type Comparable) in RolapMember.
>
> 2. Change SqlMemberSource to populate orderKey (when new property
> mondrian.rolap.compareSiblingsByOrdinalKey is true; default false).
>
> 3. Change FunUtil.compareSiblingMembers to compare based on orderKey
> when not null, otherwise revert to current behavior (first on buggy
> getOrdinal(), then on member.compareTo).
>
> 4. The comments on RolapMember.compareTo indicate that I
> shouldn't need
> to make any changes there.
>
> Where does the potential destabilization come from?
>
> Previously (for non-buggy cases) the ordering was entirely
> determined by
> the implementation of ORDER BY in the underlying DBMS, since this is
> what the assignment of absolute ordinals was based on. Now
> it will be
> determined by retrieving the column value and (in some cases) calling
> compareTo on the returned value. I say "in some cases"
> because it looks
> like member.children is relying on the fetch order of the
> child array,
> whereas level.members is relying on FunUtil.hierarchize. So,
> existing
> behavior could change, possibly becoming inconsistent or
> failing due to
> return of non-Comparable values, or nulls, or untrimmed strings, or...
>
> JVS
>
> John V. Sichi wrote:
> > Julian Hyde wrote:
> >> When writing the schema guide, I should have clearly distinguished
> >> absolute
> >> ordinal (an integer which identifies the position of a
> member within its
> >> dimension) from an ordinal property which may be any datatype and
> >> identifies
> >> a member's order within its parent. If I had done that, I
> would have
> >> realised that we need two attributes in the schema:
> ordinal (an integer,
> >> unique for members of all levels in a hierarchy) and orderKey (any
> >> datatype).
> >>
> >> Does that scheme seem to make sense? If so, please log a bug.
> >
> > Yes. Logged as
> >
> >
> https://sourceforge.net/tracker/?func=detail&atid=414613&aid=1
> 660383&group_id=35302
> >
> >
> > JVS
> >
>
> _______________________________________________
> Mondrian mailing list
> Mondrian (AT) pentaho (DOT) org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

John V. Sichi
02-21-2007, 06:40 AM
Julian Hyde wrote:
> John,
>
> This change sounds like an improvement. If you can check in the change in
> the next day or so (disabled by default) I will run it through the full
> regression suite. If nothing seems broken, I will enable this change for
> RC1.

OK, I'm hoping to be able to check in soon. I ran into one snag so far
testing with Derby: the Customers table has fullname used for both Name
and Ordinal, and if I reference it twice in the select list, Derby
complains. And it complains in a different way if I try generating
aliases. None of the complaints are reasonable by SQL:2003 standards, so...

I downloaded the latest Derby (10.2.2.0) and the complaints are gone.
Assuming it passes all tests, is it OK to upgrade the checked-in version
(10.1)? Browsing the release notes for the last few revs, it looks like
they've added enhancements for GROUP BY expression and some ORDER BY
fixes. (The GROUP BY expression enhancement should allow FoodMart.xml
to be changed to use an expression for Name, but that can wait.)

> Please update schema.html to match the new semantics. I will back out

Just to clarify, I haven't actually added a new orderKey attribute to
the schema; I'm just changing what we do with the values produced by the
existing ordinalColumn/OrdinalExpression attribute. So what I will
document is that (when the new property is enabled):

- the ordinal column/expression can be of any datatype, but the JDBC
driver must return non-null objects which implement Comparable

- the values need only be unique with respect to the parent

Note that the current Mondrian.xml schema spec says this about
ordinalColumn:

"The name of the column which holds member ordinals. Only applicable for
the last level in a hierarchy. If this column is not specified, the
hierarchy cannot be implemented in ROLAP mode."

The last two sentences are both false, right?

JVS
_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

John V. Sichi
02-21-2007, 04:30 PM
John V. Sichi wrote:
> I downloaded the latest Derby (10.2.2.0) and the complaints are gone.
> Assuming it passes all tests, is it OK to upgrade the checked-in version
> (10.1)? Browsing the release notes for the last few revs, it looks like
> they've added enhancements for GROUP BY expression and some ORDER BY
> fixes. (The GROUP BY expression enhancement should allow FoodMart.xml
> to be changed to use an expression for Name, but that can wait.)

Update on this: the new Derby passes tests, with the following impact:

1) It appears to be a little bit more memory-hungry, so it runs out of
memory for some tests unless I add -Xmx128m to junit.jvmargs; I can do
that permanently, which should also allow tests to run with Richard's
memory limit changes enabled.

2) The nagging problem with
VirtualCubeTest.testCalculatedMeasureAcrossCubes now shows up for every
full run, even on my laptop. Julian, I think you said you know what's
going on with this--that it incorrectly computes the number of passes
required in terms of the base cubes instead of the virtual cube, so the
calculated measure will come out null if the cache wasn't primed?

JVS
_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian

John V. Sichi
02-21-2007, 10:18 PM
Derby upgrade is checked in as eigenchange 8762; ordering fix is checked
in as eigenchange 8763. I ran through tests on both Derby and MySQL,
once with the new property set to false, and again with the new property
set to true.

Note that ordinal column will now ALWAYS be fetched, even when it is
ignored because the property is set to false. This allowed me to
simplify the UNION plus ORDER BY logic considerably.

For the Derby upgrade, I also had to upgrade
demo/derby/derby-foodmart.zip, otherwise the new version of Derby would
try to migrate the old catalog on first run, causing one test to fail
the first time (after that it would be OK after the migration got
saved). I did the migration myself and then updated the .zip with the
migrated catalog.

JVS

John V. Sichi wrote:
> John V. Sichi wrote:
>> I downloaded the latest Derby (10.2.2.0) and the complaints are gone.
>> Assuming it passes all tests, is it OK to upgrade the checked-in
>> version (10.1)? Browsing the release notes for the last few revs, it
>> looks like they've added enhancements for GROUP BY expression and some
>> ORDER BY fixes. (The GROUP BY expression enhancement should allow
>> FoodMart.xml to be changed to use an expression for Name, but that can
>> wait.)
>
> Update on this: the new Derby passes tests, with the following impact:
>
> 1) It appears to be a little bit more memory-hungry, so it runs out of
> memory for some tests unless I add -Xmx128m to junit.jvmargs; I can do
> that permanently, which should also allow tests to run with Richard's
> memory limit changes enabled.
>
> 2) The nagging problem with
> VirtualCubeTest.testCalculatedMeasureAcrossCubes now shows up for every
> full run, even on my laptop. Julian, I think you said you know what's
> going on with this--that it incorrectly computes the number of passes
> required in terms of the base cubes instead of the virtual cube, so the
> calculated measure will come out null if the cache wasn't primed?
>
> JVS
>

_______________________________________________
Mondrian mailing list
Mondrian (AT) pentaho (DOT) org
http://lists.pentaho.org/mailman/listinfo/mondrian