<HTML>
<style> BODY { font-family:Arial, Helvetica, sans-serif;font-size:12px; }</style>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 class="de1"> [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 class="de1"><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></HTML>