PDA

View Full Version : [Mondrian] RE: "All" Member value is not accurate



Julian Hyde
05-22-2007, 04:33 PM
Jared Cornelius wrote:


I have been working with a customer trying to figure out why his 'All
Products' member shows a different value than if you sum the children
data manually. What I found (I believe) is that this happens when there
is a record that has a NULL value that is the foreign key to a
dimension. If the field you are trying to aggregate is populated, the
aggregation on that field still takes place even though the key field is
NULL.


As an example, say I'm looking at the sales-qty for product X, and I put
a dimension in plat that only has tee time products in it. The total is
still counting up all the other sale-qty fields in other records that
have product X product ID of NULL because they are not product X.



I found the following forum thread that looks similar to this issue -
http://forums.pentaho.org/showthread.php?t=53533 - but I haven't been
able to track down a work-around if there is one.



I guess my question to you is, is this expected behavior and/or is there
a workaround? Thanks in advance for any help!

Jared,

This is expected behavior. Mondrian doesn't work too well if foreign
keys don't hold.

The remedy would be to force mondrian to always join, even when it
doesn't need to. but that would hurt performance in a big way. To
mitigate that, some people have even suggested adding a property for
that, or perhaps an attribute of the dimension. But anyway.

The workaround is to make sure that foreign keys are not null and point
to valid records in the dimension table.

By the way, the converse - having records in the dimension tables with
no corresponding facts - is perfectly fine.

Julian

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

John V. Sichi
05-22-2007, 04:40 PM
Julian Hyde wrote:
> The workaround is to make sure that foreign keys are not null and point
> to valid records in the dimension table.

Beyond workaround, this is really an ETL best practice; search for "zero
key" in this article:

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part5/c1961.mspx?mfr=true

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