[Mondrian] Spot the mistake

Ati Rosselet ati.rosselet at gmail.com
Thu Mar 11 08:24:44 EST 2010


hmm... maybe you have unique set to true for the name or caption columns?

On Thu, Mar 11, 2010 at 1:41 PM, <tom at wamonline.org.uk> 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 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
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100311/49acb1a2/attachment.html 


More information about the Mondrian mailing list