<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word"><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16441" name=GENERATOR>
<STYLE>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=purple link=blue>
<DIV dir=ltr align=left><SPAN class=650322120-22052007><FONT face=Verdana
color=#000080 size=2>Jared Cornelius wrote:</FONT></SPAN></DIV><BR>
<BLOCKQUOTE dir=ltr
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000080 2px solid; MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT face=Arial
size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">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. <o:p></o:p></SPAN></FONT></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><BR>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.<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">I found the following forum thread
that looks similar to this issue - <A
href="http://forums.pentaho.org/showthread.php?t=53533">http://forums.pentaho.org/showthread.php?t=53533</A>
– but I haven’t been able to track down a work-around if there is one.
<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">I guess my question to you is, is
this expected behavior and/or is there a workaround? Thanks in advance
for any help!</SPAN></FONT></P></DIV></BLOCKQUOTE><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">
<DIV dir=ltr align=left><SPAN class=650322120-22052007><FONT face=Verdana
color=#000080 size=2>Jared,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=650322120-22052007><FONT face=Verdana
color=#000080 size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=650322120-22052007><FONT face=Verdana
color=#000080 size=2>This is expected behavior. Mondrian doesn't work too well
if foreign keys don't hold.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=650322120-22052007><FONT face=Verdana
color=#000080 size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=650322120-22052007><FONT face=Verdana
color=#000080 size=2>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. B</FONT></SPAN><SPAN
class=650322120-22052007><FONT face=Verdana color=#000080 size=2>ut
anyway.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=650322120-22052007><FONT face=Verdana
color=#000080 size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=650322120-22052007><FONT face=Verdana
color=#000080 size=2>The workaround is to make sure that foreign keys are not
null and point to valid records in the dimension table.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=650322120-22052007><FONT face=Verdana
color=#000080 size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=650322120-22052007><FONT face=Verdana
color=#000080 size=2>By the way, the converse - having records in the dimension
tables with no corresponding facts - is perfectly fine.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=650322120-22052007><FONT face=Verdana
color=#000080 size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=650322120-22052007><FONT face=Verdana
color=#000080 size=2>Julian</FONT></SPAN></SPAN></FONT></DIV></BODY></HTML>