[Mondrian] Spot the mistake

tom at wamonline.org.uk tom at wamonline.org.uk
Thu Mar 11 10:19:34 EST 2010


 Ok here's my analysis
 If I remove the nameColumn definition from that row as I said
earlier jpivot then returns the results I would expect.
 If I then add CONTENT_TITLE to captionColumn then it returns the 3
records in the resultset.
 Which is fine in JPivot..... but PRD doesn't support captionColumn.
 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?
 Cheers
 Tom
 On Thu 11/03/10  2:26 PM , tom at wamonline.org.uk sent:
 Try it to the list this time: 
 Changing uniquemembers has no affect 
 :)
 Tom
 On Thu 11/03/10  1:24 PM , Ati Rosselet ati.rosselet at gmail.com sent:
 hmm... maybe you have unique set to true for the name or caption
columns?
 On Thu, Mar 11, 2010 at 1:41 PM,   wrote:
 Ok then,
 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. 
 Bug?
 On Thu 11/03/10 12:29 PM , tom at wamonline.org.uk [2] sent:
  Alright guys I'm having some weird issues with my scd and mondrian:
 Basically in our dimension we have 3 records with the same artist
name, same track name, but differing content_id's.
 If I do this mdx query:
 select {[Measures].[Content  Purchases]} ON COLUMNS,  
[Content].[All  Content].[Theme].Children ON ROWS
 from [Purchases]
 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.
 But if for example I run:
 select NON EMPTY {[Measures].[Content Purchases]} ON COLUMNS,
   NON EMPTY Hierarchize(Union(Union({[Content].[All Content]},
[Content].[All Content].Children), [Content].[All
Content].[Theme].Children)) ON ROWS
 from [Purchases]
 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.
 This is the SQL generated for the member lookup:
 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
 This is the SQL generated for looking up the records in the fact:
 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`]
 The content id for the missing records obviously isn't included in
the Where.. IN().
 What procedural error have I missed?
 Cheers
 Tom
 _______________________________________________
 Mondrian mailing list
 http://lists.pentaho.org/mailman/listinfo/mondrian [4]


Links:
------
[2] mailto:tom at wamonline.org.uk
[4] http://lists.pentaho.org/mailman/listinfo/mondrian
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100311/25491978/attachment.html 


More information about the Mondrian mailing list