PDA

View Full Version : RE: [Mondrian] MEMBER_ORDINAL: fubar when combined withnativefiltering?



Julian Hyde
02-15-2007, 04:50 AM
No good reason. I think I've known for a few years, at the back of my mind,
that this ordinal scheme is broken.

Space may have been a consideration at one point, but it isn't valid now,
because members take up quite a lot of space.

I think it would be a mistake to try to construct MEMBER_ORDINAL out of
strings, first because it might confuse clients, and second because
concatenated string keys tend to sort incorrectly (consider "2007:2:15",
"2007:12:4").

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.

Julian



> -----Original Message-----
> From: mondrian-bounces (AT) pentaho (DOT) org
> [mailto:mondrian-bounces (AT) pentaho (DOT) org] On Behalf Of John V. Sichi
> Sent: Wednesday, February 14, 2007 10:10 PM
> To: mondrian (AT) pentaho (DOT) org
> Subject: [Mondrian] MEMBER_ORDINAL: fubar when combined with
> nativefiltering?
>
> 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
>

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

John V. Sichi
02-15-2007, 05:30 AM
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=1660383&group_id=35302

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

John V. Sichi
02-20-2007, 09:42 PM
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=1660383&group_id=35302
>
>
> JVS
>

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