<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.18882"></HEAD>
<BODY>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Lucida Sans">This might not be it, but here's something to check
anyway.</FONT></SPAN></DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Lucida Sans"></FONT></SPAN> </DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Lucida Sans">Make sure that your caption, name etc. are functionally
dependent on the level key. For example, consider the following fictitious
dimension table for a Product dimension with levels Product and
Manufacturer:</FONT></SPAN></DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Lucida Sans"></FONT></SPAN> </DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Courier New">MFR_ID MFR_NAME PROD_ID PROD_NAME</FONT></SPAN></DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Courier New">====== ======== ======= =========</FONT></SPAN></DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Courier New">1 Sony 100 Walkman</FONT></SPAN></DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Courier New">1 Hitachi 101
Playstation</FONT></SPAN></DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Courier New">2 Toyota
102 Prius</FONT></SPAN></DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Courier New">2 Toyota
103 Camry</FONT></SPAN></DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Courier New"></FONT></SPAN> </DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Lucida Sans">Mondrian assumes that MFR_NAME is functionally dependent on
MFR_ID, but it isn't for MFR_ID = 1. Depending on whether the query selects
MFR_NAME, you'll either get 2 or 3 distinct manufacturers.</FONT></SPAN></DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Lucida Sans"></FONT></SPAN> </DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Lucida Sans">This applies to attributes of levels (name, caption, ordinal,
and parent if it is a parent-child level) and any member properties of that
level.</FONT></SPAN></DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Lucida Sans"></FONT></SPAN> </DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Lucida Sans">You might think this is lame, but for efficiency, Mondrian
gets all the properties of a member using 'SELECT DISTINCT' (or SELECT ... GROUP
BY, same thing). Basically, Mondrian delivers good performance by trusting that
you have done your job properly at ETL time.</FONT></SPAN></DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Lucida Sans"></FONT></SPAN> </DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Lucida Sans">By the way, there is a similar trade-off where Mondrian
assumes that every foreign key from the fact table (or indeed between tables in
a snowflake dimension) has referential integrity.</FONT></SPAN></DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Lucida Sans"></FONT></SPAN> </DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Lucida Sans">Schema workbench ought to have a validate mode that checks
this stuff. This is the kind of thing we might put into a future release of
Enterprise Edition.</FONT></SPAN></DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Lucida Sans"></FONT></SPAN> </DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Lucida Sans">Julian</FONT></SPAN></DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Lucida Sans"></FONT></SPAN> </DIV>
<DIV><SPAN class=355544906-12032010><FONT color=#000080 size=2
face="Lucida Sans"></FONT></SPAN> </DIV><FONT color=#000080 size=2
face="Lucida Sans"></FONT><BR>
<BLOCKQUOTE
style="BORDER-LEFT: #000080 2px solid; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px">
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> mondrian-bounces@pentaho.org
[mailto:mondrian-bounces@pentaho.org] <B>On Behalf Of </B>Ati
Rosselet<BR><B>Sent:</B> Thursday, March 11, 2010 7:27 AM<BR><B>To:</B>
tom@wamonline.org.uk; Mondrian developer mailing list<BR><B>Subject:</B> Re:
Re: [Mondrian] Spot the mistake<BR></FONT><BR></DIV>
<DIV></DIV>try posting your cube definition as well.. that might help someone
spot a problem...<BR><BR>
<DIV class=gmail_quote>On Thu, Mar 11, 2010 at 4:19 PM, <SPAN dir=ltr><<A
href="mailto:tom@wamonline.org.uk">tom@wamonline.org.uk</A>></SPAN>
wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: rgb(204,204,204) 1px solid; MARGIN: 0pt 0pt 0pt 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>Ok here's my analysis<BR><BR>If I remove the nameColumn
definition from that row as I said earlier jpivot then returns the results I
would expect.<BR>If I then add CONTENT_TITLE to captionColumn then it
returns the 3 records in the resultset.<BR><BR>Which is fine in JPivot.....
but PRD doesn't support captionColumn.<BR><BR>This must also be surely quite
a serious flaw as people's resultset's could be correct for 90% of queries
and then just randomly drop stuff
silently?<BR><BR>Cheers<BR><BR>Tom<BR><BR><SPAN style="FONT-WEIGHT: bold">On
Thu 11/03/10 2:26 PM , <A href="mailto:tom@wamonline.org.uk"
target=_blank>tom@wamonline.org.uk</A> sent:<BR></SPAN>
<DIV>
<DIV></DIV>
<DIV class=h5>
<BLOCKQUOTE
style="BORDER-LEFT: rgb(245,245,245) 2px solid; PADDING-LEFT: 5px; PADDING-RIGHT: 0px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px"><BR>Try
it to the list this time: <BR><BR>Changing uniquemembers has no affect
<BR><BR>:)<BR><BR>Tom<BR><BR><BR><SPAN style="FONT-WEIGHT: bold">On Thu
11/03/10 1:24 PM , Ati Rosselet <A href="mailto:ati.rosselet@gmail.com"
target=_blank>ati.rosselet@gmail.com</A> sent:<BR></SPAN>
<BLOCKQUOTE
style="BORDER-LEFT: rgb(245,245,245) 2px solid; PADDING-LEFT: 5px; PADDING-RIGHT: 0px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px">hmm...
maybe you have unique set to true for the name or caption
columns?<BR><BR>
<DIV class=gmail_quote>On Thu, Mar 11, 2010 at 1:41 PM, <SPAN
dir=ltr><<A>tom@wamonline.org.uk</A>></SPAN> wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: rgb(204,204,204) 1px solid; MARGIN: 0pt 0pt 0pt 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote><BR>Ok then,<BR><BR>If I remove the nameColumn and
captionColumn from the Content Dimension Track Name level, so I'm left
with bare content id's the records reappear.
<BR><BR>Bug?<BR><BR><BR><SPAN style="FONT-WEIGHT: bold">On Thu
11/03/10 12:29 PM , <A href="mailto:tom@wamonline.org.uk"
target=_blank>tom@wamonline.org.uk</A> sent:<BR></SPAN>
<DIV>
<DIV></DIV>
<DIV>
<BLOCKQUOTE
style="BORDER-LEFT: rgb(245,245,245) 2px solid; PADDING-LEFT: 5px; PADDING-RIGHT: 0px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px">Alright
guys I'm having some weird issues with my scd and
mondrian:<BR><BR>Basically in our dimension we have 3 records with
the same artist name, same track name, but differing
content_id's.<BR><BR>If I do this mdx query:<BR><BR>select
{[Measures].[Content Purchases]} ON COLUMNS,
<DIV> [Content].[All Content].[Theme].Children ON
ROWS<BR>from [Purchases]</DIV><BR>It returns 3 different with
records with the various sales against them this is the correct
result, as there are 3 different dimension entries for the
Ghostbusters Theme tune.<BR><BR>But if for example I run:<BR>
<DIV><BR>select NON EMPTY {[Measures].[Content Purchases]} ON
COLUMNS,<BR> NON EMPTY Hierarchize(Union(Union({[Content].[All
Content]}, [Content].[All Content].Children), [Content].[All
Content].[Theme].Children)) ON ROWS<BR>from [Purchases]<BR><BR>Which
is mdx generated by jpivot, it only returns the first member from
the content dimension, even though when it runs the sql queries to
retrieve members the other ghostbuster records are returned in the
initial member search, if that makes sense.<BR> <BR>This is the
SQL generated for the member lookup:<BR><BR>select
`D_CONTENT`.`CONTENT_ID` as `c0`, `D_CONTENT`.`CONTENT_TITLE` as
`c1`, `D_CONTENT`.`CONTENT_TITLE` as `c2` from `D_CONTENT` as
`D_CONTENT`, `F_CONTENT_PURCHASES` as `F_CONTENT_PURCHASES` where
`F_CONTENT_PURCHASES`.`CONTENT_TK` = `D_CONTENT`.`CONTENT_TK` and
`D_CONTENT`.`ARTIST_NAME` = 'Theme' group by
`D_CONTENT`.`CONTENT_ID`, `D_CONTENT`.`CONTENT_TITLE` order by
ISNULL(`D_CONTENT`.`CONTENT_ID`), `D_CONTENT`.`CONTENT_ID`
ASC<BR><BR>This is the SQL generated for looking up the records in
the fact:<BR><BR>select `D_CONTENT`.`ARTIST_NAME` as `c0`,
`D_CONTENT`.`CONTENT_ID` as `c1`,
count(`F_CONTENT_PURCHASES`.`TRANSACTION_ID`) as `m0` from
`D_CONTENT` as `D_CONTENT`, `F_CONTENT_PURCHASES` as
`F_CONTENT_PURCHASES` where `F_CONTENT_PURCHASES`.`CONTENT_TK` =
`D_CONTENT`.`CONTENT_TK` and `D_CONTENT`.`ARTIST_NAME` = 'Theme' and
`D_CONTENT`.`CONTENT_ID` in ('318', '324', '325', '326', '336',
'350', '354', '356', '373', '374', '381', '393', '395', '397',
'403', '406', '410', '414', '424', '427', '434', '436', '442',
'454', '455', '456', '462', '466', '475', '476', '480', '482',
'487', '495', '503', '510', '532', '540', '541', '3013', '3014',
'3015', '3025', '3026', '3027', '3028', '3029', '3030', '3031',
'3032', '3033', '3034', '3035', '3036', '3037', '3038', '3039',
'3040', '3041', '3042', '3043', '3044', '3045', '3046', '3047',
'3048', '3049', '3050', '3051', '3052', '3054', '3055', '3056',
'3057', '3058', '3059', '3060', '3061', '3062', '3063', '3064',
'3065', '3066', '3067', '3068', '3070', '3071', '3073', '3074',
'3075', '3076', '3077', '3078', '3079', '3080', '3081', '3082',
'3083', '3084', '3085', '3086', '3087', '3088', '3089', '3090',
'3092', '3096', '3116', '3139', '3140', '3141', '3142', '3144',
'3145', '3147', '3148', '3156', '3157', '3176', '3427', '3445',
'3538', '3664', '5780', '5781', '5782', '5819', '5820', '5822',
'5823') group by `D_CONTENT`.`ARTIST_NAME`,
`D_CONTENT`.`CONTENT_ID`]<BR><BR>The content id for the missing
records obviously isn't included in the Where.. IN().<BR><BR>What
procedural error have I
missed?<BR><BR>Cheers<BR><BR>Tom<BR><BR></DIV><BR></BLOCKQUOTE><BR></DIV></DIV><BR>_______________________________________________<BR>Mondrian
mailing list<BR><A>Mondrian@pentaho.org</A><BR><A
href="http://lists.pentaho.org/mailman/listinfo/mondrian"
target=_blank>http://lists.pentaho.org/mailman/listinfo/mondrian</A><BR><BR></BLOCKQUOTE></DIV><BR></BLOCKQUOTE><BR></BLOCKQUOTE><BR></DIV></DIV><BR>_______________________________________________<BR>Mondrian
mailing list<BR><A
href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</A><BR><A
href="http://lists.pentaho.org/mailman/listinfo/mondrian"
target=_blank>http://lists.pentaho.org/mailman/listinfo/mondrian</A><BR><BR></BLOCKQUOTE></DIV><BR></BLOCKQUOTE></BODY></HTML>